ContextQMD
Back to Drizzle Orm

Filter and conditional operators

src/content/docs/operators.mdx

latest10.9 KB
Original Source

import IsSupportedChipGroup from '@mdx/IsSupportedChipGroup.astro'; import Section from '@mdx/Section.astro';

Filter and conditional operators

We natively support all dialect specific filter and conditional operators.

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

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

eq

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value equal to n

<Section> ```typescript copy import { eq } from "drizzle-orm";

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


```sql copy
SELECT * FROM table WHERE table.column = 5
</Section> <Section> ```typescript import { eq } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column1 = table.column2
</Section>

ne

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is not equal to n

<Section> ```typescript import { ne } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column <> 5
</Section> <Section> ```typescript import { ne } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column1 <> table.column2
</Section>

---

gt

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is greater than n

<Section> ```typescript import { gt } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column > 5
</Section> <Section> ```typescript import { gt } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column1 > table.column2
</Section>

gte

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is greater than or equal to n

<Section> ```typescript import { gte } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column >= 5
</Section> <Section> ```typescript import { gte } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column1 >= table.column2
</Section>

lt

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is less than n

<Section> ```typescript import { lt } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column < 5
</Section> <Section> ```typescript import { lt } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column1 < table.column2
</Section>

lte

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is less than or equal to n.

<Section> ```typescript import { lte } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column <= 5
</Section> <Section> ```typescript import { lte } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column1 <= table.column2
</Section>

---

exists

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value exists

<Section> ```typescript import { exists } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE EXISTS (SELECT * from table2)
</Section>

notExists

<Section> ```typescript import { notExists } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE NOT EXISTS (SELECT * from table2)
</Section>

isNull

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is null

<Section> ```typescript import { isNull } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column IS NULL
</Section>

isNotNull

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is not null

<Section> ```typescript import { isNotNull } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column IS NOT NULL
</Section>

---

inArray

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is in array of values

<Section> ```typescript import { inArray } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column in (1, 2, 3, 4)
</Section> <Section> ```typescript import { inArray } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column IN (SELECT table2.column FROM table2)
</Section>

notInArray

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is not in array of values

<Section> ```typescript import { notInArray } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column NOT in (1, 2, 3, 4)
</Section> <Section> ```typescript import { notInArray } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column NOT IN (SELECT table2.column FROM table2)
</Section>

---

between

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is between two values

<Section> ```typescript import { between } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column BETWEEN 2 AND 7
</Section>

notBetween

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is not between two value

<Section> ```typescript import { notBetween } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column NOT BETWEEN 2 AND 7
</Section>

---

like

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is like other value, case sensitive

<Section> ```typescript import { like } from "drizzle-orm";

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


```sql
SELECT * FROM table  WHERE table.column LIKE '%llo wor%'
</Section>

ilike

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': false, 'SQLite': false, 'SingleStore': false }} />

Value is like some other value, case insensitive

<Section> ```typescript import { ilike } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column ILIKE '%llo wor%'
</Section>

notIlike

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

Value is not like some other value, case insensitive

<Section> ```typescript import { notIlike } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE table.column NOT ILIKE '%llo wor%'
</Section>

---

not

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

All conditions must return false.

<Section> ```typescript import { eq, not } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE NOT (table.column = 5)
</Section>

and

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

All conditions must return true.

<Section> ```typescript import { gt, lt, and } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE (table.column > 5 AND table.column < 7)
</Section>

or

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': true, 'SQLite': true, 'SingleStore': true }} />

One or more conditions must return true.

<Section> ```typescript import { gt, lt, or } from "drizzle-orm";

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


```sql
SELECT * FROM table WHERE (table.column > 5 OR table.column < 7)
</Section>

---

arrayContains

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': false, 'SQLite': false, 'SingleStore': false }} />

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

<Section> ```typescript 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)), ));


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

arrayContained

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': false, 'SQLite': false, 'SingleStore': false }} />

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

<Section> ```typescript import { arrayContained } from "drizzle-orm";

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


```sql
select "id" from "posts" where "posts"."tags" <@ {Typescript,ORM};
</Section>

arrayOverlaps

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': false, 'SQLite': false, 'SingleStore': false }} />

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

<Section> ```typescript import { arrayOverlaps } from "drizzle-orm";

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


```sql
select "id" from "posts" where "posts"."tags" && {Typescript,ORM}
</Section>