Migrating from MongoDB to PostgreSQL

diy-analytics now stores all data in PostgreSQL via Drizzle ORM instead of MongoDB. This is a breaking change for existing self-hosted deployments — the application no longer reads from or writes to MongoDB.

This guide walks through moving an existing deployment over to PostgreSQL, including running the automated one-time migration script (npm run db:migrate-from-mongo) to transfer workspaces, projects, goals, alerts, funnels, pageviews, custom events, and daily rollups.

Before You Start

• Back up MongoDB (mongodump): The migration script only reads from MongoDB, but keeping a snapshot is essential backup practice.
• Sessions are not migrated: Every user will need to log in again once after cutover. Session tokens are short-lived (30 days) and don't carry meaningful continuity across an infrastructure change.
• Script execution details: The migration script is a one-time, non-idempotent tool. If it fails partway or you need to rerun it, truncate target Postgres tables first or pass --force to bypass the "target already has data" guard.

Migration Steps

Step 1: Provision a PostgreSQL Database

Any PostgreSQL 14+ database works. Popular options include:

• Vercel Postgres
• Neon
• Supabase
• Railway or self-hosted PostgreSQL.

Step 2: Set DATABASE_URL

Update your .env.local (or hosting provider environment variables) with your new PostgreSQL connection string:

Step 3: Retain MongoDB Environment Variables Temporarily

Keep your existing MONGODB_URI and MONGODB_DATABASE set during migration. The migration script needs them to connect to your legacy database and read data.

Step 4: Apply the PostgreSQL Schema

Run the schema migration command:

Step 5: Run the Migration Script

Execute the automated migration script to copy data from MongoDB to PostgreSQL:

This reads every collection from MongoDB and inserts the equivalent rows into Postgres, remapping Mongo ObjectIds to new Postgres UUIDs and rewriting all foreign keys (project → workspace, pageview → project, etc.) consistently. Progress and a final row-count comparison (Mongo vs. Postgres) are printed to the console. Any row that fails to migrate is logged to migration-errors.log in the project root rather than aborting the run.

Large pageviews and events collections are streamed and inserted in batches, making it safe to run against production datasets.

Step 6: Verify Row Counts

Check the final table summary printed by the script. If any mismatches occur, review migration-errors.log to inspect failed records.

Step 7: Clean Up Legacy Environment Variables

Once migration is confirmed:

1. Remove MONGODB_URI and MONGODB_DATABASE from your environment configuration.
2. Redeploy your application.

Step 8: Re-authenticate

Ask your workspace users to log in again.

Rolling Back

Because the migration script strictly reads from MongoDB without modifying existing records, your MongoDB database remains intact. If you encounter issues during cutover, you can revert DATABASE_URL and MONGODB_URI back to point at a prior release branch that supports MongoDB.

Renamed Environment Variables