Skip to main content

Using MySQL2 with TypeScript

Installationโ€‹

npm install --save mysql2
npm install --save-dev @types/node

The @types/node ensure the proper interaction between TypeScript and the Node.js modules used by MySQL2 (net, events, stream, tls, etc.).

info

Requires TypeScript >=4.5.2.

Usageโ€‹

You can import MySQL2 in two ways:

  • By setting the esModuleInterop option to true in tsconfig.json
import mysql from 'mysql2';
import mysql from 'mysql2/promise';
  • By setting the esModuleInterop option to false in tsconfig.json
import * as mysql from 'mysql2';
import * as mysql from 'mysql2/promise';

Connectionโ€‹

import mysql, { ConnectionOptions } from 'mysql2';

const access: ConnectionOptions = {
  user: 'test',
  database: 'test',
};

const conn = mysql.createConnection(access);

Pool Connectionโ€‹

import mysql, { PoolOptions } from 'mysql2';

const access: PoolOptions = {
  user: 'test',
  database: 'test',
};

const conn = mysql.createPool(access);

Query and Executeโ€‹

A simple queryโ€‹

conn.query('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  /**
   * @rows: [ { test: 2 } ]
   */
});

conn.execute('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  /**
   * @rows: [ { test: 2 } ]
   */
});

The rows output will be these possible types:

  • RowDataPacket[]
  • RowDataPacket[][]
  • ResultSetHeader
  • ResultSetHeader[]
  • ProcedureCallPacket

In this example, you need to manually check the output types

Type Specificationโ€‹

RowDataPacket[]โ€‹

2Stable

An array with the returned rows, for example:

import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

// SELECT
conn.query<RowDataPacket[]>('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ { test: 2 } ]
   */
});

// SHOW
conn.query<RowDataPacket[]>('SHOW TABLES FROM `test`;', (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ { Tables_in_test: 'test' } ]
   */
});

Using rowsAsArray option as true:

import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  rowsAsArray: true,
});

// SELECT
conn.query<RowDataPacket[]>(
  'SELECT 1 + 1 AS test, 2 + 2 AS test;',
  (_err, rows) => {
    console.log(rows);
    /**
     * @rows: [ [ 2, 4 ] ]
     */
  }
);

// SHOW
conn.query<RowDataPacket[]>('SHOW TABLES FROM `test`;', (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ [ 'test' ] ]
   */
});

RowDataPacket[][]โ€‹

2Stable

Using multipleStatements option as true with multiple queries:

import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  multipleStatements: true,
});

const sql = `
  SELECT 1 + 1 AS test;
  SELECT 2 + 2 AS test;
`;

conn.query<RowDataPacket[][]>(sql, (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ [ { test: 2 } ], [ { test: 4 } ] ]
   */
});

ResultSetHeaderโ€‹

2Stable
History
VersionChanges
v3.5.1
OkPacket is deprecated and might be removed in the future major release.
Please use ResultSetHeader instead.
changedRows option is deprecated and might be removed in the future major release.
Please use affectedRows instead.

For INSERT, UPDATE, DELETE, TRUNCATE, etc.:

import mysql, { ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

const sql = `
  SET @1 = 1;
`;

conn.query<ResultSetHeader>(sql, (_err, result) => {
  console.log(result);
  /**
   * @result: ResultSetHeader {
      fieldCount: 0,
      affectedRows: 0,
      insertId: 0,
      info: '',
      serverStatus: 2,
      warningStatus: 0,
      changedRows: 0
    }
   */
});

ResultSetHeader[]โ€‹

2Stable
History
VersionChanges
v3.5.1
Introduce ResultSetHeader[]

For multiples INSERT, UPDATE, DELETE, TRUNCATE, etc. when using multipleStatements as true:

import mysql, { ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  multipleStatements: true,
});

const sql = `
  SET @1 = 1;
  SET @2 = 2;
`;

conn.query<ResultSetHeader[]>(sql, (_err, results) => {
  console.log(results);
  /**
   * @results: [
      ResultSetHeader {
        fieldCount: 0,
        affectedRows: 0,
        insertId: 0,
        info: '',
        serverStatus: 10,
        warningStatus: 0,
        changedRows: 0
      },
      ResultSetHeader {
        fieldCount: 0,
        affectedRows: 0,
        insertId: 0,
        info: '',
        serverStatus: 2,
        warningStatus: 0,
        changedRows: 0
      }
    ]
   */
});

ProcedureCallPacketโ€‹

2Stable
History
VersionChanges
v3.5.1
Introduce ProcedureCallPacket
tip

By performing a Call Procedure using INSERT, UPDATE, etc., the return will be a ProcedureCallPacket<ResultSetHeader> (even if you perform multiples queries and set multipleStatements to true):

import mysql, { ProcedureCallPacket, ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

/** ResultSetHeader */
conn.query('DROP PROCEDURE IF EXISTS myProcedure');

/** ResultSetHeader */
conn.query(`
    CREATE PROCEDURE myProcedure()
    BEGIN
      SET @1 = 1;
      SET @2 = 2;
    END
  `);

/** ProcedureCallPacket */
const sql = 'CALL myProcedure()';

conn.query<ProcedureCallPacket<ResultSetHeader>>(sql, (_err, result) => {
  console.log(result);
  /**
   * @result: ResultSetHeader {
      fieldCount: 0,
      affectedRows: 0,
      insertId: 0,
      info: '',
      serverStatus: 2,
      warningStatus: 0,
      changedRows: 0
    }
   */
});

For CREATE PROCEDURE and DROP PROCEDURE, these returns will be the default ResultSetHeader.

By using SELECT and SHOW queries in a Procedure Call, it groups the results as:

/** ProcedureCallPacket<RowDataPacket[]> */
[RowDataPacket[], ResultSetHeader]

For ProcedureCallPacket<RowDataPacket[]>, please see the following examples.

OkPacketโ€‹

0Deprecated

OkPacket is deprecated and might be removed in the future major release.
Please use ResultSetHeader instead.

Examplesโ€‹

You can also check some code examples using MySQL2 and TypeScript to understand advanced concepts: