SQL Best Practices

-- Names that read the same way everywhere
CREATE TABLE product_categories (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    name               VARCHAR(100) NOT NULL,
    parent_category_id INT,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                 ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_category_id) REFERENCES product_categories(id)
);

-- Readable: keywords up, identifiers down, one clause per line
SELECT
    s.name,
    s.branch,
    COUNT(o.id)                AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM students s
LEFT JOIN orders o ON o.student_id = s.id
WHERE s.joined_on >= '2026-01-01'
GROUP BY s.id, s.name, s.branch
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 20;

-- Name the columns. Both of these survive a schema change.
SELECT id, name, email FROM students WHERE id = 42;
INSERT INTO students (name, email, branch) VALUES ('Ravi K', 'ravi@example.com', 'ECE');

-- Neither of these does
-- SELECT * FROM students WHERE id = 42;
-- INSERT INTO students VALUES (NULL, 'Ravi K', 'ravi@example.com', 'ECE');

-- A reserved word needs quoting, and the quoting differs by database
-- MySQL:       SELECT `order` FROM items;
-- PostgreSQL:  SELECT "order" FROM items;
-- SQL Server:  SELECT [order] FROM items;
-- Simplest fix: do not name a column 'order'.

-- The vulnerable pattern, in any language:
--   sql = 'SELECT * FROM students WHERE email = ' + quote(input)
--
--   input = ' OR '1'='1        -> WHERE email = '' OR '1'='1'   (all rows)
--   input = '; DROP TABLE students; --   -> two statements

-- The safe pattern. The SQL is fixed; only values travel separately.

-- Python (mysql-connector, psycopg2):
--   cur.execute('SELECT id, name FROM students WHERE email = %s', (email,))

-- Node.js (mysql2):
--   conn.execute('SELECT id, name FROM students WHERE email = ?', [email])

-- PHP (PDO):
--   $stmt = $pdo->prepare('SELECT id, name FROM students WHERE email = :email');
--   $stmt->execute(['email' => $email]);

-- Numbers need parameters too. Escaping protects nothing here,
-- because there are no quotes around the value:
--   BAD:  'SELECT * FROM orders WHERE id = ' + id
--   GOOD: conn.execute('SELECT * FROM orders WHERE id = ?', [id])

-- A column name cannot be a parameter. Use an allowlist you control.
--   allowed = { 'name': 'name', 'joined': 'joined_on', 'spent': 'total' }
--   col = allowed[req.query.sort] or 'name'
--   dir = 'DESC' if req.query.dir == 'desc' else 'ASC'
--   sql = 'SELECT id, name FROM students ORDER BY ' + col + ' ' + dir
-- Safe because col and dir can only ever be strings you wrote.

-- LIKE: parameterised and still wildcard-aware
--   conn.execute(
--     "SELECT id, name FROM students WHERE name LIKE ? ESCAPE '!'",
--     ['%' + escapeWildcards(term) + '%'])
-- where escapeWildcards turns  %  _  !  into  !%  !_  !!

-- The two statements that must never happen separately
START TRANSACTION;

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

COMMIT;      -- both take effect, permanently
-- ROLLBACK; -- neither took effect, as if nothing happened

-- Do not check the balance in a separate SELECT and then decide.
-- Between the two statements another transfer can slip through.
-- Put the condition in the UPDATE itself:
START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1 AND balance >= 5000;
-- If that affected 0 rows, the balance was insufficient.
-- The application checks the affected-row count and rolls back.

UPDATE accounts SET balance = balance + 5000 WHERE id = 2;

COMMIT;

-- SAVEPOINT: roll back part of a transaction without losing all of it
START TRANSACTION;
INSERT INTO orders (student_id, ordered_at) VALUES (42, NOW());
SAVEPOINT order_created;
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 7, 2, 499.00);
-- something is wrong with the item, but keep the order
ROLLBACK TO SAVEPOINT order_created;
COMMIT;

-- Starting a transaction, by dialect
-- MySQL:       START TRANSACTION;   (BEGIN also works)
-- PostgreSQL:  BEGIN;
-- SQL Server:  BEGIN TRANSACTION;

