query-api

252 notes in this subject, read out of this brain and free to use. This is page 1 of 5.

SQL-like delete example

Example of Drizzle SQL-like delete: ```typescript await db.delete(users).where(eq(users.id, 1)) ``` This generates: `DELETE FROM users WHERE users.id = 1`

Drizzle SQLite query approaches

Drizzle provides two main approaches for querying SQLite databases: SQL-like syntax and the Queries API (Relational syntax). SQL-like syntax mirrors standard SQL and requires no learning curve if you know SQL. The Queries API is used for fetching relational, nested data more efficiently without worrying about joins or data mapping.

SQL-like update example

Example of Drizzle SQL-like update: ```typescript await db.update(users) .set({ email: 'user@gmail.com' }) .where(eq(users.id, 1)) ``` This generates: `UPDATE users SET email = 'user@gmail.com' WHERE users.id = 1`

SQL-like query capabilities

Drizzle SQL-like syntax supports select, insert, update, delete, aliases, WITH clauses, subqueries, and prepared statements. This allows replicating most of what can be done with pure SQL.

SQL-like select example

Example of Drizzle SQL-like select with join: ```typescript await db .select() .from(posts) .leftJoin(comments, eq(posts.id, comments.post_id)) .where(eq(posts.id, 10)) ``` This generates: `SELECT * FROM posts LEFT JOIN comments ON posts.id = comments.post_id WHERE posts.id = 10`

Drizzle always outputs exactly one SQL query

Drizzle queries, particularly when using the Queries API, always output exactly one SQL query. This makes it safe to use with serverless databases without worrying about performance or roundtrip costs from multiple queries.

Composing WHERE filters example

Example of composing WHERE filters independently and using them in a query: ```typescript async function getProductsBy({ name, category, maxPrice, }: { name?: string; category?: string; maxPrice?: number; }) { const filters: SQL[] = []; if (name) filters.push(like(products.name, name)); if (category) filters.push(eq(products.category, category)); if (maxPrice) filters.push(lte(products.price, maxPrice)); return db .select() .from(products) .where(and(...filters)); } ```

Relational query API example

Example of Drizzle Queries API for fetching relational nested data: ```typescript const result = await db.query.users.findMany({ with: { posts: true }, }); ``` This fetches users with their related posts data efficiently in a single SQL query.

Using subqueries in main query example

Example of separating subqueries into variables and using them in the main query: ```typescript const subquery = db .select() .from(internalStaff) .leftJoin(customUser, eq(internalStaff.userId, customUser.id)) .as('internal_staff'); const mainQuery = await db .select() .from(ticket) .leftJoin(subquery, eq(subquery.internal_staff.userId, ticket.staffId)); ```

SQL-like insert example

Example of Drizzle SQL-like insert: ```typescript await db.insert(users).values({ email: 'user@gmail.com' }) ``` This generates: `INSERT INTO users (email) VALUES ('user@gmail.com')`

Delete with WITH clause (CTE)

Use common table expressions (CTEs) with delete queries to simplify complex deletions. Example: Define a CTE with `const averageAmount = db.$with('average_amount').as(db.select({ value: sql`avg(${orders.amount})`.as('value') }).from(orders));`. Then use it in a delete: `const result = await db.with(averageAmount).delete(orders).where(gt(orders.amount, sql`(select * from ${averageAmount})`)).returning({ id: orders.id });`. The generated SQL is: `with "average_amount" as (select avg("amount") as "value" from "orders") delete from "orders" where "orders"."amount" > (select * from "average_amount") returning "id"`.

Delete with LIMIT clause

Use `.limit()` to restrict the number of rows deleted. Example: `await db.delete(users).where(eq(users.name, 'Dan')).limit(2);` generates SQL `delete from "users" where "users"."name" = ? limit ?;`

Delete with ORDER BY clause

Use `.orderBy()` to sort rows before deletion. Import `asc` and `desc` from 'drizzle-orm'. Single field: `db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name)`. Multiple fields: `db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name, users.name2)`. Use `desc()` for descending: `db.delete(users).where(eq(users.name, 'Dan')).orderBy(desc(users.name))`. Mixed: `db.delete(users).where(eq(users.name, 'Dan')).orderBy(asc(users.name), desc(users.name2))`.

