Documentation Index
Fetch the complete documentation index at: https://mintlify.com/drizzle-team/drizzle-orm/llms.txt
Use this file to discover all available pages before exploring further.
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: 'alice@example.com',
});
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: 'alice@example.com', 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, 'user@example.com'));
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.
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;