Lecture: SQL vs. NoSQL Databases in Full-Stack Development

Introduction: Why Databases Matter

Hey everyone, welcome to today’s lecture! Today, we’re diving into the world of databases—specifically SQL and NoSQL—and figuring out when to use each one in your full-stack projects. Databases are the backbone of any app that needs to store and manage data, like the blogging application we’ve been building throughout this course. We're creating a platform where users can read posts, filter them by date or tags, and an admin can add or edit posts. How do you store all that data efficiently? That’s where databases come in, and choosing the right one can make or break your app.

Why does this matter to you? In the real world, picking the wrong database can lead to slow performance, messy code, or even a complete rewrite later. By the end of this lecture, you’ll know how to decide between SQL and NoSQL and see them in action with TypeScript in a Next.js app. Let’s get started with a relatable scenario: building our blogging app step-by-step.

Section 1: Busting Misconceptions—SQL vs. NoSQL Isn’t What You Think

Let’s kick things off with a surprise. You might think SQL databases (like PostgreSQL) are old-school and rigid, while NoSQL databases (like MongoDB) are modern and flexible. Not quite! SQL databases can handle flexible queries, and NoSQL databases can be structured too. The real difference isn’t about "old vs. new"—it’s about how they organise and retrieve data.

• SQL Databases: These are relational, meaning data lives in tables with rows and columns, like a spreadsheet. Think of it as a filing cabinet with labelled folders—everything’s organised and connected.
• NoSQL Databases: These are non-relational. Data can be stored as key-value pairs, documents, or graphs—like a big, messy backpack where you can toss stuff in any way you want.

For our blogging app, SQL might seem perfect for neatly storing posts and tags, but NoSQL could work too if we want flexibility with unstructured data. Let’s explore both by building our app’s database.

Section 2: SQL Databases—Structured Power

What They Are

SQL databases use a schema—predefined rules about what data looks like. Imagine our blogging app: we’d have a posts table with columns like id, title, content, and date, and a tags table linked to it. Relationships are king here.

How They Work

You query SQL databases with Structured Query Language (SQL). Want all posts from 2025? You’d write:

SELECT * FROM posts WHERE date >= '2025-01-01';

Example in Our Blogging App

Let’s set up a simple SQL database for our app using PostgreSQL. First, we define our tables:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  date DATE NOT NULL
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE post_tags (
  post_id INT REFERENCES posts(id),
  tag_id INT REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

Now, in a Next.js app with TypeScript, we’d use a library like pg to query it. Here’s how to fetch posts by tag:

import { Pool } from 'pg';

const pool = new Pool({
  user: 'your_user',
  host: 'localhost',
  database: 'blog_db',
  password: 'your_password',
  port: 5432,
});

async function getPostsByTag(tag: string): Promise<{ id: number; title: string; content: string; date: string }[]> {
  const result = await pool.query(`
    SELECT p.id, p.title, p.content, p.date
    FROM posts p
    JOIN post_tags pt ON p.id = pt.post_id
    JOIN tags t ON t.id = pt.tag_id
    WHERE t.name = $1
  `, [tag]);
  return result.rows;
}

Advantages

• Great for structured data with clear relationships (e.g., posts and tags).
• Powerful queries for filtering and joining data.
• Ensures data integrity with schemas.

Disadvantages

• Scaling horizontally (adding more servers) is tricky.
• Less flexible if your data structure changes often.

Section 3: NoSQL Databases—Flexible Freedom

What They Are

NoSQL databases ditch tables for more dynamic structures. In MongoDB (a popular choice), data is stored as JSON-like documents. For our blogging app, a post might look like this:

{
  "_id": "123",
  "title": "My First Post",
  "content": "Hello, world!",
  "date": "2025-02-24",
  "tags": ["tech", "intro"]
}

How They Work

You query NoSQL with APIs or query languages specific to the database. In MongoDB, to find posts by tag:

db.posts.find({ tags: "tech" });

Example in Our Blogging App

Let’s use MongoDB in our Next.js app with TypeScript and the mongodb library:

import { MongoClient } from 'mongodb';

const uri = 'mongodb://localhost:27017';
const client = new MongoClient(uri);

interface Post {
  _id: string;
  title: string;
  content: string;
  date: string;
  tags: string[];
}

async function getPostsByTag(tag: string): Promise<Post[]> {
  await client.connect();
  const db = client.db('blog_db');
  const posts = await db.collection('posts').find({ tags: tag }).toArray();
  return posts as Post[];
}

Advantages

• Super flexible—add fields like tags without changing a schema.
• Scales horizontally easily by adding more servers.
• Perfect for unstructured or rapidly changing data.

Disadvantages

• No built-in relationships; you manage them manually.
• Queries can be less intuitive for complex filtering.

Section 4: When to Use Which—Decision Time

So, SQL or NoSQL for our blogging app? Let’s break it down with our example.

Use SQL When:

• Your data is structured: Posts, tags, and their relationships are predictable.
• You need complex queries: Filtering posts by date and tag with joins is straightforward.
• Data integrity matters: Schemas ensure no junk data slips in.

In our app, if the admin needs detailed reports (e.g., "posts per tag per month"), SQL shines. Example query:

SELECT t.name, COUNT(p.id) as post_count
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE p.date >= '2025-01-01'
GROUP BY t.name;

Use NoSQL When:

• Your data evolves: Maybe later, we add comments or images to posts—no schema changes needed.
• You need speed and scale: If our blog goes viral, NoSQL handles millions of users better.
• Simplicity over complexity: Storing tags inside the post document avoids joins.

For a quick prototype or a small blog with flexible post formats, NoSQL gets us up and running fast.

Advanced Example: Mixing Both

Big apps often use both! Store user accounts and post metadata in SQL for structure, and user-generated content (like comments) in NoSQL for flexibility. In our app, we could keep posts in PostgreSQL and a comments collection in MongoDB.

Conclusion: Key Takeaways

Alright, let’s wrap up! Today, we built a blogging app step-by-step to explore SQL and NoSQL databases. Here’s what to remember:

• SQL (e.g., PostgreSQL): Use it for structured data, complex queries, and relationships. It’s your go-to for our app’s post-filtering routes.
• NoSQL (e.g., MongoDB): Pick it for flexibility, scalability, and evolving data. Great if our blog’s features keep changing.
• Start Querying: With TypeScript in Next.js, libraries like pg and mongodb make connecting and fetching data easy.

Best Practices: Define your app’s needs first—structure vs. scale. Test queries early to avoid surprises. Pitfalls: Don’t overcomplicate SQL with too many joins, and don’t let NoSQL turn into a chaotic mess with no plan.

Next time, we’ll dive deeper into optimising these queries. For now, play with both in your projects—see what clicks for your blogging app! Any questions? Let’s chat!