A SQL Server ORM for Node.js that speaks T-SQL for you

#Install the mssql driver and open a pool

kastor ships no database drivers. For SQL Server 2017 or newer and Azure SQL you install the mssql package, which is the common Node.js client built on tedious and needs no native compilation. You create a ConnectionPool, connect it (the adapter also connects it on first use if you have not), and pass it to the mssql adapter from kastor-orm/mssql. Types, queries and migrations then come from your TypeScript schema. There is no generate step and no engine binary to deploy.

For local work, the official SQL Server container image is the fastest way to get a server. It listens on port 1433 and refuses to start with a weak sa password, because SQL Server enforces a password complexity policy.

npm install kastor-orm mssql
npm install -D @types/mssql
docker run -d --name mssql -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=$MSSQL_PASSWORD" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

The client module creates one pool, connects it, and builds the kastor client on top of it. Connecting first means a wrong password or an unreachable host fails at startup, with a clear error, instead of on the first request a user makes.

import sql from 'mssql'
import { kastor, one, many } from 'kastor-orm'
import { mssql } from 'kastor-orm/mssql'
import { users, posts } from './schema'

const pool = new sql.ConnectionPool({
  server: process.env.MSSQL_HOST ?? 'localhost',
  port: 1433,
  database: 'app',
  user: process.env.MSSQL_USER ?? 'sa',
  password: process.env.MSSQL_PASSWORD,
  options: {
    encrypt: true,
    trustServerCertificate: process.env.MSSQL_TRUST_CERT === 'true',
  },
  pool: { max: 10 },
})
await pool.connect()

export const db = kastor({
  adapter: mssql(pool),
  schema: { users, posts },
  relations: ({ users, posts }) => ({
    users: { posts: many(posts) },
    posts: { author: one(users, { fields: ['authorId'], references: ['id'] }) },
  }),
})

Keep a single pool per process. In frameworks with hot module reload, store the client on a module level singleton so each reload does not open a new pool. On shutdown, await db.$disconnect() closes the pool and lets the process exit cleanly.

#Configuration: encrypt, certificates and Azure SQL

Most failed first connections come down to three settings in the options object of the pool configuration.

• encrypt turns on TLS for the connection. Recent driver versions encrypt by default and Azure SQL requires it, so leave it on everywhere.
• trustServerCertificate skips certificate validation. A local container presents a self signed certificate, so local development needs this set to true. In production keep it false and give the server a certificate from an authority your hosts trust.
• database selects the database for every connection in the pool. Without it you land in master, and your tables seem to be missing.

The driver also accepts a connection string, which is convenient when your platform hands you one secret. Either form works with the adapter, since kastor only sees the connected pool.

Azure SQL Database

For Azure SQL, set the server to yourserver.database.windows.net, keep encrypt: true and trustServerCertificate: false. The server firewall blocks unknown addresses, so add a rule for your development IP and allow access from your hosting network. If you use Microsoft Entra ID instead of SQL logins, configure the authentication option that the mssql and tedious docs describe; kastor does not need to know which method you chose.

The serverless tier can pause an idle database. The first connection after a pause may take noticeably longer or time out, which kastor reports as CONNECTION_FAILED or TIMEOUT. A short retry at startup covers it. Check the Azure documentation for the current tier behavior and limits.

#What kastor handles for SQL Server

T-SQL differs from PostgreSQL and MySQL in several places that an ORM has to get right. You write the same findMany, create or upsert call on every database, and the SQL Server dialect compiles it like this:

A few consequences are worth knowing. OFFSET FETCH is only valid with an ORDER BY. When you pass skip without orderBy, kastor adds ORDER BY (SELECT NULL) so the statement runs, but that order is not stable, and pages without a stable order are unreliable on any database, so always pass orderBy with skip. For deep pagination, use findPage, which pages by cursor instead of skipping rows.

Because includes use FOR JSON PATH, a findMany call with nested include options runs as one statement per root call, not one per row. Open the playground to see the exact SQL for each database.

Bracket quoting means table and column names that are reserved words in T-SQL, like user or order, work without renaming. Strings default to nvarchar, which avoids implicit conversions when the driver sends Unicode parameters. If a column only holds ASCII codes and you want varchar, set .nativeType('varchar(64)') on it.

