drizzle-team / drizzle-team/drizzle-orm

[FEATURE]:Support catalog-qualified tables for cross-database queries

Open
#6,015 0 comments 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

### Feature hasn't been suggested before.

- [x] I have verified this feature I'm about to request hasn't been suggested before.

### Describe the enhancement you want to request

## Feature request

Support mapping a table to a SQL catalog (database), allowing queries across multiple databases on the same server.

This would primarily benefit SQL Server, but it also aligns with the SQL standard object hierarchy:

```
catalog.schema.table
```

Currently Drizzle models only:

```
schema.table
```

which makes it impossible to represent database-qualified tables.

## Use case

SQL Server natively supports querying across multiple databases on the same server:

```sql
SELECT *
FROM ERP.dbo.Users u
JOIN CRM.dbo.Customers c
ON u.CustomerId = c.Id;
```

Many enterprise SQL Server deployments organize data into multiple databases (ERP, CRM, HR, etc.) while still relying on cross-database joins.

For example:

```ts
import { users } from "./schema/erp";
import { customers } from "./schema/crm";

await db
.select()
.from(users)
.leftJoin(customers, eq(users.customerId, customers.id));
```

Currently this is not possible because Drizzle cannot represent the database (catalog) portion of a table identifier.

The only available workarounds are:

- Raw SQL
- Views
- Synonyms

These approaches either lose type safety or require changes to the database schema.

## Proposed API

Allow tables to optionally specify a catalog (database).

```ts
const users = mssqlTable(
"Users",
{
id: int(),
customerId: int(),
},
{
catalog: "ERP",
schema: "dbo",
}
);

const customers = mssqlTable(
"Customers",
{
id: int(),
},
{
catalog: "CRM",
schema: "dbo",
}
);
```

The generated SQL would become:

```sql
FROM [ERP].[dbo].[Users]
JOIN [CRM].[dbo].[Customers]
```

instead of:

```sql
FROM [dbo].[Users]
JOIN [dbo].[Customers]
```

## drizzle-kit

This feature would also make it possible for `drizzle-kit pull` to support multiple databases in the future.

For example, users could introspect different databases separately while preserving catalog information in the generated schema.

## Why use "catalog"?

Using `catalog` follows the SQL standard (`catalog.schema.table`) instead of introducing a SQL Server-specific concept.

Different dialects could map it appropriately:

- SQL Server: catalog = database
- PostgreSQL: catalog = database (usually omitted)
- MySQL: database maps naturally to catalog

Dialects that do not support catalog-qualified identifiers could simply ignore this field.

## Backward compatibility

This would be a completely optional field.

Existing schemas and applications would continue to work without any changes.

Only dialects that support catalog-qualified identifiers would emit the additional qualifier when generating SQL.

Contributor guide

Open the contributing guide

Research direction

Start at the mssqlTable entry point and trace how table identifiers are generated for the SQL Server dialect. Review the drizzle-kit pull entry point and the dialect behavior described in the issue. Done means optional catalog-qualified SQL is generated for supported dialects while existing schemas continue to work unchanged.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql, typescript
Domain
backend-api-design, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
40/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.