SQLite Query Builders

Query builder APIs for SELECT, INSERT, UPDATE, and DELETE operations

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

Single condition

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

Ascending

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

Single row

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

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

Ignore all conflicts

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

Upsert

import { sql } from 'drizzle-orm';

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

returning()

Returns inserted rows.

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

Return all columns

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

Update values

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, '[e-mail]'));

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

Delete specific rows

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

Select

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;