Multiple databases

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. 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. 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: 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. Raw queries against a named 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: 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: 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: Addressing the other connection's model directly still works, and is clearer when every query in a block targets it: Transactions 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. 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. Shutdown Related reading Connection pooling — pool sizing per connection Read replicas — many copies of one database Foreign data wrappers — cross-server queries in PostgreSQL Raw queries