How It Works

-- Tables with all column types, constraints, indexes
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);

-- Views and materialized views
CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

-- Functions with dollar-quoted bodies
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

-- Triggers
CREATE TRIGGER set_timestamp BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

-- Custom types
CREATE TYPE status AS ENUM ('active', 'inactive', 'pending');

-- Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- TimescaleDB
SELECT create_hypertable('metrics', 'time');

smallint  ->  integer  ->  bigint
VARCHAR(50)  ->  VARCHAR(100)  ->  TEXT
NUMERIC(10,2)  ->  NUMERIC(12,2)

{version}_{description}.{direction}.sql

000001_add_users_table.up.sql
000001_add_users_table.down.sql

-- !! WARNING: BREAKING CHANGE !!
-- This operation will DROP TABLE public.old_users
-- Ensure this is intentional and data has been migrated.

DROP TABLE IF EXISTS public.old_users;

schema/
├── extensions/
│   └── extensions.sql
├── types/
│   └── enums.sql
├── tables/
│   ├── users.sql
│   └── orders.sql
├── views/
│   └── reports.sql
├── functions/
│   └── triggers.sql
├── policies/
│   └── organization_scope.sql
└── timescaledb/
    └── policies.sql