drizzle-orm/queries

120 notes in this subject, read out of this brain and free to use. This is page 2 of 2.

sql.raw() for unparameterized raw SQL

Use sql.raw() to include raw SQL statements without parameterization or escaping. It generates queries as-is without converting values to ? placeholders. Example: sql.raw(`select * from users where id = ${12}`) generates select * from users where id = 12. It can be used inside sql template to include unescaped raw strings.

sql.fromList() to concatenate SQL chunks

Use sql.fromList() to combine multiple SQL chunks into a single SQL statement. It accepts an array of SQL objects (SQL[]) and concatenates them according to your custom logic. Example: const finalSql = sql.fromList(sqlChunks) aggregates chunks into one query.

sql.join() to concatenate chunks with custom separator

Use sql.join() to concatenate SQL chunks with a specified separator string. This provides more flexibility than fromList when handling spaces or custom delimiters between chunks. Example: sql.join(sqlChunks, sql.raw(' ')) joins chunks with space separator.

sql.append() to add chunks to existing SQL

Use sql.append() to dynamically add new SQL chunks to an existing sql template object. This allows incremental SQL query construction with custom logic. Example: const finalSql = sql`select * from users`; finalSql.append(sql` where `);

sql.empty() to initialize blank SQL object

Use sql.empty() to start with a blank SQL object and then dynamically append SQL chunks using append(). This enables incremental SQL query construction with full access to sql template features like parameterization, composition, and escaping.

sql in partial select queries

The sql template can be used in partial select queries to retrieve specific custom fields. Combine sql<T>, sql.mapWith(), and sql.as() for type-safe field selection. Example: await db.select({ id: usersTable.id, lowerName: sql<string>`lower(${usersTable.name})`, aliasedName: sql<string>`lower(${usersTable.name})`.as('aliased_column'), count: sql<number>`count(*)`.mapWith(Number) }).from(usersTable)

sql in where clauses

Use the sql template in WHERE clauses to leverage database-specific expressions not natively supported by Drizzle. Example: await db.select().from(usersTable).where(sql`${usersTable.id} = ${id}`) or for advanced fulltext search: await db.select().from(usersTable).where(sql`lower(${usersTable.name}) like lower(${searchPattern})`)

sql in orderBy clauses

Use the sql template in ORDER BY clauses for specific ordering functionality not available in Drizzle. Example: await db.select().from(usersTable).orderBy(sql`${usersTable.id} desc nulls first`) generates order by `users`.`id` desc nulls first;

sql in having and groupBy clauses

Use the sql template in HAVING and GROUP BY clauses for database-specific functionality. Example: await db.select({ projectId: usersTable.projectId, count: sql<number>`count(${usersTable.id})`.mapWith(Number) }).from(usersTable).groupBy(sql`${usersTable.projectId}`).having(sql`count(${usersTable.id}) > 300`) generates select `project_id`, count(`users`.`id`) from users group by `users`.`project_id` having count(`users`.`id`) > 300;

sql prevents SQL injection through parameterization

The sql template automatically prevents SQL injection by converting dynamic parameters to ? placeholders and passing values separately to the database. Tables and columns are automatically escaped.

Update with orderBy examples

import { asc, desc } from 'drizzle-orm'; await db.update(usersTable).set({ verified: true }).orderBy(usersTable.name); await db.update(usersTable).set({ verified: true }).orderBy(desc(usersTable.name)); await db.update(usersTable).set({ verified: true }).orderBy(usersTable.name, usersTable.name2); await db.update(usersTable).set({ verified: true }).orderBy(asc(usersTable.name), desc(usersTable.name2));

Undefined values ignored in update set object

When passing an object to set(), values of undefined are ignored. To explicitly set a column to null, pass null instead of undefined.

Basic update query syntax

To update records in a table, use db.update(table).set(values).where(condition). The set() method takes an object where keys must match column names in the database schema.

Using SQL functions in update

SQL expressions can be passed as values in the update set object using the sql template function, for example: .set({ updatedAt: sql`NOW()` }).

