MySQL2 Extended

Thin convenience extension for the mysql2 library.

You may be interested in this if:

• You want something more than the raw query function provided by mysql2.
• You don't want a full-blown ORM.
• You like hand-crafted SQL.
• You want typed database results.

Installation:

• npm install mysql2-extended mysql2
• yarn add mysql2-extended mysql2

Usage

Setup

import { MySQL2Extended } from 'mysql2-extended';
import { createPool } from 'mysql2/promise';

const pool = createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'test',
});

const db = new MySQL2Extended(pool);

Typings

Types can be passed to each query function in order to get typed data back without casting. Note that the types are not validated against the actual data, but just a convenient way for the developer to add types.

This package provides generic types to help with common workflows. For example, you probably want types for your tables, like this:

type User = {
    id: number;                  // Auto increment in MySQL
    email: string;
    firstname: string | null;
    lastname: string | null;
    active: boolean;             // Default 'true' in MySQL column definition
};

When creating (inserting) a new user, you don't want to be forced to provide values for columns that have default values in the database. You can use the OptionalDefaultFields utility type:

type UserDefaultFields = 'id' | 'firstname' | 'lastname' | 'active';

// CreateUser will only enforce 'email' as required
type CreateUser = OptionalDefaultFields<User, UserDefaultFields>;

Querying

Note that all supplied parameters will use parameter binding in the underlying database driver.

Insert

Single

await db.insert<CreateUser>('users', {
    email: '[e-mail]',
    firstname: 'Bob',
    active: true,
});

INSERT INTO `users` (`email`, `firstname`, `active`) VALUES ('[e-mail]', 'Bob', 1);

Bulk

await db.insert<CreateUser>('users', [
    { email: '[e-mail]' },
    { email: '[e-mail]' },
]);

INSERT INTO `users` (`email`) VALUES ('[e-mail]'), ('[e-mail]');

---

Select

Specific columns

const users = await db.select<Pick<User, 'firstname' | 'lastname'>>(
    ['firstname', 'lastname'],
    'users',
);

SELECT `firstname`, `lastname` FROM `users`;

All columns

First column argument used above is optional. If no columns are specified, all columns will be selected:

const users = await db.select<User>('users');

SELECT * FROM `users`;

WHERE conditions

Conditions provided in the object will be combined with AND. For more complex use-cases, see Raw for now.

const users = await db.select<User>('users', { id: 1, active: true });

SELECT * FROM `users` WHERE `id` = 1 AND `active` = 1;

Limit and offset

const users = await db.select<User>(
  'users',
  { id: 1, active: true }, // undefined/null if no conditions are present
  { limit: 10, offset: 5 },
);

SELECT * FROM `users` WHERE `id` = 1 AND `active` = 1 LIMIT 5, 10;

Ordering

const users = await db.select<User>(
  'users',
  { id: 1, active: 1 },
  {
    limit: 10,
    offset: 5,
    order: ['id', 'desc'],
    // order: [['firstname', 'desc'], ['lastname', 'asc']],
  },
);

SELECT * FROM `users`
WHERE `id` = 1 AND `active` = 1
ORDER BY `id` DESC
LIMIT 5, 10;

---

Update

All

await db.update<User>('users', { active: true })

UPDATE `users` SET `active` = 1;

WHERE conditions

await db.update('users', { firstname: 'Bob' }, { id: 1 }, { limit: 1 });

UPDATE `users` SET `firstname` = 'Bob' WHERE `id` = 1 LIMIT 1;

---

Delete

All

await db.delete('users');

DELETE FROM `users`;

WHERE conditions

await db.delete<User>('users', { id: 1, active: true });

DELETE FROM `users` WHERE `id` = 1 AND `active` = 1;

---

Raw

// Multiple rows
const users = await db.query<{ firstname: string }>('SELECT firstname FROM users LIMIT 2');

// One row
const [user] = await db.query<User>('SELECT * FROM users LIMIT 1');

// Parameter binding
await db.query<User>('SELECT * FROM users LIMIT ?', [1]);

---

Transactions

Managed transaction

A managed transaction will automatically commit if the supplied callback doesn't throw/reject any error. If the callback throws/rejects, it will rollback the transaction, and re-throw the error that caused the rollback.

Successful example

await db.transaction(async t => {
    await t.insert<CreateUser>('users', { email: '[e-mail]' });
    await t.insert<CreateUser>('users', { email: '[e-mail]' });
});

BEGIN;
INSERT INTO `users` (`email`) VALUES ('[e-mail]');
INSERT INTO `users` (`email`) VALUES ('[e-mail]');
COMMIT;

Rollback example

try {
    await db.transaction(async t => {
        await t.insert<CreateUser>('users', { email: '[e-mail]' });
        throw new Error('Oops')
        await t.insert<CreateUser>('users', { email: '[e-mail]' });
    })
} catch (err) {
    // err === Error('Oops')
}

BEGIN;
INSERT INTO `users` (`email`) VALUES ('[e-mail]');
ROLLBACK;

Manual transactions

// Manual transaction (user controls commit/rollback)
const transaction = await db.begin();
await transaction.insert<CreateUser>('users', { email: '[e-mail]' });
await transaction.insert<CreateUser>('users', { email: '[e-mail]' });
await transaction.commit(); // Or rollback()

BEGIN;
INSERT INTO `users` (`email`) VALUES ('[e-mail]');
INSERT INTO `users` (`email`) VALUES ('[e-mail]');
COMMIT;

---

Future/TODO

• Increase performance by optimizing hot code paths.
• Return affected row count.
• Make the library work with more databases.
• Be able to generate SQL queries in specific formats/flavours.
• Support for more complex condition objects.
• Run tests with an actual database connection.