An SQL database schema is the namespace or container for tables/views/indexes. It is an organization tool to group related objects together and define security boundaries by controlling access at the schema level.

Example in PostgreSQL:

CREATE SCHEMA billing;
CREATE TABLE billing.invoices (id SERIAL PRIMARY KEY);
CREATE TABLE billing.payments (id SERIAL PRIMARY KEY);

Database migrations are version-controlled changes to a database schema.

Without migrations, manual schema changes are needed that are hard to sync between testing and production environments. Additionally, the change history is missing and the capability to rollback to a previous schema is lost.

Changes to the schema that bump up the version are called up migrations. The reversal of those changes (bumping down the version) are known as down migrations.

Up migrations include creating tables, adding columns, creating foreign keys, etc., while down migrations involve dropping tables, removing columns and so on.

Example for a migration file:

-- Migration: 001_add_user_phone_number.sql

-- UP
ALTER TABLE users ADD COLUMN phone VARCHAR(15);

-- DOWN
ALTER TABLE users DROP COLUMN phone;

SQL can't (normally) auto-generate down migrations, since some reversals may be ambiguous or require custom logic.

For example, following migration requires custom logic to reverse:

-- UP: Split name into first/last
ALTER TABLE users ADD first_name VARCHAR(50), last_name VARCHAR(50);
UPDATE users SET first_name = SPLIT_PART(name, ' ', 1), last_name = SPLIT_PART(name, ' ', 2);
ALTER TABLE users DROP COLUMN name;

-- DOWN: Merge first/last back into name (custom logic needed)
ALTER TABLE users ADD name VARCHAR(100);
UPDATE users SET name = CONCAT(first_name, ' ', last_name);
ALTER TABLE users DROP COLUMN first_name, last_name;

• SQLite: How It Works, by Richard Hipp
• SQL Noir - A Detective SQL Game