Multiple Connections

An app can talk to more than one database. Give each one a name in config/database.ts, then pick a connection by name in queries, models, and transactions.

Quick example

import { defineDatabaseConfig } from '@holo-js/db'
import { env } from '@holo-js/config'

export default defineDatabaseConfig({
  defaultConnection: 'main',
  connections: {
    main: {
      driver: 'sqlite',
      url: env('DB_URL', './storage/database.sqlite'),
    },
    analytics: {
      driver: 'postgres',
      url: env('ANALYTICS_DATABASE_URL'),
    },
  },
})

import { DB } from '@holo-js/db'

export async function recentPageViews() {
  return DB.table('page_views', 'analytics').latest('viewed_at').limit(50).get()
}

Queries without a connection name use defaultConnection. Each driver you reference needs its package installed. See installing a driver.

Mixing drivers

Connections are independent, so they can use different drivers. A common setup is SQLite or PostgreSQL for the app plus a separate PostgreSQL or MySQL database for reporting or a legacy system.

import { defineDatabaseConfig } from '@holo-js/db'
import { env } from '@holo-js/config'

export default defineDatabaseConfig({
  defaultConnection: 'main',
  connections: {
    main: {
      driver: 'postgres',
      url: env('DATABASE_URL'),
    },
    legacy: {
      driver: 'mysql',
      host: env('LEGACY_DB_HOST', '127.0.0.1'),
      port: env('LEGACY_DB_PORT', '3306'),
      username: env('LEGACY_DB_USERNAME', 'root'),
      password: env('LEGACY_DB_PASSWORD'),
      database: env('LEGACY_DB_DATABASE', 'legacy'),
    },
  },
})

Each connection has its own settings for pooling, logging, and raw SQL. Turning on security.allowUnsafeRawSql for legacy doesn't turn it on for main.

Query a named connection

Pass the connection name as the second argument to DB.table():

import { DB } from '@holo-js/db'

const orders = await DB.table('orders', 'legacy').where('status', 'open').get()

Or get the connection first with DB.connection():

import { DB } from '@holo-js/db'

const legacy = DB.connection('legacy')

const customers = await legacy.table('customers').count()
const driver = legacy.getDialect().name

DB.connection() with no name returns the default connection. An unknown name throws a ConfigurationError.

Point a model at a connection

Set connectionName when you define the model. Every query, create, update, and delete for that model then uses that connection.

import { defineModel } from '@holo-js/db'

export default defineModel('page_views', {
  connectionName: 'analytics',
})

import PageView from '../models/PageView'

const views = await PageView.where('path', '/pricing').count()

Models without connectionName use the default connection.

The generated schema covers only the default connection, so a model on another connection is untyped, as in Laravel. Its attributes are unknown in TypeScript, and column names aren't checked. At runtime the model works from the rows the database returns:

• The primary key is id, or the column you set with primaryKey. A record you create without a key gets the database's auto-increment ID. As with Eloquent's default incrementing int key, an id key reads as a number when the driver returns it as integer text, such as a PostgreSQL bigint key. This doesn't apply to an id that a unique ID trait such as HasUuids() generates. Text that isn't a plain integer, such as '042', stays as returned, and so does a key beyond Number.MAX_SAFE_INTEGER, so it never loses digits. A primaryKey other than id reads exactly as the driver returns it, so a string key such as '42' stays a string.
• created_at and updated_at are filled unless you set timestamps: false, and they read as Date, as do the pivot columns of a withTimestamps() relation. softDeletes uses deleted_at unless you set deletedAtColumn.
• fillable decides which attributes you can write. A model that sets only guarded can also write the table's other real columns, as in Laravel: Holo reads the table's columns from that connection once, caches them, and rereads them after a schema change in the same process. Any other attribute, including a JSON path such as meta->role, throws SecurityError. With guarded: [], as with Eloquent's empty $guarded, Holo skips that check: every attribute and JSON path is writable, and the database rejects a column the table doesn't have.
• Your casts apply, and they write what the connection's database stores, as a typed model does: a 'boolean' cast writes 1 or 0, and date casts and timestamps write the same values a typed date column gets. Other attributes are the values the driver returns, such as JSON as text on SQLite.
• Relations to models on the same connection work as usual.

A table with the same name on the default connection doesn't change how the model reads or writes. TypeScript can't tell connections apart, though, so such a model is typed from the default connection's table. To fix this, rename the table or the connection.

Transactions on a named connection

Pass the connection name after the callback:

import { DB } from '@holo-js/db'

await DB.transaction(async (tx) => {
  await tx.table('orders').where('id', orderId).update({ status: 'shipped' })
  await tx.table('order_events').insert({ order_id: orderId, type: 'shipped' })
}, 'legacy')

DB.writeTransaction(), DB.afterCommit(), DB.afterRollback(), DB.unsafeQuery(), and DB.unsafeExecute() accept a connection name in the same way. See transactions.

WARNING

A transaction covers one connection only. Writes to a different connection inside the callback are not rolled back with it.

Inside a transaction, queries and models that don't name a connection join the active transaction, even when that transaction is on a non-default connection. Models with their own connectionName always use that connection.

Migrations and seeders

npx holo migrate and npx holo seed run against the default connection. Inside a migration, db is that connection, so you can check which driver you're on:

import { defineMigration } from '@holo-js/db'

export default defineMigration({
  async up({ schema, db }) {
    await schema.createTable('audit_log', (table) => {
      table.id()
      table.string('action')
      table.timestamps()
    })

    if (db.getDialect().name === 'postgres') {
      // PostgreSQL-only setup
    }
  },
  async down({ schema }) {
    await schema.dropTable('audit_log')
  },
})

Connection methods

DB.connection() returns a connection object. These are the methods you call on it.

connection()

Returns the connection with the given name, or the default connection when you pass no name. An unknown name throws a ConfigurationError.

const legacy = DB.connection('legacy')

table()

Starts a query builder on this connection. It works the same as DB.table() with a connection name.

const customers = await DB.connection('legacy').table('customers').count()

transaction()

Runs a callback in a transaction on this connection. See transactions.

await DB.connection('legacy').transaction(async (tx) => {
  await tx.table('orders').where('id', orderId).update({ status: 'shipped' })
})

getConnectionName()

Returns the name this connection was registered under.

const name = DB.connection().getConnectionName()

getDriver()

Returns the driver name, such as 'sqlite', 'postgres', or 'mysql'.

const driver = DB.connection('legacy').getDriver()

getDialect()

Returns the SQL dialect object. Its name property identifies the database.

if (DB.connection('legacy').getDialect().name === 'mysql') {
  // MySQL-only statement
}

Read and write replicas

Holo doesn't split reads and writes across replicas. Each named connection points at one database. To read from a replica, add it as its own connection and query it by name:

const report = await DB.table('orders', 'replica').where('created_at', '>', since).count()

Next steps

• Database configuration
• Transactions
• Defining models
• Migrations