Introduction

Migrating from SQL (relational) to MongoDB (NoSQL) requires understanding fundamental differences in data modeling and querying. This guide helps you translate SQL concepts to MongoDB.

Key Differences

SQL (Relational)

  • Structure: Tables with rows and columns
  • Schema: Fixed schema, enforced by database
  • Relationships: Foreign keys, JOINs
  • Queries: SQL language
  • Transactions: ACID compliant

MongoDB (Document)

  • Structure: Collections with documents (JSON-like)
  • Schema: Flexible schema (schema-on-read)
  • Relationships: Embedded documents or references
  • Queries: JavaScript-like query language
  • Transactions: Available (v4.0+)

Data Modeling

SQL: Normalized Tables

-- Users table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- Orders table
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  total DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Order items table
CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

MongoDB: Embedded Documents

// User document with embedded orders
{
  _id: ObjectId("..."),
  name: "Alice",
  email: "alice@example.com",
  orders: [
    {
      orderId: 1,
      total: 99.99,
      items: [
        { productId: 1, quantity: 2 },
        { productId: 2, quantity: 1 }
      ]
    }
  ]
}

OR with references:

// Users collection
{
  _id: ObjectId("..."),
  name: "Alice",
  email: "alice@example.com"
}

// Orders collection
{
  _id: ObjectId("..."),
  userId: ObjectId("..."),  // Reference
  total: 99.99,
  items: [...]
}

Query Translation

SELECT

SQL:

SELECT id, name, email
FROM users
WHERE status = 'active';

MongoDB:

db.users.find({ status: "active" }, { id: 1, name: 1, email: 1 });

INSERT

SQL:

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

MongoDB:

db.users.insertOne({
  name: "Alice",
  email: "alice@example.com",
});

UPDATE

SQL:

UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

MongoDB:

db.users.updateOne({ id: 1 }, { $set: { email: "newemail@example.com" } });

DELETE

SQL:

DELETE FROM users
WHERE id = 1;

MongoDB:

db.users.deleteOne({ id: 1 });

JOINs vs References

SQL JOIN

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

MongoDB: $lookup (Aggregation)

db.users.aggregate([
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders",
    },
  },
  { $unwind: "$orders" },
  {
    $project: {
      name: 1,
      total: "$orders.total",
    },
  },
]);

MongoDB: Embedded (Better Performance)

// If orders are embedded
db.users.find({}, { name: 1, "orders.total": 1 });

Aggregation

SQL GROUP BY

SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total) AS total_spent
FROM orders
GROUP BY user_id;

MongoDB Aggregation

db.orders.aggregate([
  {
    $group: {
      _id: "$userId",
      orderCount: { $sum: 1 },
      totalSpent: { $sum: "$total" },
    },
  },
]);

Migration Strategies

1. Direct Translation

Translate SQL queries to MongoDB queries:

  • Simple SELECT → find()
  • JOINs → $lookup or embedded documents
  • Aggregations → aggregation pipeline

2. Data Modeling Redesign

Before (SQL normalized):

  • Users table
  • Orders table
  • Products table
  • OrderItems table

After (MongoDB):

  • Users collection (with embedded recent orders)
  • Products collection
  • Orders collection (for history/analytics)

3. Hybrid Approach

  • Keep SQL for complex analytics
  • Use MongoDB for application data
  • Sync between systems

Tools

Use our tools:

Conclusion

Migrating SQL to MongoDB:

Key differences:

  • Tables → Collections
  • Rows → Documents
  • JOINs → Embedded or $lookup
  • SQL → MongoDB query language

Considerations:

  • Denormalize for performance
  • Embed vs reference
  • Schema flexibility
  • Query patterns

Next Steps