Building a multi database app in Node.js with one ORM

#Why apps end up with several databases

Few teams set out to build a multi database app in Node.js. It happens one reasonable decision at a time. The main data lives in PostgreSQL. Tests run on SQLite because a file database starts in milliseconds. Product analytics or audit events go to MongoDB because they are append only documents. A reporting job reads from the company's SQL Server.

The cost is usually not the databases themselves but the tooling: one ORM for the SQL side, another library for documents, raw drivers for the legacy system, each with its own types and error handling. Using one ORM for multiple databases removes most of that. kastor has adapters for PostgreSQL, CockroachDB, MySQL, MariaDB, SQLite, libSQL, D1, SQL Server, Oracle and MongoDB, all behind the same API. What remains are the real differences between databases, which this guide covers too.

#Sharing schema code

A kastor schema does not import anything dialect specific, so the same module can serve every SQL client you create. Keep the main tables in one file and the event collection in another, since they belong to different databases and have different lifecycles.

import { table, t, index } from 'kastor-orm'

// src/db/schema.ts: the relational model, used by Postgres and SQLite
export const users = table('users', {
  id: t.id(),
  email: t.string(255).unique(),
  name: t.string().nullable(),
})

export const orders = table('orders', {
  id: t.id(),
  userId: t.int().references(() => users.id, { onDelete: 'cascade' }),
  total: t.decimal(10, 2),
  createdAt: t.datetime().defaultNow(),
}, { indexes: (o) => [index(o.userId)] })

// src/db/events.ts (same import line): append only events, stored in MongoDB
export const events = table('events', {
  id: t.id(),
  type: t.string(64),
  userId: t.int(),
  payload: t.json<Record<string, unknown>>(),
  createdAt: t.datetime().defaultNow(),
})

Stick to the portable column types (t.string, t.decimal, t.datetime, t.json and so on) in shared tables. If one database needs a special type, .nativeType() sets it per column, but that column then stops being portable, so keep such tables out of the shared module.

Keys deserve one decision up front. On every SQL database t.id() is an auto increment integer typed as number. On MongoDB the adapter decides: mongodb(db) stores an ObjectId and types t.id() keys, and every column with .references() to one, as 24 character hex strings; mongodb(db, { ids: 'int' }) keeps them as increasing numbers, like SQL. The events table above keeps its own t.id() and stores the Postgres user id in a plain t.int() column without .references(), so userId stays a number on both sides whichever mode you pick.

#Several clients side by side

Wrap client creation in a function that takes an adapter. Production picks Postgres, tests pick SQLite, and both get the exact same type. The MongoDB client is created separately with its own schema.

import pg from 'pg'
import Database from 'better-sqlite3'
import { MongoClient } from 'mongodb'
import { kastor, one, many } from 'kastor-orm'
import { postgres } from 'kastor-orm/postgres'
import { sqlite } from 'kastor-orm/sqlite'
import { mongodb } from 'kastor-orm/mongodb'
import { users, orders } from './schema'
import { events } from './events'

type SqlAdapter = ReturnType<typeof postgres> | ReturnType<typeof sqlite>

export function createAppDb(adapter: SqlAdapter) {
  return kastor({
    adapter,
    schema: { users, orders },
    relations: ({ users, orders }) => ({
      users: { orders: many(orders) },
      orders: { user: one(users, { fields: ['userId'], references: ['id'] }) },
    }),
  })
}
export type AppDb = ReturnType<typeof createAppDb>

export const db = process.env.DB_KIND === 'sqlite'
  ? createAppDb(sqlite(new Database(process.env.SQLITE_FILE ?? './dev.db')))
  : createAppDb(postgres(new pg.Pool({ connectionString: process.env.DATABASE_URL })))

const mongo = new MongoClient(process.env.MONGO_URL ?? 'mongodb://localhost:27017')
export const eventsDb = kastor({ adapter: mongodb(mongo.db('app')), schema: { events } })

export async function placeOrder(userId: number, total: string) {
  const order = await db.orders.create({ data: { userId, total } })
  await eventsDb.events.create({
    data: { type: 'order.placed', userId, payload: { orderId: order.id, total } },
  })
  return order
}

Notice what placeOrder cannot do: wrap both writes in one transaction. A transaction lives on one database connection. If losing an event is not acceptable, write it to an outbox table inside the Postgres transaction and let a worker copy it to MongoDB with retries. Also call $disconnect() on every client during shutdown, not just the main one.

#Dialect differences that matter

kastor compiles the same query differently for each database, so your code stays the same. Some differences still change behavior, and you should know them before you rely on a test database that is not your production one. Open the playground to see the exact SQL for each database.

Returning rows after a write

create, update and upsert return the written row everywhere. PostgreSQL, CockroachDB and the SQLite family support RETURNING, SQL Server has OUTPUT, Oracle uses RETURNING ... INTO out binds, and where the database cannot return rows from a write (MySQL and MariaDB), kastor reads the row back by its key. That read back is a second statement, so keep such writes inside a transaction when another process may change the row at the same moment.

Upsert conflicts

The native mechanisms differ: ON CONFLICT in PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE in MySQL and MariaDB, MERGE in SQL Server (with a HOLDLOCK hint) and Oracle. On MongoDB kastor updates the matching document or inserts when there is none, retrying the update if a concurrent insert wins the race. The subtle one is MySQL, where a conflict on any unique index triggers the update, not only the one in your where. If a table has several unique columns, test upserts on MySQL specifically.

Case insensitive matching

mode: 'insensitive' works on every adapter, using ILIKE on PostgreSQL and CockroachDB, comparing lowercased values on the other SQL databases, and case insensitive regular expressions on MongoDB. The trap goes the other way: the default collations of MySQL, MariaDB and SQL Server compare text case insensitively even for plain equals, while PostgreSQL does not. On those databases kastor makes contains, startsWith and endsWith exact with a binary collation, but equals follows the collation. Code that passes on MySQL can miss rows on PostgreSQL, so be explicit with mode whenever case matters.

