# How Instatic Handles Postgres vs SQLite Differences with Database Adapters

> Instatic unifies Postgres and SQLite with adapters, offering a single API for database queries. It automatically handles differences in JSON, placeholders, and transactions for seamless integration.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: deep-dive
- Published: 2026-07-31

---

**Instatic abstracts both PostgreSQL and SQLite behind a single dialect-aware `DbClient` that automatically selects the correct adapter based on your `DATABASE_URL`, exposing a unified API for queries while normalizing engine-specific quirks like JSON handling, parameter placeholders, and transaction semantics.**

Instatic supports dual SQL engines through a clean abstraction layer located in `server/db/`. This design allows repositories, handlers, and migrations to interact with either database using identical TypeScript syntax, eliminating the need for conditional logic throughout the application code.

## URL-Based Adapter Selection

The entry point `createDbClient` in **[`server/db/index.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/index.ts)** inspects the `DATABASE_URL` environment variable to determine which engine to instantiate.

- **`isSqliteUrl`** (lines 19-25) detects SQLite-flavored URLs matching `sqlite:…`, `file:…`, or paths ending in `.db`.
- **`parseSqlitePath`** (lines 44-48) extracts the filesystem path when using SQLite.
- **`createDbClient`** (lines 63-80) returns a tuple containing the appropriate client instance and migration array (`pgMigrations` for PostgreSQL or `sqliteMigrations` for SQLite).

If the URL starts with `postgres:` or `postgresql:`, the factory instantiates `createPostgresClient`; otherwise, it routes to `createSqliteClient`.

## Unified Client Interface

The public contract is defined in **[`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts)**, ensuring both adapters implement the same surface area:

```typescript
type DbResult<Row = Record<string, unknown>> = { rows: Row[]; rowCount: number }

interface DbClient {
  <Row = Record<string, unknown>>(
    strings: TemplateStringsArray, 
    ...values: unknown[]
  ): Promise<DbResult<Row>>
  
  unsafe<Row = Record<string, unknown>>(
    sql: string, 
    params?: unknown[]
  ): Promise<DbResult<Row>>
  
  transaction<T>(fn: (tx: DbClient) => Promise<T>): Promise<T>
  
  readonly dialect: 'postgres' | 'sqlite'
}

```

Because both adapters conform to this interface, calling code never branches on the underlying engine. The `dialect` property is read-only and set at construction, enabling edge-case handling only when absolutely necessary.

## Dialect-Specific Normalisation

While the API is uniform, each adapter 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) handles engine-specific behaviours internally.

### Result Row Normalisation

**PostgreSQL:** The **`normalizePostgresRow`** function (lines 17-33) converts dates to ISO-8601 strings and automatically `JSON.parse`s any column ending with `_json`, mirroring SQLite’s automatic JSON handling.

**SQLite:** The **`parseJsonColumns`** function (lines 48-66) iterates through result rows and parses `_json` columns when they contain non-empty values, ensuring parity with PostgreSQL’s output.

### Parameter Binding

**PostgreSQL:** Uses Bun’s native `SQL` class which handles PostgreSQL-style placeholders (`$1, $2 …`) directly. No additional conversion is performed.

**SQLite:** Values must be converted to SQLite-compatible bindable types (`string`, `number`, `null`, `Uint8Array`) via **`toBindable`** (lines 17-25) before execution.

### Statement Type Detection

**PostgreSQL:** Extracts affected-row counts from the `count` property on the result array using **`resultRowCount`** (lines 46-49).

**SQLite:** Reads the `changes` field from the `info` object returned by `stmt.run()` (see lines 97-102 in the SQLite client implementation).

### Transaction Handling

**PostgreSQL:** Wraps around Bun’s `sql.begin` helper for native transaction support (lines 70-73).

**SQLite:** Because SQLite is single-connection and synchronous, Instatic serialises transactions using an internal promise chain (`txChain`, lines 30-58) to prevent overlapping `BEGIN` statements.

## Placeholder Helper

