Mongodb to SQL Migration 플랜

기존의 고객 데이터베이스들을 SQL 기반 DB로 옮기는 것 까지는 기정사실이긴 한데, 구체적으로 어느 SQL로 가야 하는가? Postgres, MySQL, MariaDB, Oracle, Microsoft, SQLite 이렇게 다양한데 판단의 기준을 세워야 할 것 같다.

판단의 기준

1. 가격

엔터프라이즈 DB는 정말 더럽게 비싸다. 목표는 MongoDB보다 저렴하고 AWS 비용까지 고려했을 때 합리적인 가격이어야 한다.

https://aws.amazon.com/rds/pricing/

AWS Aurora는 MySQL, PostgreSQL 호환이 되는 고성능 데이터베이스 서비스이다. 두가지 버전이 있는데, IO에 요금을 부과하는 Aurora Standard와 스토리지 저장용량에 요금을 부과하는 Aurora I/O-Optimized 서비스가 존재한다.

AWS Aurora는 다양한 Capacity 옵션이 존재한다. Serverless, Provisioned On-Demand Instance, Provisioned Reserved Instance, Limitless.

AWS Aurora Serverless 옵션은 서버리스 특성상 자동으로 스케일링을 조절한다. 요청이 없으면 스스로 꺼지고 갑자기 많은 양의 트랜잭션이 요청되면 자원을 수평적으로 할당한다. Provisioned 옵션과 달리 항시 켜져있지 않기 때문에 사용시간을 특별한 ACU (Aurora Capacity Units) 단위를 가지고 요금을 책정하는데, 1초 단위로 아주 정밀하게 요금을 계산한다.

서버리스가 싫으면 Provisioned On-Demand Instance 혹은 Provisioned Reserved Instance를 사용하면 된다. 전자는 일반적인 RDS 인스턴스를 월 단위로 지불하는 방식이고, Reserved Instance는 한 번에 몇개월/몇년치 금액을 지불하여 쓰는 방식이다.

2. 기술지원

돈을 많이 내면 (엔터프라이즈 요금제를 사용하면) 각종 기술지원 및 사후지원이 제공된다. PostgreSQL도 엔터프라이즈 고객에 대한 기술지원이 제공된다고 하는데, 확실히 날 것 그대로의 오픈소스 보다는 기술보증이 탄탄하니까 돈이 오가는 크리티컬한 업계라면 고려해볼만하다.

3. 성능

어차피 NoSQL에서 넘어오는데... 아무 SQL DB를 사용해도 성능향상은 보장된다.

4. ORDB

PostgreSQL 때문에 고민이다. 얘는 그냥 RDBMS가 아니라 ORDBMS라서 "객체" 개념이 들어간다. Schema에 Function이 달릴 수 있는 영락없는 객체라고. 이걸로 어떤 차별성을 가질 수 있는지도 잘 모르겠고, 우리 도메인에 ORDB가 필요한지도 알아내야 한다.

5. Type

Enum, Uuid, Decimal, Json, Array같은 타입을 우리 서비스에서 사용하는가? 그러면 가능한 최대한 많은 타입을 지원하는 DB를 써야 유리할 것이다. 안 그러면 마이그레이션할 때 변환 로직을 추가해야 할 것이다.

AI Response :: deepseek-r1:14b

1. Assessment and Planning

• Evaluate Current Schema: Analyze MongoDB collections, document structure, and relationships between documents.
• Choose SQL Database: Select based on factors like performance, scalability, cost, and ecosystem compatibility (e.g., MySQL, PostgreSQL).
• Plan Migration Strategy: Define timelines, resources, and potential risks.

2. Design Target SQL Schema

• Map Collections to Tables: Each MongoDB collection becomes an SQL table with a primary key.
• Handle Nested Documents: Decide whether to flatten or split into separate tables, considering normalization/denormalization trade-offs.
• Data Types Mapping: Convert MongoDB data types (e.g., ObjectId to string) to appropriate SQL types.

3. Extract Data from MongoDB

• Use tools like mongodump or write scripts to export data in formats suitable for import, such as JSON or CSV.

4. Transform and Load Data

• Data Transformation: Process nested documents and structure data into relational format using ETL processes.
• Load Data: Utilize SQL utilities or custom scripts to import transformed data into the target database.

5. Validation

• Compare MongoDB and SQL schemas for consistency.
• Run queries to ensure data integrity and completeness.

6. Application Testing

• Test application functionality post-migration, focusing on database interactions.
• Conduct stress testing to identify performance issues.

