drizzle-team / drizzle-team/drizzle-orm
[BUG]: node-mssql / mssql-core Relational Queries v2 support
- 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
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