You are building a data migration tool. You need to sync 1 million user records from an old SQL DB to MongoDB. How do you implement this in Node.js to ensure maximum speed without exhausting the server's RAM?

Question

Accepted Answer

Migrating 1 million records efficiently requires moving away from loading all data into memory at once. The core strategy is to process records in small, fixed-size batches. This keeps memory usage constant regardless of total data volume. You'll combine this with MongoDB's `bulkWrite()` for efficient insertion and carefully manage backpressure between reading from SQL and writing to MongoDB. The key insight from production experience is that fetching all records with `toArray()` on either side will crash your process with an out-of-memory error. Instead, you must treat the migration as a controlled data pipeline. The first critical rule: never `SELECT * FROM table` without limits. That would load 1 million rows into your Node.js memory at once, likely exceeding the default 1.5GB V8 heap limit. Instead, use SQL pagination with `LIMIT` and `OFFSET`, or for better performance with large offsets, use keyset pagination (e.g., `WHERE id > lastId ORDER BY id`). This ensures you only hold one batch of records (e.g., 1,000 rows) in memory at a time. After processing each batch, allow the garbage collector to reclaim memory by letting the batch variable go out of scope. ```javascript
// lib/sqlReader.js
const mysql = require('mysql2/promise');

class SqlReader {
  constructor(connectionConfig, tableName, batchSize = 1000) {
    this.pool = mysql.createPool(connectionConfig);
    this.tableName = tableName;
    this.batchSize = batchSize;
    this.lastId = 0;
  }

async getNextBatch() {
    const [rows] = await this.pool.query(
      `SELECT * FROM ${this.tableName} WHERE id > ? ORDER BY id LIMIT ?`,
      [this.lastId, this.batchSize]
    );
    
    if (rows.length > 0) {
      this.lastId = rows[rows.length - 1].id;
    }
    
    return rows;
  }

async hasMore() {
    const [rows] = await this.pool.query(
      `SELECT 1 FROM ${this.tableName} WHERE id > ? LIMIT 1`,
      [this.lastId]
    );
    return rows.length > 0;
  }
}

module.exports = SqlReader;
```

This approach maintains a constant memory footprint—only one batch of rows is loaded at a time, regardless of the total 1 million records. Individual `insertOne()` calls for each record would be prohibitively slow due to network round-trip overhead. Instead, use MongoDB's `bulkWrite()` to send multiple operations in a single network call. The driver automatically splits large batches to stay within the 16MB BSON limit and the server's `maxWriteBatchSize` (100,000 operations). Set `ordered: false` to allow MongoDB to execute operations in parallel and continue even if individual inserts fail, which maximizes throughput for independent records. You must also configure a `batchSize` for your bulk writes—1,000 to 5,000 operations per batch is a proven range that balances memory usage and performance. ```javascript
// lib/mongoWriter.js
const { MongoClient } = require('mongodb');

class MongoWriter {
  constructor(uri, dbName, collectionName) {
    this.client = new MongoClient(uri);
    this.dbName = dbName;
    this.collectionName = collectionName;
  }

async connect() {
    await this.client.connect();
    this.collection = this.client.db(this.dbName).collection(this.collectionName);
  }

async insertBatch(documents) {
    if (documents.length === 0) return { insertedCount: 0 };

const operations = documents.map(doc => ({
      insertOne: { document: doc }
    }));

try {
      const result = await this.collection.bulkWrite(operations, { ordered: false });
      return result;
    } catch (error) {
      // Bulk write errors still contain partial results
      if (error.name === 'MongoBulkWriteError') {
        console.error(`Batch had ${error.writeErrors.length} failures`);
        return error.result;
      }
      throw error;
    }
  }

async close() {
    await this.client.close();
  }
}

module.exports = MongoWriter;
```

Note: `bulkWrite()` with large arrays is memory-intensive within the driver, but by limiting to ~1,000 ops per batch, you stay well within safe limits. The orchestrator connects the batched SQL reader and the batched MongoDB writer. It runs a loop: read a batch from SQL, transform the records (field mapping, data type conversion), write the batch to MongoDB, and track progress. Critically, you should introduce a small delay between batches (e.g., `setTimeout(10)`) to avoid overwhelming the target database and to allow pending write operations to settle. This also prevents driver-level connection pool exhaustion. The loop continues until SQL has no more records. ```javascript
// migrate.js
const SqlReader = require('./lib/sqlReader');
const MongoWriter = require('./lib/mongoWriter');
const { transformRow } = require('./utils/transform');

const BATCH_SIZE = 1000; // Sweet spot for performance/memory balance
const DELAY_MS = 10; // Small delay to let MongoDB breathe

async function migrate() {
  const sqlReader = new SqlReader(
    { host: 'localhost', user: 'root', password: 'pass', database: 'old_db' },
    'users',

You are building a data migration tool. You need to sync 1 million user records from an old SQL DB to MongoDB. How do you implement this in Node.js to ensure maximum speed without exhausting the server's RAM?

Implement the migration using a batched, stream-like approach: read users in small chunks from SQL, transform each batch, and insert using unordered bulkWrite with controlled batch sizes to maximize throughput while keeping memory constant

You are building a data migration tool. You need to sync 1 million user records from an old SQL DB to MongoDB. How do you implement this in Node.js to ensure maximum speed without exhausting the server's RAM?

Implement the migration using a batched, stream-like approach: read users in small chunks from SQL, transform each batch, and insert using unordered bulkWrite with controlled batch sizes to maximize throughput while keeping memory constant

Scenario Questions

Follow-up Questions