What Surprised Me About Migrating Our Monolithic SQL Database to MongoDB in Production

I remember the exact moment our monolith’s database buckled under pressure. We’d just rolled out a new feature, usage spiked, and suddenly every other request was fighting for a lock on a single SQL table. If you’ve ever watched queries pile up and users refresh their screens in vain, you know the pain. That’s when we started thinking: what if it’s not the app, but the database itself that’s holding us back?

Why Move From SQL to MongoDB? (And When Not To)

For us, the decision wasn’t driven by “NoSQL hype.” It came from real problems: scaling relational data across a growing team, development velocity slowing to a crawl, schema migrations taking forever, and some tables ballooning to millions of rows—just to store simple JSON blobs.

But heads up: MongoDB is not a silver bullet. If your data is highly relational—think lots of joins, strict constraints, and transactional consistency—sticking with SQL may be smarter. But if you’re wrangling flexible documents, need to shard data easily, or want to iterate fast without a DBA breathing down your neck, MongoDB starts to look pretty attractive.

Here’s what actually surprised me during our migration—what worked, what didn’t, and what I really wish I’d known.

---

Schema: Freedom and Frustration

I was excited about dumping rigid SQL schemas. Turns out, that “freedom” is double-edged.

In SQL, if you want to add a column, you run a migration. In MongoDB, you just add the field to your JSON document. Sounds amazing, right? But when you don’t enforce structure, you (and your team) can drift into chaos fast.

Example: Evolving a User Profile

Suppose you start with a simple user table in SQL:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255)
);

In MongoDB, you’d just create documents like:

// Insert a user document in MongoDB
db.users.insertOne({
  email: "[e-mail]",
  name: "Alice"
});

A month later, you want to add a preferences field. In SQL, you’d write another migration. In MongoDB:

// Add a preferences field to an existing user
db.users.updateOne(
  { email: "[e-mail]" },
  { $set: { preferences: { theme: "dark", notifications: true } } }
);

No schema migration, no downtime. But here’s the catch: without discipline (or at least a schema validation layer in your app), you’ll end up with inconsistent documents—some with preferences, some with prefs, some with weird typos. We actually found three different spellings for the same field after a few months.

Practical tip: Even in MongoDB, define a schema in your application layer (e.g., using Mongoose schemas in Node.js, or Pydantic models in Python). MongoDB 3.6+ supports JSON Schema validation—use it.

---

Relationships: Embedding vs. Referencing

One of the first surprises was that you don’t get joins “for free” in MongoDB. You have to decide: do you embed related data in the same document, or use references (like foreign keys) and stitch things together in your app?

We had a classic blog setup: users, posts, comments.

If you embed comments in the post document, fetching a post with all its comments is one query. But what if a post has 10,000 comments? That document gets huge, and MongoDB has a 16MB document size limit.

Example: Embedding Comments

// Embedding a few comments inside a post document
db.posts.insertOne({
  title: "Hello MongoDB",
  authorId: "u123",
  comments: [
    { userId: "u456", text: "Nice post!" },
    { userId: "u789", text: "Thanks for sharing!" }
  ]
});

This works great for small, bounded lists (like recent activity or tags). But for unbounded relationships, referencing is safer.

Example: Referencing Comments

// Storing comments in a separate collection, referencing postId
db.comments.insertOne({
  postId: ObjectId("665f6e5536d7e8b9e4be6a1d"),
  userId: "u456",
  text: "Nice post!"
});

Now, you fetch comments with:

// Find all comments for a post
db.comments.find({ postId: ObjectId("665f6e5536d7e8b9e4be6a1d") });

What surprised me: We had to re-learn how to model data. Sometimes, we even denormalized (duplicated) data intentionally for performance. The thing is, there’s no single right answer—it depends on your read/write patterns. I spent a weekend rewriting our post+comment logic after we accidentally hit the document size limit. Ouch.

---

Transactions: They Exist, But Don’t Assume SQL Semantics

MongoDB does support multi-document transactions (since v4.0). But if you’re coming from SQL, don’t expect quite the same safety net.

We had code like this in SQL:

