PostgreSQL & MySQL

1. Why Databases Matter

Every real application needs to persist data. You could save JSON files, but that breaks down fast -- no concurrent access, no queries, no integrity guarantees. That's why relational databases exist.

RDBMS vs NoSQL

PostgreSQL vs MySQL

2. PostgreSQL Setup & CLI

Installation

Bash
# Ubuntu / WSL
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Mac (Homebrew)
brew install postgresql@16
brew services start postgresql@16

# Check version
psql --version

First Connection

Bash
# Switch to postgres user and open psql
sudo -u postgres psql

# Create your own user + database
CREATE USER sean WITH PASSWORD 'yourpassword' CREATEDB;
CREATE DATABASE myapp OWNER sean;
\q

# Now connect as yourself
psql -U sean -d myapp

Essential psql Commands

psql
\l              -- list all databases
\c dbname       -- connect to database
\dt             -- list tables
\d tablename    -- describe table (columns, types, constraints)
\di             -- list indexes
\du             -- list users/roles
\x              -- toggle expanded output (vertical display)
\timing         -- toggle query timing
\i file.sql     -- execute SQL file
\e              -- open editor for query
\q              -- quit

3. MySQL Setup & CLI

Installation

Bash
# Ubuntu / WSL
sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql_secure_installation

# Mac
brew install mysql
brew services start mysql

First Connection

Bash
# Connect as root
sudo mysql

# Create user + database
CREATE USER 'sean'@'localhost' IDENTIFIED BY 'yourpassword';
CREATE DATABASE myapp;
GRANT ALL PRIVILEGES ON myapp.* TO 'sean'@'localhost';
FLUSH PRIVILEGES;
EXIT;

# Connect as yourself
mysql -u sean -p myapp

Essential mysql Commands

MySQL
SHOW DATABASES;           -- list databases
USE dbname;               -- switch database
SHOW TABLES;              -- list tables
DESCRIBE tablename;       -- show table structure
SHOW INDEX FROM tablename;-- list indexes
SHOW PROCESSLIST;         -- active connections
SOURCE file.sql;          -- execute SQL file
EXIT;                     -- quit

4. SQL Fundamentals

CREATE TABLE

SQL
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,        -- auto-incrementing integer
  email       VARCHAR(255) UNIQUE NOT NULL,
  username    VARCHAR(50) NOT NULL,
  password_hash TEXT NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW(),
  is_active   BOOLEAN DEFAULT true
);

CREATE TABLE posts (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       VARCHAR(200) NOT NULL,
  body        TEXT,
  published   BOOLEAN DEFAULT false,
  created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE comments (
  id          SERIAL PRIMARY KEY,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  body        TEXT NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW()
);

INSERT

SQL
INSERT INTO users (email, username, password_hash)
VALUES ('sean@example.com', 'sean', '$2b$10$hash...');

-- Insert multiple rows
INSERT INTO users (email, username, password_hash) VALUES
  ('alice@example.com', 'alice', '$2b$10$hash1'),
  ('bob@example.com', 'bob', '$2b$10$hash2');

-- Insert and return the created row
INSERT INTO posts (user_id, title, body)
VALUES (1, 'My First Post', 'Hello world!')
RETURNING id, title, created_at;

SELECT

SQL
-- Basic query
SELECT * FROM users;

-- Specific columns with filter
SELECT id, username, email
FROM users
WHERE is_active = true
ORDER BY created_at DESC
LIMIT 10;

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- IN clause
SELECT * FROM users WHERE id IN (1, 2, 3);

-- NULL checks
SELECT * FROM posts WHERE body IS NOT NULL;

UPDATE & DELETE

SQL
-- Update with WHERE (always use WHERE!)
UPDATE users
SET username = 'seandev', is_active = false
WHERE id = 1
RETURNING *;

-- Delete specific rows
DELETE FROM comments WHERE id = 5;

-- Delete with subquery
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE is_active = false
);

5. Joins

Joins combine rows from two or more tables based on a related column. This is the core power of relational databases.

INNER JOIN

Returns only rows that have matching values in both tables.