Since PostgreSQL uses numbered placeholders (`$1, $2`) while SQLite uses anonymous placeholders (`?`), the **`placeholder`** utility in [`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) generates the correct token for dynamic query construction:

```typescript
export function placeholder(dialect: Dialect, index: number): string {
  return dialect === 'postgres' ? `$${index}` : '?'
}

```

Repositories use this helper to build `WHERE` clauses that execute correctly on either engine without string manipulation.

## Migration Parity

Both adapters expose dialect-specific migration arrays—**`pgMigrations`** and **`sqliteMigrations`**—that share identical numeric IDs. This ensures schema changes apply consistently regardless of the underlying engine. The `createDbClient` factory automatically returns the correct migration list alongside the client instance.

## Code Examples

### Creating a Client at Application Startup

```typescript
import { createDbClient } from '@/server/db/index'

const { DATABASE_URL } = process.env // e.g., "postgres://user:pw@localhost/db"
const { db, migrations } = createDbClient(DATABASE_URL!)

await db.transaction(async (tx) => {
  const { rows } = await tx`SELECT id, title FROM posts WHERE published = ${true}`
  console.log('Published posts:', rows)
})

```

The `createDbClient` call automatically selects the PostgreSQL or SQLite adapter and returns the corresponding migration list.

### Writing Dialect-Neutral Repository Queries

```typescript
import { placeholder } from '@/server/db/client'
import type { DbClient } from '@/server/db/client'

export async function findUserByEmail(db: DbClient, email: string) {
  const ph = placeholder(db.dialect, 1)
  const { rows } = await db.unsafe<{ id: number; email: string }>(
    `SELECT id, email FROM users WHERE email = ${ph}`,
    [email],
  )
  return rows[0] ?? null
}

```

The `placeholder` call resolves to `$1` for PostgreSQL and `?` for SQLite, keeping the query string valid for both engines.

### Handling JSON Columns Uniformly

```typescript
const { rows } = await db`SELECT id, meta_json FROM widgets WHERE id = ${widgetId}`
const meta = rows[0].meta_json // Already parsed to an object on both dialects

```

Both adapters automatically parse columns ending with `_json`, allowing you to work with JavaScript objects directly regardless of the database engine.

## Summary

- **Automatic Selection:** `createDbClient` in [`server/db/index.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/index.ts) routes to PostgreSQL or SQLite based on `DATABASE_URL` parsing.
- **Unified API:** The `DbClient` interface in [`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) exposes tagged template literals, `unsafe` queries, and transactions that work identically on both engines.
- **Normalised Outputs:** Row-level transformations in `normalizePostgresRow` and `parseJsonColumns` ensure JSON columns and date formats remain consistent.
- **Parameter Safety:** The `placeholder` utility abstracts dialect differences in bind variable syntax.
- **Serialized Transactions:** SQLite transactions are serialised via `txChain` to prevent conflicts, while PostgreSQL uses native Bun transactions.
- **Schema Parity:** Separate migration files with matching IDs ensure schema consistency across both engines.

## Frequently Asked Questions

### How does Instatic decide which database adapter to use?

Instatic examines the `DATABASE_URL` environment variable at startup. If the URL matches SQLite patterns (`sqlite:`, `file:`, or ends with `.db`), it instantiates the SQLite client from [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts); otherwise, it routes to the PostgreSQL client in [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts). This logic is encapsulated in the `createDbClient` factory function.

### Can I use the same SQL syntax for both PostgreSQL and SQLite?

Yes. The `DbClient` interface accepts standard SQL strings via tagged template literals or the `unsafe` method. For dynamic values, use the `placeholder` utility from [`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) to generate `$1` for PostgreSQL or `?` for SQLite. Both adapters handle parameter binding internally, so repository code remains identical across engines.

### How are JSON columns handled differently between the two engines?

PostgreSQL stores JSON as native types, while SQLite stores them as text. Instatic normalises this difference automatically: the PostgreSQL adapter parses `_json` columns in `normalizePostgresRow`, while the SQLite adapter does the same in `parseJsonColumns`. In both cases, your application receives parsed JavaScript objects without manual `JSON.parse` calls.

### What happens if I switch from SQLite to PostgreSQL in production?

You only need to change the `DATABASE_URL` environment variable. The `createDbClient` factory will return the PostgreSQL adapter and the `pgMigrations` array. Because both migration sets share identical numeric IDs and the `DbClient` API is engine-agnostic, the application logic requires no modifications. However, you must run the migrations against the new PostgreSQL instance to establish the schema.