# What Database Options Does Instatic Support? SQLite and PostgreSQL Guide

> Instatic supports SQLite for local development and PostgreSQL for production, automatically configured via the DATABASE_URL environment variable. Learn more!

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: how-to-guide
- Published: 2026-07-02

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts).

### PostgreSQL (Production)

For production workloads, Instatic supports PostgreSQL through Bun's [`Bun.sql`](https://github.com/CoreBunch/Instatic/blob/main/Bun.sql) driver. URLs beginning with `postgres:` or `postgresql:` are routed to the PostgreSQL client implementation in [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts).

## How Database Detection Works

The automatic selection happens in the `createDbClient` factory function located in [`server/db/index.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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:

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

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

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

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

Detailed dialect rules regarding JSON column naming and migration parity are documented in [`docs/reference/database-dialects.md`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/index.ts) handles dialect detection and returns the appropriate client and migrations.
- SQLite uses `bun:sqlite` via [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts); PostgreSQL uses [`Bun.sql`](https://github.com/CoreBunch/Instatic/blob/main/Bun.sql) via [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts) and [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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.