mysql
Utility class to interact with MySQL
query
Run your queries by autocommitting as soon as the query runs.
Syntax
mysql.query({db,sql,[timeout,values,nestTables,pagination]})
db
db namesql
query to execute[timeout]
query timeout[values]
opzionale, valori da passare nella query[nestTables]
overlapping column names[pagination]
paginate result if script is connect to a Grid componentreturn a Promise
Example
const queryArgs = {
db: "MASTER",
sql: "select id from table where name = ?",
values: ["PIPPO"]
}
const ret = await mysql.query(queryArgs);
Different value types are escaped differently, here is how:
Numbers are left untouched
Booleans are converted to true / false
Date objects are converted to 'YYYY-mm-dd HH:ii:ss' strings
Buffers are converted to hex strings, e.g. X'0fa5'
Strings are safely escaped
Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'
Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')
Objects that have a toSqlString method will have .toSqlString()called and the returned value is used as the raw SQL.
Objects are turned into key = 'val' pairs for each enumerable property on the object. If the property's value is a function, it is skipped; if the property's value is an object, toString() is called on it and the returned value is used. undefined / null are converted to NULL
NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.
Joins with overlapping column names
When executing joins, you are likely to get result sets with overlapping column names. Docs
By default, node-mysql
will overwrite colliding column names in the order the columns are received from MySQL, causing some of the received values to be unavailable.
However, you can also specify that you want your columns to be nested below the table name using nestTables
properties.
Examples
Example 1
var options = {sql: '...', nestTables: true};
[{
table1: {
fieldA: '...',
fieldB: '...',
},
table2: {
fieldA: '...',
fieldB: '...',
},
}, ...]
Example 2
var options = {sql: '...', nestTables: '_'};
[{
table1_fieldA: '...',
table1_fieldB: '...',
table2_fieldA: '...',
table2_fieldB: '...',
}, ...]
Pagination vs Grid component
If your query is connected to a grid store
and you want it to be paginated
you must specify it through the pagination
property, the query will adapt to the grid setup.
const queryArgs = {
db: "MASTER",
sql: "select id from table where name = ?",
values: ["PIPPO"],
pagination : true
}
const ret = await mysql.query(queryArgs);
Insert a record
Insert a record example:
const queryArgs = {
db: "MASTER",
sql: 'INSERT INTO posts SET ?',
values: {id: 1, title: 'Hello MySQL'};
}
connection.query(queryArgs);
// INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
Custom toSqlString
Insert a record with a custom toSqlString
const CURRENT_TIMESTAMP = {
toSqlString: function() { return 'CURRENT_TIMESTAMP()'; }
};
const queryArgs = {
db: "MASTER",
sql: 'UPDATE posts SET modified = ? WHERE id = ?',
values:[CURRENT_TIMESTAMP, 42]
}
connection.query(queryArgs);
//UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42
Parameter advanced
var userId = 1;
var columns = ['username', 'email'];
const queryArgs = {
db: "MASTER",
sql: 'SELECT ?? FROM ?? WHERE id = ?',
values:[columns, 'users', userId]
}
mysql.query(queryArgs);
// SELECT `username`, `email` FROM `users` WHERE id = 1
escape
If you feel the need to escape queries by yourself, you can also use the escaping function directly:
Syntax
mysql.escape(value)
value
string to exapereturn escape string
Example
var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");
escapeId
If you can't trust an SQL identifier (database / table / column name) because it is provided by a user, you should escape it.
Syntax
mysql.escapeId(value)
value
to escaplereturn escape value
Example
var sql = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId(sorter);
transactionBlock
Use this function to execute the code inside a transaction. In case of an error (eg throw) the system will rollback, otherwise the commit will be done
Syntax
mysql.transactionBlock(db, transaction)
db
db nametransaction
function execute in a transaction
console.info("AZIONE START INSERT");
var resp = await mysql.transactionBlock("ASPORTO", async (_db) => {
//transaction block
console.info("Start transaction");
const queryArgs = {
db: "ASPORTO",
sql: 'INSERT INTO asporti SET ?',
values: {
id: utils.uuid(),
user_description: "Mario Rossi",
user_image: "icon.png",
note: "note bla bla",
create_date: DateTime.now(),
update_date: null,
user_id_create: req.user.email,
user_id_update: null
}
}
console.info("Do insert");
var insertRes = await _db.query(queryArgs);
});
console.info("Commit ok");
return {success:true}
Manual Transaction
Use getConnection, beginTransaction, commit and rollback to manualy manage the transactions.
getConnection
Use this function to manage the transactions manually.
You have to handle releasing the connection in the catch/finaly
Syntax
mysql.getConnection({db})
db
db namereturn a Promise with the transaction
Example
console.info("AZIONE 2 START");
const dbAsporto = await mysql.getConnection({ db: "ASPORTO" });
try{
console.info("AZIONE START TRANSACATION");
await dbAsporto.beginTransaction();
const queryArgs = {
db: "ASPORTO",
sql: 'INSERT INTO asporti SET ?',
values: {
id: utils.uuid(),
user_description: "Mario Rossi",
user_image: "icon.png",
note: "note bla bla",
create_date: DateTime.now(),
update_date: null,
user_id_create: req.user.email,
user_id_update: null
}
}
console.info("AZIONE START INSERT");
var insertRes = await dbAsporto.query(queryArgs);
console.info("AZIONE START COMMIT");
await dbAsporto.commit();
}catch(e){
await dbAsporto.roolback();
}finaly{
console.info("RILASCIA CONNESSIONE");
await dbAsporto.release();
}
console.info("AZIONE 2 FINE");
return {success: true, message : insertRes}
Last updated