drizzle-team / drizzle-team/drizzle-orm

[BUG]: node-mssql / mssql-core Relational Queries v2 support

Open
#6,052 1 comment 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

## Summary

In `1.0.0-rc.4`, the new Relational Queries API (`defineRelations(...)` passed as `drizzle({ relations })`, exposing `db.query..findMany/findFirst`) is implemented for Postgres, SQLite, and MySQL, but **not** for MSSQL. When you construct a `node-mssql` database with a `relations` config, `db.query` is `undefined` and the `relations` option is silently ignored. Only the older `schema`-based builder (`db._query`) is available.

## Expected vs. actual

```ts
import { drizzle } from "drizzle-orm/node-mssql";
import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

const relations = defineRelations(schema, (r) => ({
/* ... */
}));
const db = drizzle({ client: pool, relations });

await db.query.users.findMany(); // Expected: RQB v2 works (as on pg/sqlite/mysql)
// Actual: TypeError — `db.query` is undefined
```

## Root cause (in the published package)

Postgres wires `config.relations` into an `async` database class that builds `this.query`:

- `drizzle-orm/node-postgres/driver.js`:
```js
const relations = config.relations ?? {};
const db = new NodePgDatabase(dialect, new NodePgSession(client, dialect, relations, {...}), relations);
```
- `drizzle-orm/pg-core/async/db.js`:
```js
this.query = {};
for (const [tableName, relation] of Object.entries(relations))
this.query[tableName] = new RelationalQueryBuilder(relations, ..., dialect, session, ...);
```

MSSQL has neither of these:

- `drizzle-orm/node-mssql/driver.js` only reads `config.schema` (the **old** relations API) and never reads `config.relations`:
```js
let schema;
if (config.schema) {
const tablesConfig = V1.extractTablesRelationalConfig(config.schema, V1.createTableRelationsHelpers);
schema = { fullSchema: config.schema, schema: tablesConfig.tables, tableNamesMap: tablesConfig.tableNamesMap };
}
const db = new MsSqlDatabase(dialect, new NodeMsSqlDriver(...).createSession(schema), schema);
```
- `drizzle-orm/mssql-core/db.js` only builds `_query` from that old `schema`, and there is **no `mssql-core/async/db.js`**:
```js
this._query = {};
if (this._.schema) for (const [tableName, columns] of Object.entries(this._.schema))
this._query[tableName] = new RelationalQueryBuilder(schema.fullSchema, this._.schema, ...);
```

Presence of the RQB-v2 `async/db.js` across cores in `1.0.0-rc.4`:

| core | `async/db.js` (RQB v2) | node driver reads `config.relations` |
| ------------------ | ---------------------- | ------------------------------------ |
| `pg-core` | yes | yes (`node-postgres`) |
| `sqlite-core` | yes | yes (`node-sqlite`, `libsql`, …) |
| `mysql-core` | yes | yes (`mysql2`) |
| **`mssql-core`** | **no** | **no** (`node-mssql`) |
| `singlestore-core` | no | — |

## Impact

Any consumer built on the v2 relations API (`db.query...`) cannot target MSSQL. Concretely: we were porting the Payload CMS database adapter to MSSQL — schema push (`drizzle-kit/payload/mssql`), inserts (`.output()`), and DDL all work, but every read goes through `db.query..findMany(...)` and fails immediately because `db.query` is undefined. There is no drop-in workaround short of maintaining a second, v1-based read path just for MSSQL.

## Ask

Bring `mssql-core`/`node-mssql` to parity with `pg-core`/`sqlite-core`/`mysql-core`:

1. Add `mssql-core/async/db.js` (the `MsSqlAsyncDatabase` equivalent that builds `this.query` from a `relations` config).
2. Have `node-mssql`'s `drizzle()` accept and forward `config.relations`.

Happy to test a build against a real SQL Server 2022 instance if that helps.

## Minimal reproduction

```ts
import * as mssql from "mssql";
import { drizzle } from "drizzle-orm/node-mssql";
import { defineRelations, sql } from "drizzle-orm";
import { int, mssqlTable, nvarchar } from "drizzle-orm/mssql-core";

const users = mssqlTable("users", {
id: int("id").identity(),
name: nvarchar("name", { length: 255 }),
});
const relations = defineRelations({ users }, () => ({}));

const pool = await new mssql.ConnectionPool(
"sqlserver://localhost:1433;database=test;user=sa;password=...;trustServerCertificate=true",
).connect();
const db = drizzle({ client: pool, relations });

console.log(typeof db.query); // "undefined" (expected: "object")
await db.query.users.findMany(); // throws: Cannot read properties of undefined (reading 'users')
```

Contributor guide

Open the contributing guide

Research direction

Read mssql-core/db.js and node-mssql/driver.js, comparing them with pg-core/async/db.js and node-postgres/driver.js. Done means node-mssql forwards config.relations, mssql-core exposes db.query table builders, and the supplied MSSQL reproduction no longer sees db.query as undefined.

Written by the indexing model from the issue text.

Assessment

Tech stack
node.js, sql, typescript
Domain
backend-api-design, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
58/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.