SQL
-- Get all posts with their author's username
SELECT p.id, p.title, u.username, p.created_at
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE p.published = true
ORDER BY p.created_at DESC;

  users                posts
  ┌──┬───────┐         ┌──┬─────────┬─────────┐
  │id│username│         │id│ title   │ user_id │
  ├──┼───────┤         ├──┼─────────┼─────────┤
  │ 1│ sean  │◄────────│ 1│ Hello   │    1    │
  │ 2│ alice │◄────────│ 2│ World   │    2    │
  │ 3│ bob   │  (no)   │ 3│ Bye     │    1    │
  └──┴───────┘         └──┴─────────┴─────────┘

  Result: posts 1, 2, 3 with their usernames
  (bob has no posts -- not in result)

LEFT JOIN

Returns ALL rows from the left table, plus matching rows from the right. If no match, right side is NULL.

SQL
-- All users, even those with no posts
SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.username
ORDER BY post_count DESC;

-- Find users who have never posted
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;

RIGHT JOIN & FULL OUTER JOIN

SQL
-- RIGHT JOIN: all posts, even if user was deleted (rare in practice)
SELECT p.title, u.username
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

-- FULL OUTER JOIN: all rows from both tables
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

Self Join

SQL
-- Employees table with manager_id referencing same table
CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100),
  manager_id  INTEGER REFERENCES employees(id)
);

-- Get employee name with their manager's name
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Multi-Table Join

SQL
-- Posts with author name and comment count
SELECT
  p.id,
  p.title,
  u.username AS author,
  COUNT(c.id) AS comment_count
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = true
GROUP BY p.id, p.title, u.username
ORDER BY comment_count DESC;

6. Aggregation & Window Functions

GROUP BY & Aggregate Functions

SQL
-- Posts per user
SELECT user_id, COUNT(*) AS total_posts
FROM posts
GROUP BY user_id;

-- Average, min, max
SELECT
  COUNT(*) AS total,
  AVG(price) AS avg_price,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  SUM(price) AS revenue
FROM orders
WHERE status = 'completed';

-- HAVING filters groups (WHERE filters rows)
SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY post_count DESC;

Window Functions

Window functions perform calculations across rows related to the current row -- without collapsing them like GROUP BY.

SQL
-- ROW_NUMBER: assign sequential numbers
SELECT
  username,
  email,
  ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

-- RANK: rank with gaps for ties
SELECT
  username,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM leaderboard;

-- DENSE_RANK: rank without gaps
SELECT
  username,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM leaderboard;

-- LAG / LEAD: access previous/next row
SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY date) AS change,
  LEAD(revenue) OVER (ORDER BY date) AS next_day
FROM daily_revenue;

-- Running total with SUM window
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Partition: window per group
SELECT
  department,
  employee,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

7. Database Design & Normalization

Normal Forms

Bad Design (Un-normalized)

SQL
-- DON'T: everything in one table
CREATE TABLE orders_bad (
  order_id    INTEGER,
  customer_name VARCHAR(100),
  customer_email VARCHAR(255),  -- duplicated per order!
  product_name  VARCHAR(200),
  product_price DECIMAL,          -- duplicated per order!
  quantity    INTEGER
);

Good Design (Normalized)

SQL
CREATE TABLE customers (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  order_id   INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  price_at_time DECIMAL(10,2) NOT NULL  -- snapshot price at order time
);

Entity Relationships

SQL
-- Many-to-Many: tags on posts
CREATE TABLE tags (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  INTEGER REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)  -- composite PK prevents duplicates
);

8. Indexes & Query Performance

Without indexes, the database scans every row (sequential scan). Indexes are like a book's index -- they let the DB jump directly to matching rows.

Creating Indexes

SQL
-- B-tree index (default, most common)
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_posts_user_published
  ON posts(user_id, published);

-- Partial index (only index a subset of rows)
CREATE INDEX idx_active_users
  ON users(email) WHERE is_active = true;

-- GIN index for full-text search / JSONB
CREATE INDEX idx_posts_body_search
  ON posts USING GIN(to_tsvector('english', body));

EXPLAIN ANALYZE

See exactly how the database executes your query:

SQL
EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 1;

