Filter and conditional operators

We natively support all dialect spicific filter and conditional operators
You can import all filter & conditional from drizzle-orm

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

eq

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

isNull

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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)

exists

✓ PostgreSQL ✓ MySQL ✓ SQLite

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 { exists } 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)

between

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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%'

ilike

✓ PostgreSQL ✕ MySQL ✕ SQLite

Value is like some other value, case insensitive

import { ilike } from "drizzle-orm";
 
db.select().from(table).where(ilike(table.column, "%llo wor%"));

SELECT * FROM table WHERE table.column ILIKE '%llo wor%'

notIlike

✓ PostgreSQL ✓ MySQL ✓ SQLite

Value is not like some other value, case insensitive

import { notIlike } from "drizzle-orm";
 
db.select().from(table).where(notIlike(table.column, "%llo wor%"));

SELECT * FROM table WHERE table.column NOT ILIKE '%llo wor%'

not

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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

✓ PostgreSQL ✓ MySQL ✓ SQLite

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)

SQLite SQL queries [CRUD]