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

> Explore Instatic's database adapter patterns, featuring a dual-dialect architecture for PostgreSQL and SQLite. Seamlessly switch databases with a unified DbClient interface and environment configuration.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: internals
- Published: 2026-07-29

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts). It wraps [`Bun.sql`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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:

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts)**: Contains PostgreSQL-specific DDL using native types and constraints
- **[`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/docs/reference/database-dialects.md).

### Cross-Dialect Advisory Locks

The [`server/db/advisoryLock.ts`](https://github.com/CoreBunch/Instatic/blob/main/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:

```typescript
// 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:

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/index.ts), ensuring repositories remain driver-agnostic.
- **Dual Adapters**: Concrete implementations in [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts) and [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts) handle dialect-specific behaviors using [`Bun.sql`](https://github.com/CoreBunch/Instatic/blob/main/Bun.sql) and `bun:sqlite` respectively.
- **Runtime Factory**: [`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts) and [`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-sqlite.ts) while maintaining synchronized version IDs.
- **Normalization Patterns**: The `*_json` column convention and [`advisoryLock.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts) and [`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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.