How to Configure Instatic to Use SQLite: A Complete Guide

Set the DATABASE_URL environment variable to a SQLite connection string (e.g., sqlite:./data/cms.db) before starting the server; Instatic automatically detects SQLite when the URL begins with sqlite: or file:, or ends with .db, selecting the appropriate adapter without code changes.

Instatic, an open-source CMS maintained by CoreBunch, abstracts its data layer behind a unified DbClient interface that supports both PostgreSQL and SQLite. Because the framework defaults to SQLite for development, you can run a local instance immediately without installing a database server. This guide explains how the environment-driven configuration works and how to deploy SQLite in production.

How Instatic Detects SQLite Automatically

The database selection logic resides in server/db/index.ts. At startup, the server reads DATABASE_URL from server/config.ts and passes it to createDbClient, which determines whether to instantiate the SQLite or PostgreSQL adapter.

URL Pattern Recognition

The helper function isSqliteUrl checks for three distinct patterns:

• Prefix sqlite:
• Prefix file:
• Suffix .db

If matched, parseSqlitePath strips the scheme to extract the absolute filesystem path. For example, sqlite:./data/cms.db resolves to ./data/cms.db for the underlying driver.

Adapter Selection via createDbClient

The createDbClient function dispatches to createSqliteClient (defined in server/db/sqlite.ts) when isSqliteUrl returns true; otherwise, it initializes the PostgreSQL client. This ensures zero-downtime dialect switching through configuration alone.

Step-by-Step SQLite Configuration

1. Configure the Environment Variable

Add the following to your project root .env file:

DATABASE_URL=sqlite:./data/cms.db
UPLOADS_DIR=./uploads
STATIC_DIR=./dist

The default value in server/config.ts is sqlite:./.tmp/dev.db, which is used only when DATABASE_URL is undefined.

2. Ensure Directory Permissions

The Bun process must have write access to the target directory. Instatic automatically creates missing directories during the first connection attempt, but the parent directory must exist and be writable.

3. Start the Development Server

Run the following command:

bun run dev

The boot log will display “SQLite client created” and apply migrations from server/db/migrations-sqlite.ts automatically.

4. Verify the Connection

Test the health endpoint to confirm the database is reachable:

curl http://localhost:3000/health

A 200 response indicates the DbClient is connected and the schema is initialized.

Deployment Configurations

Docker Compose with SQLite Override

For containerized environments, use the provided Compose override to ensure persistent storage:

docker compose -f compose.prod.yml -f compose.sqlite.yml up -d --build

This mounts a volume for the database file and sets the appropriate DATABASE_URL internally.

Render.com Deployment

The repository includes a Render blueprint at docs/deployment/render/sqlite/render.yaml that configures:

envVars:
  - key: DATABASE_URL
    value: sqlite:/app/storage/data/cms.db

VPS and Production Paths

According to docs/deployment/vps.md, production SQLite deployments should use absolute paths like sqlite:/app/data/cms.db to ensure the database persists across container restarts. Relative paths may be lost when containers are recreated.

Migration Architecture and Schema Parity

Instatic maintains separate migration files for each dialect: server/db/migrations-sqlite.ts and server/db/migrations-pg.ts. As documented in docs/reference/database-dialects.md, migration IDs must remain identical across both files to ensure consistent schema versioning regardless of the active adapter.

When running on SQLite, the server executes only the SQLite-specific migration set, ensuring compatibility with SQLite's data types and constraints.

Runtime Verification Example

You can verify the adapter selection programmatically using the Bun REPL:

import { createDbClient } from './server/db/index.js';
const { db } = createDbClient('sqlite:./data/cms.db');
await db.query`SELECT 1`.then(r => console.log('SQLite OK:', r));

This instantiates the client directly and executes a test query against the SQLite file.

Summary

• Instatic uses the DATABASE_URL environment variable to select the database dialect via server/config.ts.
• SQLite is detected automatically when the URL starts with sqlite:, file:, or ends with .db.
• The default development configuration uses sqlite:./.tmp/dev.db.
• Use compose.sqlite.yml for Docker deployments requiring persistent SQLite storage.
• Migration parity between SQLite and PostgreSQL is enforced through identical migration IDs in server/db/migrations-sqlite.ts and server/db/migrations-pg.ts.

Frequently Asked Questions

What is the default database if I don't set DATABASE_URL?

If DATABASE_URL is undefined, Instatic defaults to sqlite:./.tmp/dev.db as specified in server/config.ts. This allows immediate local development without any configuration changes.

Can I switch from SQLite to PostgreSQL without losing data?

No, the adapters are not interoperable at the data level. You must export your data from SQLite and import it into PostgreSQL manually. The application logic remains unchanged because both adapters implement the same DbClient interface defined in the source.

Does Instatic support WAL mode for SQLite?

The server/db/sqlite.ts implementation uses the standard Bun SQLite driver. While the core adapter handles connection pooling, specific SQLite pragmas like WAL mode depend on the underlying driver configuration and can be added by extending the client initialization in your own fork.

Where should I store the SQLite database file in production?

For production deployments, use absolute paths like sqlite:/app/data/cms.db to ensure the file persists outside container layers. The Docker Compose examples in docs/deployment/vps.md map host volumes to these absolute paths for durability across container restarts.

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