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 name

• sql 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 component

• return 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 exape

• return 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 escaple

• return 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 name

• transaction 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.

See mysql-promise docs

You have to handle releasing the connection in the catch/finaly

Syntax

mysql.getConnection({db})

• db db name

• return 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}