Skip to content
HappyView

Postgres to SQLite migration

This guide covers migrating an existing HappyView deployment from Postgres to SQLite. If you are staying on Postgres, no action is required.

Overview

Section titled “Overview”

The new database starts empty: records come back through backfill, and everything else is re-created by hand (step 6). HappyView’s own SQL needs no changes, and neither do happyview.db chains. A script that passes raw Postgres SQL to happyview.sql’s raw does need changing, because raw SQL reaches the backend untranslated.

Step 1: Export your data

Section titled “Step 1: Export your data”

Back up your Postgres database before making any changes:

Terminal window
pg_dump -U happyview happyview > happyview_backup.sql

Step 2: Update environment variables

Section titled “Step 2: Update environment variables”

Change your .env to use SQLite:

Terminal window
# Before
DATABASE_URL=postgres://happyview:happyview@localhost/happyview


# After
DATABASE_URL=sqlite://data/happyview.db?mode=rwc

If you had DATABASE_BACKEND set, update it as well:

Terminal window
DATABASE_BACKEND=sqlite

Step 3: Migrate raw SQL in scripts

Section titled “Step 3: Migrate raw SQL in scripts”

Convert raw SQL in Lua scripts from Postgres syntax to SQLite syntax. migrate-lua-sql automates this. It reads .lua files on disk, so copy any script you keep in the dashboard into a file first, and paste the result back afterwards. Convert raw SQL in JavaScript or TypeScript scripts by hand, using the list below.

Run the codemod tool

Section titled “Run the codemod tool”
Terminal window
# Preview the changes first
migrate-lua-sql --dry-run /path/to/lua/scripts


migrate-lua-sql /path/to/lua/scripts

The tool ships in the release archive, not the Docker image; from a clone, run cargo run --bin migrate-lua-sql -- /path/to/lua/scripts. It scans all .lua files in the given directory and rewrites Postgres SQL patterns to SQLite equivalents.

What the codemod converts automatically

Section titled “What the codemod converts automatically”
  • $1, $2, etc. parameter placeholders to ?
  • JSON operators (->, ->>) and ::jsonb casts to json_extract() calls
  • ILIKE to LIKE (SQLite LIKE is case-insensitive for ASCII by default)
  • NOW() to datetime('now')
  • NOW() + INTERVAL '...' / NOW() - INTERVAL '...' to datetime('now', '...')
  • TRUE/FALSE literals to 1/0

What it flags for manual review

Section titled “What it flags for manual review”

The tool prints warnings for patterns it cannot convert automatically:

  • JSONB ? (contains-key) operator: consider using json_each() with an EXISTS subquery
  • make_interval(): Postgres-specific, needs manual conversion
  • SIMILAR TO: use LIKE or GLOB instead
  • ANY() / ALL() array operators: no direct SQLite equivalent
  • Type casts other than ::jsonb (e.g., ::text, ::integer): may need manual conversion to CAST(... AS ...)

Review the flagged lines and update them manually.

Step 4: Import data into SQLite

Section titled “Step 4: Import data into SQLite”

There is no direct import. Start HappyView with the new DATABASE_URL, and it creates the SQLite database and runs migrations automatically. Then re-index your records from the network:

  1. Start HappyView with the new SQLite DATABASE_URL
  2. Install your interpreters and libraries, then redeploy your project, or upload your lexicons via the dashboard or admin API
  3. Run a backfill for each collection (dashboard or POST /admin/backfill)

Backfill fetches records fresh from the network, so records that exist only on this instance don’t come back.

Step 5: Update Docker Compose (if applicable)

Section titled “Step 5: Update Docker Compose (if applicable)”

If you were running Postgres via Docker Compose, switch to docker-compose.prod.sqlite.yml. See Docker.

Step 6: Re-create admin settings

Section titled “Step 6: Re-create admin settings”

Instance settings (app name, logo, terms and privacy URIs), API keys, API clients, operators, plugins, script variables and labeler subscriptions are stored in the database and are not carried over. Re-create these via the dashboard or admin API after switching. OAuth sessions don’t carry over either, so everyone signs in again.

Rollback

Section titled “Rollback”

To switch back to Postgres, revert your DATABASE_URL to the Postgres connection string, and revert any script you converted. HappyView does not modify your Postgres database during the migration to SQLite, so it stays as it was when you switched.

Next steps

Section titled “Next steps”

© HappyProto