BEGIN;

INSERT INTO orders (user_id, total) VALUES (42, 99.99) RETURNING id;

INSERT INTO shipments (order_id, address) VALUES (lastval(), '123 Main St');

COMMIT;

If anything fails, the transaction rolls back. In MongoDB, you can do the equivalent—but you need to be explicit and aware of the trade-offs.

Example: Multi-document Transaction in MongoDB (Node.js)

const session = await client.startSession();

try {
  session.startTransaction();

  // Insert an order
  const ordersColl = client.db("shop").collection("orders");
  const orderRes = await ordersColl.insertOne(
    { userId: 42, total: 99.99 },
    { session }
  );

  // Insert a shipment
  const shipmentsColl = client.db("shop").collection("shipments");
  await shipmentsColl.insertOne(
    { orderId: orderRes.insertedId, address: "123 Main St" },
    { session }
  );

  await session.commitTransaction();
} catch (err) {
  await session.abortTransaction();
  throw err;
} finally {
  await session.endSession();
}

Key things to watch out for:

• Transactions in MongoDB require a replica set (not a standalone server).
• They’re slower than single-document writes.
• If you can keep related data in a single document, MongoDB guarantees atomicity for that document—no need for a transaction.

We ended up rewriting several flows to avoid cross-collection transactions for performance reasons.

---

Querying: Flexible, But Indexes Matter More Than Ever

We loved the ability to query on arbitrary fields. But honestly, MongoDB is not magic—if you don’t create proper indexes, your queries will crawl.

Example: We had a logs collection with millions of documents. Searching by userId was blazing fast—until it wasn’t. Turns out, we forgot to index userId after importing a ton of data.

Example: Creating an Index

// Create an index on userId for fast lookup
db.logs.createIndex({ userId: 1 });

After adding the index, response times dropped from several seconds to under 100ms. Don’t trust that just because MongoDB “scales horizontally,” you’re safe—read the execution plans, and profile your queries.

---

Migration: The Messy Middle

Migrating a live SQL database to MongoDB was hands-down the hardest part. There’s no “magic” tool for this—at least not for complex apps.

We wrote scripts to read from SQL, transform rows to documents, and insert them into MongoDB. This meant handling:

• Data type mismatches (e.g., SQL NULL vs. MongoDB undefined or missing fields)
• Foreign key relationships (turning user_id into an ObjectId reference)
• Legacy data weirdness (like string booleans: "true"/"false" instead of real booleans)

At one point, I discovered we had over a thousand rows in SQL referencing users who no longer existed. MongoDB wouldn’t have caught this, so we had to clean it up manually.

If you care about zero downtime, plan for dual writes or a “big bang” cutover. We chose the latter, with a weekend maintenance window and a lot of caffeine.

---

Common Mistakes

I wish someone had made these clear before we started:

1. Assuming NoSQL Means “No Rules”
   
   Just because MongoDB is flexible doesn’t mean you can ignore schema. Enforce structure somewhere—ideally in your app and in MongoDB itself.

2. Modeling Data Like It’s Still SQL
   
   If you just copy your normalized SQL model, you’ll end up with slow queries and lots of manual joins in your code. Embrace embedding and denormalization where it makes sense.

3. Neglecting Indexes
   
   MongoDB doesn’t automatically create indexes on every field you query. Without them, performance tanks—especially as your data grows.

---

Key Takeaways

• MongoDB’s flexibility is both a blessing and a curse—enforce schemas to avoid chaos.
• Rethink your data modeling: embedding vs. referencing isn’t always obvious.
• Transactions exist, but they’re not as seamless (or cheap) as in SQL. Prefer single-document atomicity when possible.
• Indexes are critical. Profile queries early and often.
• Migration is messy—expect to write custom scripts, and plan for weird edge cases.

---

Moving from SQL to MongoDB in production was eye-opening. We unlocked new speed and agility, but it came with a learning curve and plenty of “gotchas.” If you’re on this path, hopefully my scars help you avoid a few of your own.

---

If you found this helpful, check out more programming tutorials on our blog. We cover Python, JavaScript, Java, Data Science, and more.