Multiple databases

A Prorm instance is one connection to one database. ConnectionManager holds
several of them by name, so an application can talk to its primary OLTP
database, an analytics warehouse and a legacy system at once — each with its own
dialect, pool, models and migrations.

import { createConnectionManager, DataTypes } from 'ts-prorm-orm';

const manager = createConnectionManager();

const main = await manager.addConnection('main', {
  dialect: 'postgres',
  host: 'db.internal', database: 'app',
  username: 'app', password: process.env.PG_PASSWORD!,
});

const analytics = await manager.addConnection('analytics', {
  dialect: 'clickhouse',
  host: 'ch.internal', database: 'events',
});

await main.connect();
await analytics.connect();

addConnection constructs the instance but does not connect it — call
connect() yourself, so you control when the sockets open. Adding a connection
whose name already exists throws rather than silently replacing it.

Laying out a project with several databases

Give each database its own connection settings, models and migrations, and
build the manager in one module the rest of the app imports.

src/
  db/
    index.ts          # the ConnectionManager, built once
  models/
    main/             # defined against the 'main' connection
      user.ts
      order.ts
    analytics/
      event.ts
migrations/
  main/               # prorm migration:run -- --path ./migrations/main
  analytics/
.env

MAIN_DATABASE_URL=postgres://app:secret@db.internal:5432/app
ANALYTICS_DATABASE_URL=clickhouse://default:secret@ch.internal:8123/events

import { createConnectionManager } from 'ts-prorm-orm';

export const manager = createConnectionManager();

export const main = await manager.addConnection('main', {
  dialect: 'postgres',
  url: process.env.MAIN_DATABASE_URL,
  pool: { max: 20 },
});
export const analytics = await manager.addConnection('analytics', {
  dialect: 'clickhouse',
  url: process.env.ANALYTICS_DATABASE_URL,
});

await Promise.all([main.connect(), analytics.connect()]);

The CLI works on one database at a time, so each migrations folder is run
against its own URL:

$ DATABASE_URL=$MAIN_DATABASE_URL prorm migration:run -- --path ./migrations/main
$ DATABASE_URL=$ANALYTICS_DATABASE_URL prorm migration:run -- --path ./migrations/analytics

Working with named connections

Call	Purpose
`addConnection(name, options)`	Register a connection; returns the `Prorm`.
`getConnection(name)`	Fetch it, or `undefined`.
`hasConnection(name)`	Existence check.
`getConnectionNames()`	Every registered name.
`connectionsMap`	A copy of the whole `Map`.
`removeConnection(name)`	Close and forget it; returns whether it existed.
`closeAll()`	Close every connection and clear the registry.
`defaultConnection`	Get/set the default.

The first connection added becomes the default. Removing the default promotes
whichever connection remains; removing the last one leaves it null. Setting
defaultConnection to a name that is not registered throws.

Models per connection

Models belong to the connection that defined them, so define each model against
the instance that owns its table:

const User  = main.define('User',  userAttrs,  { tableName: 'users' });
const Event = analytics.define('Event', eventAttrs, { tableName: 'events' });

await main.sync();
await analytics.sync();

There is no cross-connection include — an association spans one connection.
To join data across databases you either query both and join in application
code, or use one of the two database-side options below.

const activeUsers = await User.findAll({ where: { status: 'active' } });
const events = await analytics
  .model('Event')
  .findAll({ where: { userId: { [Op.in]: activeUsers.map((u) => u.id) } } });

Raw queries against a named connection

const rows = await manager.query('analytics',
  'SELECT day, count(*) FROM events WHERE day >= :from GROUP BY day',
  { replacements: { from: '2026-01-01' } });

A SELECT resolves to the array of rows, exactly as prorm.query() does on
that connection.

Unknown connection names throw with the name in the message rather than
returning empty.

Cross-database queries

SQLite: `ATTACH DATABASE`

SQLite can query across files once one is attached to another's connection:

A complete run, two files and one join across them:

import { createConnectionManager, DataTypes } from 'ts-prorm-orm';

const manager = createConnectionManager();
const main = await manager.addConnection('main', { dialect: 'sqlite', storage: './main.db' });
const analytics = await manager.addConnection('analytics', { dialect: 'sqlite', storage: './analytics.db' });
await main.connect();
await analytics.connect();

const User = main.define('User', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  name: { type: DataTypes.STRING },
}, { tableName: 'users' });
const Visit = analytics.define('Visit', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  userId: { type: DataTypes.INTEGER },
  path: { type: DataTypes.STRING },
}, { tableName: 'visits' });
await main.sync();
await analytics.sync();

