Declarative PostgreSQL schema management.
npm install -g pgterra1. Create schema.sql:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE
);2. Preview changes:
pgterra plan3. Apply:
pgterra apply4. Update schema.sql:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL, -- added
active BOOLEAN DEFAULT true -- added
);
CREATE TABLE posts ( -- added
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL,
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE INDEX idx_user_email ON users (LOWER(email)); -- added5. Terra generates the ALTER statements:
$ pgterra plan
ALTER TABLE users ADD COLUMN name VARCHAR(100) NOT NULL
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT true
CREATE TABLE posts (...)
CREATE INDEX idx_user_email ON users (LOWER(email))
$ pgterra applyDatabase connection via DATABASE_URL or individual variables:
export DATABASE_URL="postgres://user:password@localhost:5432/mydb"Or:
export DB_HOST=localhost
export DB_PORT=5432
export DB_NAME=mydb
export DB_USER=postgres
export DB_PASSWORD=passwordTables & Columns: All PostgreSQL column types, default values, NOT NULL constraints
Functions & Procedures: User-defined functions and procedures with full PostgreSQL feature support
Triggers: Table triggers with BEFORE/AFTER/INSTEAD OF timing
Sequences: Custom sequences with configurable properties
Constraints:
-- Primary keys
id SERIAL PRIMARY KEY
-- Foreign keys with actions
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
-- Check constraints
CONSTRAINT check_positive CHECK (quantity > 0)
-- Unique constraints
email VARCHAR(255) UNIQUE
CONSTRAINT unique_email UNIQUE (email, domain)Indexes:
-- Basic
CREATE INDEX idx_email ON users (email);
-- Partial
CREATE INDEX idx_active_users ON users (email) WHERE active = true;
-- Expression
CREATE INDEX idx_lower_email ON users (LOWER(email));
-- Concurrent (built automatically when safe)ENUM types:
CREATE TYPE status AS ENUM ('pending', 'active', 'inactive');
CREATE TABLE users (
id SERIAL PRIMARY KEY,
status status NOT NULL
);Views:
CREATE VIEW active_users AS
SELECT id, email FROM users WHERE active = true;
CREATE MATERIALIZED VIEW user_stats AS
SELECT COUNT(*) as total FROM users;Functions:
CREATE FUNCTION calculate_total(quantity INT, price DECIMAL)
RETURNS DECIMAL
AS $$
SELECT quantity * price
$$
LANGUAGE SQL IMMUTABLE;Procedures:
CREATE PROCEDURE archive_old_posts(days_old INT)
LANGUAGE SQL
AS $$
DELETE FROM posts WHERE created_at < NOW() - INTERVAL '1 day' * days_old;
$$;Triggers:
-- First create a trigger function
CREATE FUNCTION update_modified_timestamp()
RETURNS TRIGGER
AS $$
BEGIN
NEW.modified_at = NOW();
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
-- Then create the trigger
CREATE TRIGGER set_modified_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_timestamp();Sequences:
CREATE SEQUENCE custom_id_seq
START 1000
INCREMENT 1
CACHE 20;pgterra plan # Preview changes
pgterra plan -f custom.sql # Use custom schema file
pgterra apply # Apply changes
pgterra apply -f custom.sql # Apply from custom fileRequires Bun:
git clone https://github.com/elitan/terra.git
cd terra
bun install
# Start test database
docker compose up -d
# Run tests
export DATABASE_URL="postgres://test_user:test_password@localhost:5487/sql_terraform_test"
bun testMIT