MySQLtx

A wrapper for mysql2 library with a better session and transaction handling.

The component can be installed as simple as:

1npm install --save mysqltx

Usage

Starting point is always a ConnectionManager that acts as a pool.

1
2import { ConnectionManager } from 'mysqltx';
3
4const cm = new ConnectionManager({
5  host: 'localhost',  // host to connect to (required)
6  port: 3306,  // port to connect to (required)
7  user: 'root',  // username (required)
8  password: 'root',  // password (required)
9  database: 'mydb',  // database name (optional)
10  charset: 'utf8mb4',  // charset for connections (optional, defaults to 'utf8mb4')
11  connectionLimit: 10,  // active connections limit (required)
12  queueLimit: 10,  // limit for connection acquisition queue (optional, defaults to 0, which means no queue enabled)
13  multipleStatements: false,  // allow multiple statements in one query (required; if true may provide more flexibility, but also highly increases possible damage from an SQL injection attack; keep it as false unless you are very certain on what you're doing)
14  insecureAuth: false  // allow insecure authorization when connecting to the server (optional, defaults to false),
15  releaseStrategy: 'RESET_STATE'  // set connection release strategy (optional, defaults to RESET_STATE) - see Sessions and Connections
16});
17
18// ... do your work here (see further examples) ...
19
20// close the connection manager to break the wait loop once you're all finished
21await cm.close();
22

Sessions and connections

You may choose to use manually managed or automatically managed sessions, connections and transactions. The difference is that when you use manually managed ones, you're responsible for closing them.

1
2// manually managed connection
3const conn = await cm.getConnection();
4try {
5  const res = await conn.query(`SELECT 1`);
6  console.log(res.asObjects());
7} finally {
8  await conn.release();
9}
10
11// automatically managed connection
12await cm.connection(
13  async (conn) => {
14    const res = await conn.query(`SELECT 1`);
15    console.log(res.asObjects());
16  }
17);
18
19// manually managed session
20const session = cm.getSession();
21try {
22  const res = await session.query(`SELECT 1`);
23  console.log(res.asObjects());
24} finally {
25  // closing session is optional
26  session.close();
27}
28
29// automatically managed session
30await cm.session(
31  async (session) => {
32    const res = await session.query(`SELECT 1`);
33    console.log(res.asObjects());
34  }
35);
36

Session acts like a local pool so that it acquires a new connection whenever simultaneous access is required, while a single connection is restricted to one query at a time.

1
2const conn = await cm.getConnection();
3Promise.all(
4  [
5    conn.query('SELECT 1'),
6    conn.query('SELECT 2'),  // this fails, because connection is busy with the first query
7  ]
8);
9
10const session = cm.getSession();
11Promise.all(
12  [
13    session.query('SELECT 1'),  // this query runs in the first temporary connection
14    session.query('SELECT 2'),  // this query runs in the second temporary connection
15  ]
16);
17

When acquired connections (either within sessions or directly) are released, one of the following happens based on the selected releaseStrategy connection option:

• KEEP_STATE - there will be no attempt to reset connection state before returning the connection to the pool
• RESET_STATE - connection state reset process will be initiated (using change_user command), but release function returns without waiting for the connection to actually reach the pool
• WAIT_FOR_STATE_RESET - connection state reset process will be initiated (using change_user command) and release function will wait for the connection to be actually returned to the pool

Queries

Querying the database is quite straightforward. You may use query() calls on any Queryable interface like session, connection or transaction. A set of parameters may be added, which will be automatically escaped when using appropriate placeholders. Manual parameter escaping is done by escape() and escapeId() methods available on Queryable interface implementations.

1
2await cm.connection(
3  async (conn) => {
4    return await conn.query(
5      `
6        SELECT *
7        FROM ${conn.escapeId('mytable')}
8        WHERE a = :a AND b = :b AND c = ${conn.escape('somevalue')}
9      `,
10      {
11        a: 42,
12        b: 'somestring',
13      }
14    );
15    
16  }
17);
18

Currently this component is just a wrapper for mysql2 library. You may want to read more about how values are escaped here: https://github.com/mysqljs/mysql#escaping-query-values

Transactions

Further in your development you may want to use transactions. Transactions are, also, either manually (for connections only) or automatically managed.

1
2// manually managed transaction within a connection
3await cm.connection(
4  async (conn) => {
5    console.log(conn.hasActiveTransaction);  // false
6    await conn.startTransaction();  // alternatively: await conn.query('BEGIN');
7    console.log(conn.hasActiveTransaction);  // true (it'll work even if you start transaction with a direct query)
8    try {
9      await conn.query('SELECT 1');
10      await conn.query('SELECT 2');
11    } catch(err) {
12      await conn.rollback();
13      throw err;
14    }
15    await conn.commit();
16    console.log(conn.hasActiveTransaction);  // false
17  }
18);
19
20// automatically managed transaction within a connection
21await cm.connection(
22  async (conn) => {
23    await conn.transaction(
24      async (tx) => {
25        await tx.query('SELECT 1');
26        await tx.query('SELECT 2');
27      }
28    );
29  }
30);
31
32// automatically managed transaction within a session
33await cm.session(
34  async (session) => {
35    await session.transaction(
36      async (tx) => {
37        await tx.query('SELECT 1');
38        await tx.query('SELECT 2');
39      }
40    );
41  }
42);
43

Logging

For those of you who'd like to keep an eye of what's going on, there is a logger support available. You may create a default logger for ConnectionManager and/or override it per-session and per-connection.

1
2import { ConnectionManager, Logger, QueryResult, QueryParameters } from 'mysqltx';
3
4// let's define a simple logger just for demonstration purposes
5class MyLogger implements Logger {
6
7  constructor(private name: string) {
8  }
9
10  acquire(threadId: number) {
11    console.log(`${this.name}:acquire`, threadId);
12  }
13
14  query(sql: string, parameters: QueryParameters | undefined, threadId: number) {
15    console.log(`${this.name}:query`, sql, parameters, threadId);
16  }
17
18  result(queryResult: QueryResult, threadId: number) {
19    console.log(`${this.name}:result`, queryResult.asObjects(), threadId)
20  }
21
22  release(threadId: number) {
23    console.log(`${this.name}:release`, threadId);
24  }
25
26}
27
28// now we can use this logger as a connection manager option
29const cm = new ConnectionManager({
30  ... ,
31  logger: new MyLogger('default')  // this is the default logger for all sessions and connections
32});
33
34// using with session
35await cm.session(
36  async (session) => {
37    await session.query('SELECT 1');  // would log at first: "session1:query SELECT 1 undefined 1"
38  },
39  { logger: new MyLogger('session1') }  // this is a session-specific logger
40);
41
42// using with connection
43await cm.connection(
44  async (conn) => {
45    await conn.query('SELECT 1');  // would log at first: "connection1:query SELECT 1 undefined 1"
46  },
47  { logger: new MyLogger('connection1') }  // this is a connection-specific logger
48);
49

Important notes:

• This component is still in it's early days so you may expect substantional changes down the road.

Enjoy!