Migrating from Postgres to SQLite #

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

Overview #

HappyView now defaults to SQLite and writes all internal SQL in SQLite syntax. When running against Postgres, HappyView translates queries automatically. However, if you have Lua scripts that contain raw Postgres SQL, those scripts need to be updated to use SQLite syntax instead.

Step 1: Export your data #

Back up your Postgres database before making any changes:

pg_dump -U happyview happyview > happyview_backup.sql

Step 2: Update environment variables #

Change your .env to use SQLite:

# 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:

DATABASE_BACKEND=sqlite

Step 3: Migrate Lua scripts #

If you have Lua scripts with raw SQL queries, they need to be converted from Postgres syntax to SQLite syntax. A codemod tool is provided to automate this.

Run the codemod tool #

cargo run --bin migrate-lua-sql -- /path/to/lua/scripts

The tool scans all .lua files in the given directory and rewrites Postgres SQL patterns to SQLite equivalents.

What the codemod converts automatically #

• $1, $2, etc. parameter placeholders to ? positional parameters
• jsonb operators (->, ->>, @>, ?) to SQLite json_extract() calls
• ILIKE to LIKE (SQLite LIKE is case-insensitive for ASCII by default)
• NOW() to datetime('now')
• ::text, ::integer, etc. type casts to SQLite equivalents (CAST(... AS ...))
• COALESCE and other standard SQL functions (no change needed)
• TRUE/FALSE literals to 1/0
• RETURNING * clauses (removed, as SQLite has limited RETURNING support)

What it flags for manual review #

The tool prints warnings for patterns it cannot convert automatically:

• Complex Postgres-specific functions (array_agg, string_agg, generate_series, etc.)
• Window functions with Postgres-specific syntax
• ON CONFLICT clauses with complex conditions
• CTEs (WITH queries) that use Postgres-specific features
• Any SQL that the parser cannot confidently transform

Review the flagged lines and update them manually.

Step 4: Import data into SQLite #

Start HappyView with the new DATABASE_URL. It will create the SQLite database and run migrations automatically. If you need to import existing records, use the backfill feature to re-index from the network:

1. Start HappyView with the new SQLite DATABASE_URL
2. Upload your lexicons via the dashboard or admin API
3. Run a backfill for each collection (dashboard or POST /admin/backfill)

For small datasets, this is the simplest approach since backfill fetches all records fresh from the network.

Step 5: Update Docker Compose (if applicable) #

If you were running Postgres via Docker Compose, you can now comment out the postgres service since it is no longer needed. See the database setup guide for details.

Rollback #

To switch back to Postgres, revert your DATABASE_URL to the Postgres connection string. Your Postgres database remains unchanged — HappyView does not modify it during the migration to SQLite.