Delete all rows from table

To delete all rows from a table in Drizzle with SQLite, use `await db.delete(users);` where `users` is the table reference.

Delete with RETURNING clause

SQLite supports returning deleted rows. Use `.returning()` to get all deleted row columns: `const deletedUser = await db.delete(users).where(eq(users.name, 'Dan')).returning();`. Use `.returning({ deletedId: users.id })` to return only specific columns as an object.

Delete with WHERE condition

To delete rows matching a condition, chain `.where()` to the delete statement. Example: `await db.delete(users).where(eq(users.name, 'Dan'));`

Example: Standalone query builder with dynamic mode

```ts import { QueryBuilder } from 'drizzle-orm/sqlite-core'; function withFriends<T extends SQLiteSelectQueryBuilder>(qb: T) { return qb.leftJoin(friends, eq(friends.userId, users.id)); } const qb = new QueryBuilder(); let query = qb.select().from(users).where(eq(users.id, 1)).$dynamic(); query = withFriends(query); ``` This example shows how to use dynamic query building with standalone QueryBuilder instances imported from drizzle-orm/sqlite-core.

Example: Modifying query result type in dynamic mode

```ts function withFriends<T extends SQLiteSelect>(qb: T) { return qb.leftJoin(friends, eq(friends.userId, users.id)); } let query = db.select().from(users).where(eq(users.id, 1)).$dynamic(); query = withFriends(query); ``` This example demonstrates how dynamic mode allows generic functions to modify the result type of a query builder by adding operations like joins.

Dynamic query building enables shared functions to enhance queries

Dynamic mode allows you to write generic functions that take a query builder and enhance it by adding clauses like LIMIT, OFFSET, or JOIN operations. This is useful for shared utility functions that need to modify queries at runtime.

Example: Dynamic withPagination function

```ts function withPagination<T extends SQLiteSelect>( qb: T, page: number = 1, pageSize: number = 10, ) { return qb.limit(pageSize).offset((page - 1) * pageSize); } const query = db.select().from(users).where(eq(users.id, 1)); const dynamicQuery = query.$dynamic(); withPagination(dynamicQuery, 1); // ✅ OK ``` This example shows how to create a generic function that adds pagination to a dynamic query builder.

SQLiteSelect and other generic types are designed for dynamic query building

The types SQLiteSelect, SQLiteSelectQueryBuilder, SQLiteInsert, SQLiteUpdate, and SQLiteDelete can only be used in dynamic mode as generic parameters. They allow you to write generic functions that accept and modify query builders while preserving type safety.

Enable dynamic mode with $dynamic() to invoke methods multiple times

To remove the restriction of invoking query builder methods only once, call `.$dynamic()` on a query builder. This enables dynamic query building mode, which is useful when building queries dynamically or when shared functions need to enhance query builders.

Pitfall: Calling query builder methods without $dynamic()

If you pass a query builder to a generic function that invokes methods multiple times without first calling `.$dynamic()`, you will get a type error. Always enable dynamic mode before passing builders to functions that modify them.

count() with WHERE clause

To count rows that match a condition, use the .where() method after the .from() call. For example: db.select({ count: count() }).from(products).where(gt(products.price, 100)) counts all rows where price is greater than 100.

mapWith() for count result transformation

If you need to apply runtime transformations to a count result returned by the sql operator, use the .mapWith() method. For example, sql`count(*)`.mapWith(Number) casts the result to a number at runtime.

sql<number> type generic declaration

When using the sql operator with count, you can specify the expected return type using a generic parameter like sql<number>`count(*)`. This tells Drizzle that the field is expected to be a number. However, Drizzle cannot perform runtime type casts based on the type generic since that information is not available at runtime. If the type generic is specified incorrectly, the runtime value won't match the expected type.

count() function returns number type

The count() function in Drizzle casts its result to a number at runtime. When you use count() without a column argument (count(*)), it counts all rows. When you provide a column argument like count(products.discount), it counts non-NULL values in that column. The result type is always a number.

