PostgreSQL and SQLite example

A project can use more than one database without merging their grammars or generated schemas. This example keeps customer records in PostgreSQL and customer preferences in a local SQLite database, then combines both typed results in one application service.

Boundary	PostgreSQL	SQLite
Grammar	@typed-sql/postgres	@typed-sql/sqlite
Driver	Application-owned pg	Node's built-in node:sqlite
Config	postgres/typed-sql.config.ts	sqlite/typed-sql.config.ts
Generated contract	postgres/generated/db	sqlite/generated/db
Source ownership	Customer records	Customer preferences

Configure each database independently

The PostgreSQL directory owns its connection, grammar, introspection provider, schema snapshot, and queries:

import { defineConfig } from "@typed-sql/core";
import { postgres, typePolicy } from "@typed-sql/postgres";
import { pg } from "@typed-sql/postgres/pg";

export const connectionString = () =>
  process.env.POSTGRES_DATABASE_URL ?? "postgresql://typed_sql:typed_sql_examples@127.0.0.1:55442/typed_sql_multi";

export default defineConfig({
  dialect: postgres({ typePolicy }),
  schema: {
    file: "generated/db/schema.json",
    provider: pg({ connectionString, schemas: ["public"], typePolicy }),
  },
  outDir: "generated/db",
  projects: ["../tsconfig.json"],
  typePolicy,
});

import { sql } from "@typed-sql/postgres";

export const customerById = (customerId: bigint) => sql`
  SELECT customer.id, customer.email, customer.display_name
  FROM customer
  WHERE customer.id = ${customerId}
`;

The SQLite directory has a separate type policy input, generated snapshot, and query module:

import { defineConfig } from "@typed-sql/core";
import { sqlite, typePolicy } from "@typed-sql/sqlite";
import { nodeSqlite } from "@typed-sql/sqlite/node-sqlite";

export const databasePath = new URL("./example.sqlite", import.meta.url);

export default defineConfig({
  dialect: sqlite({ typePolicy }),
  schema: {
    file: "generated/db/schema.json",
    provider: nodeSqlite({ path: databasePath, typePolicy }),
  },
  outDir: "generated/db",
  projects: ["../tsconfig.json"],
  typePolicy,
});

import { sql } from "@typed-sql/sqlite";

export const preferenceByCustomerId = (customerId: bigint) => sql`
  SELECT preference.customer_id, preference.theme, preference.email_notifications
  FROM customer_preference AS preference
  WHERE preference.customer_id = ${customerId}
`;

export const updatePreference = (customerId: bigint, theme: string, emailNotifications: 0n | 1n) => sql`
  UPDATE customer_preference
  SET theme = ${theme}, email_notifications = ${emailNotifications}
  WHERE customer_id = ${customerId}
  RETURNING customer_id, theme, email_notifications
`;

Running generate refreshes both contracts. Running check analyzes each source directory against its matching grammar and schema instead of applying one dialect to the entire TypeScript project.

Combine typed results in application code

The service accepts both explicit database contracts. Each query remains owned and inferred by its grammar; the application can execute them concurrently and return one combined profile:

import type { PostgresDatabase } from "@typed-sql/postgres/runtime";
import type { SqliteDatabase } from "@typed-sql/sqlite/runtime";
import { customerById } from "../postgres/src/queries.js";
import { preferenceByCustomerId, updatePreference } from "../sqlite/src/queries.js";

export interface Databases {
  readonly postgres: PostgresDatabase;
  readonly sqlite: SqliteDatabase;
}

export async function getCustomerProfile(databases: Databases, customerId: bigint) {
  const [customer, preference] = await Promise.all([
    databases.postgres.maybeOne(customerById(customerId)),
    databases.sqlite.maybeOne(preferenceByCustomerId(customerId)),
  ]);
  return { customer, preference };
}

export async function setCustomerPreference(
  databases: Databases,
  customerId: bigint,
  theme: "light" | "dark",
  emailNotifications: boolean,
) {
  // This is intentionally a SQLite transaction only. typed-sql does not imply a
  // distributed transaction across unrelated drivers.
  return databases.sqlite.transaction((transaction) =>
    transaction.one(updatePreference(customerId, theme, emailNotifications ? 1n : 0n)),
  );
}

The write is deliberately a SQLite transaction only. Two unrelated drivers do not become an atomic distributed transaction merely because one function uses both. Applications that require cross-database atomicity need an explicit coordination pattern such as an outbox or saga.

