Skip to main content

Migrating from SQLite to Postgres

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

Overview​

HappyView writes all internal SQL in SQLite syntax and translates to Postgres automatically at runtime. This means your Lua scripts do not need any changes when switching from SQLite to Postgres — they continue to work as-is.

The main steps are: set up the Postgres database, update your environment variables, and re-index your data.

Step 1: Set up Postgres​

Create a Postgres database for HappyView:

createdb happyview

If you are using Docker Compose, uncomment the postgres service and pgdata volume in your docker-compose.yml. See the database setup guide for details.

Step 2: Back up your SQLite database​

Copy your SQLite database file before making any changes:

cp data/happyview.db data/happyview.db.backup

Step 3: Update environment variables​

Change your .env to use Postgres:

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

# After
DATABASE_URL=postgres://happyview:happyview@localhost/happyview

If you had DATABASE_BACKEND set, update it as well:

DATABASE_BACKEND=postgres

Step 4: Start HappyView​

Start HappyView with the new DATABASE_URL. It will connect to Postgres and run migrations automatically, creating all necessary tables.

Step 5: Re-index your data​

Since HappyView indexes records from the atproto network, the simplest way to populate your new Postgres database is to re-run the backfill:

  1. Upload your lexicons via the dashboard or admin API (or they will already be there if you exported and re-imported them)
  2. Run a backfill for each collection (dashboard or POST /admin/backfill)

Backfill fetches all records fresh from the network, so no data transfer between databases is needed.

tip

If you have many lexicons, you can export them from the old instance before switching. Use GET /admin/lexicons to list them and POST /admin/lexicons to re-upload after switching to Postgres.

Step 6: Re-create admin settings​

Instance settings (app name, logo, TOS/privacy URIs), API keys, users, and labeler subscriptions are stored in the database and are not carried over automatically. Re-create these via the dashboard or admin API after switching.

Lua scripts​

No changes needed. Lua scripts use SQLite syntax by default, and HappyView translates to Postgres automatically at runtime. This includes:

  • ? placeholders (translated to $1, $2, etc.)
  • json_extract() calls (translated to Postgres JSON operators)
  • datetime('now') (translated to NOW())
  • Boolean literals 1/0 (work in both backends)

If you have scripts that already use Postgres-native syntax (e.g., from direct db.raw() calls), they will not work after switching — HappyView expects SQLite syntax. Use the codemod tool to convert them.

Rollback​

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

Next steps​