README
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
runInTransaction()
Transactional queries using 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.
runWithLockTables()
Transactional queries using 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.