-- Output tells you:
--   Seq Scan = full table scan (slow)
--   Index Scan = used an index (fast)
--   Bitmap Index Scan = used index for many rows
--   actual time = real execution time
--   rows = actual rows returned

9. Transactions & ACID

Basic Transaction

SQL
-- Transfer money between accounts
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Check constraint: balance >= 0
-- If any statement fails, both are rolled back

COMMIT;
-- or ROLLBACK; to cancel

Isolation Levels

SQL
-- Set isolation level
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... your queries ...
COMMIT;

-- Row-level locking (SELECT FOR UPDATE)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- This row is now locked until COMMIT/ROLLBACK
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

10. Views, CTEs & Subqueries

Views

SQL
-- Create a view: a saved query that acts like a table
CREATE VIEW published_posts AS
SELECT p.id, p.title, u.username, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.published = true;

-- Use it like a table
SELECT * FROM published_posts WHERE username = 'sean';

-- Materialized view: cached result, must be refreshed
CREATE MATERIALIZED VIEW user_stats AS
SELECT u.id, u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

REFRESH MATERIALIZED VIEW user_stats;

Common Table Expressions (CTEs)

SQL
-- CTE: temporary named result set
WITH active_authors AS (
  SELECT user_id, COUNT(*) AS post_count
  FROM posts
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
  HAVING COUNT(*) >= 3
)
SELECT u.username, aa.post_count
FROM active_authors aa
JOIN users u ON aa.user_id = u.id
ORDER BY aa.post_count DESC;

-- Recursive CTE: traverse hierarchies
WITH RECURSIVE org_chart AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees under each manager
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', depth) || name AS org_tree
FROM org_chart
ORDER BY depth, name;

11. PostgreSQL-Specific Features

JSONB

PostgreSQL's JSONB type lets you store and query JSON data with indexes. It's like having MongoDB inside PostgreSQL.

SQL
CREATE TABLE events (
  id   SERIAL PRIMARY KEY,
  type VARCHAR(50),
  data JSONB NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO events (type, data) VALUES
('click', '{"page": "/home", "x": 120, "y": 450, "user_agent": "Chrome"}'),
('purchase', '{"product_id": 42, "amount": 29.99, "currency": "USD"}');

-- Access JSON fields
SELECT data->'page' AS page           -- returns JSON: "/home"
FROM events WHERE type = 'click';

SELECT data->>'page' AS page          -- returns text: /home
FROM events WHERE type = 'click';

-- Nested access
SELECT data->'address'->>'city' FROM customers;

-- Filter by JSON field
SELECT * FROM events
WHERE data->>'currency' = 'USD'
  AND (data->>'amount')::numeric > 20;

-- Contains operator @>
SELECT * FROM events
WHERE data @> '{"currency": "USD"}';

-- Index JSONB for fast queries
CREATE INDEX idx_events_data ON events USING GIN(data);

Arrays

SQL
CREATE TABLE articles (
  id   SERIAL PRIMARY KEY,
  title TEXT,
  tags  TEXT[] DEFAULT '{}'
);

INSERT INTO articles (title, tags) VALUES
('Learn SQL', ARRAY['sql', 'database', 'tutorial']);

-- Check if array contains value
SELECT * FROM articles WHERE 'sql' = ANY(tags);

-- Array overlap
SELECT * FROM articles WHERE tags && ARRAY['sql', 'go'];

-- Unnest: expand array into rows
SELECT id, unnest(tags) AS tag FROM articles;

Full-Text Search

SQL
-- Basic full-text search
SELECT title, body
FROM posts
WHERE to_tsvector('english', title || ' ' || body)
  @@ to_tsquery('english', 'database & design');

-- Add a generated tsvector column for performance
ALTER TABLE posts ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

12. MySQL-Specific Features

Storage Engines

SQL
-- Check engine of a table
SHOW TABLE STATUS LIKE 'users';

-- Create with specific engine
CREATE TABLE logs (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  msg  TEXT
) ENGINE=InnoDB;

-- MySQL JSON support
CREATE TABLE configs (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100),
  settings JSON
);

INSERT INTO configs (name, settings) VALUES
('app', '{"theme": "dark", "lang": "en", "notifications": true}');

