# `@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).
