Overview
Query methods retrieve data from your database. All methods return promises that resolve to formatted results based on your model’s format function.
findMany
Fetch all rows matching the current query conditions.
Syntax
model.findMany() => ModelQueryResult<T[]>
Return Type
- Returns:
Promise<T[]> where T is your table row type after formatting
- Empty array if no rows match
Examples
Basic
With Filter
With Relations
Selected Fields
const users = await userModel.findMany();
// Returns all users
const verified = await userModel
.where({ isVerified: esc(true) })
.findMany();
const usersWithPosts = await userModel
.findMany()
.with({ posts: true });
const users = await userModel
.findMany()
.select({ id: true, name: true });
// Only fetches id and name columns
findFirst
Fetch the first row matching the current query conditions.
Syntax
model.findFirst() => ModelQueryResult<T | undefined>
Return Type
- Returns:
Promise<T | undefined> where T is your table row type
undefined if no row matches
Examples
Basic
With Filter
With Relations
Safe Execution
const user = await userModel.findFirst();
// Returns first user or undefined
if (user) {
console.log(user.name);
}
const user = await userModel
.where({ id: esc(1) })
.findFirst();
const user = await userModel
.findFirst()
.with({ posts: true, invitee: true });
// user.posts and user.invitee are loaded
const result = await userModel
.where({ id: esc(999) })
.findFirst()
.safe();
if (result.error) {
console.error(result.error);
} else {
console.log(result.data); // T | undefined
}
count
Count rows matching the current query conditions.
Syntax
model.count() => Promise<number>
Return Type
- Returns:
Promise<number>
- Returns
0 if no rows match
Examples
Total Count
Filtered Count
Multiple Conditions
const total = await userModel.count();
// Total number of users
const verified = await userModel
.where({ isVerified: esc(true) })
.count();
const adults = await userModel
.where({
age: esc.gte(18),
isVerified: esc(true)
})
.count();
where
Add filter conditions to your query. Can be chained multiple times (conditions are combined with AND).
Syntax
model.where(conditions) => Model
Parameters
Object mapping column names to values or operator expressions.All literal values must be wrapped with esc() or use esc.method() chainable operators.
Where Clause Operators
Equality (Implicit)
{ name: esc("Alex") }
// WHERE name = 'Alex'
Comparison Operators
{ age: esc.gt(18) } // age > 18
{ age: esc.gte(18) } // age >= 18
{ age: esc.lt(65) } // age < 65
{ age: esc.lte(65) } // age <= 65
{ status: esc.not("banned") } // status != 'banned'
Pattern Matching
{ name: esc.like("%Alex%") } // LIKE '%Alex%'
{ email: esc.ilike("%@GMAIL.COM") } // ILIKE (case-insensitive)
Array Operators
{ status: esc.in(["active", "pending"]) } // IN (...)
{ role: esc.nin(["banned", "suspended"]) } // NOT IN (...)
Range Operators
{ age: esc.between(18, 65) } // BETWEEN 18 AND 65
{ price: esc.notBetween(0, 10) } // NOT BETWEEN 0 AND 10
Null Checks
{ deletedAt: { isNull: true } } // IS NULL
Multiple Conditions (AND)
.where({
age: esc.gte(18),
isVerified: esc(true),
status: esc.in(["active", "premium"])
})
// WHERE age >= 18 AND is_verified = true AND status IN ('active', 'premium')
Logical OR
.where({
email: {
or: [
esc.like("%@gmail.com"),
esc.like("%@yahoo.com")
]
}
})
// WHERE email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com'
Examples
Simple Filter
Multiple Conditions
Chained Where
Complex Filters
Using Drizzle Operators
Raw SQL
const users = await userModel
.where({ name: esc("Alex") })
.findMany();
const users = await userModel
.where({
age: esc.gte(18),
isVerified: esc(true)
})
.findMany();
const users = await userModel
.where({ isVerified: esc(true) })
.where({ age: esc.gte(18) })
.findMany();
// Both conditions are combined with AND
const users = await userModel
.where({
age: esc.between(18, 65),
status: esc.in(["active", "premium"]),
email: esc.like("%@company.com")
})
.findMany();
import { gte } from "drizzle-orm";
const users = await userModel
.where({ age: esc(gte, 18) })
.findMany();
import { sql } from "drizzle-orm";
const users = await userModel
.where(sql`age > 18 AND status = 'active'`)
.findMany();
Return Value
Returns a new model instance with the where clause applied. The original model is unchanged.
const base = userModel;
const filtered = base.where({ isVerified: esc(true) });
// base is unmodified
await base.count(); // All users
// filtered includes the condition
await filtered.count(); // Only verified users
Chaining Queries
All query methods support method chaining with refinement methods:
const users = await userModel
.where({ isVerified: esc(true) })
.findMany()
.with({ posts: true })
.select({ id: true, name: true })
.exclude({ email: true });
Type Safety
All query methods preserve full type information:
const user = await userModel.findFirst();
// user: User | undefined
const users = await userModel.findMany();
// users: User[]
const count = await userModel.count();
// count: number
Notes
Why esc()? Explicit escaping prevents accidental plain values in where clauses, ensuring type safety and preventing SQL injection.
The where() method returns a new model instance. Always chain or assign the result.// ❌ Wrong - discards the result
userModel.where({ id: esc(1) });
await userModel.findFirst(); // No filter applied!
// ✅ Correct
await userModel.where({ id: esc(1) }).findFirst();