SQL vs NoSQL Databases

The Two Major Database Families

When you build a backend application, one of the earliest architectural decisions is: which database should I use? The field is broadly split into two families — SQL (Structured Query Language) databases and NoSQL (Not Only SQL) databases. Understanding the differences will help you make the right choice for each project.

SQL Databases

SQL databases are also called relational databases. They store data in tables made up of rows and columns. Every table has a fixed schema — a strict definition of what columns exist and what type of data each column can hold.

Popular SQL databases: PostgreSQL, MySQL, SQLite, Microsoft SQL Server

Defining a SQL Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  title VARCHAR(200) NOT NULL,
  body TEXT,
  published_at TIMESTAMP
);

The user_id column in posts is a foreign key that links each post to its author in the users table. This relationship is enforced by the database — you cannot insert a post with a user_id that does not exist.

ACID Compliance

SQL databases are built around ACID guarantees:

• Atomicity — a transaction either fully completes or fully rolls back. No partial saves.
• Consistency — the database always remains in a valid state.
• Isolation — concurrent transactions do not interfere with each other.
• Durability — once committed, data survives crashes.

These properties make SQL databases the gold standard for financial systems, e-commerce orders, and any situation where data accuracy is non-negotiable.

Querying SQL

-- Get all posts by a specific user
SELECT posts.title, posts.published_at
FROM posts
JOIN users ON posts.user_id = users.id
WHERE users.email = 'amaka@example.com'
ORDER BY posts.published_at DESC;

SQL is an extremely expressive language that lets you join multiple tables, aggregate data, and filter with great precision.

NoSQL Databases

NoSQL databases abandon the rigid table structure. Instead of rows, they store documents, key-value pairs, graphs, or wide-column data depending on the type.

The most common NoSQL type is the document database. Popular examples: MongoDB, Firestore, CouchDB.

A MongoDB Document

Instead of a table row, MongoDB stores a JSON-like document inside a collection:

{
  "_id": "64abc9f3",
  "name": "Amaka",
  "email": "amaka@example.com",
  "posts": [
    { "title": "My First Post", "publishedAt": "2024-01-10" },
    { "title": "Learning MongoDB", "publishedAt": "2024-02-15" }
  ],
  "preferences": {
    "theme": "dark",
    "notifications": true
  },
  "createdAt": "2024-01-01"
}

Notice that the user's posts are embedded directly in the user document. There is no separate posts table to join. This makes reads very fast — one query returns everything you need.

Schema Flexibility

NoSQL documents in the same collection do not need identical fields:

// Document 1
{ "_id": "1", "name": "Amaka", "bio": "Developer" }

// Document 2 — different shape, perfectly valid
{ "_id": "2", "name": "Chukwu", "age": 28, "skills": ["Node.js", "MongoDB"] }

This flexibility is powerful during rapid development when your data model is still evolving.

Horizontal Scaling

SQL databases traditionally scale vertically — you make the server bigger (more RAM, faster CPU). MongoDB is designed to scale horizontally — you add more servers and distribute data across them through a process called sharding. This makes NoSQL well-suited for applications handling massive amounts of data.

SQL vs NoSQL Side by Side

When to Choose SQL

Choose SQL when:

• Data integrity and consistency are critical (payments, banking, medical records)
• You have well-defined relationships between entities
• You need complex reporting queries across multiple tables
• Your team already knows SQL

Real example: Flutterwave processes billions of naira in transactions. Their core payment ledger uses PostgreSQL because every credit and debit must balance perfectly — ACID compliance is essential.

When to Choose NoSQL

Choose NoSQL when:

• Your data structure is flexible or evolving rapidly
• You need to store large amounts of unstructured data
• Horizontal scaling will be needed
• Your reads are simple and embedding data makes sense

Real example: A social media startup building user profiles, posts, and reactions would benefit from MongoDB because the data structure is document-shaped and the schema changes frequently during early product development.

Practice Exercise

Compare the two approaches by modelling the same data both ways:

1. Design a SQL schema for a blog with users, posts, and comments — define the tables and foreign key relationships
2. Model the same blog in MongoDB — decide what to embed versus what to reference as a separate document
3. Write a SQL SELECT query to fetch all comments on a post by a specific user
4. Write the equivalent MongoDB query using the JavaScript driver syntax
5. Reflect on which felt more natural for this use case and why