7. Optimization

• Indexing: Optimize SQL tables with appropriate indexes for query performance.
• Query Adjustments: Refine queries to leverage SQL strengths, possibly adjusting from MongoDB-specific operations.

8. Documentation and Support

• Document the migration process, challenges, and solutions.
• Monitor SQL database performance post-deployment and provide ongoing support.

9. Consider Tools and Features

• Explore tools like mongomigrate for direct data transfer.
• Address MongoDB-specific features (e.g., aggregation pipelines) in SQL using stored procedures or complex queries.

By following these steps, the migration can be executed methodically, ensuring minimal disruption and successful transition to a SQL-based system.

AI Response :: llama3.1:8b

Migrating from MongoDB to a SQL-based database system involves several steps, including understanding the schema and data structure of your current MongoDB setup, choosing a target SQL-based database (e.g., MySQL, PostgreSQL), designing and implementing the conversion process, testing the new database, and ensuring data integrity. Here's a comprehensive plan to guide you through this transition:

1. Understand Your Current MongoDB Schema

Before migrating to a SQL-based system, it's crucial to understand how your data is structured in MongoDB.

• Inspect Existing Collections: Look at each collection (or table equivalent) in your MongoDB database.
• Document Structure: Understand the fields and their types within each document. This will be analogous to defining columns with specific data types in a SQL-based system.
• Relationships Between Documents: If there are relationships between documents (similar to foreign keys in SQL), you'll need to define how these relationships are maintained in your SQL schema.

2. Choose Your Target SQL-Based Database

Select the SQL-based database that best fits your needs, considering factors such as:

• Features and Performance Requirements: Ensure it can handle the volume of data and complexity of queries.
• Cost and Support: Balance costs with the level of support you require for development and maintenance.

Popular choices include MySQL, PostgreSQL, Microsoft SQL Server (if on Windows), Oracle Database (for more robust enterprise scenarios), and SQLite for local/small-scale applications.

3. Design Your New SQL Schema

Based on your understanding of MongoDB schema, design a new database schema in your chosen SQL system. Consider:

• Normalization: Ensure data is normalized to prevent redundancy and improve scalability.
• Indexes: Plan for efficient querying by defining appropriate indexes based on how you expect data will be retrieved.
• Foreign Keys: Implement foreign keys if relationships between tables are defined.

4. Convert Your MongoDB Data

Implement the actual migration process:

1. Export Existing Data from MongoDB:

   • Use mongoexport command-line tool or a library like Mongoose in Node.js to export your data into CSV, JSON, or another format suitable for importing into your SQL database.

2. Prepare SQL Database and Schema:

   • Create the tables with appropriate fields and indexes.
   • Import the exported data into these tables using tools provided by your chosen SQL system (e.g., LOAD DATA INFILE in MySQL).

3. Transform Complex Data Structures (if necessary):

   • If MongoDB data contains complex structures like embedded documents, arrays, or nested collections, you might need custom scripts to flatten them for easier import into the relational database.

5. Test the New Database

• Verify Schema and Data Integrity: Run queries to ensure that data is correctly migrated with no inconsistencies.
• Performance Testing: Stress-test your new system under heavy loads to ensure it meets performance expectations.

6. Ensure Compatibility and Minimize Downtime (if applicable)

If you're migrating a live application:

• Implement A/B Testing or Staging Environment: Allow for testing the new database without affecting production users.
• Minimize Downtime: Schedule downtime carefully, if necessary, to avoid peak usage times.

Migrating from MongoDB to a SQL-based system requires careful planning and execution. The key is understanding your data model in both systems, choosing an appropriate SQL system, and ensuring that the conversion process maintains integrity and performance.

AI Response :: o3-mini-high

Below is a comprehensive plan for migrating from MongoDB to a SQL-based system using Prisma ORM. This plan assumes you already have a well-structured Prisma schema for MongoDB and outlines the steps from schema conversion to data migration and verification.

1. Assess and Redesign the Data Model

• Review Your Existing Schema:
  Since your MongoDB schema is structured via Prisma, start by reviewing your current models. Identify:
  • Collections (models) and their fields.
  • Data types that differ between MongoDB and SQL (for example, MongoDB’s ObjectID vs. SQL’s UUID or auto-incrementing integer).
  • Any embedded documents or arrays that might require normalization in a relational model.
