MySQL2 ORM

An ORM built on MySQL2, designed to be intuitive, productive and focused on essential functionality.

This project uses mysql2/promise, createPool and execute behind the scenes.

Why

• An user-friendly ORM for INSERT, SELECT, UPDATE, DELETE and WHERE clauses.
• Automatic Prepared Statements (including LIMIT and OFFSET).
• You can also simply use QueryBuilder to mount your queries and use them in your original MySQL2 connection.
• It will smartly detect and release the connection when using commit or rollback in a transaction.
• It exposes the execute and query original methods from MySQL2 Pool class.
• Strictly Typed: No usage of any, as neither satisfies at all.

---

Installation

TypeScript

npm i mysql2-orm
npm i -D @types/node

JavaScript

npm i mysql2-orm

---

Getting Started

Connect

import { MySQL } from 'mysql2-orm';

const pool = new MySQL({
  host: '',
  user: '',
  database: '',
  // ...
});

Credentials extends PoolOptions from MySQL2.

Close Connection

await pool.end();

---

Documentation

See detailed specifications and usage in Documentation section.

---

Comparing with MySQL2

MySQL2 ORM can be used in two ways:

• MySQL2 ORM Class
• QueryBuilder (constructor only)

The following examples are based on TypeScript and ES Modules, but you can also use JavaScript and CommonJS.

Returning a single user

MySQL2 ORM

import { MySQL, OP } from 'mysql2-orm';

export const getUser = async (pool: MySQL, id: number) => {
  const user = await pool.select({
    table: 'users',
    where: OP.eq('id', id),
    limit: 1,
  });

  return user;
};

Due to limit: 1, it returns a direct object with the row result.

OP Explanation

OP = Operation 🧙🏻

OP.eq: is column equal to value

See all available operators here.

MySQL2

import type { Pool, PoolConnection, RowDataPacket } from 'mysql2/promise';

export const getUser = async (pool: Pool | PoolConnection, id: number) => {
  const sql = 'SELECT * FROM `users` WHERE `id` = ?';
  const params = [id];

  try {
    const [users] = await pool.execute<RowDataPacket[]>(sql, params);
    const user = users?.[0] || false;

    return user;
  } catch (_) {
    return false;
  }
};

QueryBuilder + MySQL2

import { QueryBuilder, OP } from 'mysql2-orm';
import type { Pool, PoolConnection, RowDataPacket } from 'mysql2/promise';

export const getUser = async (pool: Pool | PoolConnection, id: number) => {
  const { sql, params } = QueryBuilder.select({
    table: 'users',
    where: OP.eq('id', id),
    limit: 1,
  });

  try {
    const [users] = await pool.execute<RowDataPacket[]>(sql, params);
    const user = users?.[0] || false;

    return user;
  } catch (_) {
    return false;
  }
};

OP Explanation

OP = Operation 🧙🏻

OP.eq: is column equal to value

See all available operators here.

---

Returning enabled users based on their specializations

MySQL2 ORM

import { MySQL, OP, Param } from 'mysql2-orm';

export const getUsersBySpecializations = async (
  pool: MySQL,
  specializations: Param[]
) => {
  const users = await pool.select({
    table: 'users',
    columns: ['name', 'age'],
    where: [
      OP.eq('status', true),
      'AND',
      OP.in('specialization', specializations),
    ],
    orderBy: ['name'],
    limit: 10,
  });

  return users || [];
};

OP Explanation

OP = Operation 🧙🏻

OP.eq: is column equal to value
OP.in: is in column [values,]

See all available operators here.

MySQL2

import type { Pool, PoolConnection, RowDataPacket } from 'mysql2/promise';

export const getUsersBySpecializations = async (
  pool: Pool | PoolConnection,
  specializations: unknown[]
) => {
  const specializationsPlaceholders = specializations.map(() => '?').join(', ');
  const sql = `SELECT \`name\`, \`age\` FROM \`users\` WHERE \`status\` = ? AND \`specialization\` IN (${specializationsPlaceholders}) ORDER BY \`name\` ASC LIMIT ?`;
  const params = [true, ...specializations, '10'];

  try {
    const [users] = await pool.execute<RowDataPacket[]>(sql, params);

    return users;
  } catch (_) {
    return [];
  }
};

QueryBuilder + MySQL2

import { QueryBuilder, OP, Param } from 'mysql2-orm';
import type { Pool, PoolConnection, RowDataPacket } from 'mysql2/promise';

export const getUsersBySpecializations = async (
  pool: Pool | PoolConnection,
  specializations: Param[]
) => {
  const { sql, params } = QueryBuilder.select({
    table: 'users',
    columns: ['name', 'age'],
    where: [
      OP.eq('status', true),
      'AND',
      OP.in('specialization', specializations),
    ],
    orderBy: ['name'],
    limit: 10,
  });

  try {
    const [users] = await pool.execute<RowDataPacket[]>(sql, params);

    return users;
  } catch (_) {
    return [];
  }
};

OP Explanation

OP = Operation 🧙🏻

OP.eq: is column equal to value
OP.in: is in column [values,]

See all available operators here.

---

When Not to Use the ORM Class

• Avoid using the ORM transaction in scenarios that require parallel execution of multiple transactions in asynchronous loops (no await). The connection management can hinder efficiency in high concurrency scenarios where several transactions need to be processed at the same time in the same node process. This limitation doesn't apply to QueryBuilder or scenarios that properly utilize synchronous operations and await to manage transactions.
• When you need Models (such as Sequelize, TypeORM, etc.).

---

Curiosity

Why a dice?

While in English dice and data each have their own word, in Brazil, both the dice and "data" are called "dado" even though they refer to different things:

	🇺🇸 English	🇧🇷 Portuguese (BR)
	data	dado
	dice	dado

---

Acknowledgements

• MySQL2 is maintained by @sidorares.
• The operator names eq, ne, gt, lt, gte and lte are inspired by Sequelize.
• Contributors.