How Instatic Handles Postgres vs SQLite Differences with Database Adapters

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 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, ensuring both adapters implement the same surface area:

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 and 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.parses 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 generates the correct token for dynamic query construction:

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

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

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

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 routes to PostgreSQL or SQLite based on DATABASE_URL parsing.
• Unified API: The DbClient interface in 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; otherwise, it routes to the PostgreSQL client in 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 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.

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