Update with limit clause

The limit() method can be chained to an update query to limit the number of rows affected: db.update(table).set(values).limit(n).

Update with orderBy clause

The orderBy() method can be chained to an update query to sort rows before updating. Multiple fields can be specified as arguments. Use asc() or desc() functions to control sort direction: db.update(table).set(values).orderBy(asc(column1), desc(column2)).

Basic update example

await db.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan'));

Update with SQL expression example

await db.update(users).set({ updatedAt: sql`NOW()` }).where(eq(users.name, 'Dan'));

Update with limit example

await db.update(usersTable).set({ verified: true }).limit(2);

createUpdateSchema function for SingleStore

The createUpdateSchema function from drizzle-orm/valibot generates a Valibot schema that validates the shape of data to be updated in the database. It can be used to validate API requests. Generated columns and primary key columns cannot be updated. All updatable columns become optional in the update schema.

Delete a record by id using eq operator

Use db.delete(todo).where(eq(todo.id, id)) to delete a record where the id matches. The eq operator from drizzle-orm is used to create a WHERE clause comparing todo.id to the provided id value.

Update a record using set and where clauses

Use db.update(todo).set({ done: not(todo.done) }).where(eq(todo.id, id)) to update a record. The set method defines which columns to update and their new values. The where clause filters which records to update. The not operator negates a boolean value.

PostgreSQL table and column schema definition

Define PostgreSQL tables using pgTable from drizzle-orm/pg-core. Example: export const todo = pgTable("todo", { id: integer("id").primaryKey(), text: text("text").notNull(), done: boolean("done").default(false).notNull() }); This creates a todo table with an integer primary key id, a required text column, and a boolean done column that defaults to false.

Select all records from a table

Use db.select().from(todo) to fetch all records from a table. This returns a promise that resolves to an array of records. Example: const data = await db.select().from(todo);

Using Drizzle ORM in Encore API endpoints

Implement Drizzle queries within Encore API endpoints. Import the orm instance from database.ts and schema tables. Use orm.select().from(users) for reading data and orm.insert(users).values({...}).returning() for creating records. Endpoints can return query results directly.

Querying users table with Drizzle in HTTP server

Example of querying all users from a PostgreSQL table using Drizzle in a Bun HTTP server: ```typescript import { drizzle } from "drizzle-orm/node-postgres"; import { migrate } from "drizzle-orm/node-postgres/migrator"; import { usersTable } from "./schema"; const db = drizzle(process.env.DATABASE_URL!); await migrate(db, { migrationsFolder: "./migrations" }); const server = Bun.serve({ port: process.env.PORT || 3000, async fetch(req) { const url = new URL(req.url); if (url.pathname === "/users") { const users = await db.select().from(usersTable); return new Response(JSON.stringify(users), { headers: { "Content-Type": "application/json" }, }); } return new Response("OK"); }, }); console.log(`Server running on port ${server.port}`); ```

Select with aggregation and grouping

Use db.select() with getColumns() to select all columns from a table, combine with count() to include aggregate functions, use leftJoin() for left joins, groupBy() to group results, and limit() and offset() for pagination.

Timestamp with default current time in Postgres

Use timestamp('column_name').notNull().defaultNow() to create a timestamp column that defaults to the current time when a row is inserted.

Timestamp with auto-update on modification

Use timestamp('column_name').notNull().$onUpdate(() => new Date()) to automatically update the timestamp column whenever the row is modified.

Delete data with Drizzle ORM

Use db.delete(tableName).where(condition) to delete records from a table. The condition is typically an equality check using eq() operator.

Update data with Drizzle ORM

Use db.update(tableName).set(data).where(condition) to update records in a table. The data parameter should be a partial object matching the table schema.

getColumns function for selecting all table columns

The getColumns() function is available starting from drizzle-orm@1.0.0-beta.2 and is used to select all columns from a table in a select query. For pre-1 versions like 0.45.1, use getTableColumns() instead.

Example insert and select in Supabase Edge Function