count() with column vs count(*)

The count() function can be used in two ways. count() counts all rows. count(columnName) counts only rows where the specified column contains non-NULL values. Both return an integer result in SQLite.

count with joins example

Example of count() with joins and groupBy: ```ts import { count, eq } from 'drizzle-orm'; import { countries, cities } from './schema'; await db .select({ country: countries.name, citiesCount: count(cities.id), }) .from(countries) .leftJoin(cities, eq(countries.id, cities.countryId)) .groupBy(countries.id) .orderBy(countries.name); ``` This generates: `select countries.name, count("cities"."id") from countries left join cities on countries.id = cities.country_id group by countries.id order by countries.name;`

count() with joins and groupBy

The count() function works with joins and aggregations. When using count() with joins, you typically combine it with .groupBy() to aggregate counts by a grouped column. For example, you can count related records from a joined table, such as counting cities in each country using a leftJoin.

sql operator count example

Example using the sql operator for count: ```ts import { sql } from 'drizzle-orm'; await db.select({ count: sql<number>`count(*)` }).from(products); await db.select({ count: sql<number>`count(${products.discount})` }).from(products); ``` This generates the SQL: `select count(*) from products;` and `select count("discount") from products;` respectively.

count(column) query example

Example showing count() with a specific column: ```ts await db.select({ count: count(products.discount) }).from(products); ``` This generates the SQL: `select count("discount") from products;` and returns a result type of `{ count: number }[]`. It counts only rows where the discount column is not NULL.

count() query example

Example showing count() usage: ```ts import { count } from 'drizzle-orm'; import { products } from './schema'; const db = drizzle(...); await db.select({ count: count() }).from(products); ``` This generates the SQL: `select count(*) from products;` and returns a result type of `{ count: number }[]`.

Example: Raw SQL queries with sql template tag

```ts import { sql } from 'drizzle-orm'; const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const res: unknown[] = db.all(statement) const res: unknown = db.get(statement) const res: unknown[][] = db.values(statement) const res: Database.RunResult = db.run(statement) ``` This example shows how to use raw parametrized SQL queries with the sql template tag and the different execution methods.

Example: Type inference from SQLite table

```ts import { integer, text, sqliteTable } from 'drizzle-orm/sqlite-core'; import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm' const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), }); type SelectUser = typeof users.$inferSelect; type InsertUser = typeof users.$inferInsert; type SelectUserAlt = InferSelectModel<typeof users>; type InsertUserAlt = InferInsertModel<typeof users>; ``` This example shows both ways to infer select and insert types from a SQLite table schema.

Example: Exclude fields with getColumns

```ts import { getColumns } from "drizzle-orm"; import { users } from "./schema"; const { password, role, ...rest } = getColumns(users); await db.select({ ...rest }).from(users); ``` This example shows how to exclude password and role fields from a SELECT query using getColumns.

Example: Multi-project schema with sqliteTableCreator

```ts import { integer, text, sqliteTableCreator } from 'drizzle-orm/sqlite-core'; const sqliteTable = sqliteTableCreator((name) => `project1_${name}`); const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), }); ``` The sqliteTableCreator function takes a callback that prefixes table names. This creates a users table as project1_users in the database.

Example: Standalone QueryBuilder

```ts import { QueryBuilder } from 'drizzle-orm/sqlite-core'; const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL(); ``` This example shows how to use the standalone QueryBuilder to build a query without a database instance and get the SQL and params.

is() function for type checking Drizzle objects

Use is(value, Column) instead of instanceof to check Drizzle object types. This narrows the type of the value to the specified class.

getTableConfig to inspect table metadata

Use getTableConfig from 'drizzle-orm/sqlite-core' to inspect table metadata. It returns an object with columns, indexes, foreignKeys, checks, primaryKeys, name, and uniqueConstraints properties.

getColumns utility to exclude fields from selection

Import getColumns from 'drizzle-orm' and pass a table to get a typed column map. You can then destructure to exclude fields: const { password, role, ...rest } = getColumns(users); and use the rest in db.select().

