query-api

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

Relational query API for fetching nested data

The Queries API allows you to fetch relational, nested data without manual joins or data mapping. For example: const result = await db.query.users.findMany({ with: { posts: true } }). This outputs exactly one SQL query.

Drizzle query types available

Drizzle supports select, insert, update, delete, as well as aliases, WITH clauses, subqueries, and prepared statements.

DELETE query syntax

To delete data, use db.delete(table).where(condition). For example: await db.delete(users).where(eq(users.id, 1))

Compose WHERE clauses independently

WHERE statements can be composed independently from the main query by building an array of filter conditions and passing them to the where() method using the and() operator. Filters can be conditionally added based on parameters before being used in the query.

INSERT query syntax

To insert data, use db.insert(table).values(object). For example: await db.insert(users).values({ email: 'user@gmail.com' })

SQL-like syntax for SELECT queries

The SQL-like syntax in Drizzle allows you to select data using methods like db.select().from(table).leftJoin(otherTable, condition).where(filter). For example: await db.select().from(posts).leftJoin(comments, eq(posts.id, comments.post_id)).where(eq(posts.id, 10))

Use subqueries as variables in main queries

Subqueries can be separated into different variables using the .as(alias) method, and then used in the main query. For example: const subquery = db.select().from(table).leftJoin(otherTable, condition).as('alias'); const mainQuery = await db.select().from(mainTable).leftJoin(subquery, joinCondition);

UPDATE query syntax

To update data, use db.update(table).set(updates).where(condition). For example: await db.update(users).set({ email: 'user@gmail.com' }).where(eq(users.id, 1))

Delete all rows from a table

To delete all rows from a table, use db.delete(table) without any conditions.

DELETE with LIMIT clause

Use .limit(n) to restrict the number of rows deleted. For example, db.delete(users).where(eq(users.name, 'Dan')).limit(2) deletes up to 2 rows matching the condition.

Delete rows with WHERE condition

To delete rows matching specific conditions, use db.delete(table).where() with a condition. For example, db.delete(users).where(eq(users.name, 'Dan')) deletes all rows where the name equals 'Dan'.

DELETE with ORDER BY clause

Use .orderBy() to sort rows before deletion. Single field ordering: 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 asc() for ascending order and desc() for descending order. For example, db.delete(users).where(eq(users.name, 'Dan')).orderBy(asc(users.name), desc(users.name2)).

drizzle-kit export as CLI options

You can provide all drizzle-kit export configuration options through CLI if necessary, for example in CI/CD pipelines: npx drizzle-kit export --dialect=mysql --schema=./src/schema.ts

drizzle-kit export config file with MySQL dialect

A drizzle.config.ts file for MySQL export should include: import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "mysql", schema: "./src/schema.ts", }); Then run: npx drizzle-kit export

drizzle-kit export example with MySQL

Example of exporting a Drizzle schema to console. The schema file contains: import { mysqlTable, int, text } from 'drizzle-orm/mysql-core'; export const users = mysqlTable('users', { id: int('id').primaryKey().autoincrement(), email: text('email').notNull(), name: text('name') }); Running npx drizzle-kit export --config=./configs/drizzle.config.ts outputs: CREATE TABLE "users" ( `id` int AUTO_INCREMENT PRIMARY KEY, `email` varchar(255) NOT NULL, "name" text );

Ignore conflicts with --ignore-conflicts option

In case you need the generate command to skip commutativity checks, you can use the --ignore-conflicts CLI option. This bypasses conflict checks during migration generation.

Migration folder structure and naming

Generated migrations are stored in a migrations folder (default ./drizzle) with subfolders named as a timestamp followed by the migration name, such as 20242409125510_premium_mister_fear. Each migration folder contains migration.sql and snapshot.json files.

drizzle-kit generate config file example

Example configuration in drizzle.config.ts: import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "mysql", schema: "./src/schema.ts", });

drizzle-kit generate CLI invocation

You can invoke drizzle-kit generate with or without a config file. Without config: npx drizzle-kit generate --dialect=mysql --schema=./src/schema.ts. With config: npx drizzle-kit generate.

Example: Dynamic query building with standalone QueryBuilder

This example shows dynamic query building with a standalone QueryBuilder instance: ```ts import { QueryBuilder } from 'drizzle-orm/mysql-core'; function withFriends<T extends MySqlSelectQueryBuilder>(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); ``` Standalone QueryBuilder instances can be used with dynamic query building functions in the same way as DB query builders.

Dynamic query building enables shared functions to enhance queries

Dynamic query building is useful when you want to build a query dynamically, such as when you have a shared function that takes a query builder and enhances it by invoking methods multiple times. This is the primary use case for .$dynamic().

QueryBuilder types work with standalone query builders

The MySqlSelectQueryBuilder, MySqlInsert, MySqlUpdate, and MySqlDelete types (with the QueryBuilder suffix) are for usage with standalone query builder instances. DB query builders are subclasses of these types, so you can use the QueryBuilder types as generic parameters for both DB query builders and standalone query builders.

Example: Composing dynamic query building functions