await db.insert(usersTable).values({ name: "Alice", age: 25 }) const data = await db.select().from(usersTable);

Update record with Drizzle

Use db.update(tableName).set(data).where(condition). For example: db.update(postsTable).set(data).where(eq(postsTable.id, id)) where data is Partial<Omit<SelectPost, 'id'>> to update a post excluding the id field.

Select single record by ID with Drizzle

Use db.select().from(tableName).where(eq(column, value)). For example, to get a user by ID: db.select().from(usersTable).where(eq(usersTable.id, id)) returns an array of matching records.

Select with date range filtering using SQL expressions

Use db.select() with where(between(column, sqlStart, sqlEnd)) to filter by date range. Use sql template for raw SQL expressions. Example: db.select().from(postsTable).where(between(postsTable.createdAt, sql`now() - interval '1 day'`, sql`now()`)) gets posts from the last 24 hours.

Delete record with Drizzle

Use db.delete(tableName).where(condition). For example: db.delete(usersTable).where(eq(usersTable.id, id)) to delete a user by ID.

Turso with Drizzle delete query example

Use `db.delete(table).where(condition)` to delete records from a Turso table.

Turso with Drizzle select query with count and join

Use `db.select()` with `getColumns()` helper to select all columns from a table, `count()` to aggregate, `leftJoin()` to join tables, `groupBy()` to group results, `orderBy()` to sort, `limit()` and `offset()` for pagination.

Turso with Drizzle update query example

Use `db.update(table).set(data).where(condition)` to update records in a Turso table, where `eq()` creates an equality condition.

Select with between and sql operators

Example select query filtering by date range: import { asc, between, sql } from 'drizzle-orm'; export async function getPostsForLast24Hours(page = 1, pageSize = 5) { return db.select({ id: postsTable.id, title: postsTable.title }).from(postsTable).where(between(postsTable.createdAt, sql`now() - interval '1 day'`, sql`now()`)).orderBy(asc(postsTable.title), asc(postsTable.id)).limit(pageSize).offset((page - 1) * pageSize); }

Update data with Drizzle ORM

Example update operation: import { eq } from 'drizzle-orm'; import { db } from '../index'; import { SelectPost, postsTable } from '../schema'; export async function updatePost(id: SelectPost['id'], data: Partial<Omit<SelectPost, 'id'>>) { await db.update(postsTable).set(data).where(eq(postsTable.id, id)); }

Delete data with Drizzle ORM

Example delete operation: import { eq } from 'drizzle-orm'; import { db } from '../index'; import { SelectUser, usersTable } from '../schema'; export async function deleteUser(id: SelectUser['id']) { await db.delete(usersTable).where(eq(usersTable.id, id)); }

Timestamp columns with defaultNow and onUpdate

Use .defaultNow() to set a column to default to the current timestamp when created, and .$onUpdate(() => new Date()) to automatically update the timestamp on record updates: createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().$onUpdate(() => new Date()).

PostgreSQL schema example with users and posts

