PostgreSQL Fundamentals

brew install postgresql@16
brew services start postgresql@16

# Start a PostgreSQL container
docker run --name postgres-dev \
    -e POSTGRES_PASSWORD=password \
    -e POSTGRES_DB=myapp \
    -p 5432:5432 \
    -d postgres:16

psql -h localhost -U postgres -d myapp
# or with docker:
docker exec -it postgres-dev psql -U postgres -d myapp

-- Create a users table
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) UNIQUE NOT NULL,
    name        VARCHAR(255) NOT NULL,
    password    VARCHAR(255) NOT NULL,
    role        VARCHAR(50) DEFAULT 'user',
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW()
);

-- Create a courses table
CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    slug        VARCHAR(255) UNIQUE NOT NULL,
    title       VARCHAR(500) NOT NULL,
    description TEXT,
    price       DECIMAL(10, 2) DEFAULT 0,
    published   BOOLEAN DEFAULT false,
    author_id   INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at  TIMESTAMP DEFAULT NOW()
);

-- Create an enrollments junction table (many-to-many)
CREATE TABLE enrollments (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER REFERENCES users(id) ON DELETE CASCADE,
    course_id   INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT NOW(),
    completed   BOOLEAN DEFAULT false,
    UNIQUE(user_id, course_id)    -- prevent duplicate enrollments
);

-- Insert a single row
INSERT INTO users (email, name, password)
VALUES ('alice@example.com', 'Alice Johnson', 'hashed_password_here');

-- Insert and return the created record
INSERT INTO users (email, name, password)
VALUES ('bob@example.com', 'Bob Smith', 'hashed_password')
RETURNING id, email, name, created_at;

-- Insert multiple rows
INSERT INTO courses (slug, title, price) VALUES
    ('react-complete', 'React Complete Course', 49.00),
    ('python-2026', 'Python Complete 2026', 39.00),
    ('javascript', 'JavaScript Complete', 44.00);

-- Get all users
SELECT * FROM users;

-- Select specific columns
SELECT id, email, name, created_at FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE role = 'admin';
SELECT * FROM courses WHERE published = true AND price < 50;

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM courses WHERE title ILIKE '%react%';  -- case insensitive

-- NULL checks
SELECT * FROM courses WHERE author_id IS NOT NULL;

-- Sort
SELECT * FROM courses ORDER BY price ASC, created_at DESC;

-- Limit and offset (pagination)
SELECT * FROM courses ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT AVG(price), MIN(price), MAX(price) FROM courses;
SELECT role, COUNT(*) AS count FROM users GROUP BY role;

-- Update a specific row
UPDATE users SET name = 'Alice Williams' WHERE id = 1;

-- Update with RETURNING to get the updated record
UPDATE courses
SET published = true, updated_at = NOW()
WHERE id = 5
RETURNING *;

-- Update based on a condition
UPDATE enrollments SET completed = true WHERE user_id = 1 AND course_id = 3;

-- Delete a row
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM enrollments WHERE enrolled_at < NOW() - INTERVAL '1 year';

-- Delete and return deleted rows
DELETE FROM courses WHERE published = false RETURNING id, title;

-- INNER JOIN — only rows matching in both tables
SELECT 
    u.name,
    u.email,
    c.title AS course_title,
    e.enrolled_at
FROM enrollments e
INNER JOIN users u ON e.user_id = u.id
INNER JOIN courses c ON e.course_id = c.id
ORDER BY e.enrolled_at DESC;

-- LEFT JOIN — all users, even those with no enrollments
SELECT 
    u.name,
    COUNT(e.id) AS enrollment_count
FROM users u
LEFT JOIN enrollments e ON u.id = e.user_id
GROUP BY u.id, u.name
ORDER BY enrollment_count DESC;

-- Get a course with its author details
SELECT 
    c.title,
    c.price,
    c.slug,
    u.name AS author_name,
    u.email AS author_email
FROM courses c
LEFT JOIN users u ON c.author_id = u.id
WHERE c.slug = 'react-complete';

-- Subquery: users who have at least one enrollment
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM enrollments);

-- CTE (Common Table Expression) — cleaner complex queries
WITH active_users AS (
    SELECT DISTINCT user_id FROM enrollments
    WHERE enrolled_at > NOW() - INTERVAL '30 days'
),
course_stats AS (
    SELECT course_id, COUNT(*) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT 
    c.title,
    cs.student_count
FROM courses c
JOIN course_stats cs ON c.id = cs.course_id
WHERE cs.student_count > 10
ORDER BY cs.student_count DESC;

-- Index on a frequently searched column
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_courses_slug ON courses(slug);
CREATE INDEX idx_enrollments_user_id ON enrollments(user_id);
CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);

-- Composite index for queries that filter on multiple columns
CREATE INDEX idx_courses_published_price ON courses(published, price);

-- See query plan (use EXPLAIN ANALYZE before a slow query)
EXPLAIN ANALYZE SELECT * FROM courses WHERE slug = 'react-complete';

BEGIN;

INSERT INTO enrollments (user_id, course_id) VALUES (1, 5);
UPDATE users SET enrollment_count = enrollment_count + 1 WHERE id = 1;
UPDATE courses SET student_count = student_count + 1 WHERE id = 5;

COMMIT;

-- If anything goes wrong:
ROLLBACK;

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function enrollUser(userId: number, courseId: number) {
    const client = await pool.connect();
    
    try {
        await client.query("BEGIN");
        
        await client.query(
            "INSERT INTO enrollments (user_id, course_id) VALUES ($1, $2)",
            [userId, courseId]
        );
        
        await client.query(
            "UPDATE users SET enrollment_count = enrollment_count + 1 WHERE id = $1",
            [userId]
        );
        
        await client.query("COMMIT");
    } catch (error) {
        await client.query("ROLLBACK");
        throw error;
    } finally {
        client.release();
    }
}

-- Create a table with a JSON column
CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(255),
    metadata JSONB    -- JSONB is binary JSON, faster and indexable
);

-- Insert with JSON
INSERT INTO products (name, metadata)
VALUES ('React Course', '{"level": "beginner", "tags": ["react", "hooks"], "rating": 4.8}');

-- Query inside JSON
SELECT name, metadata->>'level' AS level FROM products;
SELECT * FROM products WHERE metadata->>'level' = 'beginner';
SELECT * FROM products WHERE metadata @> '{"tags": ["react"]}';

npm install pg
npm install -D @types/pg

import { Pool } from "pg";

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    // or
    host: process.env.DB_HOST,
    port: 5432,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    max: 20,   // connection pool size
});

// Query with parameters (always use parameterized queries to prevent SQL injection)
const result = await pool.query(
    "SELECT * FROM courses WHERE slug = $1",
    [slug]
);

const course = result.rows[0];

// Always use $1, $2, $3 placeholders — never string interpolation
// ❌ NEVER: `SELECT * FROM courses WHERE slug = '${slug}'`  — SQL injection!
// ✅ ALWAYS: "SELECT * FROM courses WHERE slug = $1", [slug]