# How Instatic's Database Abstraction Supports Both Postgres and SQLite

> Instatic's database abstraction supports Postgres and SQLite seamlessly. Discover how its dialect-neutral DbClient interface normalizes syntax, results, and transactions for consistent repository code.

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

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) and providing dialect‑specific drivers 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), 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`](https://github.com/CoreBunch/Instatic/blob/main/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.

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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.

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/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.

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

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

```

## Runtime Selection

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

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

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

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

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts) and `createSqliteClient` in [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) and [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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.