Drizzle ORM is designed to be a thin typed layer on top of SQL.
We truly believe we’ve designed the best way to operate an SQL database from TypeScript and it’s time to make it better.
Relational queries are meant to provide you with a great developer experience for querying
nested relational data from an SQL database, avoiding multiple joins and complex data mappings.
It is an extension to the existing schema definition and query builder.
You can opt-in to use it based on your needs.
We’ve made sure you have both the best-in-class developer experience and performance.
Important
Inside relational queries, references to a table’s columns must go through the callback parameter, not through the imported table object.
This applies to every clause that accepts a callback — orderBy, where.RAW, extras and subqueries inside extras.
The callback exposes the aliased table for the current query scope, which is required for correct SQL generation in nested or self-referential queries.
Relational queries are an extension to Drizzle’s original query builder.
You need to provide all tables and relations from your schema file/files upon drizzle()
initialization and then just use the db.query API.
drizzle import path depends on the database driver you’re using.
index.ts
schema.ts
relations.ts
import { relations } from './relations';import { drizzle } from 'drizzle-orm/...';const db = drizzle(process.env.DATABASE_URL,{ relations });await db.query.users.findMany(...);
select "d0"."id" as "id", "d0"."name" as "name", "d0"."age" as "age"from "users" as "d0"where ( (not ("d0"."id" > 10)) and ("d0"."name" like 'John%') )
// schema.tsimport { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";import { sql } from "drizzle-orm";export const users = sqliteTable("users", { id: integer().primaryKey(), name: text(), email: text().notNull(), age: integer(), createdAt: text("created_at"), lastLogin: text("last_login"), subscriptionEnd: text("subscription_end"), lastActivity: text("last_activity"), preferences: text("preferences", { mode: "json" }), // JSON column for user settings/preferences interests: text("interests"), // Comma-separated user interests});
SELECT "d0"."id" AS "id", "d0"."name" AS "name", "d0"."email" AS "email", "d0"."age" AS "age", "d0"."created_at" AS "createdAt", "d0"."last_login" AS "lastLogin", "d0"."subscription_end" AS "subscriptionEnd", "d0"."last_activity" AS "lastActivity", "d0"."preferences" AS "preferences", "d0"."interests" AS "interests"FROM "users" AS "d0"WHERE ( ( ( (LOWER("d0"."name") LIKE 'john%') OR ("d0"."name" LIKE 'jane%') ) ) AND ("d0"."age" BETWEEN 25 AND 35) )
Relations Filters
With Drizzle Relations, you can filter not only by the table you’re querying but also by any table you include in the query.
Example: Get all users whose ID>10 and who have at least one post with content starting with “M”
Relational query API lets you add custom additional fields.
It’s useful when you need to retrieve data and apply additional functions to it.
IMPORTANT
Aggregate functions over the rows of the queried table, such as count(*) or sum(...), are not supported in extras.
Relational queries do not add a GROUP BY clause.
To count or sum related rows, use a subquery in extras, for example db.$count(...). See Include subqueries.
For grouped results, use core queries.
lowerName as a key will be included to all fields in returned object.
IMPORTANT
If you will specify .as("<alias>") for any extras field - drizzle will ignore it
To retrieve all users with groups, but with the fullName field included (which is a concatenation of firstName and lastName),
you can use the following query with the Drizzle relational query builder.
SELECT "d0"."id" AS "id", "d0"."name" AS "name", "d0"."age" AS "age", "d0"."invited_by" AS "invitedBy", ( ( SELECT count(*) FROM "posts" WHERE "posts"."author_id" = "d0"."id" ) ) AS "totalPostsCount", coalesce( ( SELECT json_group_array(json_object('id', "id", 'content', "content", 'authorId', "authorId", 'createdAt', "createdAt")) AS "r" FROM ( SELECT "d1"."id" AS "id", "d1"."content" AS "content", "d1"."author_id" AS "authorId", "d1"."created_at" AS "createdAt" FROM "posts" AS "d1" WHERE "d0"."id" = "d1"."author_id" ) AS "t" ), jsonb_array () ) AS "posts"FROM "users" AS "d0"
Prepared statements
Prepared statements are designed to massively improve query performance — see here.
In this section, you can learn how to define placeholders and execute prepared statements
using the Drizzle relational query builder.