This example shows how to compose multiple dynamic query building functions: ```ts function withFriends<T extends MySqlSelect>(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); ``` The generic approach allows you to modify the result type of the query builder inside the function, such as adding a join.

Generic types for dynamic query building in MySQL

The generic types used for dynamic query building in MySQL are: MySqlSelect or MySqlSelectQueryBuilder for SELECT queries, MySqlInsert for INSERT queries, MySqlUpdate for UPDATE queries, and MySqlDelete for DELETE queries. These types are specifically designed to be used in dynamic mode and can only be used in dynamic mode.

count() function basic usage

The count() function counts all rows in a table. Usage: await db.select({ count: count() }).from(products). The result type is { count: number }[] and generates SQL: select count(*) from products;

count() with sql operator and mapWith

The count() function can also be used with the sql operator: await db.select({ count: sql`count(*)`.mapWith(Number) }).from(products). The count() function casts its result to a number at runtime. This alternative syntax produces the same SQL: select count(*) from products;

Custom count function with CAST to unsigned

In MySQL, count() can be returned as a string by the driver. Create a custom count function that casts to unsigned integer for numeric results: const customCount = (column?: AnyColumn) => { if (column) { return sql<number>`cast(count(${column}) as unsigned)`; } else { return sql<number>`cast(count(*) as unsigned)`; } };

count() with JOIN and GROUP BY

Example of count() with joins and aggregations: 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 SQL: 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 WHERE clause

To count rows that match a condition, use the .where() method: await db.select({ count: count() }).from(products).where(gt(products.price, 100));. This generates SQL: select count(*) from products where price > 100.

Custom logger implementation

Create a custom logger by implementing the Logger interface with a logQuery(query: string, params: unknown[]): void method. Pass the instance to drizzle initialization. Example: class MyLogger implements Logger { logQuery(query: string, params: unknown[]): void { console.log({ query, params }); } }; const db = drizzle(process.env.DB_URL, { logger: new MyLogger() });

Custom log writer with DefaultLogger

Create a custom log writer by implementing the LogWriter interface with a write(message: string) method, then pass it to DefaultLogger. Example: class MyLogWriter implements LogWriter { write(message: string) { } }; const logger = new DefaultLogger({ writer: new MyLogWriter() }); const db = drizzle(process.env.DB_URL, { logger });

Enable query logging with logger: true

Pass { logger: true } to the drizzle initialization function to enable default query logging. Example: const db = drizzle({ logger: true });

Multi-project schema with mysqlTableCreator

Use mysqlTableCreator to define a custom table name prefix for keeping schemas of different projects in one database. Pass a function that takes the table name and returns the prefixed name. Example: const mysqlTable = mysqlTableCreator((name) => `project1_${name}`);

InferSelectModel and InferInsertModel type helpers

Use InferSelectModel and InferInsertModel from 'drizzle-orm' to retrieve types from your table schema for select and insert queries. Alternatively, use typeof users.$inferSelect and typeof users.$inferInsert. Example: type SelectUser = InferSelectModel<typeof users>; type InsertUser = InferInsertModel<typeof users>;

Use is() function instead of instanceof for type checking

Use the is() function from 'drizzle-orm' to check if an object is a specific Drizzle type, instead of instanceof operator. Example: if (is(value, Column)) { /* value type narrowed to Column */ }

Get table configuration with getTableConfig()

Use getTableConfig() from 'drizzle-orm/mysql-core' to retrieve table metadata. Returns object with properties: columns, indexes, foreignKeys, checks, primaryKeys, name, schema.

Get typed columns map with getColumns()

Use getColumns() function from 'drizzle-orm' to get a typed columns map from a table. Useful for omitting certain columns during selection. Example: const { password, role, ...rest } = getColumns(user); await db.select({ ...rest }).from(user);

Standalone query builder without database instance

Use QueryBuilder from 'drizzle-orm/mysql-core' to build queries without creating a database instance. Call toSQL() to get generated SQL. Example: const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL();

Execute raw parametrized SQL queries

Use db.execute() method with sql template to execute complex raw parametrized queries. Returns MySqlRawQueryResult. Example: const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const res: MySqlRawQueryResult = await db.execute(statement);

Print SQL query with toSQL() method

Call toSQL() on a query builder to get the generated SQL and parameters. Returns an object with sql (string) and params (array) properties. Example: const query = db.select().from(users).toSQL(); returns { sql: 'select ... from ...', params: [] }

$returningId for autoincrement primary keys

MySQL does not support RETURNING after INSERT natively. Use `$returningId()` method to get inserted IDs for tables with autoincrement primary keys. For a table with `id: int().autoincrement().primaryKey()`, calling `db.insert(table).values([...]).$returningId()` returns `{ id: number }[]`.

Insert into select with query builder

Pass a query builder directly to the `select()` method. Example: `await db.insert(employees).select(db.select({ id: users.id, name: users.name }).from(users).where(eq(users.role, 'employee')));` or use `new QueryBuilder()` instance.

Do nothing on duplicate key in MySQL

