drizzle-orm/operators

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

eq operator - equal comparison

The eq operator tests if a value is equal to n. It can compare a column to a literal value or two columns to each other. Import from drizzle-orm. Example: db.select().from(table).where(eq(table.column, 5)) generates SELECT * FROM table WHERE table.column = 5. Also: db.select().from(table).where(eq(table.column1, table.column2)) generates SELECT * FROM table WHERE table.column1 = table.column2.

ne operator - not equal comparison

The ne operator tests if a value is not equal to n. It can compare a column to a literal value or two columns to each other. Import from drizzle-orm. Example: db.select().from(table).where(ne(table.column, 5)) generates SELECT * FROM table WHERE table.column <> 5. Also: db.select().from(table).where(ne(table.column1, table.column2)) generates SELECT * FROM table WHERE table.column1 <> table.column2.

gte operator - greater than or equal comparison

The gte operator tests if a value is greater than or equal to n. It can compare a column to a literal value or two columns to each other. Import from drizzle-orm. Example: db.select().from(table).where(gte(table.column, 5)) generates SELECT * FROM table WHERE table.column >= 5. Also: db.select().from(table).where(gte(table.column1, table.column2)) generates SELECT * FROM table WHERE table.column1 >= table.column2.

lte operator - less than or equal comparison

The lte operator tests if a value is less than or equal to n. It can compare a column to a literal value or two columns to each other. Import from drizzle-orm. Example: db.select().from(table).where(lte(table.column, 5)) generates SELECT * FROM table WHERE table.column <= 5. Also: db.select().from(table).where(lte(table.column1, table.column2)) generates SELECT * FROM table WHERE table.column1 <= table.column2.

exists operator - subquery existence check

The exists operator checks if a subquery returns any rows. Import from drizzle-orm. Example: const query = db.select().from(table2); db.select().from(table).where(exists(query)) generates SELECT * FROM table WHERE EXISTS (SELECT * from table2).

notExists operator - subquery non-existence check

The notExists operator checks if a subquery returns no rows. Import from drizzle-orm. Example: const query = db.select().from(table2); db.select().from(table).where(notExists(query)) generates SELECT * FROM table WHERE NOT EXISTS (SELECT * from table2).

isNotNull operator - non-null value check

The isNotNull operator tests if a value is not null. Import from drizzle-orm. Example: db.select().from(table).where(isNotNull(table.column)) generates SELECT * FROM table WHERE table.column IS NOT NULL.

inArray operator - membership in array or subquery

The inArray operator checks if a value is in an array of values or matches results of a subquery. Import from drizzle-orm. Example with array: db.select().from(table).where(inArray(table.column, [1, 2, 3, 4])) generates SELECT * FROM table WHERE table.column in (1, 2, 3, 4). Example with subquery: const query = db.select({ data: table2.column }).from(table2); db.select().from(table).where(inArray(table.column, query)) generates SELECT * FROM table WHERE table.column IN (SELECT table2.column FROM table2).

notInArray operator - non-membership in array or subquery

The notInArray operator checks if a value is not in an array of values or does not match results of a subquery. Import from drizzle-orm. Example with array: 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). Example with subquery: const query = db.select({ data: table2.column }).from(table2); db.select().from(table).where(notInArray(table.column, query)) generates SELECT * FROM table WHERE table.column NOT IN (SELECT table2.column FROM table2).

between operator - range check

The between operator checks if a value is between two values inclusive. Import from drizzle-orm. Example: db.select().from(table).where(between(table.column, 2, 7)) generates SELECT * FROM table WHERE table.column BETWEEN 2 AND 7.

notBetween operator - range exclusion check

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

like operator - pattern matching case sensitive

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

notIlike operator - pattern non-matching case insensitive

The notIlike operator performs case-insensitive pattern non-matching. Import from drizzle-orm. Example: db.select().from(table).where(notIlike(table.column, "%llo wor%")) generates SELECT * FROM table WHERE table.column NOT ILIKE '%llo wor%'.

not operator - logical negation

The not operator negates a condition so that all conditions must return false. Import from drizzle-orm. Example: db.select().from(table).where(not(eq(table.column, 5))) generates SELECT * FROM table WHERE NOT (table.column = 5).

and operator - logical AND

The and operator combines multiple conditions so that all conditions must return true. Import from drizzle-orm. Example: 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 - logical OR

The or operator combines multiple conditions so that one or more conditions must return true. Import from drizzle-orm. Example: 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).

Drizzle ORM filter operators import

All filter and conditional operators are available from the drizzle-orm package. They include eq, ne, gt, gte, lt, lte, exists, notExists, isNull, isNotNull, inArray, notInArray, between, notBetween, like, notIlike, not, and, and or.

Import operators for queries

Import eq and not from drizzle-orm to use comparison operators in WHERE clauses. Example: import { eq, not } from "drizzle-orm";

Select with between filter for date range

Use the between() operator with sql`now() - interval '1 day'` and sql`now()` to filter records within a date range, such as posts created in the last 24 hours.

Give your agent this brain