# mysql

## query

Run your queries by autocommitting as soon as the query runs.

### Syntax

```javascript
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

```javascript
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](https://github.com/mysqljs/mysql#joins-with-overlapping-column-names)

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

```javascript
var options = {sql: '...', nestTables: true};
```

```javascript
[{
    table1: {
      fieldA: '...',
      fieldB: '...',
    },
    table2: {
      fieldA: '...',
      fieldB: '...',
    },
  }, ...]
```

Example 2

```javascript
var options = {sql: '...', nestTables: '_'};
```

```javascript
[{
    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.

```javascript
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:

```javascript
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

```javascript
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

```javascript
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

```js
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

```js
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

```javascript
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](#getconnection), beginTransaction, commit and rollback to manualy manage the transactions.

## getConnection

Use this function to manage the transactions manually.&#x20;

[See mysql-promise docs](https://github.com/mysqljs/mysql)

{% hint style="danger" %}
You have to handle releasing the connection in the catch/finaly
{% endhint %}

### Syntax

```
mysql.getConnection({db})
```

* `db` db name
* **return** a Promise with the transaction

### Example

```javascript
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}
```
