ext-mysql
Extended use of mysql2.
Install
npm i --save ext-mysql
Usage
A wrapper for mysql2 connections.
const MySQL = ; // Create a pool for connections using the default way. // Or, you can set one `MySQL.POOL = myMysql2CreatedPool;`MySQL; // You can set a logger for your connections.// Each query you request calls logger.MySQLLOGGER = consoleinfo; // Create a instance.var conn = ; // Here it calls POOL.getConnection().// Set ENCODE from process.env.ENCODEawait conn; // Just like you'd do without it, plus, you have logconst rows fields = await conn;console
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; // Insert using array, plus, log var rawResults ids = await conn; console;// [1, 2] // All good await conn;catch err // In case of error, show it. console;finally // In success or error, we end it making commit or rollback. await conn;
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;// addPerson();// ... conn;// setGoods(); // ... conn;// setGoodsAddresses(); conn; conn; // ... conn; conn;//...conn;conn;
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 = 27var rawResult = await conn;console;
Delete
// This will run two delete queries, first matching John and second, Maryconn;
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 >= 100conn
Release connection
After your use you must release your connection.
conn;
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;
Result (example):
[