How to support multiple database providers (For example, SQlite + Postgres)

[eyueldk]

I've a project that has a distributed (postgres) and standalone (sqlite) mode. What is the recommended approach for using these databases with the same code? The issue mainly comes up on mutations

For example

const config = isLocal ? { db: sqliteDb, schema: sqliteSchema } : { db:  pgDb, schema: pgSchema }
await config.db.insert(config.schema.table).values({}).returning();

The issue is, config.schema.table type widens and assumes it is sqliteSchema.table | pgSchema.table instead of the one of the respective schema.
Hence, sqliteSchema.table | pgSchema.table is always wrong since insert only accepts one and rejects the other.

I hope that makes sense.

What is the recommended approach, for using multiple database providers without duplicating code?

[sueun-dev]

The root cause is a TypeScript structural typing issue. When you write:

const config = isLocal ? { db: sqliteDb, schema: sqliteSchema } : { db: pgDb, schema: pgSchema }

TypeScript infers config as { db: SQLiteDb | PgDb; schema: sqliteSchema | pgSchema }. It doesn't understand that sqliteDb and sqliteSchema are correlated — so it won't let you pass config.schema.table (which could be from either schema) to config.db.insert() (which only accepts its own schema's table).

There are a few ways to fix this, depending on how much code you want to share.

Option 1: Initialize once at module level (simplest)

Don't create a union — just pick one concrete type at startup:

import { drizzle as drizzlePg } from 'drizzle-orm/postgres-js';
import { drizzle as drizzleSqlite } from 'drizzle-orm/better-sqlite3';

const db = isLocal
  ? drizzleSqlite(sqliteConnection, { schema: sqliteSchema })
  : drizzlePg(pgConnection, { schema: pgSchema });

// Then in each module, do the narrowing once:
if (isLocal) {
  await (db as ReturnType<typeof drizzleSqlite>).insert(sqliteSchema.table).values({}).returning();
}

This works fine at runtime since Drizzle's API surface is consistent across adapters, but you still need to narrow for TypeScript.

Option 2: Discriminated union (proper type narrowing)

Add a discriminator field so TypeScript can narrow the whole object:

type DbConfig =
  | { kind: 'sqlite'; db: typeof sqliteDb; table: typeof sqliteSchema.table }
  | { kind: 'postgres'; db: typeof pgDb; table: typeof pgSchema.table };

const config: DbConfig = isLocal
  ? { kind: 'sqlite', db: sqliteDb, table: sqliteSchema.table }
  : { kind: 'postgres', db: pgDb, table: pgSchema.table };

// TypeScript narrows both `db` and `table` together here
if (config.kind === 'sqlite') {
  await config.db.insert(config.table).values({}).returning();
} else {
  await config.db.insert(config.table).values({}).returning();
}

Still two branches, but TypeScript is happy and each branch is fully type-safe.

Option 3: Repository/service layer (least duplication)

If you have many operations to share, abstract the database behind an interface:

interface MyRepository {
  insertRecord(values: { name: string }): Promise<{ id: number; name: string }[]>;
}

class SqliteRepo implements MyRepository {
  constructor(private db: typeof sqliteDb) {}
  insertRecord(values: { name: string }) {
    return this.db.insert(sqliteSchema.table).values(values).returning();
  }
}

class PostgresRepo implements MyRepository {
  constructor(private db: typeof pgDb) {}
  insertRecord(values: { name: string }) {
    return this.db.insert(pgSchema.table).values(values).returning();
  }
}

const repo: MyRepository = isLocal ? new SqliteRepo(sqliteDb) : new PostgresRepo(pgDb);

// All callers use the interface — no duplication
await repo.insertRecord({ name: 'test' });

The schema-specific code lives in the two repo classes, but all your business logic just calls repo.insertRecord().

---

Which to pick? If you only have a few operations, the discriminated union (Option 2) is the least overhead. If you have many operations across multiple modules, the repository pattern (Option 3) scales better and keeps the SQLite/Postgres details in one place.

One more thing: make sure your sqliteSchema and pgSchema table definitions have the same column names and types (even if the Drizzle column functions differ — text() vs varchar(), integer() vs serial()). The insert value types need to be compatible for this to stay maintainable long-term.

[eyueldk]

As much as I appreciate the answer, this still wordy. Repeating the same code twice doesn't seem like an elegant to me - just my personal preference. But I do understand that there's no better way at the moment, I was just hoping other people would have that insight

[Rick1330]

Handling Provider-Specific Return Types (Postgres vs SQLite)

I see you're running into the classic "Type Widening" problem when trying to share a single mutation function between SQLite and Postgres. The issue is that .returning() behaves differently (and has different return types) between the two providers.

The Root Cause:
Drizzle's .insert() returns a PgInsert or SQLiteInsert object. When you group them into a config object, TypeScript loses the "locked" relationship between the specific database instance and its specific schema. It sees (PG | SQLite).insert(PGTable | SQLiteTable) and errors because it can't guarantee that you aren't trying to insert a Postgres table into a SQLite database.

The Solution: Using a Generic Factory Function

Instead of a static config object, use a generic function that "locks" the database and table types together.

import { PgDatabase } from 'drizzle-orm/pg-core';
import { SQLiteBaseDatabase } from 'drizzle-orm/sqlite-core';

function createMutation<TDB extends PgDatabase<any, any, any> | SQLiteBaseDatabase<any, any, any>>(
  db: TDB,
  table: any // You can refine this to a generic Table type
) {
  return async (values: any) => {
    // Drizzle's internal dispatch will handle the correct SQL generation
    return await db.insert(table).values(values).returning();
  };
}

// Usage:
const runMutation = isLocal 
  ? createMutation(sqliteDb, sqliteSchema.users) 
  : createMutation(pgDb, pgSchema.users);

await runMutation({ name: 'John' });

Best Practice: The "Data Access Object" (DAO) Pattern

For complex apps, we recommend defining an interface for your mutations and implementing it twice:

interface UserRepo {
  create(name: string): Promise<User>;
}

class PgUserRepo implements UserRepo { ... }
class SqliteUserRepo implements UserRepo { ... }

const repo: UserRepo = isLocal ? new SqliteUserRepo() : new PgUserRepo();

Why .returning() is tricky:

Postgres supports .returning() natively, while Drizzle polyfills/emulates it for SQLite. By using the generic wrapper above, you let Drizzle's type inference at the call site determine the correct return shape.

If this helps you resolve the multi-DB typing conflict, please consider marking it as the answer!

[eyueldk]

Thanks; interesting... I'm surprised there isn't a standard mutation builder like how there's a query builder.

[Edcko]

The core issue here is that TypeScript cannot correlate union members across properties. When you write:

```ts
const config = isLocal
  ? { db: sqliteDb, schema: sqliteSchema }
  : { db: pgDb, schema: pgSchema };

TypeScript infers config as:

{
  db: SQLiteDb | PgDb;
  schema: SQLiteSchema | PgSchema;
}

It loses the relationship between db and schema.

This is a known TypeScript limitation: it does not track correlated unions across object properties.

The existing answers cover discriminated unions and the repository pattern well. But there is a more elegant approach that avoids duplicating business logic: a generic factory that captures the correlated types once at initialization.

The key insight: lock db + schema together with generics

Define your schemas with a shared column structure. Names and types must be compatible.

SQLite schema

// schema/sqlite.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull(),
});

PostgreSQL schema

// schema/pg.ts
import { pgTable, text, serial } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
});

Now create a typed database context that binds the database and its tables together:

// lib/db-context.ts
import type { BaseSQLiteDatabase } from 'drizzle-orm/sqlite-core';
import type { PgDatabase } from 'drizzle-orm/pg-core';

type AnySQLiteDb = BaseSQLiteDatabase<any, any, any, any>;
type AnyPgDb = PgDatabase<any, any, any>;

export interface DbContext<TTables> {
  db: AnySQLiteDb | AnyPgDb;
  tables: TTables;
}

Then create the context once at app startup:

// lib/db.ts
import { drizzle as drizzleSqlite } from 'drizzle-orm/better-sqlite3';
import { drizzle as drizzlePg } from 'drizzle-orm/node-postgres';

import * as sqliteSchema from './schema/sqlite';
import * as pgSchema from './schema/pg';

const isLocal = process.env.MODE === 'local';

export const ctx = isLocal
  ? {
      db: drizzleSqlite(sqliteConnection),
      tables: sqliteSchema,
    }
  : {
      db: drizzlePg(pgConnection),
      tables: pgSchema,
    };

The trick: write operations once with a builder function

Since Drizzle's query builder API is structurally identical across adapters, you can create a mutation builder that captures the type correlation once:

function createQueries<TDb extends AnySQLiteDb | AnyPgDb, TTables>(
  db: TDb,
  tables: TTables,
) {
  return {
    createUser: (input: { name: string; email: string }) =>
      db.insert(tables.users).values(input).returning(),
  } as const;
}

Initialized once, used everywhere:

export const queries = isLocal
  ? createQueries(drizzleSqlite(sqliteConnection), sqliteSchema)
  : createQueries(drizzlePg(pgConnection), pgSchema);

This is essentially the mutation builder you were hoping for. It captures the type correlation once and exposes a clean API for the rest of your app.

Why this works

• One code path: no if (isLocal) branches in your business logic.
• Runtime guarantee: ctx is initialized once, so db and tables always match.
• Schema migrations stay separate: each provider has its own migration files.

Caveat

The column names and value types in both schemas must be compatible.

If you need provider-specific columns, you will need to handle those in provider-specific query files or use Omit / Pick to narrow the shared interface.