Skip to content
Active Record
Esc
↑↓navigate↵open⌘Jpreview
On this page

Multiple databases

Use two or more databases from one Worker, on D1, Postgres, or both.

Binding the databases

Add each database to your Wrangler config, with its own migrations_dir:

{
  "d1_databases": [
    { "binding": "DB", "database_name": "my-app-db", "database_id": "…" },
    {
      "binding": "LOGS",
      "database_name": "my-app-logs",
      "database_id": "…",
      "migrations_dir": "logs-migrations",
    },
  ],
}

Wrangler applies a folder’s migrations to one database, so each needs its own folder.

Generating a schema for each

Generate the schemas as usual:

npx active-record schema

With several databases, it writes one of each per binding:

Binding Schema Base class
DB src/db/DB.ts DbRecord, in src/models/db-record.ts
LOGS src/db/LOGS.ts LogsRecord, in src/models/logs-record.ts

Each base class holds its database’s schema, which names its binding, so its Models query that database:

import { LogsRecord } from "./logs-record";

export class Entry extends LogsRecord {}

await Entry.where({ level: "error" }).count(); // on LOGS

Generating Models for one database

Name the binding with --database:

npx active-record g model Entry 'level:string!' message --database LOGS
npx active-record migrate --database LOGS

Using both in a request

Query each Model as usual. Each one uses the database its base class names:

// src/worker.ts: each Model queries its own database.
export default {
  async fetch(request) {
    const email = new URL(request.url).searchParams.get("email") ?? "";
    const user = await User.findByOrThrow({ email }); // on DB
    await Entry.create({ level: "info", message: `${user.email} signed in` }); // on LOGS
    return Response.json(user);
  },
} satisfies ExportedHandler<Env>;

Records remember their database, so user.save() writes back to DB, and entry.save() to LOGS.

Writing to both

Each write goes to one database. Write to the database that matters most first:

export async function signUp(email: string) {
  const user = await User.createOrThrow({ email }); // on DB first: if it throws, nothing is logged
  await Entry.create({ level: "info", message: `${user.email} signed up` }); // then on LOGS
  return user;
}

If createOrThrow throws, nothing reaches LOGS.

Associating Models on different databases

An association can link Models on different databases. Declare it as usual:

import { belongsTo, hasMany } from "@forestfuture/active-record";

export class User extends DbRecord {
  entries = hasMany(Entry); // on LOGS
}

export class Entry extends LogsRecord {
  user = belongsTo(User, { optional: true }); // on DB; an entry may have no user
}

Each read goes to the database its Model is on:

export async function errorEntriesOf(userId: number) {
  const user = await User.find(userId); // on DB
  return user.entries().where({ level: "error" }).toArray(); // on LOGS
}

export async function entriesWithUsers() {
  return Entry.includes("user").toArray(); // the entries on LOGS, then their users on DB
}

includes loads the users from DB once the entries are loaded, in one more round trip. Each other database a preload reaches adds one.

Records you create through an association are written to their own database:

export async function logFor(user: User, message: string) {
  return user.entries().create({ level: "info", message }); // on LOGS, with the user's key
}

Saving a record also saves the records built through it, each on its own database. Here the user is written to DB first, then the entry to LOGS with the user’s new key:

export async function signUpWithEntry(email: string) {
  const user = User.build({ email });
  user.entries().build({ level: "info", message: "signed up" });
  await user.saveOrThrow(); // the user on DB, then the entry on LOGS
  return user;
}

dependent strategies work the same way. With dependent: "destroy" on a user’s entries, destroying the user destroys its entries on LOGS, then deletes the user from DB.

Reading your own writes on each

With read replication, give each database its own Session. Nest a withSession call for each, and give each its own cookie name:

import { withSession } from "@forestfuture/active-record/d1";

// src/worker.ts: each database's bookmark travels in its own cookie.
export const replicas = {
  fetch: (request, env) =>
    withSession(request, env.DB, () =>
      withSession(request, env.LOGS, { cookie: "logs-bookmark" }, () => handle(request)),
    ),
} satisfies ExportedHandler<Env>;

Every query in handle goes through the Session of its Model’s database. See Read replicas for where each Session starts.

Testing with several databases

Outside a Worker, a Model can’t look up its binding. Give your tests the bindings by mocking cloudflare:workers. First, let Vitest’s mocks reach Active Record:

import { defineConfig } from "vitest/config";

export default defineConfig({
  test: { server: { deps: { inline: ["@forestfuture/active-record"] } } },
});

Then mock the module with your test databases as its env, using the testDatabase helper from Testing:

import { afterAll, beforeAll, expect, test, vi } from "vitest";
import worker from "../src/index";
import { testDatabase } from "./d1";

const env = vi.hoisted(() => ({}) as Env);
vi.mock("cloudflare:workers", () => ({ env }));

let databases: Awaited<ReturnType<typeof testDatabase>>[];
beforeAll(async () => {
  databases = await Promise.all([testDatabase(), testDatabase("logs-migrations")]);
  Object.assign(env, { DB: databases[0].binding, LOGS: databases[1].binding });
});
afterAll(() => Promise.all(databases.map((database) => database.dispose())));

test("signing in logs an entry", async () => {
  const request = new Request("https://example.com/?email=ada@example.com");
  const response = await worker.fetch(request, env);
  expect(response.status).toBe(200);
});

Each Model now finds its own database, as it does in a Worker. Call your Worker’s fetch without withDatabase.

Opening one in the console

Name the binding with --database:

npx active-record console --database LOGS

Using databases on different adapters

One project can keep some of its tables on D1 and others on Postgres. First install pg and PGlite, as PostgreSQL describes.

Name each database’s adapter in active-record.jsonc, and give the Postgres one its own migrations folder:

{
  "databases": {
    "DB": { "adapter": "d1" },
    "HYPERDRIVE": {
      "adapter": "postgresql",
      "migrationsDir": "pg-migrations",
      "url": "postgres://localhost/shop_development",
    },
  },
}

D1 and Postgres write their SQL differently, so each database needs a folder of its own. Both adapters read migrations/ by default, and the command refuses two databases on different adapters that share one.

To add your Hyperdrive bindings to a file that names only D1 databases, run init with the adapter:

npx active-record init --adapter postgresql

npx active-record schema reads each database with its own adapter. src/db/DB.ts imports d1, and src/db/HYPERDRIVE.ts imports postgresql. Build each Model on its database’s base class:

import { HyperdriveRecord } from "./hyperdrive-record";

export class Order extends HyperdriveRecord {}

Name the database with --database for the generators, migrate, and the console:

npx active-record g model Order total:integer --database HYPERDRIVE
npx active-record migrate --database HYPERDRIVE

Last updated on