or operator - logical disjunction
The or operator combines multiple conditions where one or more must be true. Example: or(gt(table.column, 5), lt(table.column, 7)) generates WHERE ([table].[column] > 5 OR [table].[column] < 7).
query-builder
271 notes in this subject, read out of this brain and free to use. This is page 5 of 5.
and operator - logical conjunction
The and operator combines multiple conditions where all must be true. Example: and(gt(table.column, 5), lt(table.column, 7)) generates WHERE ([table].[column] > 5 AND [table].[column] < 7).
not operator - logical negation
The not operator negates a condition, returning true when the condition is false. Example: not(eq(table.column, 5)) generates WHERE NOT ([table].[column] = 5).
notLike operator - pattern non-matching case sensitive
The notLike operator performs case-sensitive pattern non-matching using the NOT LIKE operator. Example: notLike(table.column, "%llo wor%") generates WHERE [table].[column] NOT LIKE '%llo wor%'.
inArray operator - membership in array
The inArray operator checks if a column value is in a list of values or in a subquery result. With array: inArray(table.column, [1, 2, 3, 4]) generates WHERE [table].[column] IN (1, 2, 3, 4). With subquery: const query = db.select({ data: table2.column }).from(table2); inArray(table.column, query) generates WHERE [table].[column] IN (SELECT [table2].[column] FROM [table2]).
isNotNull operator - non-null value check
The isNotNull operator checks if a column value is not null. Example: isNotNull(table.column) generates WHERE ([table].[column] IS NOT NULL).
isNull operator - null value check
The isNull operator checks if a column value is null. Example: isNull(table.column) generates WHERE ([table].[column] IS NULL).
lte operator - less than or equal comparison
The lte operator checks if a value is less than or equal to another value. It can compare a column to a literal value or compare two columns. Example: lte(table.column, 5) generates WHERE [table].[column] <= 5. When comparing columns: lte(table.column1, table.column2) generates WHERE [table].[column1] <= [table].[column2].
gte operator - greater than or equal comparison
The gte operator checks if a value is greater than or equal to another value. It can compare a column to a literal value or compare two columns. Example: gte(table.column, 5) generates WHERE [table].[column] >= 5. When comparing columns: gte(table.column1, table.column2) generates WHERE [table].[column1] >= [table].[column2].
Empty columns with relations
You can include empty columns: {} to exclude all fields from the parent table while still including nested relations. Example: await db._query.users.findMany({ columns: {}, with: { posts: true } }); This returns only the posts data without any user fields.
Mixing true and false in columns parameter
When both true and false select options are present in the columns parameter, all false options are ignored. If you include the 'name' field and exclude the 'id' field, the id exclusion will be redundant as all fields apart from name would be excluded anyways.
MSSQL relational queries initialization with schema
To initialize Drizzle for relational queries with MSSQL, import your schema and pass it to drizzle(). If schema is declared in multiple files, spread them into a single schema object. Import the drizzle function from 'drizzle-orm/node-mssql'. Example: import * as schema from './schema'; import { drizzle } from 'drizzle-orm/node-mssql'; const db = drizzle({ schema }); Or with multiple schema files: import * as schema1 from './schema1'; import * as schema2 from './schema2'; const db = drizzle({ schema: { ...schema1, ...schema2 } });
MSSQL schema with relations example
Example MSSQL schema with multiple tables and relations: import { type AnyMsSqlColumn, bit, int, mssqlTable, primaryKey, text, datetime2 } from 'drizzle-orm/mssql-core'; import { relations } from 'drizzle-orm/_relations'; export const users = mssqlTable('users', { id: int('id').primaryKey().identity(), name: text('name').notNull(), verified: bit('verified').notNull(), invitedBy: int('invited_by').references(() => users.id) }); export const usersRelations = relations(users, ({ one, many }) => ({ invitee: one(users, { fields: [users.invitedBy], references: [users.id] }), usersToGroups: many(usersToGroups), posts: many(posts) })); export const groups = mssqlTable('groups', { id: int('id').primaryKey().identity(), name: text('name').notNull(), description: text('description') }); export const groupsRelations = relations(groups, ({ many }) => ({ usersToGroups: many(usersToGroups) })); export const usersToGroups = mssqlTable('users_to_groups', { id: int('id').primaryKey().identity(), userId: int('user_id').notNull().references(() => users.id), groupId: int('group_id').notNull().references(() => groups.id) }, (t) => [primaryKey({ columns: [t.userId, t.groupId] })]); export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({ group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }), user: one(users, { fields: [usersToGroups.userId], references: [users.id] }) })); export const posts = mssqlTable('posts', { id: int('id').primaryKey().identity(), content: text('content').notNull(), authorId: int('author_id').references(() => users.id), createdAt: datetime2('created_at').notNull().defaultGetDate() }); export const postsRelations = relations(posts, ({ one, many }) => ({ author: one(users, { fields: [posts.authorId], references: [users.id] }), comments: many(comments) })); export const comments = mssqlTable('comments', { id: int('id').primaryKey().identity(), content: text('content').notNull(), creator: int('creator').references(() => users.id), postId: int('post_id').references(() => posts.id), createdAt: datetime2('created_at').notNull().defaultGetDate() }); export const commentsRelations = relations(comments, ({ one, many }) => ({ post: one(posts, { fields: [comments.postId], references: [posts.id] }), author: one(users, { fields: [comments.creator], references: [users.id] }), likes: many(commentLikes) })); export const commentLikes = mssqlTable('comment_likes', { id: int('id').primaryKey().identity(), creator: int('creator').references(() => users.id), commentId: int('comment_id').references(() => comments.id), createdAt: datetime2('created_at').notNull().defaultGetDate() }); export const commentLikesRelations = relations(commentLikes, ({ one }) => ({ comment: one(comments, { fields: [commentLikes.commentId], references: [comments.id] }), author: one(users, { fields: [commentLikes.creator], references: [users.id] }) }));
Multiple placeholders in prepared statements
You can use multiple placeholders in a single prepared statement. Example: const prepared = db._query.users.findMany({ limit: placeholder('uLimit'), offset: placeholder('uOffset'), where: (users, { eq, or }) => or(eq(users.id, placeholder('id')), eq(users.id, 3)), with: { posts: { where: (users, { eq }) => eq(users.id, placeholder('pid')), limit: placeholder('pLimit') } } }).prepare(); const result = await prepared.execute({ pLimit: 1, uLimit: 3, uOffset: 1, id: 2, pid: 6 });
Prepared statements with offset placeholder
You can use placeholders in the offset parameter (top-level only). Example: const prepared = db._query.users.findMany({ offset: placeholder('offset'), with: { posts: true } }).prepare(); const result = await prepared.execute({ offset: 1 });
Prepared statements with limit placeholder
You can use placeholders in the limit parameter. Example: const prepared = db._query.users.findMany({ with: { posts: { limit: placeholder('limit') } } }).prepare(); const result = await prepared.execute({ limit: 1 });
Prepared statements in relational queries
Prepared statements improve query performance. Use .prepare() to create a prepared statement, then call .execute() with placeholder values. You can use placeholders in where clauses, limit, and offset. Example with where placeholder: const prepared = db._query.users.findMany({ where: (users, { eq }) => eq(users.id, sql.placeholder('id')), with: { posts: { where: (posts, { eq }) => eq(posts.id, sql.placeholder('pid')) } } }).prepare(); const result = await prepared.execute({ id: 1, pid: 1 });
Extras with complex calculations
You can use extras to calculate derived fields like concatenation or string length. Example with concatenation: await db._query.users.findMany({ extras: { fullName: sql<string>`concat(${users.name}, " ", ${users.name})`.as('full_name') }, with: { usersToGroups: { columns: {}, with: { group: true } } } }); Example with length calculation: await db._query.posts.findMany({ extras: (table, { sql }) => ({ contentLength: sql<number>`length(${table.content})`.as('content_length') }), with: { comments: { extras: { commentSize: sql<number>`length(${comments.content})`.as('comment_size') } } } });
Order by in relational queries
The orderBy parameter allows sorting results. You can use the core API with asc() and desc() functions, or use them from the callback syntax with no imports. Example with import: import { desc, asc } from 'drizzle-orm'; await db._query.posts.findMany({ orderBy: [asc(posts.id)] }); Example with callback: await db._query.posts.findMany({ orderBy: (posts, { asc }) => [asc(posts.id)] }); You can order nested relations differently: await db._query.posts.findMany({ orderBy: (posts, { asc }) => [asc(posts.id)], with: { comments: { orderBy: (comments, { desc }) => [desc(comments.id)] } } });
Offset in relational queries
The offset parameter is only available for top-level queries, not for nested relations. Using offset on nested relations is incorrect. Example: await db._query.posts.findMany({ limit: 5, offset: 2, with: { comments: true } }); // correct - offset at top level. Using offset: 3 in comments would be incorrect.
Limit in relational queries
The limit parameter restricts the number of records returned. It can be used at the top level and for nested relations. Example to find 5 posts: await db._query.posts.findMany({ limit: 5 }); Example to get 3 comments at most per post: await db._query.posts.findMany({ with: { comments: { limit: 3 } } });
Select filters in relational queries
Relational queries support filters and conditions using operators from Drizzle. You can import operators from 'drizzle-orm' or use them from the callback syntax. Example with import: import { eq } from 'drizzle-orm'; const users = await db._query.users.findMany({ where: eq(users.id, 1) }); Example with callback: const users = await db._query.users.findMany({ where: (users, { eq }) => eq(users.id, 1) }); You can also filter nested relations: await db._query.posts.findMany({ where: (posts, { eq }) => eq(posts.id, 1), with: { comments: { where: (comments, { lt }) => lt(comments.createdAt, new Date()) } } });
Example: dynamic query building with pagination function
This example demonstrates a generic function that dynamically modifies a query builder: ```ts function withPagination<T extends PgSelect>( 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 ``` The function is generic, allowing it to modify the query builder's result type, such as adding joins. The generic type must extend PgSelect (or the equivalent for other databases) and only works when the query builder is in dynamic mode.
Standalone query builder types for dynamic building
For standalone query builder instances (not created via db.select(), db.insert(), etc.), use the ...QueryBuilder types: PgSelectQueryBuilder, MySqlSelectQueryBuilder, SQLiteSelectQueryBuilder, and their insert/update/delete equivalents. These are the base types that DB query builders subclass, so they can be used interchangeably in generic function signatures. Import them from 'drizzle-orm/pg-core' (or equivalent for other databases).
Generic types for dynamic query building
Dynamic query building supports the following generic types for type-safe function parameters. For Postgres: PgSelect, PgSelectQueryBuilder, PgInsert, PgUpdate, PgDelete. For MySQL: MySqlSelect, MySqlSelectQueryBuilder, MySqlInsert, MySqlUpdate, MySqlDelete. For SQLite: SQLiteSelect, SQLiteSelectQueryBuilder, SQLiteInsert, SQLiteUpdate, SQLiteDelete. The ...QueryBuilder types are for standalone query builder instances, while DB query builders are subclasses of them, so both can be used.
Composing multiple dynamic query builder functions
Multiple generic functions can be chained together when working with dynamic query builders. For example, you can apply one function that adds pagination and another that adds joins. Each function receives the query builder in dynamic mode and returns it modified. The generic types (PgSelect, MySqlSelect, etc.) are specifically designed for this pattern of dynamic query composition.
Example: chaining dynamic query builder functions
This example shows how to compose multiple functions that enhance a dynamic query builder: ```ts function withFriends<T extends PgSelect>(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 query builder remains in dynamic mode throughout the composition, allowing additional modifications to be applied.
sql.placeholder() for dynamic values in prepared statements
Use sql.placeholder(...) to embed dynamic runtime values in prepared statements. The placeholder accepts a parameter name as a string, and values are passed as an object to the execute() method. Example: .where(eq(customers.id, sql.placeholder('id'))).prepare() then await prepared.execute({ id: 10 }).
Create prepared statement with prepare() method
To create a prepared statement, call the .prepare() method on a query chain. Example: const prepared = db.select().from(customers).prepare(); Then execute it multiple times with prepared.execute().
Database driver prepared statement support varies
Different database drivers support prepared statements in different ways. Drizzle ORM can sometimes achieve faster performance than individual drivers like better-sqlite3.
Query execution steps in database
When a query runs on the database, three main steps occur: query builder configurations are concatenated into a SQL string, that string and parameters are sent to the database driver, and the driver compiles the SQL query to binary SQL executable format and sends it to the database.