🡐 Go to main README page

MySQL Client

class Connection

Connection is a helper that makes it easy to access and send queries to a MySQL or MariaDB server. (see examples here)

Methods

| Method | Returns | Description | | ------------------------------------------------------------------------------------------------------------------------------------------------------ | --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | constructor(options: ConnectionOptions) | Connection | Creates a new instance of Connection | | query(sql: string, params?: any[]) | Promise<any> | Sends a query to MySQL server and return a result | | runInTransaction(callback: TransactionFunction) | Promise<any> | Executes a list of statements in a MySQL transactional way, managing the transaction (begin, commit, rollback) automatically | | runWithLockTables(locks: LockTableOption[], callback: TransactionFunction) | Promise<any> | Same as runInTransaction() method, except it explicitly locks tables before running the transaction (calling LOCK TABLES instead of START TRANSACTION) | | close() | Promise<void> | Closes all opened connections to the database and prevent new connections to be created |

Details

constructor(options: ConnectionOptions)

Creates a new instance of Connection

Parameters

| Name | Type | Required | Description | | ------- | ------------------- | :------: | -------------------------------------------------- | | options | ConnectionOptions | Yes | The parameters used to connect to the MySQL server |

ConnectionOptions properties

See here for more detail about options properties.

| Name | Type | Required | Description | | --------------- | -------- | :------: | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | host | string | Yes | MySQL server hostname or IP address | | database | string | Yes | Name of database to use | | port | number | No | MySQL port (default: 3306) | | user | string | No | MySQL username (default: null) | | password | string | No | MySQL password (default: null) | | connectionLimit | number | No | Maximum number of parallel connections in internal MySQL connection pool (default: 10) | | timezone | string | No | The timezone configured on the MySQL server. This is used to type cast server date/time values to JavaScript Date object and vice versa. (default: 'local') |

query(sql: string, params?: any[])

Sends a query to MySQL server and return a result

Parameters

| Name | Type | Required | Description | | ------ | -------- | :------: | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | sql | string | Yes | SQL query | | params | any[] | No | SQL query params for a query with parameters (will be protected against SQL injections, see mysql npm module for more detail) |

Return value

| Type | Description | | -------------- | ---------------------------- | | Promise<any> | Result of the executed query |

runInTransaction(callback: TransactionFunction)

Executes a list of statements in a MySQL transactional way, managing the transaction (begin, commit, rollback) automatically

Parameters

| Name | Type | Required | Description | | -------- | --------------------- | :------: | ----------------------------------------------------------------------------------------------- | | callback | TransactionFunction | Yes | Function in which all the MySQL statements can be executed (will be run in a MySQL transaction) |

TransactionFunction definition

TransactionFunction is a callback function that will be called with a transaction parameter, this transaction exposes a query function, which has the exact same profile as the query function above. You are therefore able to call transaction.query() to send MySQL queries in a transactional context. See examples for more detail.

Return value

| Type | Description | | -------------- | ---------------------------------- | | Promise<any> | Result of the executed transaction |

runWithLockTables(locks: LockTableOption[], callback: TransactionFunction)

Same as runInTransaction() method, except it explicitly locks tables before running the transaction (calling LOCK TABLES instead of START TRANSACTION)

Parameters

| Name | Type | Required | Description | | -------- | --------------------- | :------: | ----------------------------------------------------------------------------------------------- | | locks | LockTableOption[] | Yes | Array of LockTableOption (tables to lock with lock mode) | | callback | TransactionFunction | Yes | Function in which all the MySQL statements can be executed (will be run in a MySQL transaction) |

LockTableOption properties

| Name | Type | Required | Description | | ---- | ------------------- | :------: | ------------------------------------------------------ | | name | string | Yes | Name of the table to lock | | mode | 'READ'|'WRITE' | Yes | Lock mode to use, must be one of 'READ' or 'WRITE' |

TransactionFunction definition

Definition for TransactionFunction is available in runInTransaction() method above. See examples for more detail.

Return value

| Type | Description | | -------------- | ---------------------------------- | | Promise<any> | Result of the executed transaction |

close()

Closes all opened connections to the database and prevent new connections to be created

Examples

Transactional queries using runInTransaction()

import { Connection } from '@ssense/framework';

// Create connection
const connection = new Connection({ ...params });

// Run multiple MySQL commands inside a managed transaction
const result = await connection.runInTransaction(async (transaction) => {
    const users = await transaction.query('SELECT * FROM USERS');
    if (users.length > 0) {
        await transaction.query('UPDATE users set name=.....');
    }

    return users[0];
});

// result will be the object returned by the runInTransaction() method, here users[0]
// All the MySQL transaction commands (BEGIN, COMMIT or ROLLBACK) are automatically performed, so you just have to focus on your business case.

Transactional queries using runWithLockTables()

import { Connection } from '@ssense/framework';

// Create connection
const connection = new Connection({ ...params });

// Run multiple MySQL commands inside a managed transaction
const result = await connection.runWithLockTables(
    [
        { name: 'users', mode: 'WRITE' },
        { name: 'accounts', mode: 'WRITE' },
    ],
    async (transaction) => {
        // When reaching this part of the code, both "users" and "accounts" tables will be locked, even if we don't perfom any query on the "accounts" table
        const users = await transaction.query('SELECT * FROM USERS');
        if (users.length > 0) {
            await transaction.query('UPDATE users set name=.....');
        }

        return users[0];
    },
);

// result will be the object returned by the runWithLockTables() method, here users[0]
// All the MySQL transaction commands (BEGIN, COMMIT or ROLLBACK) are automatically performed, so you just have to focus on your business case.