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.