Drizzle MSSQL driver support
Drizzle has native support for MSSQL connections with the node-mssql driver.
mssql/setup
17 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
MSSQL connection environment variable
Set up a DATABASE_URL environment variable to configure the connection to your MSSQL database.
MSSQL initial packages to install
Install drizzle-orm@rc, mssql, and dotenv as dependencies. Install drizzle-kit@rc and tsx as development dependencies.
Getting started with MSSQL setup steps
The complete setup process for Drizzle ORM with MSSQL involves: installing packages, setting up connection variables, connecting Drizzle ORM to the database, creating a table, setting up the Drizzle config file with mssql dialect, applying changes to the database, seeding and querying the database, and running the TypeScript file.
MSSQL prerequisites
To get started with Drizzle ORM and MSSQL, you need three prerequisites: dotenv package for managing environment variables, tsx package for running TypeScript files, and node-mssql package for querying your MSSQL database.
MSSQL pagination method names differ from other databases
MSSQL uses fetch() instead of limit() for specifying the number of rows to return in pagination queries.
MSSQL Pool initialization requires await
The node-mssql driver requires await on Pool initialization. When providing your own Pool instance to Drizzle, you must await it before each request unless you provide a pre-initialized Pool instance. To access db.$client, you first need to await it and then use it: const awaitedClient = await db.$client.$instance(); const response = awaitedClient.query...;
Initialize Drizzle with MSSQL config object
You can initialize Drizzle with an MSSQL config object containing a connection property: const db = drizzle({ connection: process.env.DATABASE_URL });
MSSQL driver import path
To use Drizzle with MSSQL, import the drizzle function from 'drizzle-orm/node-mssql'.
MSSQL packages to install
To use Drizzle with MSSQL, install drizzle-orm@rc, mssql, and drizzle-kit@rc as a dev dependency.
Initialize Drizzle with MSSQL connection pool
You can provide your own MSSQL connection pool to Drizzle: import { connect } from 'mssql'; const pool = await connect(process.env.DATABASE_URL!); const db = drizzle({ client: pool });
InferSelectModel and InferInsertModel type helpers
Use InferSelectModel and InferInsertModel from drizzle-orm to infer TypeScript types from an MSSQL table schema. These can be accessed via typeof table.$inferSelect and typeof table.$inferInsert respectively, or by directly calling InferSelectModel<typeof table> and InferInsertModel<typeof table>.
getTableConfig to inspect MSSQL table metadata
Call getTableConfig(table) to inspect table metadata and receive an object with properties: columns, indexes, foreignKeys, checks, primaryKeys, name, and schema.
Mock driver with drizzle.mock()
Create a typed database object without a real connection by calling drizzle.mock({ schema }). This is useful for testing when you need a typed database interface.
mssqlTableCreator for multi-project schemas
Use mssqlTableCreator to customize table names across multiple projects sharing one database. The function accepts a callback that receives the table name and returns a prefixed version. Example: const mssqlTable = mssqlTableCreator((name) => `project1_${name}`).
Enable query logging in MSSQL
Pass { logger: true } to the drizzle() function to enable default query logging. Example: const db = drizzle(process.env.DB_URL, { logger: true });
MSSQL schema definition example with countries and cities
The example shows MSSQL table definitions: countries table has id (int IDENTITY primary key) and name (varchar 256); cities table has id (int IDENTITY primary key), name (varchar 256), and countryId (int foreign key referencing countries.id).