Example schema definition at src/db/schema.ts: import { integer, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'; export const usersTable = pgTable('users_table', { id: serial('id').primaryKey(), name: text('name').notNull(), age: integer('age').notNull(), email: text('email').notNull().unique() }); export const postsTable = pgTable('posts_table', { id: serial('id').primaryKey(), title: text('title').notNull(), content: text('content').notNull(), userId: integer('user_id').notNull().references(() => usersTable.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().$onUpdate(() => new Date()) }); export type InsertUser = typeof usersTable.$inferInsert; export type SelectUser = typeof usersTable.$inferSelect; export type InsertPost = typeof postsTable.$inferInsert; export type SelectPost = typeof postsTable.$inferSelect;

Select data with getColumns helper

The getColumns function is available starting from drizzle-orm@1.0.0-beta.2. Import it with: import { getColumns } from 'drizzle-orm'; Use it in select queries like: .select({ ...getColumns(usersTable), postsCount: count(postsTable.id) }). For pre-1 versions like 0.45.1, use getTableColumns instead.

Select with groupBy and aggregate example

Example select query with join, groupBy, and aggregation: import { asc, count, eq, getColumns } from 'drizzle-orm'; export async function getUsersWithPostsCount(page = 1, pageSize = 5) { return db.select({ ...getColumns(usersTable), postsCount: count(postsTable.id) }).from(usersTable).leftJoin(postsTable, eq(usersTable.id, postsTable.userId)).groupBy(usersTable.id).orderBy(asc(usersTable.id)).limit(pageSize).offset((page - 1) * pageSize); }

Basic query execution with select

Use db.select().from(tableName) to query all rows from a table. Example: const users = await db.select().from(usersTable);

SQL-like query syntax in Drizzle

Drizzle is built to be SQL-like at its core. If you know SQL, you know Drizzle. Developers do not need to learn a separate framework API on top of SQL knowledge.

Drizzle Queries API for relational nested data

The Queries API allows you to fetch relational nested data from the database in a convenient and performant way without thinking about joins and data mapping. Drizzle always outputs exactly 1 SQL query regardless of the complexity.

Example of Drizzle Queries API for nested relational data

The following code shows how to fetch users with their related posts using the Queries API: ```ts const result = await db.query.users.findMany({ with: { posts: true }, }); ```

Example of SQL-like select query with join in Drizzle

The following code shows how to access data using SQL-like syntax with a left join: ```typescript await db .select() .from(countries) .leftJoin(cities, eq(cities.countryId, countries.id)) .where(eq(countries.id, 10)) ```

No explicit WHERE clause needed for tenant data isolation

When using tenantDB wrapper with Nile, queries do not need to include a WHERE clause for tenant_id filtering. The tenant context automatically restricts queries to that tenant's virtual database, ensuring no data from other tenants can be accessed.

Express routes with Drizzle and Nile example

Example multi-tenant routes: POST /api/tenants creates a tenant using `tx.insert(tenantSchema).values({ name }).returning()`, GET /api/tenants lists all tenants, POST /api/tenants/:tenantId/todos creates a todo, PUT /api/tenants/:tenantId/todos updates a todo, and GET /api/tenants/:tenantId/todos lists todos for a specific tenant. All routes use the tenantDB wrapper to execute queries within the tenant context.

Example Vercel Edge Function with Drizzle

Example GET route handler: import { db } from '@/db'; import { usersTable } from '@/db/schema'; import { NextResponse } from 'next/server'; export const dynamic = 'force-dynamic'; export const runtime = 'edge'; export async function GET(request: Request) { const users = await db.select().from(usersTable); return NextResponse.json({ users, message: 'success' }); }

Select with between and sql operators

Example of selecting posts from the last 24 hours using between operator with sql functions: db.select({ id: postsTable.id, title: postsTable.title }).from(postsTable).where(between(postsTable.createdAt, sql`now() - interval '1 day'`, sql`now()`)).orderBy(asc(postsTable.title), asc(postsTable.id)).limit(pageSize).offset((page - 1) * pageSize)

Delete example with eq operator

Example of deleting data with a specific id: export async function deleteUser(id: SelectUser['id']) { await db.delete(usersTable).where(eq(usersTable.id, id)); }

Update example with eq operator

Example of updating data with a specific id: export async function updatePost(id: SelectPost['id'], data: Partial<Omit<SelectPost, 'id'>>) { await db.update(postsTable).set(data).where(eq(postsTable.id, id)); }

getColumns function availability in Drizzle

The getColumns function is available starting from drizzle-orm@1.0.0-beta.2. For pre-1.0 versions (like 0.45.1), use getTableColumns instead.

Select with count and join example

Example of selecting users with their posts count using getColumns, leftJoin, count aggregation, and groupBy: db.select({ ...getColumns(usersTable), postsCount: count(postsTable.id) }).from(usersTable).leftJoin(postsTable, eq(usersTable.id, postsTable.userId)).groupBy(usersTable.id).orderBy(asc(usersTable.id)).limit(pageSize).offset((page - 1) * pageSize)

Give your agent this brain