What Database Options Does Instatic Support? SQLite and PostgreSQL Guide

Instatic supports two database engines—SQLite for local development and PostgreSQL for production—automatically selected from the DATABASE_URL environment variable when the server boots.

Instatic, an open-source project by CoreBunch, implements a "one database, two engines" architecture that lets you choose between file-based SQLite or robust PostgreSQL without changing application code. The database options are determined at runtime by parsing the connection string in server/db/index.ts, making the entire data-access layer dialect-agnostic.

Supported Database Engines

Instatic can run against two database engines, each optimized for different deployment scenarios.

SQLite (Local Development)

SQLite is the default for local development and testing. Instatic detects SQLite when DATABASE_URL starts with sqlite:, file:, or ends with .db. Under the hood, it uses Bun's built-in bun:sqlite driver via the createSqliteClient factory in server/db/sqlite.ts.

PostgreSQL (Production)

For production workloads, Instatic supports PostgreSQL through Bun's Bun.sql driver. URLs beginning with postgres: or postgresql: are routed to the PostgreSQL client implementation in server/db/postgres.ts.

How Database Detection Works

The automatic selection happens in the createDbClient factory function located in server/db/index.ts. This function analyzes the DATABASE_URL string and returns a dialect-specific client along with matching migration sets.

Detection logic in server/db/index.ts (lines 13-25 and 73-78):

• SQLite patterns: sqlite:, file:, or .db extension
• PostgreSQL patterns: postgres: or postgresql:

Because the repository's data-access layer is dialect-naive, the same queries work on both engines; only the client implementation differs.

Connecting to Your Database

Using createDbClient (Recommended)

The standard way to initialize the database is through the central factory:

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

const DATABASE_URL = process.env.DATABASE_URL ?? 'sqlite:./.tmp/dev.db'
const { db, migrations } = createDbClient(DATABASE_URL)

// Works identically whether SQLite or PostgreSQL
await db.run('SELECT 1')

This returns both the client (db) and the appropriate migration array (migrations), which will be either sqliteMigrations or pgMigrations depending on the detected dialect.

Direct SQLite Access

For low-level SQLite operations, import the dedicated client factory:

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

const sqliteDb = createSqliteClient('./data/my-db.sqlite')
sqliteDb.query('PRAGMA journal_mode = WAL')

Direct PostgreSQL Access

Similarly, for direct PostgreSQL control:

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

const pgDb = createPostgresClient('postgres://user:pass@host:5432/instatic')
await pgDb.query('SELECT NOW()')

Running Migrations on Both Engines

Because the architecture is dialect-naive, the same migration helper works regardless of which database option you choose:

import { runMigrations } from '@/server/db/runMigrations'

await runMigrations({ db, migrations })

The runMigrations helper receives the migration set from createDbClient, ensuring the correct SQL dialect is applied automatically.

Architecture Benefits

According to the Architecture guide in docs/architecture.md, this dual-engine approach provides several advantages:

• Development flexibility: Use lightweight SQLite locally without container setup
• Production scalability: Deploy to PostgreSQL for concurrent connections and advanced features
• Code portability: The same queries run on both engines because the dialect-specific logic is isolated in server/db/index.ts, server/db/sqlite.ts, and server/db/postgres.ts

Detailed dialect rules regarding JSON column naming and migration parity are documented in docs/reference/database-dialects.md.

Summary

• Instatic supports SQLite and PostgreSQL as its two database options, automatically detected via DATABASE_URL.
• The createDbClient factory in server/db/index.ts handles dialect detection and returns the appropriate client and migrations.
• SQLite uses bun:sqlite via server/db/sqlite.ts; PostgreSQL uses Bun.sql via server/db/postgres.ts.
• The same query interface works across both engines, making the application database-agnostic.
• Migrations run identically on both engines through the runMigrations helper.

Frequently Asked Questions

Can I use MySQL with Instatic?

No. As implemented in CoreBunch/Instatic, the supported database options are limited to SQLite and PostgreSQL. The createDbClient factory in server/db/index.ts only recognizes URLs for these two engines, and there is no MySQL client implementation in the current codebase.

How does Instatic handle dialect differences?

Instatic isolates dialect-specific code in separate client files (server/db/sqlite.ts and server/db/postgres.ts). The createDbClient function returns a unified interface, allowing the rest of the application to run the same queries regardless of which database engine is active. Specific rules for JSON handling and type mappings are documented in docs/reference/database-dialects.md.

Is the SQLite client compatible with serverless deployments?

While SQLite works well for local development and single-node deployments, it requires a writable filesystem. For serverless environments (like AWS Lambda or Vercel Functions), you should use the PostgreSQL option instead, as it supports stateless connections over the network.

What happens if DATABASE_URL is unset?

If the DATABASE_URL environment variable is not defined, the application will fail to initialize because createDbClient requires a valid connection string. For development, you can provide a default like sqlite:./.tmp/dev.db as shown in the code examples, but production deployments must explicitly set this variable to either a SQLite or PostgreSQL URL.

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