PostgreSQL CRUD Operations¶

-- Basic select all (please don't do this in production with large tables)
SELECT * FROM app.users;

-- Select specific columns - always prefer this over SELECT *
SELECT id, email, username, created_at FROM app.users;

-- Filtering with WHERE
SELECT id, email, username, role
FROM app.users
WHERE is_active = TRUE
  AND created_at >= '2024-01-01'
  AND role IN ('admin', 'moderator');

-- ORDER BY and LIMIT - pagination basics
SELECT id, email, username
FROM app.users
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- OFFSET-based pagination gets slow at high offsets
-- Use cursor-based pagination for production at scale

-- LIKE and pattern matching (case-sensitive)
SELECT * FROM app.users
WHERE email LIKE '%@example.com';

-- ILIKE - case-insensitive LIKE
SELECT * FROM app.users
WHERE username ILIKE 'admin%';  -- Matches admin, Admin, ADMIN

-- DISTINCT - unique values only
SELECT DISTINCT role FROM app.users;

-- Single row insert with RETURNING (saves a second query)
INSERT INTO app.users (email, username, password_hash, role)
VALUES ('omar@example.com', 'omar_hacker', '$2b$12$LJ3m...', 'user')
RETURNING id, created_at;

-- Multiple rows in one statement (much faster than individual inserts)
INSERT INTO app.users (email, username, password_hash, role)
VALUES
    ('ali@example.com', 'ali_sec', '$2b$12$ABC...', 'user'),
    ('khaled@example.com', 'khaled_pwn', '$2b$12$DEF...', 'admin'),
    ('mohsen@example.com', 'mohsen_dev', '$2b$12$GHI...', 'user')
RETURNING id, email;

-- Insert from SELECT - copy data between tables
INSERT INTO app.archived_users (user_id, email, archived_at)
SELECT id, email, NOW()
FROM app.users
WHERE is_active = FALSE
  AND updated_at < NOW() - INTERVAL '1 year';

-- Basic update
UPDATE app.users
SET last_login = NOW()
WHERE id = '123e4567-e89b-12d3-a456-426614174000';

-- Update multiple columns
UPDATE app.users
SET
    username = 'new_handle',
    updated_at = NOW()
WHERE id = '123e4567-e89b-12d3-a456-426614174000';

-- Conditional update with CASE
UPDATE app.orders
SET status = CASE
    WHEN status = 'pending' AND paid_at IS NOT NULL THEN 'confirmed'
    WHEN status = 'confirmed' AND shipped_at IS NOT NULL THEN 'shipped'
    ELSE status  -- Keep current status if no condition matches
    END,
    updated_at = NOW()
WHERE id = 'order-uuid-here';

-- Always use WHERE in UPDATE
-- Running UPDATE without WHERE updates ALL rows
-- This is how production gets destroyed at 3AM on a Friday
-- "I forgot the WHERE clause" - every developer who's ever worked with databases

-- Delete specific row
DELETE FROM app.users
WHERE id = '123e4567-e89b-12d3-a456-426614174000';

-- Delete with RETURNING - see what you deleted (audit purposes)
DELETE FROM app.sessions
WHERE expires_at < NOW()
RETURNING id, user_id, created_at;

-- TRUNCATE vs DELETE - know the difference
-- TRUNCATE removes ALL rows , resets sequences , can't be filtered
-- DELETE removes rows individually , can be filtered , can be rolled back
-- TRUNCATE is faster but more dangerous

TRUNCATE TABLE app.temp_logs;  -- Nuke the whole table
DELETE FROM app.temp_logs WHERE logged_at < NOW() - INTERVAL '30 days';  -- Selective

-- Multiple conditions with AND/OR
SELECT * FROM app.orders
WHERE total_amount > 100
  AND (status = 'pending' OR status = 'confirmed')
  AND user_id IN (SELECT id FROM app.users WHERE is_active = TRUE);

-- BETWEEN for ranges
SELECT * FROM app.orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
  AND total_amount BETWEEN 50 AND 500;

-- IS NULL / IS NOT NULL (never use = NULL because NULL = NULL is... not true)
SELECT * FROM app.users
WHERE last_login IS NULL  -- Users who've never logged in ... probably abandoned accounts
   OR email IS NOT NULL;  -- Should be all of them

-- EXISTS - more efficient than IN for large subqueries
SELECT * FROM app.users u
WHERE EXISTS (
    SELECT 1 FROM app.orders o
    WHERE o.user_id = u.id
      AND o.total_amount > 1000
);

-- INNER JOIN - only matching rows from both tables
SELECT u.email, o.id AS order_id, o.total_amount, o.created_at
FROM app.users u
INNER JOIN app.orders o ON o.user_id = u.id
WHERE u.is_active = TRUE;

-- LEFT JOIN - all rows from left , NULLs where right doesn't match
SELECT u.email, COUNT(o.id) AS order_count
FROM app.users u
LEFT JOIN app.orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

-- This is how you find users who never ordered (churn analysis)
SELECT u.email, u.created_at
FROM app.users u
LEFT JOIN app.orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- JOIN with multiple conditions
SELECT u.email, o.id AS order_id, p.name AS product_name, oi.quantity, oi.price
FROM app.users u
JOIN app.orders o ON o.user_id = u.id
JOIN app.order_items oi ON oi.order_id = o.id
JOIN app.products p ON p.id = oi.product_id
WHERE o.status = 'delivered'
ORDER BY o.created_at DESC
LIMIT 100;

-- Count orders per user
SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
FROM app.orders
GROUP BY user_id
ORDER BY total_spent DESC;

-- HAVING - filter after aggregation (WHERE filters before)
SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
FROM app.orders
WHERE status != 'cancelled'  -- WHERE runs before GROUP BY
GROUP BY user_id
HAVING SUM(total_amount) > 500  -- HAVING runs after GROUP BY
ORDER BY total_spent DESC;

-- GROUP BY with multiple columns
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    status,
    COUNT(*) AS count,
    SUM(total_amount) AS revenue
FROM app.orders
GROUP BY year, status
ORDER BY year, status;

// NEVER do this - string concatenation is how databases get owned
// Attacker sets username to: '; DROP TABLE users; --
const query = `SELECT * FROM users WHERE username = '${req.body.username}'`;

// ALWAYS do this - parameterized queries
const query = 'SELECT * FROM users WHERE username = $1';
const result = await client.query(query, [req.body.username]);

// Node.js pg driver - parameterized INSERT
const insertUser = `
    INSERT INTO app.users (email, username, password_hash, role)
    VALUES ($1, $2, $3, $4)
    RETURNING id, created_at
`;
const values = [email, username, hash, role];
const result = await pool.query(insertUser, values);