SQL Server quickstart

This tutorial uses the stable grammar, compiler and Node integration. Application code imports sql from the grammar and keeps generated schema files as compiler inputs.

1. Prepare the connection

Use Node.js 24.11.1 or newer on Linux x64, unixODBC, Microsoft ODBC Driver 18, and a boxed SQL Server 2017–2025 database. Create an empty database named app and use a login with schema creation and VIEW DEFINITION permissions. Compatibility distinguishes supported families from exact tested builds.

Set your connection string:

export SQLSERVER_CONNECTION_STRING='Driver={ODBC Driver 18 for SQL Server};Server=localhost,1433;Database=app;UID=app;PWD=your-password;Encrypt=yes;TrustServerCertificate=yes;'

2. Install the application dependencies

mkdir typed-sql-sqlserver && cd typed-sql-sqlserver
pnpm init
pnpm pkg set type=module
pnpm add @typed-sql/core @typed-sql/sqlserver koffi
pnpm add -D @typed-sql/cli @typed-sql/build esbuild typescript @types/node
mkdir src

The native SDK belongs to your application. Installing the grammar does not install a driver. Create tsconfig.json:

{
  "compilerOptions": {
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "target": "ES2024",
    "strict": true,
    "types": ["node"],
    "noEmit": true
  },
  "include": ["src/**/*.ts", "typed-sql.config.ts"]
}

3. Create the commerce schema

Save this fixture as schema.sql:

CREATE TABLE dbo.users (
  id bigint IDENTITY(1,1) NOT NULL CONSTRAINT users_pk PRIMARY KEY,
  email nvarchar(320) NOT NULL CONSTRAINT users_email_uq UNIQUE,
  status varchar(16) NOT NULL CONSTRAINT users_status_default DEFAULT 'active',
  profile nvarchar(max) NOT NULL CONSTRAINT users_profile_default DEFAULT N'{}',
  created_at datetime2(7) NOT NULL CONSTRAINT users_created_default DEFAULT SYSUTCDATETIME(),
  CONSTRAINT users_profile_json CHECK (ISJSON(profile)=1)
);
CREATE TABLE dbo.teams (
  id bigint IDENTITY(1,1) NOT NULL CONSTRAINT teams_pk PRIMARY KEY,
  name nvarchar(160) NOT NULL
);
CREATE TABLE dbo.memberships (
  user_id bigint NOT NULL CONSTRAINT memberships_user_fk REFERENCES dbo.users(id),
  team_id bigint NOT NULL CONSTRAINT memberships_team_fk REFERENCES dbo.teams(id),
  role varchar(16) NOT NULL,
  CONSTRAINT memberships_pk PRIMARY KEY(user_id,team_id)
);
CREATE TABLE dbo.products (
  id bigint NOT NULL CONSTRAINT products_pk PRIMARY KEY,
  sku nvarchar(64) NOT NULL CONSTRAINT products_sku_uq UNIQUE,
  name nvarchar(160) NOT NULL,
  price_cents int NOT NULL,
  CONSTRAINT products_price_check CHECK(price_cents>=0)
);
CREATE TABLE dbo.orders (
  id bigint NOT NULL CONSTRAINT orders_pk PRIMARY KEY,
  user_id bigint NOT NULL CONSTRAINT orders_user_fk REFERENCES dbo.users(id),
  status varchar(32) NOT NULL CONSTRAINT orders_status_default DEFAULT 'new',
  total_cents int NOT NULL,
  amount decimal(38,10) NOT NULL,
  placed_at datetime2(7) NOT NULL CONSTRAINT orders_placed_default DEFAULT SYSUTCDATETIME()
);
CREATE TABLE dbo.order_items (
  order_id bigint NOT NULL CONSTRAINT items_order_fk REFERENCES dbo.orders(id),
  product_id bigint NOT NULL CONSTRAINT items_product_fk REFERENCES dbo.products(id),
  quantity int NOT NULL,
  unit_price_cents int NOT NULL,
  CONSTRAINT order_items_pk PRIMARY KEY(order_id,product_id),
  CONSTRAINT items_quantity_check CHECK(quantity>0)
);
CREATE TABLE dbo.audit_events (
  id bigint NOT NULL CONSTRAINT audit_events_pk PRIMARY KEY,
  user_id bigint NULL CONSTRAINT audit_events_user_fk REFERENCES dbo.users(id),
  action nvarchar(64) NOT NULL,
  payload nvarchar(max) NOT NULL,
  created_at datetimeoffset(7) NOT NULL CONSTRAINT audit_created_default DEFAULT SYSDATETIMEOFFSET(),
  CONSTRAINT audit_payload_json CHECK(ISJSON(payload)=1)
);
CREATE INDEX orders_user_placed ON dbo.orders(user_id,placed_at DESC) INCLUDE(status,total_cents);
CREATE INDEX audit_user_present ON dbo.audit_events(user_id) WHERE user_id IS NOT NULL;