Standalone QueryBuilder without database instance

Use new QueryBuilder() from 'drizzle-orm/sqlite-core' to create a query builder without a database instance. You can build queries and call .toSQL() to get the SQL and params.

Raw SQL query methods: db.all, db.get, db.values, db.run

Drizzle provides methods to execute raw parametrized SQL queries: db.all(statement) returns unknown[], db.get(statement) returns unknown, db.values(statement) returns unknown[][], and db.run(statement) returns Database.RunResult. Use sql template tag to build the parametrized query with interpolation.

toSQL() method returns sql and params

Call .toSQL() on a query builder to get the generated SQL. It returns an object with sql (the SQL string) and params (an array of parameter values).

sqliteTableCreator for multi-project schema prefix

Use sqliteTableCreator to customize table names when several projects share one database. It takes a function that receives the table name and returns the prefixed name. For example: const sqliteTable = sqliteTableCreator((name) => `project1_${name}`).

drizzle.mock() for testing without real connection

Call drizzle.mock({ relations }) to create a typed database object without a real SQLite connection. This is useful for testing.

Custom Logger implementation

Implement the Logger interface with a logQuery(query: string, params: unknown[]): void method to provide a custom logger to drizzle(). Pass an instance of your custom logger to the logger option.

Custom LogWriter for query logs

Create a DefaultLogger instance with a custom LogWriter to change where query logs are written. Implement the LogWriter interface with a write(message: string) method to write logs to file, stdout, or other destinations.

Enable query logging with logger: true

Pass { logger: true } to the drizzle() function to enable default query logging.

InferSelectModel and InferInsertModel type helpers

Drizzle provides InferSelectModel and InferInsertModel type helpers to infer select and insert models from a SQLite table schema. You can import them from 'drizzle-orm' and use them as InferSelectModel<typeof table> and InferInsertModel<typeof table>.

$inferSelect and $inferInsert type inference

You can infer select and insert types directly from a table by using typeof table.$inferSelect and typeof table.$inferInsert.

onConflictDoUpdate updates row on conflict

The onConflictDoUpdate() method updates the conflicting row instead of canceling the insert. It requires a target column and a set object specifying which columns to update. Example: `.onConflictDoUpdate({ target: users.id, set: { name: 'John' } })`.

INSERT multiple rows in single query

Pass an array of value objects to .values() to insert multiple rows in a single insert statement. Example: `db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }])`.

onConflictDoNothing prevents insert on conflict

The onConflictDoNothing() method cancels the insert operation if a conflict occurs. You can optionally specify the conflict target column: `.onConflictDoNothing({ target: users.id })`. If no target is specified, the behavior applies to any conflict.

targetWhere and setWhere in upserts

On conflict do update can include two different WHERE clauses. Use targetWhere for partial index conditions on the conflict target, and setWhere for conditions on the update clause itself. Both accept sql template tags for custom conditions.

INSERT values are parameterized automatically

All values provided to .values() in an insert query are automatically parameterized. For example, the query `db.insert(users).values({ id: 1, name: 'Andrew' })` translates to the SQL `insert into "users" ("id", "name") values (?, ?)` with parameters `['Andrew']`, preventing SQL injection.

Infer insert type from table schema

Use `typeof tableName.$inferInsert` to get the TypeScript type for inserting rows into a specific table. For example, `type NewUser = typeof users.$inferInsert` creates a type that matches the expected shape for inserting into the users table.

INSERT returning clause in SQLite

After inserting a row, use .returning() to retrieve the inserted row. You can return all columns with .returning() or return specific columns with .returning({ insertedId: users.id }). Partial returns allow selecting which columns to retrieve.

Composite key upserts

For upserts on composite indexes or composite primary keys, pass an array of columns to the target property: `.onConflictDoUpdate({ target: [users.firstName, users.lastName], set: { firstName: 'John1' } })`.

WITH clause (CTE) purpose

Common table expressions (CTEs) created with the WITH clause help simplify complex queries by splitting them into smaller subqueries. They can be used with insert, select, update, and delete statements.

Give your agent this brain