WHERE Clauses

The WHERE clause is essential for filtering records in SELECT, UPDATE, and DELETE operations.
With MySQL2 ORM, you can use the OP object to construct complex queries efficiently.

The operator names eq, ne, gt, lt, gte and lte are inspired by Sequelize.
See all available operators here.

The following examples apply as well to SELECT, UPDATE, DELETE from both MySQL2 ORM class and QueryBuilder.

Single condition

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

const pool = new MySQL({
  // ...
});

await pool.select({
  table: 'users',
  where: OP.eq('id', 15),
});

SQL Query

SELECT * FROM `users`
WHERE `id` = ?

-- params [15]

---

Multiple conditions

Ensure to use AND, OR, XOR or NOT between each condition.

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

const pool = new MySQL({
  // ...
});

await pool.select({
  table: 'users',
  where: [OP.in('id', [15, 30]), 'AND', OP.eq('status', 1)],
});

SQL Query

SELECT * FROM `users`
WHERE `id` IN (?, ?) AND `status` = ?

-- params [15, 30, 1]

---

Combine conditions

Ensure to use AND, OR, XOR or NOT between each combination.

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

const pool = new MySQL({
  // ...
});

await pool.select({
  table: 'users',
  where: [[OP.lt('age', 18)], 'OR', [OP.eq('status', 'locked')]],
});

SQL Query

SELECT * FROM `users`
WHERE (`age` < ?) OR (`status` = ?)

-- params [18, 'locked']

---

Combine multiple conditions

Ensure to use AND, OR, XOR or NOT between each condition and combination.

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

const pool = new MySQL({
  // ...
});

await pool.select({
  table: 'users',
  where: [
    [OP.gte('age', 18), 'AND', OP.eq('status', 'locked')],
    'OR',
    [OP.lt('age', 18), 'AND', OP.eq('status', 'enabled')],
  ],
});

SQL Query

SELECT * FROM `users`
WHERE (`age` >= ? AND `status` = ?) OR (`age` < ? AND `status` = ?);

-- params [18, 'locked', 18, 'enabled']

• You can play into depth with combined conditions by encapsulating your conditions in an array. But note that this can become complex.

---

Manual conditions

You can also use where as a string, creating your conditions and passing the params manually:

Discouraged: You may prefer to use a more robust ORM in these cases that covers native MySQL Server functions, such as NOW, YEAR, etc.

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

const pool = new MySQL({
  // ...
});

await pool.select({
  table: 'users',
  where: 'YEAR(`createdAt`) = ?',
  params: [2024],
});

SQL Query

SELECT * FROM `users`
WHERE YEAR(`createdAt`) = ?

-- params [2024]