• Redesign for Relational Constraints:
  • Convert embedded documents into separate tables if needed.
  • Define explicit relationships (one-to-many, many-to-many) using foreign keys.
  • Adjust indexes and constraints (unique, not null, etc.) to suit a relational engine.

2. Update the Prisma Schema

• Change the Provider:
  In your schema.prisma file, switch the provider from "mongodb" to your chosen SQL provider (e.g., "postgresql", "mysql", or "sqlite"). For example:

  datasource db {
    provider = "postgresql" // or "mysql" / "sqlite"
    url      = env("DATABASE_URL")
  }
  

• Adapt Model Definitions:

  • Update field types where necessary (for example, converting String @id @default(auto()) from MongoDB’s ObjectID to an appropriate SQL equivalent, such as using @id @default(autoincrement()) for integer keys or @id @default(uuid()) for UUIDs).
  • Define relation fields using Prisma’s relational syntax.

• Regenerate the Prisma Client:
  Once the schema is updated, run:

  npx prisma generate
  

  This will ensure your client is in sync with the updated schema.

3. Set Up the SQL Database Environment

• Provision a New SQL Instance:
  Set up your SQL database (e.g., PostgreSQL or MySQL) and update your environment variables (DATABASE_URL) accordingly.

• Run Prisma Migrations:
  Use Prisma Migrate to create and apply migration files that build the new SQL schema:

  npx prisma migrate dev --name init
  

  This creates the SQL schema in your new database.

4. Plan and Execute Data Migration

• Data Extraction:
  Write a script (using Node.js and the Prisma Client) that connects to your MongoDB instance, reads the data, and prepares it for insertion into the SQL database.

• Data Transformation:

  • Convert data types (for example, format dates, convert MongoDB ObjectIDs, and flatten nested documents if necessary).
  • Handle relational data by ensuring that foreign key relationships are correctly established. This might involve a two-phase approach: first inserting “parent” records, then “child” records.

• Data Loading:
  Write to your new SQL database using Prisma Client. Depending on the volume:

  • For smaller datasets, a straightforward ETL script might suffice.
  • For larger datasets, consider batching the operations and using transactions to maintain consistency.

  Example (simplified):

  const { PrismaClient } = require('@prisma/client');
  const prismaMongo = new PrismaClient({ datasources: { db: { url: process.env.MONGO_URL } } });
  const prismaSQL = new PrismaClient({ datasources: { db: { url: process.env.SQL_URL } } });
  
  async function migrateData() {
    // Extract data from MongoDB
    const mongoRecords = await prismaMongo.myCollection.findMany();
    
    // Transform and load into SQL
    for (const record of mongoRecords) {
      await prismaSQL.myCollection.create({
        data: {
          // Map and transform fields as needed
          name: record.name,
          createdAt: new Date(record.createdAt),
          // ...other fields...
        },
      });
    }
  }
  
  migrateData()
    .then(() => console.log('Migration completed'))
    .catch((e) => console.error(e))
    .finally(async () => {
      await prismaMongo.$disconnect();
      await prismaSQL.$disconnect();
    });
  

• Testing the Migration:

  • Verify that all records have been transferred accurately.
  • Compare record counts and perform spot-checks on key data.
  • Ensure relational integrity is maintained.

5. Update Application Code and Deployment

• Switch the Database Connection:
  Change your application’s configuration to point to the new SQL database. Make sure all environment variables and Prisma client initializations reference the correct DATABASE_URL.

• Test the Application:
  Run end-to-end tests to ensure that your application works with the SQL backend. Check all CRUD operations, relations, and any SQL-specific features.

• Plan for Rollback:
  Keep the MongoDB instance running in parallel (if possible) until you are confident that the SQL database is fully operational. Have a rollback plan in case issues arise.

6. Monitor and Optimize

• Performance Monitoring:
  After migration, monitor database performance. SQL databases have different indexing and query optimization strategies than MongoDB.

• Optimize Indexes and Queries:
  Based on your application’s usage patterns, consider adjusting indexes and query structures to take full advantage of the relational database engine.

• Document Learnings:
  Record any challenges or adjustments made during the migration process to aid future migrations or troubleshooting.

Summary

This migration plan involves careful redesign of your data model, updating the Prisma schema, setting up a new SQL environment, and writing an ETL process for data migration. Testing at every stage is crucial to ensure data integrity and application stability. Once the migration is complete, updating your application configuration and monitoring performance will ensure a smooth transition from MongoDB to your new SQL-based system.

Feel free to reach out with any follow-up questions or for further details on specific parts of the migration process.