The default collations ignore case, so equals on a string column matches regardless of case on SQL Server. contains, startsWith and endsWith are exact, because kastor compares them with a binary collation and escapes the [ wildcard; add mode: 'insensitive' to lower both sides. Decimals are read as text so no digits are lost, and datetimes are sent as text so DATETIME2 keeps milliseconds exactly. Unique indexes on nullable columns are filtered with WHERE col IS NOT NULL, so several rows may hold NULL, as on other databases.

Some SQL Server rules apply to any client. kastor writes rows back with OUTPUT and no INTO, and SQL Server rejects that on a table with enabled triggers, so check trigger heavy legacy tables before writing to them through the ORM. SQL Server also refuses cascading foreign keys that point to their own table or that would give a row two cascade paths; kastor writes those keys with NO ACTION, so deleting such a parent fails with FOREIGN_KEY_VIOLATION while children exist. Upserts use MERGE with a HOLDLOCK hint, which serializes concurrent upserts of the same key; keep a unique index on the key anyway, since it is what the database enforces.

#Migrations and db pull for existing databases

The config file tells the CLI where your schema lives and how to reach the database. The adapter is a function, so the CLI creates and connects the pool only when it needs it.

import sql from 'mssql'
import { defineConfig } from 'kastor-orm'
import { mssql } from 'kastor-orm/mssql'

export default defineConfig({
  schema: './src/db/schema.ts',
  adapter: () => mssql(new sql.ConnectionPool(process.env.MSSQL_URL!)),
  migrations: './kastor/migrations',
})

Run npx kastor migrate dev --name init during development. It diffs your TypeScript schema against the live database, writes a migration.sql file, and applies it. When it sees a likely rename, it asks instead of dropping the column. In CI and production, npx kastor migrate deploy applies pending files in order. SQL Server can run DDL inside a transaction, which makes a failed migration easier to recover from than on databases that commit DDL implicitly.

Most SQL Server projects start from a database that already exists. Run npx kastor db pull to introspect it into a TypeScript schema file. Review the result: rename fields to the casing you want in code and keep the database names with .map('LegacyColumn'), and check that nullable columns and defaults match what the application expects. From then on, edit the TypeScript file and let migrate dev write each change as a migration. The migrations guide covers the workflow in more depth.

#Transactions and isolation levels

An interactive transaction runs every statement on one connection taken from the pool. kastor commits when the callback resolves and rolls back when it throws. For a fixed list of writes, the array form is shorter.

import { db } from './db/client'

await db.$transaction(async (tx) => {
  const user = await tx.users.findUniqueOrThrow({ where: { id: 42 } })
  await tx.posts.create({ data: { title: 'Order confirmed', authorId: user.id } })
}, { isolationLevel: 'serializable' })

await db.$transaction([
  db.posts.deleteMany({ where: { published: false, authorId: 42 } }),
  db.users.update({ where: { id: 42 }, data: { role: 'user' } }),
])

SQL Server defaults to read committed, where readers can block on writers. Many teams turn on READ_COMMITTED_SNAPSHOT at the database level so reads use row versions instead of shared locks. That is a database setting, made once with ALTER DATABASE, and it changes behavior for every client, so test it before enabling it in production.

Serializable transactions and busy tables lead to deadlocks sooner or later. SQL Server picks a victim and aborts it with error 1205, which kastor reports as DEADLOCK. Retry the whole transaction callback, not the single statement, since the server already rolled back the work.

#Common errors and their fixes

kastor wraps driver errors in KastorError with a code, a plain English message and a hint. The SQL Server errors you will meet most often:

• Self signed certificate on connect: local server with trustServerCertificate left false. Set it to true for local development only.
• Login failed for user: wrong credentials, or SQL authentication is disabled on the instance. Containers enable it; some managed installs only allow Windows or Entra authentication.
• Connection timeout: TCP/IP is disabled, the port is not 1433, or a named instance needs its port set explicitly. Run npx kastor doctor to check the config and the connection.
• Errors 2627 and 2601 (duplicate key) arrive as UNIQUE_VIOLATION, error 547 as FOREIGN_KEY_VIOLATION (or CHECK_VIOLATION when a check constraint failed), and error 515 as NOT_NULL_VIOLATION.

import { isKastorError } from 'kastor-orm'
import { db } from './db/client'

export async function signUp(email: string) {
  try {
    return await db.users.create({ data: { email } })
  } catch (e) {
    if (isKastorError(e) && e.code === 'UNIQUE_VIOLATION') {
      return db.users.findUniqueOrThrow({ where: { email } })
    }
    throw e
  }
}

If one codebase needs SQL Server next to another database, the multi-database guide shows how to share one schema across adapters.

#Questions people ask

#Related guides