Database Adapter Patterns in Instatic: Dual-Dialect Architecture with PostgreSQL and SQLite

Instatic implements a runtime adapter pattern that exposes a unified DbClient interface while delegating to dialect-specific implementations for PostgreSQL and SQLite, enabling seamless database switching via environment configuration alone.

The Instatic codebase is architected to run identically on either PostgreSQL or SQLite without conditional branching in business logic. This portability is achieved through a strict adapter pattern that abstracts driver-specific details behind a common interface, allowing the same repository code to execute against either database engine.

The Unified DbClient Interface

All database interactions in Instatic flow through a dialect-agnostic abstraction defined in server/db/index.ts. This module exports a DbClient type that specifies the contract for query execution, transaction management, and utility methods. Repository files (such as those in server/repositories/) import the client exclusively from this entry point, ensuring zero direct dependencies on PostgreSQL or SQLite specifics.

The interface standardizes methods like query(), transaction(), and close(), allowing higher-level code to remain ignorant of the underlying driver implementation.

Dialect-Specific Adapter Implementations

Two concrete implementations of the DbClient interface live side-by-side in the server/db/ directory. Both export factory functions that return an object satisfying the shared interface, but they utilize entirely different drivers under the hood.

PostgreSQL Adapter

The PostgreSQL implementation resides in server/db/postgres.ts. It wraps Bun.sql to provide connection pooling and native PostgreSQL type conversion. This adapter handles PostgreSQL-specific features like advisory locks and jsonb columns natively, mapping them to the standard DbClient methods expected by the rest of the application.

SQLite Adapter

The SQLite counterpart in server/db/sqlite.ts implements the same interface using bun:sqlite. This adapter includes special handling for JSON serialization on columns suffixed with *_json, automatically stringifying objects on write and parsing them on read to mirror PostgreSQL's native JSON support.

Factory Pattern for Runtime Selection

Database instantiation is centralized in server/db/client.ts, which acts as the sole decision point for adapter selection. This factory reads the DATABASE_URL environment variable at runtime and returns the appropriate implementation:

// server/db/client.ts
import { DATABASE_URL } from './util/env';
import { createPostgresClient } from './postgres';
import { createSqliteClient } from './sqlite';

export function getDbClient() {
  if (DATABASE_URL?.startsWith('postgres://')) {
    return createPostgresClient();
  }
  // Fallback to SQLite (including when DATABASE_URL is undefined)
  return createSqliteClient();
}

By isolating this logic in a single factory function, Instatic ensures that changing database backends requires only a configuration update—no code changes are necessary in repository or service layers.

Migration Strategy for Multiple Dialects

Schema migrations are maintained separately for each dialect but follow identical versioning sequences to keep both databases in sync. Migration files are split into:

• server/db/migrations-pg.ts: Contains PostgreSQL-specific DDL using native types and constraints
• server/db/migrations-sqlite.ts: Mirrors the migration IDs from the PostgreSQL file but uses SQLite-compatible syntax

This separation prevents dialect-specific SQL from polluting a shared migration file while preserving the sequential integrity required for deterministic schema versioning. The migration runner detects the active adapter and executes the appropriate file set.

Handling JSON Columns and Advisory Locks

Instatic employs two additional patterns to normalize behavioral differences between databases.

JSON Column Convention

Columns intended to store JSON objects must be named with the *_json suffix. The SQLite adapter automatically manages serialization for these columns, while the PostgreSQL adapter works with native jsonb types. This convention eliminates dialect-specific handling in business logic and is documented in docs/reference/database-dialects.md.

Cross-Dialect Advisory Locks

The server/db/advisoryLock.ts module provides a unified locking API that maps to PostgreSQL's pg_advisory_xact_lock when running on Postgres, and returns a no-op implementation when running on SQLite (which lacks native advisory locking). This allows background workers and cron jobs to request exclusive locks without caring about the underlying database capabilities.

Practical Implementation Example

Repository code remains completely agnostic of the underlying adapter:

// src/server/repositories/users.ts
import { getDbClient } from '@core/db';   // Re-exports from server/db/index.ts

export async function getUserById(userId: string) {
  const db = getDbClient();
  const rows = await db.query<{ id: string; name: string }>`
    SELECT id, name FROM users WHERE id = ${userId}
  `;
  return rows[0] ?? null;
}

Running migrations is equally abstracted:

import { runMigrations } from '@core/db/runMigrations';

await runMigrations();   // Automatically selects pg or sqlite migrations

Summary

• Unified Interface: All database operations flow through the DbClient type exported from server/db/index.ts, ensuring repositories remain driver-agnostic.
• Dual Adapters: Concrete implementations in server/db/postgres.ts and server/db/sqlite.ts handle dialect-specific behaviors using Bun.sql and bun:sqlite respectively.
• Runtime Factory: server/db/client.ts selects the appropriate adapter by inspecting the DATABASE_URL environment variable for the postgres:// prefix.
• Separated Migrations: Schema changes are isolated in migrations-pg.ts and migrations-sqlite.ts while maintaining synchronized version IDs.
• Normalization Patterns: The *_json column convention and advisoryLock.ts abstraction smooth over behavioral differences between PostgreSQL and SQLite.

Frequently Asked Questions

How does Instatic determine which database adapter to instantiate?

Instatic checks the DATABASE_URL environment variable in server/db/client.ts. If the string starts with postgres://, the factory returns the PostgreSQL adapter; otherwise, it falls back to the SQLite implementation. This ensures the correct driver loads automatically based on configuration alone.

Why are migrations split into separate files for each dialect?

Maintaining separate migrations-pg.ts and migrations-sqlite.ts files allows each dialect to use native SQL syntax and data types while preserving identical migration version sequences. This approach prevents conditional logic within migration definitions and ensures both databases evolve through the same schema states.

Can applications switch between SQLite and PostgreSQL without code changes?

Yes. Because all database interactions occur through the abstract DbClient interface, switching databases requires only updating the DATABASE_URL environment variable and running the appropriate migration set. No changes are needed in repository code or business logic.

What is the purpose of the *_json column naming convention?

This convention signals the SQLite adapter to automatically serialize and deserialize JSON objects, mimicking PostgreSQL's native jsonb support. Columns not following this pattern are treated as standard text or blob types in SQLite, ensuring type safety and consistent behavior across both database engines.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}