Execute and prove both drivers

The entrypoint creates and closes both adapters:

import { typePolicy as postgresTypePolicy } from "@typed-sql/postgres";
import { createPgDatabase } from "@typed-sql/postgres/pg";
import { typePolicy as sqliteTypePolicy } from "@typed-sql/sqlite";
import { createNodeSqliteDatabase } from "@typed-sql/sqlite/node-sqlite";
import { connectionString } from "../postgres/typed-sql.config.js";
import { setupSqliteDatabase } from "../sqlite/setup.js";
import { databasePath } from "../sqlite/typed-sql.config.js";
import { getCustomerProfile, setCustomerPreference } from "./service.js";

export async function runMultiDatabaseExample() {
  await setupSqliteDatabase();
  const postgres = await createPgDatabase({ connectionString, typePolicy: postgresTypePolicy });
  const sqlite = await createNodeSqliteDatabase({ path: databasePath, typePolicy: sqliteTypePolicy });
  const databases = { postgres, sqlite };

  try {
    const before = await getCustomerProfile(databases, 1n);
    const updatedPreference = await setCustomerPreference(databases, 1n, "light", false);
    const after = await getCustomerProfile(databases, 1n);
    return { before, updatedPreference, after };
  } finally {
    await Promise.all([postgres.close(), sqlite.close()]);
  }
}

The real Poku suite starts from reproducible PostgreSQL and SQLite schemas, reads both sources, updates SQLite, reads the combined profile again, and closes both drivers:

import { typePolicy as postgresTypePolicy } from "@typed-sql/postgres";
import { createPgDatabase } from "@typed-sql/postgres/pg";
import { typePolicy as sqliteTypePolicy } from "@typed-sql/sqlite";
import { createNodeSqliteDatabase } from "@typed-sql/sqlite/node-sqlite";
import { describe, it, strict } from "poku";
import { connectionString } from "../postgres/typed-sql.config.js";
import { setupSqliteDatabase } from "../sqlite/setup.js";
import { databasePath } from "../sqlite/typed-sql.config.js";
import { getCustomerProfile, setCustomerPreference } from "../src/service.js";

await setupSqliteDatabase();
const postgres = await createPgDatabase({ connectionString, typePolicy: postgresTypePolicy });
const sqlite = await createNodeSqliteDatabase({ path: databasePath, typePolicy: sqliteTypePolicy });
const databases = { postgres, sqlite };

function plainValue(value: unknown): unknown {
  if (Array.isArray(value)) return value.map(plainValue);
  if (value === null || typeof value !== "object") return value;
  return Object.fromEntries(Object.entries(value).map(([key, child]) => [key, plainValue(child)]));
}

try {
  await describe("one application with PostgreSQL and SQLite", async () => {
    await it("combines independently inferred rows from both drivers", async () => {
      strict.deepStrictEqual(plainValue(await getCustomerProfile(databases, 1n)), {
        customer: { id: 1n, email: "alice@example.com", display_name: "Alice" },
        preference: { customer_id: 1n, theme: "dark", email_notifications: 1n },
      });
    });

    await it("keeps writes scoped to the selected database", async () => {
      strict.deepStrictEqual(plainValue(await setCustomerPreference(databases, 1n, "light", false)), {
        customer_id: 1n,
        theme: "light",
        email_notifications: 0n,
      });
      strict.deepStrictEqual(plainValue(await getCustomerProfile(databases, 1n)), {
        customer: { id: 1n, email: "alice@example.com", display_name: "Alice" },
        preference: { customer_id: 1n, theme: "light", email_notifications: 0n },
      });
    });
  });
} finally {
  await Promise.all([postgres.close(), sqlite.close()]);
}

From the repository root:

pnpm --filter @typed-sql/example-multi-database db:up
pnpm --filter @typed-sql/example-multi-database generate
pnpm --filter @typed-sql/example-multi-database check
pnpm --filter @typed-sql/example-multi-database test
pnpm --filter @typed-sql/example-multi-database start
pnpm --filter @typed-sql/example-multi-database test:database
pnpm --filter @typed-sql/example-multi-database db:down

Use node examples/e2e.mjs multi-database to run that complete lifecycle with guaranteed container teardown. For dialect-specific hovers in Zed, open either database subdirectory as a workspace; each contains settings that select its own config while sharing the parent TypeScript project.