query api & operators

22 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Filter operators are automatically parameterized

All values provided to filter operators and to the sql function are parameterized automatically. For example, await db.select().from(users).where(eq(users.id, 42)) is translated to select "id", "name", "age" from "users" where "users"."id" = $1 with params: [42].

eq operator - value equal to n

The eq operator checks if a value equals n. Usage: import { eq } from 'drizzle-orm'; db.select().from(table).where(eq(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" = 5. It can also compare two columns: eq(table.column1, table.column2) generates SELECT * FROM "table" WHERE "table"."column1" = "table"."column2".

ne operator - value not equal to n

The ne operator checks if a value is not equal to n. Usage: import { ne } from 'drizzle-orm'; db.select().from(table).where(ne(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" <> 5. It can also compare two columns: ne(table.column1, table.column2) generates SELECT * FROM "table" WHERE "table"."column1" <> "table"."column2".

gt operator - value greater than n

The gt operator checks if a value is greater than n. Usage: import { gt } from 'drizzle-orm'; db.select().from(table).where(gt(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" > 5. It can also compare two columns: gt(table.column1, table.column2) generates SELECT * FROM "table" WHERE "table"."column1" > "table"."column2".

gte operator - value greater than or equal to n

The gte operator checks if a value is greater than or equal to n. Usage: import { gte } from 'drizzle-orm'; db.select().from(table).where(gte(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" >= 5. It can also compare two columns: gte(table.column1, table.column2) generates SELECT * FROM "table" WHERE "table"."column1" >= "table"."column2".

lte operator - value less than or equal to n

The lte operator checks if a value is less than or equal to n. Usage: import { lte } from 'drizzle-orm'; db.select().from(table).where(lte(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" <= 5. It can also compare two columns: lte(table.column1, table.column2) generates SELECT * FROM "table" WHERE "table"."column1" <= "table"."column2".

exists operator - value exists

The exists operator checks if a value exists. Usage: import { exists } from 'drizzle-orm'; const query = db.select().from(table2); db.select().from(table).where(exists(query)) generates SELECT * FROM "table" WHERE EXISTS (SELECT * FROM "table2").

notExists operator - value not exists

The notExists operator checks if a value does not exist. Usage: import { notExists } from 'drizzle-orm'; const query = db.select().from(table2); db.select().from(table).where(notExists(query)) generates SELECT * FROM "table" WHERE NOT EXISTS (SELECT * FROM "table2").

isNull operator - value is null

The isNull operator checks if a value is null. Usage: import { isNull } from 'drizzle-orm'; db.select().from(table).where(isNull(table.column)) generates SELECT * FROM "table" WHERE ("table"."column" IS NULL).

isNotNull operator - value is not null

The isNotNull operator checks if a value is not null. Usage: import { isNotNull } from 'drizzle-orm'; db.select().from(table).where(isNotNull(table.column)) generates SELECT * FROM "table" WHERE ("table"."column" IS NOT NULL).

inArray operator - value in array

The inArray operator checks if a value is in an array of values. Usage: import { inArray } from 'drizzle-orm'; db.select().from(table).where(inArray(table.column, [1, 2, 3, 4])) generates SELECT * FROM "table" WHERE "table"."column" IN (1, 2, 3, 4). It can also use a subquery: db.select().from(table).where(inArray(table.column, db.select({ data: table2.column }).from(table2))) generates SELECT * FROM "table" WHERE "table"."column" IN (SELECT "table2"."column" FROM "table2").

notInArray operator - value not in array

The notInArray operator checks if a value is not in an array of values. Usage: import { notInArray } from 'drizzle-orm'; db.select().from(table).where(notInArray(table.column, [1, 2, 3, 4])) generates SELECT * FROM "table" WHERE "table"."column" NOT IN (1, 2, 3, 4). It can also use a subquery: db.select().from(table).where(notInArray(table.column, db.select({ data: table2.column }).from(table2))) generates SELECT * FROM "table" WHERE "table"."column" NOT IN (SELECT "table2"."column" FROM "table2").

between operator - value between two values

The between operator checks if a value is between two values inclusive. Usage: import { between } from 'drizzle-orm'; db.select().from(table).where(between(table.column, 2, 7)) generates SELECT * FROM "table" WHERE "table"."column" BETWEEN 2 AND 7.

notBetween operator - value not between two values

The notBetween operator checks if a value is not between two values. Usage: import { notBetween } from 'drizzle-orm'; db.select().from(table).where(notBetween(table.column, 2, 7)) generates SELECT * FROM "table" WHERE "table"."column" NOT BETWEEN 2 AND 7.

like operator - case sensitive pattern matching

The like operator performs case-sensitive pattern matching. Usage: import { like } from 'drizzle-orm'; db.select().from(table).where(like(table.column, '%llo wor%')) generates SELECT * FROM "table" WHERE "table"."column" LIKE '%llo wor%'.

ilike operator - case insensitive pattern matching

The ilike operator performs case-insensitive pattern matching. Usage: import { ilike } from 'drizzle-orm'; db.select().from(table).where(ilike(table.column, '%llo wor%')) generates SELECT * FROM "table" WHERE "table"."column" ILIKE '%llo wor%'.

notIlike operator - case insensitive pattern not matching

The notIlike operator performs case-insensitive negative pattern matching. Usage: import { notIlike } from 'drizzle-orm'; db.select().from(table).where(notIlike(table.column, '%llo wor%')) generates SELECT * FROM "table" WHERE "table"."column" NOT ILIKE '%llo wor%'.

not operator - negate condition

The not operator negates a condition, all conditions must return false. Usage: import { eq, not } from 'drizzle-orm'; db.select().from(table).where(not(eq(table.column, 5))) generates SELECT * FROM "table" WHERE NOT ("table"."column" = 5).

and operator - all conditions must be true

The and operator combines conditions where all must return true. Usage: import { gt, lt, and } from 'drizzle-orm'; db.select().from(table).where(and(gt(table.column, 5), lt(table.column, 7))) generates SELECT * FROM "table" WHERE ("table"."column" > 5 AND "table"."column" < 7).

or operator - one or more conditions must be true

The or operator combines conditions where one or more must return true. Usage: import { gt, lt, or } from 'drizzle-orm'; db.select().from(table).where(or(gt(table.column, 5), lt(table.column, 7))) generates SELECT * FROM "table" WHERE ("table"."column" > 5 OR "table"."column" < 7).

arrayContained operator - list contains all elements of column

The arrayContained operator tests that the list passed as the second argument contains all elements of a column or expression. Usage: import { arrayContained } from 'drizzle-orm'; db.select({ id: posts.id }).from(posts).where(arrayContained(posts.tags, ['Typescript', 'ORM'])) generates select "id" from "posts" where "posts"."tags" <@ {Typescript,ORM}.

arrayOverlaps operator - column contains any elements

The arrayOverlaps operator tests that a column or expression contains any elements of the list passed as the second argument. Usage: import { arrayOverlaps } from 'drizzle-orm'; db.select({ id: posts.id }).from(posts).where(arrayOverlaps(posts.tags, ['Typescript', 'ORM'])) generates select "id" from "posts" where "posts"."tags" && {Typescript,ORM}.

Give your agent this brain