-- Access JSON (MySQL uses -> and ->>)
SELECT name, settings->>'$.theme' AS theme FROM configs;

-- MySQL AUTO_INCREMENT vs PostgreSQL SERIAL
-- MySQL: INT AUTO_INCREMENT
-- PostgreSQL: SERIAL (or GENERATED ALWAYS AS IDENTITY)

MySQL Replication Basics

SQL
-- Check binary logging (needed for replication)
SHOW VARIABLES LIKE 'log_bin';

-- On source server: create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- On replica: configure and start
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.1.100',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'password',
  SOURCE_LOG_FILE = 'mysql-bin.000001',
  SOURCE_LOG_POS = 0;

START REPLICA;

13. Connecting from Node.js

PostgreSQL with pg

JavaScript
// npm install pg
import pg from 'pg';

// Connection pool (recommended for production)
const pool = new pg.Pool({
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  user: 'sean',
  password: process.env.DB_PASSWORD,
  max: 20,              // max connections in pool
  idleTimeoutMillis: 30000,
});

// Simple query
const result = await pool.query('SELECT * FROM users WHERE id = $1', [1]);
console.log(result.rows[0]);

// Parameterized queries prevent SQL injection
const { rows } = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND is_active = $2',
  [email, true]
);

// Insert and return
const { rows: [newPost] } = await pool.query(
  'INSERT INTO posts (user_id, title, body) VALUES ($1, $2, $3) RETURNING *',
  [userId, title, body]
);

// Transaction
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, fromId]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, toId]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

MySQL with mysql2

JavaScript
// npm install mysql2
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'sean',
  password: process.env.DB_PASSWORD,
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
});

// MySQL uses ? for parameters (not $1, $2)
const [rows] = await pool.execute(
  'SELECT * FROM users WHERE email = ? AND is_active = ?',
  [email, true]
);

// Insert
const [result] = await pool.execute(
  'INSERT INTO posts (user_id, title) VALUES (?, ?)',
  [userId, title]
);
console.log(result.insertId);

ORMs: Prisma & Drizzle

JavaScript
// Prisma -- schema-first ORM
// prisma/schema.prisma defines your models
// Then: npx prisma generate && npx prisma migrate dev

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Find users with posts
const users = await prisma.user.findMany({
  where: { isActive: true },
  include: { posts: true },
  orderBy: { createdAt: 'desc' },
  take: 10,
});

// Create with relations
const user = await prisma.user.create({
  data: {
    email: 'sean@dev.com',
    username: 'sean',
    posts: {
      create: [{ title: 'First Post', body: 'Hello!' }],
    },
  },
  include: { posts: true },
});

// Transaction in Prisma
await prisma.$transaction([
  prisma.account.update({ where: { id: fromId }, data: { balance: { decrement: 100 } } }),
  prisma.account.update({ where: { id: toId }, data: { balance: { increment: 100 } } }),
]);

JavaScript
// Drizzle -- SQL-like TypeScript ORM
// npm install drizzle-orm pg

import { drizzle } from 'drizzle-orm/node-postgres';
import { pgTable, serial, text, boolean, timestamp } from 'drizzle-orm/pg-core';
import { eq } from 'drizzle-orm';

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  username: text('username').notNull(),
  isActive: boolean('is_active').default(true),
});

const db = drizzle(pool);

// Select
const activeUsers = await db.select().from(users).where(eq(users.isActive, true));

// Insert
await db.insert(users).values({ email: 'sean@dev.com', username: 'sean' });

14. Best Practices

Migrations

Backups

Bash
# PostgreSQL backup
pg_dump -U sean myapp > backup_2024_01_15.sql
pg_dump -U sean -Fc myapp > backup.dump  # compressed format

# Restore
psql -U sean myapp < backup_2024_01_15.sql
pg_restore -U sean -d myapp backup.dump

# MySQL backup
mysqldump -u sean -p myapp > backup.sql
mysql -u sean -p myapp < backup.sql

# Automate with cron (daily at 2am)
# 0 2 * * * pg_dump -U sean myapp | gzip > /backups/myapp_$(date +\%Y\%m\%d).sql.gz

Security Checklist

Query Optimization Tips