Get started

Manage schema

Overview Column types Indexes & Constraints Sequences Migrations Views Schemas Extensions

Access your data

Query Select Insert Update Delete Filters

eq ne gt gte lt lte isNull isNotNull inArray notInArray exists notExists between notBetween like ilike notIlike not and or arrayContains arrayContained arrayOverlaps

Joins Magic sql`` operator

Performance

Queries Serverless

Advanced

Set Operations Generated Columns Transactions Batch Dynamic query building Read Replicas Custom types Goodies

Extensions

Prisma ESLint Plugin drizzle-zod drizzle-typebox drizzle-valibot drizzle-graphql

Become a Sponsor

Twitter

Discord

Filter and conditional operators

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

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)

arrayContains

PostgreSQL

MySQL

SQLite

Test that a column or expression contains all elements of the list passed as the second argument

import { arrayContains } from "drizzle-orm";

const contains = await db.select({ id: posts.id }).from(posts)
  .where(arrayContains(posts.tags, ['Typescript', 'ORM']));

const withSubQuery = await db.select({ id: posts.id }).from(posts)
  .where(arrayContains(
    posts.tags,
    db.select({ tags: posts.tags }).from(posts).where(eq(posts.id, 1)),
  ));

select "id" from "posts" where "posts"."tags" @> {Typescript,ORM};
select "id" from "posts" where "posts"."tags" @> (select "tags" from "posts" where "posts"."id" = 1);

arrayContained

PostgreSQL

MySQL

SQLite

Test that the list passed as the second argument contains all elements of a column or expression

import { arrayContained } from "drizzle-orm";

const contained = await db.select({ id: posts.id }).from(posts)
  .where(arrayContained(posts.tags, ['Typescript', 'ORM']));

select "id" from "posts" where "posts"."tags" <@ {Typescript,ORM};

arrayOverlaps

PostgreSQL

MySQL

SQLite

Test that a column or expression contains any elements of the list passed as the second argument.

import { arrayOverlaps } from "drizzle-orm";

const overlaps = await db.select({ id: posts.id }).from(posts)
  .where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']));

select "id" from "posts" where "posts"."tags" && {Typescript,ORM}

Become a Gold Sponsor