INSERT INTO dbo.users(email,profile) VALUES
  (N'ada@example.com',N'{"displayName":"Ada","notifications":true}'),
  (N'grace@example.com',N'{"displayName":"Grace","notifications":false}');
INSERT INTO dbo.teams(name) VALUES (N'Engineering'),(N'Operations');
INSERT INTO dbo.memberships(user_id,team_id,role) VALUES (1,1,'owner'),(2,1,'member'),(2,2,'owner');
INSERT INTO dbo.products(id,sku,name,price_cents) VALUES
  (1001,N'TEA-01',N'Green tea',1299),(1002,N'MUG-01',N'Ceramic mug',1999);
INSERT INTO dbo.orders(id,user_id,status,total_cents,amount,placed_at) VALUES
  (9001,1,'paid',3298,32.9800000000,'2026-01-02T03:04:05.1234567'),
  (9002,2,'new',2598,25.9800000000,'2026-01-03T04:05:06.7654321');
INSERT INTO dbo.order_items(order_id,product_id,quantity,unit_price_cents) VALUES
  (9001,1001,1,1299),(9001,1002,1,1999),(9002,1001,2,1299);
INSERT INTO dbo.audit_events(id,user_id,action,payload) VALUES
  (5001,1,N'order.paid',N'{"orderId":9001}'),(5002,NULL,N'system.ready',N'{}');

Create src/setup.ts:

import { readFile } from "node:fs/promises";
import { createSqlServerOdbcDatabase } from "@typed-sql/sqlserver/odbc";

const connection = await createSqlServerOdbcDatabase({
  connectionString: process.env.SQLSERVER_CONNECTION_STRING!,
});
try {
  await connection.queryable.all(await readFile("schema.sql", "utf8"));
} finally {
  await connection.close();
}

node src/setup.ts

4. Select the grammar and shared policy

Create typed-sql.config.ts:

import { defineConfig } from "@typed-sql/core";
import { sqlserver, typePolicy } from "@typed-sql/sqlserver";
import { sqlServerOdbc } from "@typed-sql/sqlserver/odbc";

export default defineConfig({
  dialect: sqlserver({ typePolicy }),
  schema: {
    file: "generated/db/schema.json",
    provider: sqlServerOdbc({
      connectionString: () => process.env.SQLSERVER_CONNECTION_STRING!,
      includeSchemas: ["dbo"],
      typePolicy,
    }),
  },
  outDir: "generated/db",
  projects: ["tsconfig.json"],
  typePolicy,
});

The provider and runtime establish the same canonical session settings on connections they own. The snapshot records the actual product, engine, compatibility level, collation and native types.

5. Generate the compiler inputs

pnpm exec typed-sql generate

Commit generated/db so schema changes are reviewable. Application code does not import it.

6. Write the query

Create src/account.ts:

import { sql } from "@typed-sql/sqlserver";

export const accountById = (accountId: bigint) => sql`
  SELECT account.id, account.email, account.status
  FROM dbo.users AS account
  WHERE account.id = ${accountId}
`;

Values become parameters. The native adapter owns positional ODBC conversion and binding.

7. Check the inferred contract

pnpm exec typed-sql check --project tsconfig.json --show-types

The checked contract is:

type AccountByIdQuery = Query<
  { "id": bigint; "email": string; "status": string; },
  readonly [bigint]
>;

The stable CLI check is authoritative. An ordinary TypeScript server can show the conservative published Query<unknown> declaration; compiler and editor workflow explains the distinction.

8. Execute the checked query

Create src/run.ts:

import { typePolicy } from "@typed-sql/sqlserver";
import { createSqlServerOdbcDatabase } from "@typed-sql/sqlserver/odbc";
import { accountById } from "./account.js";

const database = await createSqlServerOdbcDatabase({
  connectionString: () => process.env.SQLSERVER_CONNECTION_STRING!,
  typePolicy,
});
try {
  console.log(await database.maybeOne(accountById(1n)));
} finally {
  await database.close();
}

Create build.mjs so inline templates receive checked execution plans:

import { build } from "esbuild";
import { typedSql } from "@typed-sql/build/esbuild";

await build({
  entryPoints: ["src/run.ts"],
  bundle: true,
  packages: "external",
  platform: "node",
  format: "esm",
  outfile: "dist/run.mjs",
  plugins: [typedSql()],
});

node build.mjs
node dist/run.mjs

The stable build integration validates source and attaches checked native value plans. Dependencies stay external, including the application-owned native SDK. A plain TypeScript execution command does not compile SQL or supply missing physical target evidence. The Node preload compiles SQL file imports; inline templates use this build adapter.

9. Verify the error boundary

Replace the bigint argument with a string interpolation:

const wrongId = "1";
sql`SELECT id FROM dbo.users WHERE id = ${wrongId}`;

Run typed-sql check again. The checker reports the mismatched bigint parameter position. Restore the valid query before running the application.

10. Add native features

Follow the SQL Server examples for named SQL files, APPLY, multi-row OUTPUT, exact decimals, transactions and streaming. Review build integrations for deployment options and execution for ownership and controls.