SQLite Query Builders

Select Query Builder

The select query builder provides a fluent API for constructing SELECT queries.

from()

Specifies the table or subquery to select from.

from<TFrom extends SQLiteTable | Subquery | SQLiteViewBase | SQL>(
  source: TFrom
): SQLiteSelectQuery

const result = await db.select().from(users);

where()

Adds a WHERE clause to filter results.

where(condition: SQL | undefined): this

import { eq } from 'drizzle-orm';

const result = await db
  .select()
  .from(users)
  .where(eq(users.id, 1));

orderBy()

Orders results by one or more columns.

orderBy(...columns: (SQLiteColumn | SQL)[]): this

import { asc } from 'drizzle-orm';

const result = await db
  .select()
  .from(users)
  .orderBy(users.name);
// or explicitly:
  .orderBy(asc(users.name));

limit()

Limits the number of rows returned.

limit(limit: number | Placeholder): this

const result = await db
  .select()
  .from(users)
  .limit(10);

offset()

Skips a specified number of rows.

offset(offset: number | Placeholder): this

const result = await db
  .select()
  .from(users)
  .limit(10)
  .offset(20);

groupBy()

Groups results by one or more columns.

groupBy(...columns: (SQLiteColumn | SQL)[]): this

import { count } from 'drizzle-orm';

const result = await db
  .select({
    role: users.role,
    count: count(),
  })
  .from(users)
  .groupBy(users.role);

having()

Filters grouped results (use with groupBy).

having(condition: SQL | undefined): this

import { count, gt } from 'drizzle-orm';

const result = await db
  .select({
    role: users.role,
    count: count(),
  })
  .from(users)
  .groupBy(users.role)
  .having(gt(count(), 5));

Joins

leftJoin()

Performs a LEFT JOIN.

leftJoin<TJoinedTable>(
  table: TJoinedTable,
  on: SQL | undefined
): this

const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

rightJoin()

Performs a RIGHT JOIN.

rightJoin<TJoinedTable>(
  table: TJoinedTable,
  on: SQL | undefined
): this

const result = await db
  .select()
  .from(users)
  .rightJoin(posts, eq(users.id, posts.authorId));

innerJoin()

Performs an INNER JOIN.

innerJoin<TJoinedTable>(
  table: TJoinedTable,
  on: SQL | undefined
): this

const result = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

fullJoin()

Performs a FULL JOIN.

fullJoin<TJoinedTable>(
  table: TJoinedTable,
  on: SQL | undefined
): this

const result = await db
  .select()
  .from(users)
  .fullJoin(posts, eq(users.id, posts.authorId));

Selecting from Joins

const result = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Set Operations

union()

Combines results from multiple queries (removes duplicates).

union<T extends AnySQLiteSelect>(other: T): SQLiteSetOperator

const activeUsers = db.select().from(users).where(eq(users.active, true));
const adminUsers = db.select().from(users).where(eq(users.role, 'admin'));

const result = await activeUsers.union(adminUsers);

unionAll()

Combines results from multiple queries (keeps duplicates).

unionAll<T extends AnySQLiteSelect>(other: T): SQLiteSetOperator

const result = await query1.unionAll(query2);

intersect()

Returns rows that appear in both queries.

intersect<T extends AnySQLiteSelect>(other: T): SQLiteSetOperator

const result = await query1.intersect(query2);

except()

Returns rows from the first query that don’t appear in the second.

except<T extends AnySQLiteSelect>(other: T): SQLiteSetOperator

const allUsers = db.select().from(users);
const inactiveUsers = db.select().from(users).where(eq(users.active, false));

const activeUsers = await allUsers.except(inactiveUsers);

Insert Query Builder

values()

Specifies the values to insert.

values(
  value: SQLiteInsertValue<TTable>
): SQLiteInsertBase
values(
  values: SQLiteInsertValue<TTable>[]
): SQLiteInsertBase

await db.insert(users).values({
  name: 'Alice',
  email: '[email protected]',
});

select()

Inserts rows from a SELECT query.

select(selectQuery: SQLiteSelectQuery): SQLiteInsertBase

await db.insert(usersCopy)
  .select(
    db.select().from(users).where(eq(users.active, true))
  );

onConflictDoNothing()

Ignores conflicts (SQLite: INSERT OR IGNORE).

onConflictDoNothing(config?: { target?: IndexColumn | IndexColumn[] }): this

await db.insert(users)
  .values({ id: 1, name: 'Alice' })
  .onConflictDoNothing();

onConflictDoUpdate()

Updates on conflict (SQLite: INSERT OR REPLACE).

onConflictDoUpdate(config: {
  target: IndexColumn | IndexColumn[];
  set: SQLiteUpdateSetSource<TTable>;
  where?: SQL;
  targetWhere?: SQL;
  setWhere?: SQL;
}): this