-- MySQL: this table cannot be part of a transaction at all
-- CREATE TABLE legacy_log (...) ENGINE=MyISAM;

-- The lost update, written the dangerous way:
--   SELECT stock FROM products WHERE id = 7;   -- application reads 10
--   ... application computes 10 - 1 ...
--   UPDATE products SET stock = 9 WHERE id = 7;
-- Two requests at once both read 10, both write 9. One sale lost.

-- Fix 1: let the database do the arithmetic, in one statement
UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock > 0;
-- 0 rows affected means it was out of stock. No race window.

-- Fix 2: lock the row for the length of the transaction
START TRANSACTION;
SELECT stock FROM products WHERE id = 7 FOR UPDATE;
-- other transactions now wait here until this one finishes
UPDATE products SET stock = stock - 1 WHERE id = 7;
COMMIT;

-- Fix 3: optimistic locking with a version column
-- read: SELECT stock, version FROM products WHERE id = 7;   -- version 4
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 7 AND version = 4;
-- 0 rows affected means somebody else got there first: re-read and retry

-- Inspecting and setting the level
SELECT @@transaction_isolation;              -- MySQL 8.0
-- SHOW transaction_isolation;               -- PostgreSQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- MySQL: what actually caused the last deadlock
SHOW ENGINE INNODB STATUS;

-- Step 1: always. See what you are about to change.
SELECT id, name, branch FROM students WHERE roll_number = 'CS2026114';

-- Step 2: the same WHERE clause, now doing the work
UPDATE students SET branch = 'ECE' WHERE roll_number = 'CS2026114';

-- For anything that matters, make it reversible while you look
START TRANSACTION;
UPDATE students SET branch = 'ECE' WHERE roll_number = 'CS2026114';
-- the client reports: 1 row affected  -> as expected
COMMIT;
-- had it reported 400 rows affected:
-- ROLLBACK;

-- MySQL: refuse UPDATE/DELETE that lack a key in the WHERE clause
SET SQL_SAFE_UPDATES = 1;

-- The three ways to remove data
DELETE FROM orders WHERE ordered_at < '2020-01-01';  -- filtered, undoable
TRUNCATE TABLE session_log;   -- whole table, resets AUTO_INCREMENT
DROP TABLE session_log;       -- the table itself is gone

-- In MySQL, TRUNCATE cannot be rolled back:
-- START TRANSACTION;
-- TRUNCATE TABLE session_log;   -- implicit COMMIT happens here
-- ROLLBACK;                     -- too late, the table is empty
-- In PostgreSQL, the same sequence would restore the rows.

-- An application account that cannot destroy anything (MySQL)
CREATE USER 'campus_app'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus.* TO 'campus_app'@'localhost';
-- deliberately not granted: DROP, ALTER, CREATE, GRANT
FLUSH PRIVILEGES;

-- MySQL: back up one database, consistently, and compress it
-- mysqldump -u root -p --single-transaction campus > campus_20260802.sql
-- mysqldump -u root -p --single-transaction campus | gzip > campus.sql.gz

-- Schema only, or data only
-- mysqldump -u root -p --no-data campus > schema_only.sql
-- mysqldump -u root -p --no-create-info campus > data_only.sql

-- Restore into a database that already exists
-- mysql -u root -p campus < campus_20260802.sql

-- PostgreSQL
-- pg_dump -U postgres campus > campus_20260802.sql
-- psql   -U postgres -d campus -f campus_20260802.sql

-- Test the restore somewhere harmless, then check it looks right
CREATE DATABASE campus_restore_test;
-- mysql -u root -p campus_restore_test < campus_20260802.sql
SELECT COUNT(*) FROM campus_restore_test.students;
SELECT COUNT(*) FROM campus_restore_test.orders;
DROP DATABASE campus_restore_test;

-- Is point-in-time recovery even possible on this server? (MySQL)
SHOW VARIABLES LIKE 'log_bin';    -- ON means binary logging is enabled