Don't want to set this up manually? Paste a link to this page into a Devin session and ask it to set everything up for you. Analyze your MongoDB collections and design relational equivalents. If your data involves multiple collections with relationships, create an Entity-Relationship Diagram first to validate normalization decisions before writing any SQL. Key translation patterns: * **Document IDs**: Convert MongoDB `_id` fields to UUID or SERIAL primary keys * **Nested objects**: Flatten into separate tables with foreign key relationships * **Arrays**: Use Postgres array types or separate junction tables * **Embedded documents**: Extract into related tables with proper normalization ```txt Analyze MongoDB schema and design Postgres tables theme={null} Analyze our existing MongoDB collections and document schemas. For each collection: 1. Design a PostgreSQL table equivalent with proper column types 2. Identify relationships and create foreign keys 3. Flatten nested objects into separate tables where appropriate 4. Create an ERD showing all entities and relationships 5. Convert MongoDB ObjectIds to UUIDs 6. Write the SQL migration to create the schema ``` Once the schema is ready, set up security: create Postgres roles with appropriate privileges, and enable Row Level Security (RLS) policies on tables that need row-level access control. Replace your MongoDB driver (e.g. `mongoose`, `mongodb`) with a Postgres client library — Prisma, Drizzle, TypeORM, or `node-postgres` depending on your stack. Update your environment config with `DATABASE_URL` or equivalent connection credentials. Query migration patterns: * **Find operations** → SQL `SELECT` queries or ORM `.findMany()` / `.select()` * **Aggregation pipelines** → `JOIN`s, `GROUP BY`, subqueries, window functions * **Updates** → SQL `UPDATE` statements * **Inserts** → SQL `INSERT` or bulk insert methods ```txt Migrate backend from MongoDB to Postgres theme={null} Replace our MongoDB driver with [Prisma / Drizzle / node-postgres]. For each backend module: 1. Replace MongoDB imports and connection setup 2. Rewrite all queries to use the new Postgres client 3. Update error handling (e.g. unique constraint violations are Postgres code 23505) 4. Preserve all existing API response formats 5. Run tests after each module to verify nothing breaks ``` If your app uses custom JWT auth with a MongoDB user store, update the user lookup queries to use Postgres while keeping the same token generation and validation logic. Update frontend services to handle any data shape changes from the migration — most commonly `_id` becoming `id`. Keep existing service method signatures so components don't need changes. * Update authentication services if the auth flow changed * Adjust HTTP client request/response handling for new data shapes * Update route guards and resolvers if data fetching patterns changed Export your MongoDB data, transform it to match the Postgres schema, and import it. ```txt Create a data migration script theme={null} Write a migration script that: 1. Exports data from our MongoDB collections 2. Transforms documents to match the PostgreSQL schema (ObjectId → UUID, flatten embedded docs, convert dates) 3. Imports into PostgreSQL preserving foreign key relationships 4. Verifies data integrity: row counts, key relationships, spot-checks ``` After import, run the full test suite against the Postgres backend. Verify all CRUD operations, authentication flows, and role-based access work correctly. Deploy with feature flags for gradual rollout. Keep the MongoDB instance as a fallback during the transition. Post-deploy checklist: * Add indexes for frequently queried columns * Use `EXPLAIN ANALYZE` to identify slow queries * Set up connection pooling (e.g. PgBouncer) * Monitor query performance and connection utilization * Document rollback procedures for critical issues ```txt Post-migration optimization theme={null} The MongoDB to Postgres migration is deployed. Run EXPLAIN ANALYZE on our most frequent queries and suggest indexes. Check for: 1. Sequential scans on large tables that should use indexes 2. Missing indexes on foreign key columns 3. Queries that could benefit from partial or composite indexes 4. Connection pool sizing recommendations based on our workload ``` Once everything is stable and verified, decommission the MongoDB instance. If you need to run this migration pattern across multiple services or repositories, save it as a [playbook](/product-guides/creating-playbooks) so every session follows the same process. Below is an example playbook for a MongoDB to Postgres migration: ```txt MongoDB to PostgreSQL Migration theme={null} ## Overview Migrate a project from MongoDB to PostgreSQL. This covers schema design, backend API migration, frontend service layer updates, data migration, and deployment. ## What's Needed From User - Access to the source MongoDB database (connection string or export) - Target PostgreSQL database credentials (host, port, database, user, password) - Description of the application's data model and key queries - Any authentication/authorization requirements for the new system ## Phase 1: Database Schema Design & Setup ### Step 1: Project Setup 1. Provision a PostgreSQL database and note connection credentials 2. Analyze existing MongoDB collections and document schemas 3. Design PostgreSQL table equivalents with proper relationships ### Step 2: ERD Creation If the source MongoDB schema involves multiple collections with relationships (embedded documents, references via ObjectIds, or junction patterns), create an Entity-Relationship Diagram before designing PostgreSQL tables: 1. Identify all entities and their attributes from the MongoDB collections 2. Map relationships between entities (one-to-one, one-to-many, many-to-many) 3. Produce an ERD using a tool like dbdiagram.io, Mermaid, or draw.io 4. Use the ERD to validate normalization decisions and foreign key design before writing any SQL Skip this step if the migration involves only a single collection or a handful of independent collections with no meaningful relationships. ### Step 3: Schema Translation Patterns - Document IDs: Convert MongoDB _id fields to PostgreSQL UUID or SERIAL primary keys - Nested Objects: Flatten into separate tables with foreign key relationships - Arrays: Use PostgreSQL array types or separate junction tables - Embedded Documents: Extract into related tables with proper normalization ### Step 4: Security Configuration 1. Create PostgreSQL roles and users with appropriate privileges (read-only, read-write, admin) 2. Use GRANT/REVOKE to restrict table and column access per role 3. If the application needs row-level access control, enable Row Level Security (RLS) policies on relevant tables ### Step 5: Database Functions & Triggers 1. Create user management triggers for automatic record creation (e.g., audit timestamps, default values) 2. Implement business logic functions in PL/pgSQL to replace application-level validations where appropriate ## Phase 2: Backend API Migration ### Step 1: Dependency Management 1. Replace MongoDB drivers (e.g., mongodb, mongoose) with a PostgreSQL client library (e.g., pg for Node.js, psycopg2 for Python, or an ORM like Prisma/Sequelize/TypeORM) 2. Update environment configuration with PostgreSQL connection credentials (DATABASE_URL or individual host/port/db/user/ password vars) 3. Remove or replace any MongoDB-specific middleware or utilities ### Step 2: Authentication System Updates 1. If the existing app uses custom JWT auth, keep it and update the user lookup queries to use PostgreSQL 2. If migrating to a new auth system, replace token generation and validation middleware accordingly 3. Maintain existing API response formats for frontend compatibility ### Step 3: Query Migration Patterns - Find Operations: Convert collection.find() to SQL SELECT queries (or ORM equivalents like .findMany(), .select()) - Aggregation Pipelines: Replace with PostgreSQL JOINs, GROUP BY, subqueries, and window functions - Updates: Convert updateOne/updateMany to SQL UPDATE statements - Inserts: Replace insertOne/insertMany with SQL INSERT statements or bulk insert methods ### Step 4: Error Handling Updates 1. Replace MongoDB-specific error codes with PostgreSQL error handling (e.g., unique constraint violations are code 23505) 2. Maintain consistent error response formats for frontend services ## Phase 3: Frontend Service Layer Updates ### Step 1: Authentication Service Migration 1. Update user authentication service to work with the new backend auth endpoints 2. If auth approach changed, update login/logout flows and token management accordingly 3. Modify user object creation to work with the new user data format from PostgreSQL ### Step 2: HTTP Client Updates 1. Update API endpoint URLs if backend structure changes 2. Adjust request/response handling if data shapes changed (e.g., _id becoming id) 3. Maintain existing service method signatures for component compatibility ### Step 3: Route Guard & Resolver Updates 1. Update authentication guards to work with the new auth system 2. Modify route resolvers if data fetching patterns changed 3. Test that existing component data flows remain intact ## Phase 4: Data Migration & Testing ### Step 1: Data Export & Transformation 1. Export MongoDB collections using mongoexport or custom scripts 2. Transform data to match PostgreSQL schema requirements 3. Handle data type conversions (ObjectId to UUID, dates, embedded documents to foreign keys, etc.) ### Step 2: Data Import Process 1. Import data using psql \copy, pg_restore, or custom migration scripts 2. Verify foreign key relationships and constraints after import 3. Run counts and spot-checks to confirm data integrity and completeness ### Step 3: End-to-End Testing 1. Test all CRUD operations with real data 2. Verify authentication flows work correctly 3. Test admin functionality and role-based access ## Phase 5: Deployment & Monitoring ### Step 1: Deployment Strategy 1. Deploy backend changes with feature flags for gradual rollout 2. Update frontend configuration for production PostgreSQL-backed endpoints 3. Implement database connection pooling (e.g., PgBouncer) and monitoring ### Step 2: Performance Optimization 1. Add database indexes for frequently queried columns 2. Use EXPLAIN ANALYZE to identify and optimize slow queries 3. Monitor query performance and connection pool utilization ### Step 3: Rollback Planning 1. Maintain MongoDB instance as backup during transition period 2. Document rollback procedures for critical issues 3. Create data synchronization scripts if needed ## Key Migration Patterns ### Authentication Migration - From: Custom JWT with MongoDB user store - To: Custom JWT (or framework auth) with PostgreSQL user store - Preserve: Existing user object interfaces and component contracts ### Database Query Migration - From: MongoDB aggregation pipelines and document queries - To: SQL queries with JOINs, GROUP BY, and window functions - Preserve: API response formats and data structures ### Security Migration - From: Application-level authorization with MongoDB - To: PostgreSQL roles/privileges plus optional RLS policies - Preserve: User role and permission logic ## Specifications - All existing API contracts and response formats must be preserved - Data integrity must be verified post-migration (row counts, key relationships, spot-checks) - The MongoDB instance should remain available as a fallback during the transition period - Validation: Run the application's test suite end-to-end against the PostgreSQL backend and confirm all tests pass ```

{title}