How Instatic's Database Abstraction Supports Both Postgres and SQLite

Instatic abstracts its data layer behind a dialect‑neutral DbClient interface implemented separately for Postgres and SQLite, normalizing query syntax, result shapes, and transactions so that repository code works identically regardless of which database is configured.

Instatic is an open‑source application in the CoreBunch repository that requires flexibility to run on PostgreSQL in production and SQLite for local development or testing. The project achieves this through a concise database abstraction layer centered in the server/db/ directory. By defining a common contract in server/db/client.ts and providing dialect‑specific drivers in server/db/postgres.ts and server/db/sqlite.ts, Instatic ensures that SQL queries, JSON handling, and transaction semantics remain consistent across both database engines.

The DbClient Interface

The abstraction begins with the DbClient interface defined in server/db/client.ts. This contract exposes a callable tag‑template function for parameterized queries, an unsafe() method for raw SQL execution, a transaction() helper, and a dialect field indicating which SQL syntax the underlying driver expects.

// Conceptual structure based on server/db/client.ts
interface DbClient {
  dialect: 'postgres' | 'sqlite';
  <T>(strings: TemplateStringsArray, ...values: any[]): Promise<DbResult<T>>;
  unsafe(query: string): Promise<void>;
  transaction<T>(fn: (tx: DbClient) => Promise<T>): Promise<T>;
}

Placeholder Normalization

Both implementations share a placeholder helper exported from server/db/client.ts. The placeholder(dialect, index) function generates positional parameters ($1, $2… for Postgres, ? for SQLite), allowing repository modules to build queries that work on either backend without modification.

Postgres Implementation

The Postgres client is created by createPostgresClient in server/db/postgres.ts. This implementation uses Bun's native SQL class to connect to a Postgres connection string and performs several normalization steps to ensure compatibility with the SQLite implementation.

Key features include:

• Row normalization: Automatically converts ISO‑8601 dates and parses JSON from columns ending with _json so that JavaScript values match those returned by the SQLite driver.
• Unified row count: Exposes a consistent rowCount property using the .count value that Bun returns for Postgres commands.

import { createPostgresClient } from '@/server/db/postgres';

const db = createPostgresClient(process.env.DATABASE_URL!);

SQLite Implementation

The SQLite client is provided by createSqliteClient in server/db/sqlite.ts. This wraps bun:sqlite's Database object and implements the DbClient contract with SQLite‑specific adaptations.

Key features include:

• Value binding: Transforms JavaScript values into SQLite‑compatible bindable parameters via a toBindable helper.
• JSON parsing: Mirrors the Postgres behavior by automatically parsing any column ending with _json back into JavaScript objects.
• Statement detection: Determines whether a query is SELECT‑like to return either result rows or just an affected‑row count.
• Transaction serialization: Implements a serialized transaction runner that queues overlapping calls, preventing SQLite's "cannot start a transaction within a transaction" errors.

import { createSqliteClient } from '@/server/db/sqlite';

const db = createSqliteClient(':memory:'); // or file path

Runtime Selection

The entry point server/db/index.ts selects the appropriate implementation at runtime based on the DATABASE_URL environment variable. If the URL starts with postgres://, it instantiates the Postgres client; otherwise, it uses SQLite. The rest of the application imports the client through this index file, remaining completely dialect‑agnostic.

// server/db/index.ts (simplified)
export const db = process.env.DATABASE_URL?.startsWith('postgres')
  ? createPostgresClient(process.env.DATABASE_URL)
  : createSqliteClient(process.env.DATABASE_URL || 'sqlite://./data.db');

Usage Examples

Parameterized Queries

Repository functions use the tag‑template API, which works identically on both dialects:

import type { DbClient } from '@/server/db/client';

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

The db object automatically handles the correct placeholder syntax ($1 vs ?) and result normalization.

Raw SQL Execution

For migrations or complex statements, use the unsafe() method:

await db.unsafe(`
  CREATE TABLE IF NOT EXISTS example (
    id TEXT PRIMARY KEY,
    data_json JSONB NOT NULL DEFAULT '{}'::jsonb
  );
`);

Both drivers handle multi‑statement strings correctly through this interface.

Transaction Handling

Transactions use a callback pattern that ensures proper isolation:

await db.transaction(async tx => {
  await tx`INSERT INTO posts (id, title) VALUES (${postId}, ${title})`;
  // Additional operations...
  // Automatic rollback on throw, commit on success
});

On SQLite, this implementation serializes concurrent transaction attempts to avoid conflicts on the single connection.

Summary

• Unified Interface: The DbClient contract in server/db/client.ts defines a consistent API for queries, transactions, and raw SQL across both databases.
• Dialect‑Specific Drivers: createPostgresClient in server/db/postgres.ts and createSqliteClient in server/db/sqlite.ts handle engine‑specific connections and normalization.
• Syntax Abstraction: The shared placeholder() helper allows repositories to use the same SQL strings regardless of whether Postgres ($1) or SQLite (?) syntax is required.
• Runtime Flexibility: server/db/index.ts selects the implementation based on DATABASE_URL, enabling seamless switching between Postgres production instances and SQLite local files.

Frequently Asked Questions

How does Instatic handle JSON columns differently between Postgres and SQLite?

Instatic normalizes JSON handling automatically in both drivers. The Postgres client in server/db/postgres.ts parses JSONB columns (specifically those named with the _json suffix) during row normalization, while the SQLite client in server/db/sqlite.ts uses the same _json suffix rule to parse text columns back into JavaScript objects. This ensures repository code receives identical object structures regardless of which database is backing the application.

Can I use the same database migrations for both Postgres and SQLite?

While the core schema logic remains compatible, Instatic maintains parallel migration files: server/db/migrations-pg.ts and server/db/migrations-sqlite.ts. These files handle dialect‑specific syntax differences (such as JSONB vs TEXT for JSON storage) while keeping the overall schema consistent. The unsafe() method on the DbClient interface allows both migration files to execute raw SQL appropriate to their target database.

Why does the SQLite implementation require transaction serialization?

SQLite's single‑connection model cannot handle overlapping transactions, which would raise "cannot start a transaction within a transaction" errors. The createSqliteClient implementation in server/db/sqlite.ts therefore serializes transaction calls, queuing concurrent attempts to ensure that only one transaction runs at a time per database connection. The Postgres implementation does not require this restriction as it utilizes PostgreSQL's native multi‑transaction support.

What happens if DATABASE_URL is not set?

According to the runtime selection logic in server/db/index.ts, if DATABASE_URL is undefined or does not start with postgres://, the application defaults to the SQLite implementation. Typically, this falls back to a local file path or in‑memory database, ensuring the application can start for development or testing without requiring a PostgreSQL server connection.

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