# `@askdb/sqlserver` Microsoft SQL Server integration for AskDB. Bundles three pieces: 1. **Dialect** — re-exports `SQLSERVER_DIALECT` from `@askdb/core`. Pass `dialect: SQLSERVER_DIALECT` to `ask()` to target SQL Server (T-SQL). 2. **Connector** — `createSqlServerConnector()` implements the `Connector` contract from `@askdb/introspect` for live introspection mode. 3. **Catalog runner** — `createSqlServerCatalogQueryRunner(connectionString)` returns an introspection-only `CatalogQueryRunner` backed by `mssql` (peer dependency, lazy-loaded). Introspects via `sys.*` catalog views. ## Install ```bash pnpm add @askdb/core @askdb/introspect @askdb/sqlserver ai ``` `ai` (Vercel AI SDK 6 or 7) is a required peer dependency of `@askdb/core`; your app owns its version. `mssql` is an **optional peer dependency** — install it only when using live introspection mode: ```bash pnpm add mssql ``` For one-off CLI introspection, include the driver in the same ephemeral command: ```bash pnpm dlx -p askdb -p mssql askdb introspect --engine sqlserver --url "$SQLSERVER_URL" npx -p askdb -p mssql askdb introspect --engine sqlserver --url "$SQLSERVER_URL" ``` ## Usage ### NL→SQL ```ts import { ask, loadSchema } from "@askdb/core"; import { SQLSERVER_DIALECT } from "@askdb/sqlserver"; const schema = loadSchema("./my-app.schema"); const { sql } = await ask({ question: "How many paid orders were created last month?", schema, model, dialect: SQLSERVER_DIALECT, }); ``` ### Introspection ```ts import { introspect } from "@askdb/introspect"; import { createSqlServerConnector, createSqlServerCatalogQueryRunner } from "@askdb/sqlserver"; const result = await introspect( { mode: "live", runner: createSqlServerCatalogQueryRunner( "Server=host,1433;Database=mydb;User Id=sa;Password=pass;TrustServerCertificate=True;", ), }, { outDir: "./my-app.schema", schemaId: "my-app" }, { connector: createSqlServerConnector() }, ); ``` ### Connection string formats `createSqlServerCatalogQueryRunner` and `databaseUrl` in `askdb.config.ts` accept three formats: | Format | Example | |--------|---------| | `mssql://` URL | `mssql://sa:pass@localhost:1433/MyDb` | | Prisma `sqlserver://` | `sqlserver://localhost:1433;database=MyDb;user=sa;password=pass;encrypt=true` | | ADO.NET (`Key=Value;`) | `Server=localhost,1433;Database=MyDb;User Id=sa;Password=pass;` | In the Prisma form, wrap a value that contains `;` in curly braces, as Prisma does: `password={Pass:Word;}` is `Pass:Word;`. AskDB reads braces this way only where its earlier parser couldn't read the value, so strings that worked before keep connecting with the same values: - A value is an escape when it starts with `{`, has a `;` before its first `}`, and ends at the first `}` that is followed only by blanks and then a `;` or the end of the string, where the `;`-separated piece that ends at that `}` has no `=`. Everything between the braces is the value, read verbatim: `{{a;b}}` is `{a;b}`. - Any other brace is a plain character, as before: `password={abc}` is `{abc}`, and a `{` that is never closed is part of the value. - Prisma reads every `{…}` as an escape, so a URL shared with Prisma that braces a value without a `;` (such as `password={Pass:Word}`) reaches SQL Server with its braces when AskDB connects. - Prisma's credential keys `username`, `uid` and `pwd` are also read when `user` or `password` is absent (`username` before `uid`); the canonical key wins when both are set. In both URL forms, `encrypt` and `trustServerCertificate` read `true`, `yes` and `1` as true and `false`, `no` and `0` as false, in any case. Any other `encrypt` value turns encryption on, and any other `trustServerCertificate` value leaves the server certificate untrusted. An ADO.NET string is any string that doesn't start with a scheme (`name://`), so a password holding `://` doesn't stop AskDB from reading its spaced keys such as `Trust Server Certificate=True`. **TLS / self-signed certificates** SQL Server uses TLS by default. If you connect to a local or dev instance with a self-signed certificate you will see a `self-signed certificate` error unless you tell the driver to trust it: - **`mssql://` URL** — append `?trustServerCertificate=true`: ``` mssql://sa:pass@localhost:1433/MyDb?trustServerCertificate=true ``` - **Prisma URL** — append `;trustServerCertificate=true`: ``` sqlserver://localhost:1433;database=MyDb;user=sa;password=pass;trustServerCertificate=true ``` - **ADO.NET string** — add `TrustServerCertificate=True;` (note: use the camelCase form without spaces — `Trust Server Certificate` with spaces is a known parsing gap in mssql v12): ``` Server=localhost,1433;Database=MyDb;User Id=sa;Password=pass;Encrypt=True;TrustServerCertificate=True; ``` > **Never set `TrustServerCertificate=True` in production** unless you have verified the server's certificate through another means. Use a properly signed certificate, or install the CA cert in the system trust store (`NODE_EXTRA_CA_CERTS` / `--use-system-ca`). **Labels for display** `sqlServerConnectorProvider` parses a connection into display-safe parts, so a connector registry's `connectionLabel()` (and the `sourceLabel` from `resolveConnection()`) shows only the host, port and database from any of the three formats: `Server=localhost,1433;Database=app;User Id=sa;Password=pass;` becomes `sqlserver://localhost:1433/app`. The parts come from the same code the connection uses: `resolveConnectionInput()` for `mssql://` and `sqlserver://`, and `@tediousjs/connection-string` (the parser `mssql` uses, a dependency of this package) for ADO.NET strings, so the label names the host and database the driver will use. A string the driver rejects, a named instance or pipe, an `@` in the `sqlserver://` form, a `sqlserver://` string that can be read more than one way (it holds a `{`, a quote, a piece without `=`, or an empty piece before another one), or JDBC becomes `configured sqlserver connection`. ## Captured metadata Tables, views, columns (SQL Server native type strings), primary keys, unique constraints, foreign keys (with referential actions), and indexes. Objects shipped by SQL Server itself (`is_ms_shipped = 1`, e.g. replication `MS*` tables) are excluded. ## License Apache-2.0 © [Yahya Gilany](https://yahyagilany.io). See [LICENSE](./LICENSE) and [NOTICE](./NOTICE).