@ssense/mysql v1.0.4
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.