import { sql } from 'drizzle-orm';

await db.insert(users)
  .values({ email: '[email protected]', name: 'Alice' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: sql`excluded.name` },
  });

returning()

Returns inserted rows.

returning(): SQLiteInsert
returning<TSelectedFields>(
  fields: TSelectedFields
): SQLiteInsert

const result = await db.insert(users)
  .values({ name: 'Alice' })
  .returning();
// [{ id: 1, name: 'Alice', email: null, ... }]

Update Query Builder

set()

Specifies the values to update.

set(values: SQLiteUpdateSetSource<TTable>): this

await db.update(users)
  .set({ name: 'Alice Updated' })
  .where(eq(users.id, 1));

where()

Filters which rows to update.

where(condition: SQL | undefined): this

await db.update(users)
  .set({ active: false })
  .where(eq(users.email, '[email protected]'));

returning()

Returns updated rows.

returning(): SQLiteUpdate
returning<TSelectedFields>(
  fields: TSelectedFields
): SQLiteUpdate

const result = await db.update(users)
  .set({ active: true })
  .where(eq(users.id, 1))
  .returning({ id: users.id, active: users.active });

from()

Updates using joins with other tables.

from<TFrom extends SQLiteTable | Subquery | SQLiteViewBase | SQL>(
  source: TFrom
): this

await db.update(users)
  .set({ role: 'premium' })
  .from(subscriptions)
  .where(and(
    eq(users.id, subscriptions.userId),
    eq(subscriptions.status, 'active')
  ));

Delete Query Builder

where()

Filters which rows to delete.

where(condition: SQL | undefined): this

await db.delete(users)
  .where(eq(users.id, 1));

returning()

Returns deleted rows.

returning(): SQLiteDelete
returning<TSelectedFields>(
  fields: TSelectedFields
): SQLiteDelete

const deleted = await db.delete(users)
  .where(eq(users.id, 1))
  .returning();
console.log('Deleted user:', deleted[0]);

orderBy()

Orders rows before deletion (used with LIMIT).

orderBy(...columns: (SQLiteColumn | SQL)[]): this

import { desc } from 'drizzle-orm';

// Delete oldest 10 inactive users
await db.delete(users)
  .where(eq(users.active, false))
  .orderBy(users.createdAt)
  .limit(10);

limit()

Limits the number of rows to delete.

limit(limit: number | Placeholder): this

// Delete up to 100 old records
await db.delete(logs)
  .where(lt(logs.createdAt, thirtyDaysAgo))
  .limit(100);

Prepared Statements

All query builders support prepared statements for better performance.

prepare()

Creates a prepared statement.

prepare(): SQLitePreparedQuery

const prepared = db
  .select()
  .from(users)
  .where(eq(users.id, placeholder('id')))
  .prepare();

const user1 = await prepared.get({ id: 1 });
const user2 = await prepared.get({ id: 2 });

Dynamic Queries

Create conditional queries at runtime.

$dynamic()

Enables dynamic mode for conditional query building.

$dynamic(): this

let query = db.select().from(users).$dynamic();

if (filters.role) {
  query = query.where(eq(users.role, filters.role));
}

if (filters.minAge) {
  query = query.where(gte(users.age, filters.minAge));
}

if (orderBy === 'name') {
  query = query.orderBy(users.name);
} else if (orderBy === 'age') {
  query = query.orderBy(users.age);
}

const result = await query;

PostgreSQL

MySQL

SQLite

Core APIs

Drizzle Kit

​Select Query Builder

​from()

​where()

​orderBy()

​limit()

​offset()

​groupBy()

​having()

​Joins

​leftJoin()

​rightJoin()

​innerJoin()

​fullJoin()

​Selecting from Joins

​Set Operations

​union()

​unionAll()

​intersect()

​except()

​Insert Query Builder

​values()

​select()

​onConflictDoNothing()

​onConflictDoUpdate()

​returning()

​Update Query Builder

​set()

​where()

​returning()

​from()

​Delete Query Builder

​where()

​returning()

​orderBy()

​limit()

​Prepared Statements

​prepare()

​Dynamic Queries

​$dynamic()

Build docs developers (and LLMs) love

Select Query Builder

from()

where()

orderBy()

limit()

offset()

groupBy()

having()

Joins

leftJoin()

rightJoin()

innerJoin()

fullJoin()

Selecting from Joins

Set Operations

union()

unionAll()

intersect()

except()

Insert Query Builder

values()

select()

onConflictDoNothing()

onConflictDoUpdate()

returning()

Update Query Builder

set()

where()

returning()

from()

Delete Query Builder

where()

returning()

orderBy()

limit()

Prepared Statements

prepare()

Dynamic Queries

$dynamic()