await User.bulkCreate([{ name: 'Ada' }, { name: 'Linus' }]);
await Visit.bulkCreate([{ userId: 1, path: '/' }, { userId: 1, path: '/docs' }, { userId: 2, path: '/' }]);

await manager.attachDatabase('main', 'analytics', './analytics.db');
const rows = await manager.query('main', `
  SELECT u.name, count(v.id) AS visits
  FROM users u JOIN analytics.visits v ON v.userId = u.id
  GROUP BY u.name ORDER BY u.name`);
console.log(rows); // [ { name: 'Ada', visits: 2 }, { name: 'Linus', visits: 1 } ]
await manager.detachDatabase('main', 'analytics');

await manager.closeAll();

The alias is validated against ^[A-Za-z_][A-Za-z0-9_]*$ because it sits in an
identifier position that cannot be parameterised; the path is bound, so a path
containing a quote cannot break out of the literal.

PostgreSQL: foreign tables

PostgreSQL reaches another server through a foreign-data wrapper, which makes a
remote table queryable as if it were local — including joins against local
tables. See Foreign data wrappers.

Per-query connection selection

using: '<connection name>' routes a single query to another registered
connection, leaving the rest of the model's queries where they are:

await User.findAll({ where: { status: 'active' }, using: 'analytics' });

addConnection() registers the manager on each Prorm instance it creates, so
any model on any of its connections can resolve a name. The query is
re-dispatched to the target connection's model of the same name, with using
stripped.

Both failure modes name what is actually available rather than failing blankly:

Connection 'nope' not found. Available: main, analytics
Model 'User' is not defined on connection 'analytics'. Defined there: Event

Addressing the other connection's model directly still works, and is clearer
when every query in a block targets it:

const Event = manager.getConnection('analytics')!.model('Event')!;
await Event.findAll({ where: { userId: 42 } });

Transactions

[!WARNING] A transaction belongs to one connection
There is no two-phase commit across connections, so a "transaction"
spanning two databases can leave one committed and the other rolled back.

await main.transaction(async (t) => {
  await Order.create(order, { transaction: t });
  // Do NOT write to `analytics` here expecting atomicity.
});

When two databases must end up consistent, write to the primary inside the
transaction and drive the second write from an outbox row that a worker
processes after commit.

If the worker dies between the two last steps the event is sent again, so the
analytics side must tolerate duplicates (an idempotency key, or a
ReplacingMergeTree on ClickHouse).

import { DataTypes, Op } from 'ts-prorm-orm';
// main and analytics: the connections exported by src/db/index.ts above.

const Order = main.define('Order', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  total: { type: DataTypes.DECIMAL(12, 2) },
}, { tableName: 'orders' });

const Outbox = main.define('Outbox', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  event: { type: DataTypes.STRING(64) },
  payload: { type: DataTypes.JSON },
  sentAt: { type: DataTypes.DATE, allowNull: true },
}, { tableName: 'outbox' });

const Event = analytics.define('Event', {
  id: { type: DataTypes.STRING(64), primaryKey: true }, // the idempotency key
  name: { type: DataTypes.STRING(64) },
  payload: { type: DataTypes.JSON },
}, { tableName: 'events', timestamps: false });

// In the request: both rows commit, or neither does.
export async function placeOrder(total: number) {
  return main.transaction(async (t) => {
    const order = await Order.create({ total }, { transaction: t });
    await Outbox.create(
      { event: 'order.placed', payload: { orderId: order.get('id'), total } },
      { transaction: t },
    );
    return order;
  });
}

// In the worker: at-least-once delivery to the second database.
export async function drainOutbox() {
  const pending = await Outbox.findAll({ where: { sentAt: { [Op.is]: null } }, limit: 100 });
  for (const row of pending) {
    await Event.create({ id: `outbox-${row.get('id')}`, name: row.get('event'), payload: row.get('payload') });
    await row.update({ sentAt: new Date() });
  }
}

Shutdown

process.on('SIGTERM', async () => {
  await manager.closeAll();
});

Connection pooling — pool sizing per connection

Read replicas — many copies of one database

Foreign data wrappers — cross-server queries in PostgreSQL

Raw queries

Multiple databases

Laying out a project with several databases#

Working with named connections#

Models per connection#

Raw queries against a named connection#

Cross-database queries#

SQLite: ATTACH DATABASE#

PostgreSQL: foreign tables#

Per-query connection selection#

Transactions#

Shutdown#

Related reading#