Filter and conditional operators

All the values provided to filter operators and to the sql function are parameterized automatically. For example, this query:

await db.select().from(users).where(eq(users.id, 42));

will be translated to:

select [id], [name], [age] from [users] where [users].[id] = @par0; -- params: [42]

We natively support all dialect specific filter and conditional operators.

You can import all filter & conditional from drizzle-orm:

import { eq, ne, gt, gte, ... } from "drizzle-orm";

eq

Value equal to n

import { eq } from "drizzle-orm";

db.select().from(table).where(eq(table.column, 5));

SELECT * FROM [table] WHERE [table].[column] = 5

import { eq } from "drizzle-orm";

db.select().from(table).where(eq(table.column1, table.column2));

SELECT * FROM [table] WHERE [table].[column1] = [table].[column2]

ne

Value is not equal to n

import { ne } from "drizzle-orm";

db.select().from(table).where(ne(table.column, 5));

SELECT * FROM [table] WHERE [table].[column] <> 5

import { ne } from "drizzle-orm";

db.select().from(table).where(ne(table.column1, table.column2));

SELECT * FROM [table] WHERE [table].[column1] <> [table].[column2]

---

gt

Value is greater than n

import { gt } from "drizzle-orm";

db.select().from(table).where(gt(table.column, 5));

SELECT * FROM [table] WHERE [table].[column] > 5

import { gt } from "drizzle-orm";

db.select().from(table).where(gt(table.column1, table.column2));

SELECT * FROM [table] WHERE [table].[column1] > [table].[column2]

gte

Value is greater than or equal to n

import { gte } from "drizzle-orm";

db.select().from(table).where(gte(table.column, 5));

SELECT * FROM [table] WHERE [table].[column] >= 5

import { gte } from "drizzle-orm";

db.select().from(table).where(gte(table.column1, table.column2));

SELECT * FROM [table] WHERE [table].[column1] >= [table].[column2]

lt

Value is less than n

import { lt } from "drizzle-orm";

db.select().from(table).where(lt(table.column, 5));

SELECT * FROM [table] WHERE [table].[column] < 5

import { lt } from "drizzle-orm";

db.select().from(table).where(lt(table.column1, table.column2));

SELECT * FROM [table] WHERE [table].[column1] < [table].[column2]

lte

Value is less than or equal to n.

import { lte } from "drizzle-orm";

db.select().from(table).where(lte(table.column, 5));

SELECT * FROM [table] WHERE [table].[column] <= 5

import { lte } from "drizzle-orm";

db.select().from(table).where(lte(table.column1, table.column2));

SELECT * FROM [table] WHERE [table].[column1] <= [table].[column2]

---

exists

Value exists

import { exists } from "drizzle-orm";

const query = db.select().from(table2)
db.select().from(table).where(exists(query));

SELECT * FROM [table] WHERE EXISTS (SELECT * FROM [table2])

notExists

import { notExists } from "drizzle-orm";

const query = db.select().from(table2)
db.select().from(table).where(notExists(query));

SELECT * FROM [table] WHERE NOT EXISTS (SELECT * FROM [table2])

isNull

Value is null

import { isNull } from "drizzle-orm";

db.select().from(table).where(isNull(table.column));

SELECT * FROM [table] WHERE ([table].[column] IS NULL)

isNotNull

Value is not null

import { isNotNull } from "drizzle-orm";

db.select().from(table).where(isNotNull(table.column));

SELECT * FROM [table] WHERE ([table].[column] IS NOT NULL)

---

inArray

Value is in array of values

import { inArray } from "drizzle-orm";

db.select().from(table).where(inArray(table.column, [1, 2, 3, 4]));

SELECT * FROM [table] WHERE [table].[column] IN (1, 2, 3, 4)

import { inArray } from "drizzle-orm";

const query = db.select({ data: table2.column }).from(table2);
db.select().from(table).where(inArray(table.column, query));

SELECT * FROM [table] WHERE [table].[column] IN (SELECT [table2].[column] FROM [table2])

notInArray

Value is not in array of values

import { notInArray } from "drizzle-orm";

db.select().from(table).where(notInArray(table.column, [1, 2, 3, 4]));

SELECT * FROM [table] WHERE [table].[column] NOT IN (1, 2, 3, 4)

import { notInArray } from "drizzle-orm";

const query = db.select({ data: table2.column }).from(table2);
db.select().from(table).where(notInArray(table.column, query));

SELECT * FROM [table] WHERE [table].[column] NOT IN (SELECT [table2].[column] FROM [table2])

---

between

Value is between two values

import { between } from "drizzle-orm";

db.select().from(table).where(between(table.column, 2, 7));

SELECT * FROM [table] WHERE [table].[column] BETWEEN 2 AND 7

notBetween

Value is not between two value

import { notBetween } from "drizzle-orm";

db.select().from(table).where(notBetween(table.column, 2, 7));

SELECT * FROM [table] WHERE [table].[column] NOT BETWEEN 2 AND 7

---

like

Value is like other value, case sensitive

import { like } from "drizzle-orm";

db.select().from(table).where(like(table.column, "%llo wor%"));

SELECT * FROM [table] WHERE [table].[column] LIKE '%llo wor%'

notLike

Value is not like other value, case sensitive

import { notLike } from "drizzle-orm";

db.select().from(table).where(notLike(table.column, "%llo wor%"));

SELECT * FROM [table] WHERE [table].[column] NOT LIKE '%llo wor%'

---

not

All conditions must return false.

import { eq, not } from "drizzle-orm";

db.select().from(table).where(not(eq(table.column, 5)));

SELECT * FROM [table] WHERE NOT ([table].[column] = 5)

and

All conditions must return true.

import { gt, lt, and } from "drizzle-orm";

db.select().from(table).where(and(gt(table.column, 5), lt(table.column, 7)));

SELECT * FROM [table] WHERE ([table].[column] > 5 AND [table].[column] < 7)

or

One or more conditions must return true.

import { gt, lt, or } from "drizzle-orm";

db.select().from(table).where(or(gt(table.column, 5), lt(table.column, 7)));

SELECT * FROM [table] WHERE ([table].[column] > 5 OR [table].[column] < 7)