Query where is now object v2
In v2, where in relational queries is now an object syntax instead of a function: db.query.users.findMany({ where: { id: 1 } }) instead of db._query.users.findMany({ where: (users, { eq }) => eq(users.id, 1) }).
relational queries
10 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
Query orderBy is now object v2
In v2, orderBy in relational queries is now an object syntax instead of a function: db.query.users.findMany({ orderBy: { id: "asc" } }) instead of db._query.users.findMany({ orderBy: (users, { asc }) => [asc(users.id)] }).
Filtering by relations in v2
In v2, you can filter by related entity properties directly in the where clause: db.query.usersTable.findMany({ where: { id: { gt: 10 }, posts: { content: { like: 'M%' } } } }).
limit and offset with relational queries v2
Both limit and offset can be used on the main query and on related objects within the with clause: await db.query.posts.findMany({ limit: 5, offset: 2, with: { comments: { offset: 3, limit: 3 } } }).
where operators AND OR NOT RAW
In v2 relational queries, filter objects support AND, OR, NOT, and RAW operators along with comparison operators. AND is implicit when multiple properties are specified at the same level. RAW accepts a function that receives the table and returns SQL: { RAW: (table) => sql`${table.name} LIKE 'john%'` }.
Complex filter example with AND OR NOT RAW
Example of complex where filter in v2: db.query.users.findMany({ where: { AND: [{ OR: [{ RAW: (table) => sql`${table.name} LIKE 'john%'` }, { name: { like: "jane%" } }] }, { RAW: (table) => sql`${table.age} BETWEEN 25 AND 35` }] } }).
Relational queries with 'with' clause
Relational queries use the 'with' object to load related data. The syntax is db.query.tableName.findMany({ with: { relationKey: true } }). Related data is automatically joined and included in the result.
Many-to-many query example
Query: const res = await db.query.users.findMany({ with: { groups: true } }). Returns type Response = { id: number; name: string | null; groups: { id: number; name: string | null }[] }[].
Drizzle always outputs exactly one SQL query
When using Drizzle queries, the ORM always outputs exactly one SQL query. This makes it safe to use with serverless databases without worrying about performance or roundtrip costs.
Relational query with nested data example
Example of relational syntax for fetching nested data: `const result = await db.query.users.findMany({ with: { posts: true } });` fetches users with their related posts.