$count query utility not supported in MSSQL
The $count query utility is not currently supported for MSSQL databases in Drizzle ORM.
mssql
67 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
UNION ALL example with builder-pattern
import { onlineSales, inStoreSales } from './schema' const result = await db .select({ transaction: onlineSales.transactionId }) .from(onlineSales) .unionAll( db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales) );
INTERSECT example with import-pattern
import { intersect } from 'drizzle-orm/mssql-core' import { depA, depB } from './schema' const departmentACourses = db.select({ courseName: depA.courseName }).from(depA); const departmentBCourses = db.select({ courseName: depB.courseName }).from(depB); const result = await intersect(departmentACourses, departmentBCourses);
INTERSECT example with builder-pattern
import { depA, depB } from './schema' const result = await db .select({ courseName: depA.courseName }) .from(depA) .intersect(db.select({ courseName: depB.courseName }).from(depB));
EXCEPT example with import-pattern
import { except } from 'drizzle-orm/mssql-core' import { depA, depB } from './schema' const departmentACourses = db.select({ courseName: depA.projectsName }).from(depA); const departmentBCourses = db.select({ courseName: depB.projectsName }).from(depB); const result = await except(departmentACourses, departmentBCourses);
SQL set operations standard: UNION, INTERSECT, EXCEPT, UNION ALL
SQL set operations combine the results of multiple query blocks into a single result. The SQL standard defines four set operations: UNION, INTERSECT, EXCEPT, and UNION ALL.
EXCEPT example with builder-pattern
import { depA, depB } from './schema' const result = await db .select({ courseName: depA.projectsName }) .from(depA) .except(db.select({ courseName: depB.projectsName }).from(depB));
UNION example with import-pattern
import { union } from 'drizzle-orm/mssql-core' import { users, customers } from './schema' const allNamesForUserQuery = db.select({ name: users.name }).from(users); const result = await union( allNamesForUserQuery, db.select({ name: customers.name }).from(customers) );
UNION ALL example with import-pattern
import { unionAll } from 'drizzle-orm/mssql-core' import { onlineSales, inStoreSales } from './schema' const onlineTransactions = db.select({ transaction: onlineSales.transactionId }).from(onlineSales); const inStoreTransactions = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales); const result = await unionAll(onlineTransactions, inStoreTransactions);
UNION example with builder-pattern
import { users, customers } from './schema' const result = await db .select({ name: users.name }) .from(users) .union(db.select({ name: customers.name }).from(customers));
createSchemaFactory for advanced use cases
createSchemaFactory from drizzle-orm/typebox allows creating schema factory functions with custom configuration. It accepts a configuration object with typeboxInstance property to use an extended TypeBox instance, enabling integration with frameworks like Elysia that provide their own TypeBox extensions.
Example: createInsertSchema with Value.Parse validation
The following example shows how to use createInsertSchema with TypeBox Value.Parse to validate data before insertion: ```ts import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createInsertSchema } from 'drizzle-orm/typebox-legacy'; import { Value } from '@sinclair/typebox/value'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), age: int().notNull() }); const userInsertSchema = createInsertSchema(users); const user = { name: 'Jane', age: 30 }; const parsed: { name: string, age: number } = Value.Parse(userInsertSchema, user); await db.insert(users).values(parsed); ```
Example: createUpdateSchema with Value.Parse validation
The following example shows how to use createUpdateSchema with TypeBox Value.Parse to validate partial update data: ```ts import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createUpdateSchema } from 'drizzle-orm/typebox-legacy'; import { Value } from '@sinclair/typebox/value'; import { eq } from "drizzle-orm"; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), age: int().notNull() }); const userUpdateSchema = createUpdateSchema(users); const user = { age: 35 }; const parsed: { name?: string | undefined, age?: number | undefined } = Value.Parse(userUpdateSchema, user); await db.update(users).set(parsed).where(eq(users.name, 'Jane')); ```
Example: Schema refinements with callbacks and overwrites
The following example shows how to use refinements to extend or overwrite field schemas: ```ts import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createSelectSchema } from 'drizzle-orm/typebox-legacy'; import { Type } from '@sinclair/typebox'; import { Value } from '@sinclair/typebox/value'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), bio: text(), preferences: text() }); const userSelectSchema = createSelectSchema(users, { name: (schema) => Type.String({ ...schema, maxLength: 20 }), bio: (schema) => Type.String({ ...schema, maxLength: 1000 }), preferences: Type.Object({ theme: Type.String() }) }); const parsed: { id: number; name: string, bio: string | null; preferences: { theme: string; }; } = Value.Parse(userSelectSchema, ...); ```
Example: createSchemaFactory with extended TypeBox instance
The following example shows how to use createSchemaFactory with an extended TypeBox instance from Elysia: ```ts import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createSchemaFactory } from 'drizzle-orm/typebox'; import { t } from 'elysia'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), age: int().notNull() }); const { createInsertSchema } = createSchemaFactory({ typeboxInstance: t }); const userInsertSchema = createInsertSchema(users, { name: (schema) => t.Number({ ...schema, error: "`name` must be a string" }), }); ```
createInsertSchema generates validation schema for INSERT operations
createInsertSchema from drizzle-orm/typebox-legacy generates a TypeBox schema that describes the shape of data to be inserted into the database. It can be used to validate API requests. Columns with no default values and marked as notNull are required in the insert schema.
createUpdateSchema generates validation schema for UPDATE operations
createUpdateSchema from drizzle-orm/typebox-legacy generates a TypeBox schema that describes the shape of data to be updated in the database. It can be used to validate API requests. All fields in the update schema are optional, allowing partial updates.
Example: createSelectSchema with Value.Parse validation
The following example shows how to use createSelectSchema with TypeBox Value.Parse to validate SELECT query results: ```ts import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createSelectSchema } from 'drizzle-orm/typebox-legacy'; import { Value } from '@sinclair/typebox/value'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), age: int().notNull() }); const userSelectSchema = createSelectSchema(users); const rows = await db.select().from(users).limit(1); const parsed: { id: number; name: string; age: number } = Value.Parse(userSelectSchema, rows[0]); ```
Refine fields in typebox schemas
You can refine typebox schema fields by passing a function as the value in the second parameter. The function receives the generated schema and returns a modified typebox Type, allowing you to add constraints like minimum value or change field properties before they become nullable/optional in the final schema.
typebox schema generation with MSSQL example
Example showing typebox schema generation with MSSQL: ```ts import { int, mssqlTable, text, datetime2 } from 'drizzle-orm/mssql-core'; import { createInsertSchema, createSelectSchema, createUpdateSchema } from 'drizzle-orm/typebox'; import { Type } from 'typebox'; import { Value } from 'typebox/value'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), email: text().notNull(), role: text({ enum: ['admin', 'user'] }).notNull(), createdAt: datetime2('created_at').notNull(), }); // Schema for inserting a user - can be used to validate API requests const insertUserSchema = createInsertSchema(users); // Schema for updating a user - can be used to validate API requests const updateUserSchema = createUpdateSchema(users); // Schema for selecting a user - can be used to validate API responses const selectUserSchema = createSelectSchema(users); // Overriding the fields const insertUserSchema = createInsertSchema(users, { role: Type.String(), }); // Refining the fields const insertUserSchema = createInsertSchema(users, { id: (schema) => Type.Number({ ...schema, minimum: 0 }), role: Type.String(), }); // Usage const isUserValid: boolean = Value.Check(insertUserSchema, { name: 'John Doe', email: 'johndoe@test.com', role: 'admin', }); ```
Value.Check for typebox schema validation
The Value.Check function from the typebox/value module validates data against a typebox schema, returning a boolean indicating whether the data is valid.
typebox schema generation features
Typebox integration allows you to create select schemas, insert schemas, and update schemas for Drizzle ORM tables. The supported dialect is MSSQL.
countDistinct aggregation function in MSSQL
Use `countDistinct()` from drizzle-orm to count non-duplicate values. Example: `countDistinct(users.id)` returns the count of distinct id values. Generated SQL: `count(distinct [id])`. Equivalent to `sql`count(distinct ${users.id})`.mapWith(Number)`.
Partial select in MSSQL
To select only specific columns from a table, pass a selection object to `.select()` with field mappings. Example: `.select({ field1: users.id, field2: users.name }).from(users)`. You can also use arbitrary expressions as selection fields, not just table columns, such as `.select({ id: users.id, lowerName: sql<string>`lower(${users.name})` }).from(users)`.
Conditional select in MSSQL
To build a dynamic selection object based on conditions, use the spread operator with ternary expressions. Example: `.select({ id: users.id, ...(withName ? { name: users.name } : {}) }).from(users)`.
getColumns helper in MSSQL
The `getColumns()` function returns all columns from a table, allowing you to add computed fields or exclude specific columns. Example to add a computed field: `await db.select({ ...getColumns(posts), titleLength: sql<number>`length(${posts.title})` }).from(posts)`. Example to exclude a column: `const { content, ...rest } = getColumns(posts); await db.select({ ...rest }).from(posts);`
Basic filtering with operators in MSSQL
Use filter operators like `eq()`, `lt()`, `gte()`, and `ne()` in the `.where()` method to filter query results. Examples: `eq(users.id, 42)` for equality, `lt(users.id, 42)` for less than, `gte(users.id, 42)` for greater than or equal, `ne(users.id, 42)` for not equal.
Custom filters with sql function in MSSQL
You can write arbitrary SQL filters using the `sql` function. Example: `await db.select().from(users).where(sql`${users.id} = 42`)`. All values are parameterized automatically. All filter operators are implemented using the `sql` function, and you can inspect their implementations for reference.
Parameter safety in MSSQL queries
All values provided to filter operators and the `sql` function are parameterized automatically. For example, `eq(users.id, 42)` translates to `select [id], [name], [age] from [users] where [users].[id] = @par0; -- params: [42]`. This protects against SQL injection.
NOT operator in MSSQL
Invert filter conditions using the `not()` operator. Example: `not(eq(users.id, 42))` or `sql`not ${users.id} = 42``. This generates SQL with NOT clauses.
Schema safety with template interpolation in MSSQL
Drizzle uses template interpolation to reference tables and columns in queries, so altering schema and renaming tables and columns will automatically be reflected in your queries. This is safer than hardcoding column or table names in raw SQL.
OR operator in MSSQL
Combine multiple filter conditions with the `or()` operator. Example: `or(eq(users.id, 42), eq(users.name, 'Dan'))`. You can also use raw SQL: `sql`${users.id} = 42 or ${users.name} = 'Dan'``.
FETCH and OFFSET in MSSQL
In MSSQL, FETCH and OFFSET are part of the ORDER BY clause, so they can only be used after `.orderBy()`. Example: `await db.select().from(users).orderBy(asc(users.id)).offset(5)` or `await db.select().from(users).orderBy(asc(users.id)).offset(5).fetch(10)`. Generated SQL: `select [id], [name], [age] from [users] offset 5 rows` and `select [id], [name], [age] from [users] offset 5 rows fetch next 10 rows`.
TOP clause in MSSQL
Use `.top(n)` to limit the rows returned in a query result set to a specified number of rows. Example: `await db.select().top(10).from(users)` generates `select top (10) [id], [name], [age] from [users]`.
ORDER BY in MSSQL
Use `.orderBy()` to add an ORDER BY clause to sort results. Pass column references directly for ascending order, or use `asc()` and `desc()` for explicit direction control. Example: `await db.select().from(users).orderBy(users.name)` or `await db.select().from(users).orderBy(desc(users.name))`. Multiple columns: `await db.select().from(users).orderBy(users.name, users.name2)` or `await db.select().from(users).orderBy(asc(users.name), desc(users.name2))`.
Limit-offset pagination in MSSQL
Implement limit-offset pagination using `.orderBy()` (mandatory), `.offset()` to skip rows, and `.fetch()` to limit result count. Example: `await db.select().from(users).orderBy(asc(users.id)).offset(4).fetch(4)`.
Cursor-based pagination in MSSQL
Implement cursor-based pagination using `.top()` for limit, conditionally apply `.where()` with `gt()` operator to fetch rows after a cursor, and `.orderBy()` for consistent ordering. Example: `return await db.select().top(pageSize).from(users).where(cursor ? gt(users.id, cursor) : undefined).orderBy(asc(users.id))`. Pass the cursor of the last row from the previous page (e.g., id value).
WITH clause (CTE) in MSSQL
Use Common Table Expressions (CTEs) to simplify complex queries by splitting them into smaller subqueries. Example: `const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))); const result = await db.with(sq).select().from(sq);` generates `with [sq] as (select [id], [name], [age] from [users] where [users].[id] = 42) select [id], [name], [age] from [sq];`.
GROUP BY and aggregation in MSSQL
Use `.groupBy()` to group rows and aggregate functions like `sum`, `count`, `avg` to compute values per group. When selecting aggregating functions and other columns in one query, use the `.groupBy()` clause. Example with cast: `await db.select({ age: users.age, count: sql<number>`cast(count(${users.id}) as int)` }).from(users).groupBy(users.age)`.
HAVING clause in MSSQL
Use `.having()` to filter groups after aggregation. Example: `await db.select({ age: users.age, count: sql<number>`cast(count(${users.id}) as int)` }).from(users).groupBy(users.age).having(({ count }) => gt(count, 1))` filters to show only groups with count greater than 1.
count aggregation function in MSSQL
Use `count()` from drizzle-orm to count rows. Returns the number of values in an expression. Examples: `count()` counts all rows, `count(users.id)` counts non-null id values. Generated SQL: `count(*)` and `count([id])`. Equivalent to `sql`count(*)`.mapWith(Number)` and `sql`count(${users.id})`.mapWith(Number)` respectively.
avg aggregation function in MSSQL
Use `avg()` from drizzle-orm to compute the average (arithmetic mean) of all non-null values. Example: `avg(users.id)` returns the average id. Generated SQL: `avg([id])`. Equivalent to `sql`avg(${users.id})`.mapWith(String)`.
avgDistinct aggregation function in MSSQL
Use `avgDistinct()` from drizzle-orm to compute the average (arithmetic mean) of all non-null and non-duplicate values. Example: `avgDistinct(users.id)` returns the average of distinct id values. Generated SQL: `avg(distinct [id])`. Equivalent to `sql`avg(distinct ${users.id})`.mapWith(String)`.
sum aggregation function in MSSQL
Use `sum()` from drizzle-orm to compute the sum of all non-null values. Example: `sum(users.id)` returns the sum of all id values. Generated SQL: `sum([id])`. Equivalent to `sql`sum(${users.id})`.mapWith(String)`.
sumDistinct aggregation function in MSSQL
Use `sumDistinct()` from drizzle-orm to compute the sum of all non-null and non-duplicate values. Example: `sumDistinct(users.id)` returns the sum of distinct id values. Generated SQL: `sum(distinct [id])`. Equivalent to `sql`sum(distinct ${users.id})`.mapWith(String)`.
max aggregation function in MSSQL
Use `max()` from drizzle-orm to get the maximum value. Example: `max(users.id)` returns the maximum id value. Generated SQL: `max([id])`. Equivalent to `sql`max(${users.id})`.mapWith(users.id)`.
min aggregation function in MSSQL
Use `min()` from drizzle-orm to get the minimum value. Example: `min(users.id)` returns the minimum id value. Generated SQL: `min([id])`. Equivalent to `sql`min(${users.id})`.mapWith(users.id)`.
Complex aggregation query example in MSSQL
Example of a complex aggregation query using multiple aggregation functions with joins and grouping: select orders.id, orders.shippedDate, orders.shipName, orders.shipCity, orders.shipCountry, count of products, sum of quantity, and total price by multiplying quantity * unitPrice, from orders left join order_detail on orders.id = details.orderId, group by orders.id, orders.shipName, orders.shippedDate, orders.shipCity, orders.shipCountry, and order by orders.id ascending.
Iterator for large result sets in MSSQL
To return a very large amount of rows without loading them all into memory, use `.iterator()` to convert a query into an async iterator. Example: `const iterator = db.select().from(users).iterator(); for await (const row of iterator) { console.log(row); }`. This also works with prepared statements: `const query = db.select().from(users).prepare(); const iterator = query.iterator();`.
mapWith method for runtime type transformation in MSSQL
Use `.mapWith()` to apply runtime transformations to SQL expression results when you need to cast values at runtime. This is useful when Drizzle cannot perform type casts based on the provided type generic. Example: use `.mapWith(Number)` to cast a value to a number at runtime.
$count aggregation not yet supported in MSSQL
The `$count` API for counting is currently not supported in MSSQL. Use the `count()` aggregation helper instead.
MSSQL update with SQL expression as value
You can pass SQL as a value in the update object using `sql` tag. For example: `await db.update(users).set({ updatedAt: sql`NOW()` }).where(eq(users.name, 'Dan'))`.
MSSQL update output clause
The `.output()` method allows returning values from rows affected by an update query. MSSQL supports returning `INSERTED` (new row values) and `DELETED` (old row values). If `.output()` is called with no parameters, all inserted values are returned by default.
MSSQL update output default behavior
Calling `.output()` with no arguments or `.output({ inserted: true })` returns all new inserted values from the updated rows.
MSSQL update output returning old values
Call `.output({ deleted: true })` to return all old values from the rows before the update was applied.
MSSQL update query parameterization
All values provided to `.set()` are parameterized automatically in MSSQL update queries. For example, `await db.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan"))` is translated to `update [users] set [name] = @par0 where [users].[name] = @par1; -- params: ['Mr. Dan', 'Dan']`.
MSSQL update output with partial old and new values
You can specify which columns to return from old and new values using `.output({ deleted: { oldColor: cars.color }, inserted: { newColor: cars.color } })`. This allows selecting specific columns from both `DELETED` and `INSERTED` states.
MSSQL update set object keys must match column names
The object passed to `.set()` must have keys that match column names in the database schema. Values of `undefined` are ignored in the object. To set a column to `null`, pass `null` explicitly.
Refinements example with pipe and maxLength
Refinements can use valibot's pipe and maxLength functions to extend schemas. For example, passing (schema) => pipe(schema, maxLength(20)) extends the name field with a max length constraint. This approach preserves the field's original nullability.
MSSQL valibot data type mappings
MSSQL data type mappings in valibot schemas follow the MSSQL column builders. Refer to the MSSQL data types documentation page for the full column reference.