JSON aggregation for includes

Nested includes load in one statement per root call on every SQL adapter, each with its own JSON functions: json_agg on PostgreSQL and CockroachDB, JSON_ARRAYAGG on MySQL, MariaDB and Oracle, json_group_array on the SQLite family and FOR JSON PATH on SQL Server. MongoDB uses one aggregation pipeline with $lookup stages. Both are free of N+1 queries, but large includes still mean large responses, so use take and select inside includes.

Foreign keys

The SQL databases enforce .references() themselves, with a few dialect gaps: Oracle supports only cascade and set null on delete, MySQL's InnoDB has no set default, and SQL Server writes self referencing or multi path cascades as NO ACTION. MongoDB enforces nothing, so kastor emulates foreign keys there: onDelete actions on delete and an existence check before inserts and updates, for writes made through kastor only.

Schema changes

PostgreSQL, the SQLite family and SQL Server can run DDL inside a transaction. MySQL, MariaDB and Oracle commit each DDL statement on its own, CockroachDB runs schema changes outside explicit transactions, and MongoDB collection and index commands cannot run in one either, so a migration that fails halfway can leave part of it applied there. Keep migrations for those databases small, one logical change per file. On D1, which has no interactive transactions, statements also run one by one.

#Testing the same code against several databases

Run the suite once per target. A config file that reads an environment variable lets the CLI prepare each database, with a separate migrations folder per dialect since migration SQL is dialect specific.

import { defineConfig } from 'kastor-orm'
import { postgres } from 'kastor-orm/postgres'
import { sqlite } from 'kastor-orm/sqlite'
import pg from 'pg'
import Database from 'better-sqlite3'

const kind = process.env.DB_KIND ?? 'postgres'

export default defineConfig({
  schema: './src/db/schema.ts',
  adapter: () =>
    kind === 'sqlite'
      ? sqlite(new Database(process.env.SQLITE_FILE ?? './dev.db'))
      : postgres(new pg.Pool({ connectionString: process.env.DATABASE_URL })),
  migrations: `./kastor/migrations/${kind}`,
})

import { describe, it, expect, beforeEach, afterAll } from 'vitest'
import pg from 'pg'
import Database from 'better-sqlite3'
import { postgres } from 'kastor-orm/postgres'
import { sqlite } from 'kastor-orm/sqlite'
import { createAppDb } from '../src/db/clients'

const targets = [
  { name: 'sqlite', makeDb: () => createAppDb(sqlite(new Database('./test.db'))) },
  { name: 'postgres', makeDb: () => createAppDb(postgres(new pg.Pool({ connectionString: process.env.TEST_DATABASE_URL }))) },
]

describe.each(targets)('users on $name', ({ makeDb }) => {
  const db = makeDb()
  beforeEach(async () => {
    await db.$execute`delete from orders`
    await db.$execute`delete from users`
  })
  afterAll(() => db.$disconnect())

  it('matches emails case insensitively', async () => {
    await db.users.create({ data: { email: 'Ana@Example.com' } })
    const found = await db.users.findFirst({
      where: { email: { equals: 'ana@example.com', mode: 'insensitive' } },
    })
    expect(found?.email).toBe('Ana@Example.com')
  })
})

Before the run, sync both schemas with DB_KIND=sqlite SQLITE_FILE=./test.db npx kastor db push and DATABASE_URL=$TEST_DATABASE_URL npx kastor db push. Locally you can run only SQLite for speed; in CI run every target.

Not every test needs to run everywhere. Focus the cross database runs on the behavior that actually differs:

• Text matching: lookups by email or username, with and without mode: 'insensitive'.
• Upserts on tables with more than one unique column.
• Sorting with nulls: PostgreSQL and Oracle put nulls last in ascending order, MySQL, SQLite, SQL Server and MongoDB put them first. Pass orderBy: { name: { sort: 'asc', nulls: 'last' } } to get the same order everywhere (kastor emulates it where the database has no NULLS LAST), or assert on order only where the column cannot be null.
• Timestamps: fractional second precision depends on the database and column type, so compare dates with a tolerance or truncate them.

#Switching databases

A common path is SQLite during prototyping and PostgreSQL in production. With a shared schema the code change is the adapter line. The rest is data and history:

1. Create the new migration history with DB_KIND=postgres npx kastor migrate dev --name init. The old folder stays for the old database.
2. Copy the data with a short script that reads from one client and writes to the other in batches.
3. Reset auto increment sequences, because rows were inserted with explicit ids.

import pg from 'pg'
import Database from 'better-sqlite3'
import { postgres } from 'kastor-orm/postgres'
import { sqlite } from 'kastor-orm/sqlite'
import { createAppDb } from '../src/db/clients'

const from = createAppDb(sqlite(new Database('./dev.db')))
const to = createAppDb(postgres(new pg.Pool({ connectionString: process.env.DATABASE_URL })))

let lastId = 0
for (;;) {
  const batch = await from.users.findMany({ where: { id: { gt: lastId } }, orderBy: { id: 'asc' }, take: 500 })
  if (batch.length === 0) break
  await to.users.createMany({ data: batch })
  lastId = batch[batch.length - 1].id
}

await to.$sql`select setval(pg_get_serial_sequence('users', 'id'), (select max(id) from users))`
await Promise.all([from.$disconnect(), to.$disconnect()])

Copy parent tables before child tables so foreign keys resolve, then run the test suite against the new database before switching traffic. For more on migration workflows, read database migrations explained.

#Questions people ask

#Related guides