MySQL does not directly support doing nothing on conflict. To achieve a no-op, set any column's value to itself. Example: `await db.insert(users).values({ id: 1, name: 'John' }).onDuplicateKeyUpdate({ set: { id: sql\`id\` } });`

ON DUPLICATE KEY UPDATE in MySQL

MySQL supports `ON DUPLICATE KEY UPDATE` instead of `ON CONFLICT`. MySQL automatically determines the conflict target based on primary key and unique indexes. Use `.onDuplicateKeyUpdate({ set: { column: value } })` to update on conflict. Example: `await db.insert(users).values({ id: 1, name: 'John' }).onDuplicateKeyUpdate({ set: { name: 'John' } });`

$returningId returns empty object when no primary key

If a table has no primary keys, `$returningId()` returns `{}[]` type.

Insert into select with SQL template tag

Pass a custom SQL query using the `sql` template tag. Example: `await db.insert(employees).select(sql\`select users.id as id, users.name as name from users where users.role = 'employee'\`);` or wrap in a callback with `sql\`...\` ` for lazy evaluation.

$returningId with custom primary keys

When a primary key is defined with `$default` function (e.g., `$defaultFn(createId)`), Drizzle automatically returns those generated keys in the `$returningId()` call. Example: `db.insert(usersTableDefFn).values([...]).$returningId()` returns `{ customId: string }[]` for a custom ID column.

Join result mapping for many-to-one relationships

Drizzle returns name-mapped results from the driver without changing structure. Use reduce to transform many-one relational data: rows.reduce((acc, row) => { if (!acc[user.id]) acc[user.id] = { user, pets: [] }; if (row.pet) acc[user.id].pets.push(row.pet); return acc; }, {}) to group results by user.

sql operator with type parameter for join results

When using sql operator for aggregations or expressions in partial select with joins, explicitly specify the nullable type: sql<string | null>`upper(${pets.name})`. Without explicit typing, the result type is inferred as unknown. This is necessary because the ORM cannot automatically determine if an expression should be nullable.

Partial select with joins for flat response type

Use db.select({ userId: users.id, petId: pets.id }).from(users).leftJoin(...) to select specific fields and get a flat response type. Drizzle automatically infers which fields can be null based on the join type. In left joins, fields from the right table become nullable.

leftJoin method syntax and type safety

Use db.select().from(users).leftJoin(pets, eq(users.id, pets.ownerId)) to perform a left join. The method generates SQL like 'select ... from `users` left join `pets` on `users`.`id` = `pets`.`owner_id`'. Drizzle automatically infers return types where joined tables that can be null appear as union types with null (e.g., pets: {...} | null).

crossJoinLateral method for subqueries

Use db.select().from(users).crossJoinLateral(subquery) to perform a lateral cross join with a subquery. The method generates SQL like 'select ... from `users` cross join lateral (select ... from `pets` where `users`.`age` >= 16) `userPets`'. No ON condition is required.

crossJoin method syntax

Use db.select().from(users).crossJoin(pets) to perform a cross join without an ON condition. The method generates SQL like 'select ... from `users` cross join `pets`'. Both tables are required in the result type.

innerJoinLateral method for subqueries

Use db.select().from(users).innerJoinLateral(subquery, sql`true`) to perform a lateral inner join with a subquery. The method generates SQL like 'select ... from `users` inner join lateral (select ... from `pets` where `users`.`age` >= 16) `userPets` on true'. Neither table appears as nullable in the result type.

innerJoin method syntax

Use db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId)) to perform an inner join. The method generates SQL like 'select ... from `users` inner join `pets` on `users`.`id` = `pets`.`owner_id`'. Both tables are required in the result type with no null unions.

rightJoin method syntax

Use db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId)) to perform a right join. The method generates SQL like 'select ... from `users` right join `pets` on `users`.`id` = `pets`.`owner_id`'. The left table appears as union with null in return type.

leftJoinLateral method for subqueries

Use db.select().from(users).leftJoinLateral(subquery, sql`true`) to perform a lateral left join with a subquery. The subquery must be created with .as('alias'). Example: const subquery = db.select().from(pets).where(gte(users.age, 16)).as('userPets'). The join condition can be sql`true` for all rows.

Nested select object syntax for joins with many columns

Use nested select objects to avoid multiple nullable fields: db.select({ userId: users.id, pet: { id: pets.id, name: pets.name } }).from(users).leftJoin(pets, eq(users.id, pets.ownerId)). Drizzle's type inference makes the entire nested object nullable (pet: {...} | null) instead of making individual fields nullable. This is cleaner when joining tables with many columns.

Drizzle MySQL join types available

Drizzle ORM supports INNER JOIN, INNER JOIN LATERAL, LEFT JOIN, LEFT JOIN LATERAL, RIGHT JOIN, and CROSS JOIN with LATERAL variants. LATERAL variants are available for INNER JOIN, LEFT JOIN, and CROSS JOIN.

Type inference with $inferSelect for joins

Use type User = typeof users.$inferSelect to infer the type of a table. This is useful when mapping join results to aggregate relationships, such as grouping pets by user.

Give your agent this brain