List records with partial columns
Retrieve a collection of records with selected fields.
Drizzle ORM · Module
db.query
Drizzle’s db.query API provides type-safe relational queries for nested data. Initialize Drizzle with the schema’s tables and relations, then use methods such as findMany and findFirst with with and columns options.
How it works
The relational query API is available through db.query after tables and relations are supplied during drizzle() initialization. Use findMany or findFirst with options such as columns to control selected fields and with to load nested related records; Drizzle builds the relational result for you.
Syntax
db.query.<table>.findMany(config?)
db.query.<table>.findFirst(config?)Parameters
| Name | Type | Description |
|---|---|---|
config | object | Query configuration, including relation inclusion and selected columns. |
with | object | Relations to include in nested results. |
columns | object | Columns to include or omit from the result. |
Returns
Typed query results, optionally containing nested related records.
Key points
- The API is designed for convenient retrieval of nested relational data.
- Relational queries build on the schema and existing query-builder model rather than replacing them.
- After initialization, access relational queries through
db.query. - The main entry points are methods for querying many records or the first matching record.
- Use
withto include and aggregate related table data in nested results. - Use
columnsto narrow or exclude selected fields.
Common mistakes
- The database instance must be initialized with every relevant table and relation before using
db.query. findFirstrestricts the query to one row, so usefindManywhen multiple records are expected.
Example: Query users with related posts
import { drizzle } from "drizzle-orm/node-postgres";
import { users, posts } from "./schema";
import { relations } from "./relations";
const db = drizzle(process.env.DATABASE_URL!, { relations });
const result = await db.query.users.findMany({
columns: { id: true, name: true },
with: { posts: true },
});
console.log(result);Initialize Drizzle with the schema relations, then use the relational query API to select user columns and include related posts.
When to use it
Find the first record
Fetch one matching record efficiently.
Load relational data
Return parent records together with nested related data.
Interview questions
QWhat is the purpose of db.query?
db.query is Drizzle’s relational query API for retrieving nested relational data. It extends the query builder and uses declared schema relations to map related records into the result.
Follow-up: How can you avoid selecting every column? Use the columns option to include or omit specific fields.
QHow do findMany and findFirst differ?
findMany returns multiple matching records, while findFirst is intended to return one record. The latter adds limit 1 to the generated query.
Follow-up: How would you load related rows? Add a with configuration describing the relation to include.
QHow are relations included in a relational query?
The with operator combines data from related tables and aggregates it into nested results. This lets the relational API return connected data without manually assembling join results.
Follow-up: What controls the fields on the primary table? The columns option specifies which columns are included or omitted.
Related
findManyfindFirstwithcolumnsrelationsquery builder