Mysqltx NPM | npm.io

MySQLtx

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

The component can be installed as simple as:

npm install --save mysqltx

Usage

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

import { ConnectionManager } from 'mysqltx';

const cm = new ConnectionManager({
  host: 'localhost',  // host to connect to (required)
  port: 3306,  // port to connect to (required)
  user: 'root',  // username (required)
  password: 'root',  // password (required)
  database: 'mydb',  // database name (optional)
  charset: 'utf8mb4',  // charset for connections (optional, defaults to 'utf8mb4')
  connectionLimit: 10,  // active connections limit (required)
  queueLimit: 10,  // limit for connection acquisition queue (optional, defaults to 0, which means no queue enabled)
  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)
  insecureAuth: false  // allow insecure authorization when connecting to the server (optional, defaults to false),
  releaseStrategy: 'RESET_STATE'  // set connection release strategy (optional, defaults to RESET_STATE) - see Sessions and Connections
});

// ... do your work here (see further examples) ...

// close the connection manager to break the wait loop once you're all finished
await cm.close();

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.

// manually managed connection
const conn = await cm.getConnection();
try {
  const res = await conn.query(`SELECT 1`);
  console.log(res.asObjects());
} finally {
  await conn.release();
}

// automatically managed connection
await cm.connection(
  async (conn) => {
    const res = await conn.query(`SELECT 1`);
    console.log(res.asObjects());
  }
);

// manually managed session
const session = cm.getSession();
try {
  const res = await session.query(`SELECT 1`);
  console.log(res.asObjects());
} finally {
  // closing session is optional
  session.close();
}

// automatically managed session
await cm.session(
  async (session) => {
    const res = await session.query(`SELECT 1`);
    console.log(res.asObjects());
  }
);

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.

const conn = await cm.getConnection();
Promise.all(
  [
    conn.query('SELECT 1'),
    conn.query('SELECT 2'),  // this fails, because connection is busy with the first query
  ]
);

const session = cm.getSession();
Promise.all(
  [
    session.query('SELECT 1'),  // this query runs in the first temporary connection
    session.query('SELECT 2'),  // this query runs in the second temporary connection
  ]
);

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.

await cm.connection(
  async (conn) => {
    return await conn.query(
      `
        SELECT *
        FROM ${conn.escapeId('mytable')}
        WHERE a = :a AND b = :b AND c = ${conn.escape('somevalue')}
      `,
      {
        a: 42,
        b: 'somestring',
      }
    );
    
  }
);

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.

// manually managed transaction within a connection
await cm.connection(
  async (conn) => {
    console.log(conn.hasActiveTransaction);  // false
    await conn.startTransaction();  // alternatively: await conn.query('BEGIN');
    console.log(conn.hasActiveTransaction);  // true (it'll work even if you start transaction with a direct query)
    try {
      await conn.query('SELECT 1');
      await conn.query('SELECT 2');
    } catch(err) {
      await conn.rollback();
      throw err;
    }
    await conn.commit();
    console.log(conn.hasActiveTransaction);  // false
  }
);

// automatically managed transaction within a connection
await cm.connection(
  async (conn) => {
    await conn.transaction(
      async (tx) => {
        await tx.query('SELECT 1');
        await tx.query('SELECT 2');
      }
    );
  }
);

// automatically managed transaction within a session
await cm.session(
  async (session) => {
    await session.transaction(
      async (tx) => {
        await tx.query('SELECT 1');
        await tx.query('SELECT 2');
      }
    );
  }
);

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.

import { ConnectionManager, Logger, QueryResult, QueryParameters } from 'mysqltx';

// let's define a simple logger just for demonstration purposes
class MyLogger implements Logger {

  constructor(private name: string) {
  }

  acquire(threadId: number) {
    console.log(`${this.name}:acquire`, threadId);
  }

  query(sql: string, parameters: QueryParameters | undefined, threadId: number) {
    console.log(`${this.name}:query`, sql, parameters, threadId);
  }

  result(queryResult: QueryResult, threadId: number) {
    console.log(`${this.name}:result`, queryResult.asObjects(), threadId)
  }

  release(threadId: number) {
    console.log(`${this.name}:release`, threadId);
  }

}

// now we can use this logger as a connection manager option
const cm = new ConnectionManager({
  ... ,
  logger: new MyLogger('default')  // this is the default logger for all sessions and connections
});

// using with session
await cm.session(
  async (session) => {
    await session.query('SELECT 1');  // would log at first: "session1:query SELECT 1 undefined 1"
  },
  { logger: new MyLogger('session1') }  // this is a session-specific logger
);

// using with connection
await cm.connection(
  async (conn) => {
    await conn.query('SELECT 1');  // would log at first: "connection1:query SELECT 1 undefined 1"
  },
  { logger: new MyLogger('connection1') }  // this is a connection-specific logger
);

Important notes: