terradb

Declarative schema management for PostgreSQL and SQLite.

Install

npm install -g terradb

Quick Start

PostgreSQL

-- schema.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
terradb plan -f schema.sql   # preview changes
terradb apply -f schema.sql  # apply changes

SQLite

-- schema.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

export DATABASE_URL="sqlite:///path/to/database.db"
terradb plan -f schema.sql
terradb apply -f schema.sql

How It Works

1. Write your desired schema as CREATE statements
2. Run terradb plan to see what changes are needed
3. Run terradb apply to execute the changes

terradb compares your schema file against the current database state and generates the necessary ALTER/DROP/CREATE statements.

Configuration

PostgreSQL

export DATABASE_URL="postgres://user:password@localhost:5432/mydb"

Or individual variables:

export DB_HOST=localhost
export DB_PORT=5432
export DB_NAME=mydb
export DB_USER=postgres
export DB_PASSWORD=password

SQLite

export DATABASE_URL="sqlite:///path/to/database.db"
# or
export DATABASE_URL="/path/to/database.db"
# or in-memory
export DATABASE_URL=":memory:"

Feature Support

Feature	PostgreSQL	SQLite
Tables & Columns	Yes	Yes
Primary Keys	Yes	Yes
Foreign Keys	Yes	Yes
Indexes	Yes	Yes
Unique Constraints	Yes	Yes
Check Constraints	Yes	Yes
Views	Yes	Yes
ENUM Types	Yes	No
Sequences	Yes	No
Functions	Yes	No
Procedures	Yes	No
Triggers	Yes	No
Materialized Views	Yes	No
Schemas	Yes	No
Extensions	Yes	No

SQLite uses table recreation for schema changes that ALTER TABLE doesn't support (column type changes, constraint modifications, etc.).

Commands

terradb plan -f schema.sql      # Preview changes
terradb plan -f custom.sql      # Use custom schema file
terradb apply -f schema.sql     # Apply changes
terradb apply -f custom.sql     # Apply from custom file
terradb plan -f schema.sql --format json
terradb apply -f schema.sql --dry-run --format json
terradb apply -f schema.sql --no-color

Examples

Constraints

-- Primary keys
id SERIAL PRIMARY KEY           -- PostgreSQL
id INTEGER PRIMARY KEY          -- SQLite

-- Foreign keys
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

-- Check constraints
CONSTRAINT check_positive CHECK (quantity > 0)

-- Unique constraints
CONSTRAINT unique_email UNIQUE (email)

Indexes

CREATE INDEX idx_email ON users (email);
CREATE INDEX idx_active ON users (email) WHERE active = true;  -- partial index
CREATE UNIQUE INDEX idx_unique_email ON users (email);

PostgreSQL-only Features

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

-- Views
CREATE VIEW active_users AS SELECT * FROM users WHERE active = true;
CREATE MATERIALIZED VIEW user_stats AS SELECT COUNT(*) FROM users;

-- Functions
CREATE FUNCTION add(a INT, b INT) RETURNS INT AS $$ SELECT a + b $$ LANGUAGE SQL;

-- Sequences
CREATE SEQUENCE custom_seq START 1000 INCREMENT 1;

Development

Requires Bun:

git clone https://github.com/elitan/terradb.git
cd terradb
bun install

# check local test env
bun run test:doctor

# PostgreSQL tests
docker compose up -d
bun run test:pg:17

# PostgreSQL matrix
bun run test:pg:14
bun run test:pg:15
bun run test:pg:16
bun run test:pg:17

# Extension tests
bun run test:pg:extensions

# SQLite tests (no docker needed)
bun run test:sqlite

# full PR matrix
bun run test:matrix:pr

Testing docs:

• docs/testing-roadmap.md
• docs/test-matrix.md
• docs/pg-version-variance.md

License

MIT