README
ext-mysql
Extended use of mysql2.
Install
npm i --save ext-mysql
Usage
A wrapper for mysql2 connections.
const MySQL = require('ext-mysql');
// Create a pool for connections using the default way.
// Or, you can set one `MySQL.POOL = myMysql2CreatedPool;`
MySQL.CREATE_POOL({
host: 'localhost',
user: 'root',
database: 'test',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
// You can set a logger for your connections.
// Each query you request calls logger.
MySQL.LOGGER = console.info;
// Create a instance.
var conn = new MySQL();
// Here it calls POOL.getConnection().
// Set ENCODE from process.env.ENCODE
await conn.init();
// Just like you'd do without it, plus, you have log
const [rows, fields] = await conn.execute( 'select * from table where id = ?', [10] );
console.log( rows )
Insert
You should (but not required) begin, set success and end a transaction. This is the following way of doing that.
try
{
// Start a transaction.
await conn.beginTransaction();
// Insert using array, plus, log
var [rawResults, ids] = await conn.insert( 'table', [{ name:"John", age:27 }, { name:"Mary", age:25 }] );
console.log( ids );// [1, 2]
// All good
await conn.setTransactionSuccessful();
}
catch( err )
{
// In case of error, show it.
console.error( err );
}
finally
{
// In success or error, we end it making commit or rollback.
await conn.endTransaction();
}
Transaction
Based on the idea of SQLiteDatabase. You can nest your transaction. Calling beginTransaction
many times you want, but you have to call the same amount setTransactionSuccessful
and endTransaction
in order to commit or rollback.
conn.beginTransaction();// addPerson();
// ...
conn.beginTransaction();// setGoods();
// ...
conn.beginTransaction();// setGoodsAddresses();
conn.setTransactionSuccessful();
conn.endTransaction();
// ...
conn.setTransactionSuccessful();
conn.endTransaction();
//...
conn.setTransactionSuccessful();
conn.endTransaction();
A transaction ended without calling setTransactionSuccessful
will trigger and error and rollout all your changes.
Update
// UPDATE table SET father_id = 123 WHERE name = "John" AND age = 27
var rawResult = await conn.update( 'table', { father_id:123 }, { name:"John", age:27 } );
console.log( rawResult.affectedRows );
Delete
// This will run two delete queries, first matching John and second, Mary
conn.delete( 'table', [{name:John}, {name:Mary}] );
Custom values
You may need custom set of value for an insert or update, even a delete.
// UPDATE table SET balance = balance + 10 WHERE cost >= 100
conn.update( 'table', [{balance:["balance + ?", 10}, {cost:[">= ?", 100]] );
Release connection
After your use you must release your connection.
conn.release();
Select with array group
Select and build arrays (when you do joins).
Consider the columns_category_id
, _category_name
. The rows will be grouped to a column-array (ex: category[{id:X, name:Y}]
).
conn.selectWithArray(
sql, values,
'id', // groupBy - The column name used to find a new row (`id`)
{ category:["id", "name"] } // columns - A list of the columns to build `{ "categories":["id", "name"] }`. The first array`s item will to group it (No duplicated items).
);
Result (example):
[
{
id:1,
category:
[
{ id:1, name:"cat 1" },
{ id:2, name:"cat 2" },
{ id:3, name:"cat 3" }
],
"_category_id": 1,
"_category_name": "cat 1"
},
{
id:2,
category:[
{
id:6,
name:"cat 6"
}],
"_category_id": 6,
"_category_name": "cat 6"
}
]