MERN Course: MySQL Essentials

Introduction

SQL skills complement MongoDB in full-stack careers. This guide completes the MySQL Essentials syllabus with schema design, safe queries, and ORM patterns for Node.js APIs.

Course Reference

Quick index

Week 19: MySQL Foundations

1. Diving into the World of Databases

Relational databases store data in tables with strict schemas. MySQL excels at structured data, transactions, and complex joins — common in enterprise MERN+SQL stacks.

• Tables = rows + columns with defined types
• Primary keys uniquely identify rows
• SQL is the standard query language for relational DBs

Example:

CREATE DATABASE mern_course;
USE mern_course;

Back to index

2. Database Design Fundamentals

Normalize to reduce duplication. Identify entities (User, Post, Comment), attributes, and relationships (1:N, N:M).

Example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  body TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Back to index

3. SQL Fundamentals (SELECT)

SELECT retrieves data. WHERE filters, ORDER BY sorts, LIMIT paginates. Always parameterize queries — never string-concatenate user input.

Example:

SELECT id, title, slug, created_at
FROM posts
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

Back to index

Week 20: CRUD, Integrity & Joins

4. Data Manipulation (INSERT, UPDATE, DELETE)

INSERT adds rows, UPDATE modifies, DELETE removes. Wrap multi-step changes in transactions.

Example:

START TRANSACTION;
 
INSERT INTO posts (user_id, title, slug, body) VALUES (1, 'MERN Guide', 'mern-guide', '...');
 
UPDATE users SET name = 'Kazi R.' WHERE id = 1;
 
DELETE FROM posts WHERE slug = 'draft-post';
 
COMMIT;

Back to index

5. Constraints and Data Integrity

NOT NULL, UNIQUE, CHECK, DEFAULT, and FOREIGN KEY enforce rules at the database layer — not just in app code.

Example:

ALTER TABLE posts
  ADD CONSTRAINT chk_title_length CHECK (CHAR_LENGTH(title) >= 3);
 
ALTER TABLE users
  ADD COLUMN status ENUM('active', 'suspended') NOT NULL DEFAULT 'active';

Back to index

6. Joins and Subqueries

INNER JOIN returns matching rows only. LEFT JOIN keeps all left rows with NULLs where no match. Subqueries nest SELECT inside WHERE/FROM.

Example:

SELECT p.title, p.slug, u.name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.created_at >= '2026-01-01'
ORDER BY p.created_at DESC;
 
-- Subquery: users with more than 5 posts
SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM posts GROUP BY user_id HAVING COUNT(*) > 5
);

Back to index

Week 21: Performance, ORMs & Project

7. Indexing and Transactions

B-tree indexes accelerate WHERE/JOIN/ORDER BY. Transactions guarantee ACID — all steps succeed or none do.

Example:

CREATE INDEX idx_posts_user_created ON posts (user_id, created_at DESC);
 
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Back to index

8. ORMs: Sequelize or Prisma

ORMs map tables to models in TypeScript. Prisma offers type-safe queries and migrations; Sequelize is mature and flexible.

Example:

// Prisma schema excerpt
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  slug      String   @unique
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}
 
// Query
const posts = await prisma.post.findMany({
  where: { authorId: 1 },
  select: { title: true, slug: true },
  orderBy: { createdAt: 'desc' },
  take: 10,
})

Back to index

9. Final MySQL Project

Build a library, todo, or movie app with normalized schema, CRUD API via Express + Prisma/Sequelize, and pagination.

• Draw ERD before writing SQL
• Seed database with realistic sample data
• Add integration tests against a test DB container

Back to index