SchemaForge

A modern CLI tool for database schema management with a clean DSL and automatic SQL migration generation.

Website: schemaforge.xuby.cl · npm package: @xubylele/schema-forge · Roadmap: ROADMAP.md

Features

• Simple DSL - Define your schema with a clean, intuitive syntax
• Migration Generation - Automatically generate SQL migrations from schema changes
• State Tracking - Built-in state management to track your schema evolution
• Type Safety - Validates your schema before generating SQL
• Default Change Detection - Detects added/removed/modified column defaults and generates ALTER COLUMN SET/DROP DEFAULT
• Postgres/Supabase - Currently supports PostgreSQL and Supabase
• Constraint Diffing - Detects UNIQUE and PRIMARY KEY changes with deterministic constraint names
• Live PostgreSQL Introspection - Extract normalized schema directly from information_schema
• Policy (RLS) support - Define Row Level Security policies in the DSL: for select|insert|update|delete|all, optional to role1 role2 (e.g. anon, authenticated). Invalid policies produce clear CLI errors during validation. See RLS policy patterns for user-owned rows, public read/authenticated write, and multi-tenant examples.

Installation

Install globally via npm:

npm install -g @xubylele/schema-forge

Or use directly with npx (no installation required):

npx @xubylele/schema-forge init

Programmatic API

Use the programmatic API from Node (e.g. scripts, GitHub Actions) instead of invoking the CLI via shell:

const { generate, EXIT_CODES } = require('@xubylele/schema-forge/api');

const result = await generate({ name: 'MyMigration' });
if (result.exitCode !== EXIT_CODES.SUCCESS) process.exit(result.exitCode);

Exports: init, generate, diff, doctor, validate, introspect, importSchema (each returns Promise<RunResult>), RunResult ({ exitCode: number }), EXIT_CODES, and option types (InitOptions, GenerateOptions, DiffOptions, etc.). Entrypoint: @xubylele/schema-forge/api. Exit code semantics: docs/exit-codes.json.

Development

Clone the repository and install dependencies:

git clone <repository-url>
cd schema-forge
npm install

Build the project:

npm run build

Run in development mode:

npm run dev -- [command]

Run tests:

npm test

Run real-db drift integration tests:

npm run test:integration:drift

Notes:

• Local explicit run: set SF_RUN_REAL_DB_TESTS=true (uses Testcontainers postgres:16-alpine, Docker required).
• CI/service mode: set SF_USE_CI_POSTGRES=true and DATABASE_URL to reuse an existing Postgres service.

Getting Started

Here's a quick walkthrough to get started with SchemaForge:

1. Initialize a new project

schema-forge init [provider]

Optional provider: postgres (default) or supabase. You can also use --provider <provider>.

• postgres – Creates migrations/ at the project root and sets outputDir to migrations.
• supabase – Uses supabase/migrations/ for migrations. If supabase/ does not exist, it is created; if it already exists (e.g. from supabase init), SchemaForge config is set to use supabase/migrations/.

This creates:

• schemaforge/schema.sf - Your schema definition file
• schemaforge/config.json - Project configuration (includes provider and outputDir)
• schemaforge/state.json - State tracking file
• migrations/ or supabase/migrations/ - Directory for generated migrations (depends on provider)

2. Define your schema

Edit schemaforge/schema.sf:

# SchemaForge schema definition

table users {
  id uuid pk
  email varchar unique not null
  name text not null
  created_at timestamptz default now()
}

table posts {
  id uuid pk
  user_id uuid fk users.id not null
  title varchar not null
  content text
  published boolean default false
  created_at timestamptz default now()
}

3. Generate your first migration

schema-forge generate

This generates a timestamped SQL migration file with CREATE TABLE statements and updates the state file.

4. Make schema changes

Edit schemaforge/schema.sf to add a new column:

table users {
  id uuid pk
  email varchar unique not null
  name text not null
  avatar_url text          # New column!
  created_at timestamptz default now()
}

5. Generate a migration for the changes

schema-forge generate --name "add user avatar"

This generates a new migration file with ALTER TABLE statements.

6. Check for pending changes

schema-forge diff

If your schema matches the state file, you'll see "No changes detected". If there are changes, it will display the SQL that would be generated.

Default value changes

Schema Forge also tracks default changes on existing columns when diffing schema.sf against state.json.

Supported migration output:

ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT <expr>;
ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP DEFAULT;

Examples:

• Add default: created_at timestamptz -> created_at timestamptz default now()
• Remove default: created_at timestamptz default now() -> created_at timestamptz
• Modify default: default now() -> default timezone('utc', now())

For common function-style defaults, comparisons are normalized to avoid obvious false positives (for example NOW() and now()).

Commands

schema-forge init

Initialize a new SchemaForge project in the current directory.

schema-forge init [provider]
schema-forge init --provider <provider>

• [provider] (optional) – postgres or supabase. Default is postgres.
• --provider <provider> – Same as above; overrides the positional argument if both are given.

Behavior:

• postgres (default): Creates migrations/ at the project root. Config gets provider: "postgres" and outputDir: "migrations".
• supabase: Uses supabase/migrations/ for generated migrations. If the supabase/ folder does not exist, it is created along with supabase/migrations/. If supabase/ already exists (e.g. from an existing Supabase project), only supabase/migrations/ is ensured and config is set to use it. Config gets provider: "supabase" and outputDir: "supabase/migrations".

Creates the necessary directory structure and configuration files.

schema-forge generate

Generate SQL migration from schema changes.

schema-forge generate [--name "migration description"] [--migration-format hyphen|underscore] [--safe] [--force]

Options:

• --name - Optional name for the migration (default: "migration")
• --migration-format <format> - Migration file name: hyphen (e.g. 20250315120000-add-users.sql) or underscore (e.g. 20250315120000_add_users.sql, Supabase CLI style). Overrides migrationFileNameFormat in config.
• --safe - Block execution if destructive operations are detected (exits with error)
• --force - Bypass safety checks and proceed with destructive operations (shows warning)

Safety Behavior:

When destructive or risky operations are detected (like dropping columns or tables), SchemaForge will:

1. Without flags - Display an interactive prompt showing the risky operations and ask for confirmation (yes/no)
2. With --safe - Block execution immediately and exit with error code 1, listing all destructive operations
3. With --force - Bypass safety checks, show a warning message, and proceed with generating the migration
4. In CI environment (CI=true) - Skip the interactive prompt, fail with exit code 3 for destructive operations unless --force is used

See CI Behavior for more details.

Compares your current schema with the tracked state, generates SQL for any changes, and updates the state file.

schema-forge diff

Compare your schema with the current state without generating files.

schema-forge diff [--safe] [--force]

Options:

• --safe - Block execution if destructive operations are detected (exits with error)
• --force - Bypass safety checks and proceed with displaying destructive SQL (shows warning)
• --url - PostgreSQL connection URL for live database diff (fallback: DATABASE_URL)
• --schema - Comma-separated schema names to introspect (default: public)

Shows what SQL would be generated if you ran generate. Useful for previewing changes. Safety behavior is the same as generate command. In CI environments, exits with code 3 if destructive operations are detected unless --force is used. See CI Behavior for more details.

When --url (or DATABASE_URL) is provided, diff compares your target DSL schema against the live PostgreSQL schema introspected from information_schema.

schema-forge import

Reconstruct schemaforge/schema.sf from existing PostgreSQL/Supabase SQL migrations.

schema-forge import <path-to-sql-file-or-migrations-dir>

Options:

• --out <path> - Optional output schema file path (default: schemaforge/schema.sf)

Behavior:

• Parses supported DDL statements in order from a file or from sorted migration filenames in a directory
• Ignores unsupported SQL safely and prints warnings
• Writes a normalized SchemaForge DSL schema file

schema-forge config

Update schemaforge/config.json settings without editing the file.

schema-forge config migration-format <format>

Subcommands:

• migration-format <format> – Set migration file name format: hyphen (e.g. 20250315120000-add-users.sql) or underscore (e.g. 20250315120000_add_users.sql, Supabase CLI style). Writes migrationFileNameFormat to config.

Requires an initialized project. Other config keys are left unchanged.

schema-forge validate

Detect destructive or risky schema changes before generating/applying migrations.

schema-forge validate

Live drift validation:

schema-forge validate --url "$DATABASE_URL" --json

Live --json output returns a structured DriftReport:

{
  "missingTables": ["users_archive"],
  "extraTables": ["audit_log"],
  "columnDifferences": [
    {
      "tableName": "users",
      "missingInLive": ["nickname"],
      "extraInLive": ["last_login"]
    }
  ],
  "typeMismatches": [
    {
      "tableName": "users",
      "columnName": "email",
      "expectedType": "varchar",
      "actualType": "text"
    }
  ]
}

Validation checks include:

• Policy validation – Each policy must reference an existing table, use a valid command (select / insert / update / delete / all), and have at least one of using or with check. Invalid policies cause validation to fail with a clear error (exit code 1).
• Dropped tables (DROP_TABLE, error)
• Dropped columns (DROP_COLUMN, error)
• Column type changes (ALTER_COLUMN_TYPE, warning/error based on compatibility heuristics)
• Nullable to NOT NULL changes (SET_NOT_NULL, warning)

Use JSON mode for CI and automation:

schema-forge validate --json

Live mode options:

• --url - PostgreSQL connection URL for live drift validation (fallback: DATABASE_URL)
• --schema - Comma-separated schema names to introspect (default: public)

In live mode, exit code 2 is used when drift is detected between state.json and the live database. For all exit codes used by validate, see Exit code standards.

schema-forge doctor

Check live database drift against your tracked state.json.

schema-forge doctor --url "$DATABASE_URL"

Use JSON mode for CI and automation:

schema-forge doctor --url "$DATABASE_URL" --json

Options:

• --url - PostgreSQL connection URL (fallback: DATABASE_URL)
• --schema - Comma-separated schema names to introspect (default: public)
• --json - Output structured drift report JSON

Exit codes: see Exit code standards (doctor uses 0, 2).

schema-forge introspect

Extract normalized schema directly from a live PostgreSQL database.

schema-forge introspect --url "$DATABASE_URL" --json

Options:

• --url - PostgreSQL connection URL (fallback: DATABASE_URL)
• --schema - Comma-separated schema names to introspect (default: public)
• --json - Output normalized schema as JSON
• --out <path> - Write normalized schema JSON to a file

CI Behavior

SchemaForge ensures deterministic behavior in Continuous Integration (CI) environments to prevent accidental destructive operations.

Detecting CI Environment

CI mode is automatically activated when either environment variable is set:

• CI=true
• CONTINUOUS_INTEGRATION=true

Exit code standards

SchemaForge uses specific exit codes for deterministic CI and script behavior. The following is the single source of truth.

| Exit Code | Name | Meaning | | --------- | ---- | ------- | | 0 | SUCCESS | No changes or no destructive operations detected | | 1 | VALIDATION_ERROR | Invalid DSL, config errors, missing files, or operation declined (e.g. with --safe) | | 2 | DRIFT_DETECTED | Drift between expected state and live database schema | | 3 | CI_DESTRUCTIVE | Destructive operations detected in CI without --force |

Per-command exit codes:

| Command | Possible exit codes | | ------- | -------------------- | | validate | 0, 1, 2, 3 | | doctor | 0, 2 | | diff, generate | 0, 1, 3 | | init, import | 0 |

(Global CLI errors, e.g. unknown command or missing config, exit with 1.)

A machine-readable exit code contract is available at docs/exit-codes.json. It includes a version field and an optional commands map for tooling.

Destructive Operations in CI

When running in a CI environment, destructive operations (those flagged as error or warning level findings) trigger exit code 3:

Operations classified as destructive:

• Dropping tables (DROP_TABLE)
• Dropping columns (DROP_COLUMN)
• Changing column types in incompatible ways
• Making columns NOT NULL when they allow NULL

Overriding in CI

To proceed with destructive operations in CI, use the --force flag:

# This will fail with exit code 3 if destructive changes detected
schema-forge generate

# This will proceed despite destructive changes (requires explicit acknowledgment)
schema-forge generate --force

No Interactive Prompts in CI

When CI=true, SchemaForge will:

• ✅ Never show interactive prompts, preventing script hangs
• ✅ Fail deterministically (exit code 3) for destructive operations
• ✅ Allow explicit override with --force flag
• ❌ Not accept user input for confirmation

Drift Integration Tests in CI

For drift reliability checks against a real database, run:

npm run test:integration:drift

The integration harness supports two deterministic paths:

• SF_USE_CI_POSTGRES=true + DATABASE_URL: uses the CI Postgres service directly.
• No CI Postgres env: spins up an isolated Testcontainers Postgres instance.

Using --safe in CI

The --safe flag is compatible with CI and blocks execution of destructive operations:

# Blocks execution if destructive operations detected, exits with code 1
schema-forge generate --safe

This is useful for strict CI pipelines where all destructive changes must be reviewed and merged separately.

Using exit codes in CI

In CI, rely on the process exit code to fail the job when validation or safety checks fail. Example with a shell script:

schema-forge validate --json
if [ $? -ne 0 ]; then
  echo "Schema validation failed (exit code: $?)"
  exit 1
fi

When using the schema-forge-action, the action passes through the CLI exit code: a non-zero exit from Schema Forge fails the job with that same code, so no extra script is needed.

Constraint Change Detection

SchemaForge detects and generates migrations for:

• Column-level unique added/removed
• Table primary key added/removed/changed (single-column PK)

Deterministic Constraint Names

To keep migrations stable and safe to drop later, generated constraint names are deterministic:

• Primary key: pk_<table> (example: pk_users)
• Unique (column): uq_<table>_<column> (example: uq_users_email)

Identifiers are normalized to lowercase, non-alphanumeric characters are replaced with _, and repeated _ are collapsed.

Generated SQL Examples

Add unique:

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Remove unique:

ALTER TABLE users DROP CONSTRAINT IF EXISTS uq_users_email;
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_key;

Change primary key column (id -> user_id):

ALTER TABLE users DROP CONSTRAINT IF EXISTS pk_users;
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_pkey;

ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (user_id);

When dropping constraints, SchemaForge attempts deterministic names first, then PostgreSQL legacy defaults (<table>_pkey, <table>_<column>_key) for compatibility.

Also includes nullability migrations when not null is added or removed:

ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

Schema DSL Format

Schemas are defined using the .sf format with a clean, readable syntax.

Basic Syntax

# Comments start with # or //

table table_name {
  column_name column_type [modifiers...]
}

Supported Column Types

• uuid - UUID/GUID
• varchar - Variable-length string
• text - Long text
• int - Integer
• boolean - Boolean value
• timestamptz - Timestamp with timezone
• date - Date without time

Column Modifiers

• pk - Primary key
• unique - Unique constraint
• not null - Disallow NULL values
• nullable - Allow NULL values (default when not null is not provided)
• default <value> - Default value (e.g., default now(), default false, default 0)
• fk <table>.<column> - Foreign key reference (e.g., fk users.id)

Policies (RLS)

Row Level Security policies are declared at the top level (the table must be defined first). Each policy must have a for command and at least one of using or with check.

table users {
  id uuid pk
  email text unique
}

policy "Users can read themselves" on users
for select
using auth.uid() = id

• First line: policy "<name>" on <table>
• Continuation: for <command> (required): select, insert, update, delete, or all (applies to all commands). Optional to role1 role2 (e.g. to anon authenticated). Optional using <expr> and with check <expr>.

Example with for all and to:

policy "Own rows" on profiles
for all
using auth.uid() = id
with check auth.uid() = id

policy "Public read" on items
for select to anon authenticated
using true

Invalid policies (missing table, invalid command, or no expressions) fail schema-forge validate with a clear error message. For common patterns (user-owned rows, public read / authenticated write, multi-tenant), see RLS policy patterns in the core package.

Examples

Simple table

table users {
  id uuid pk
  email varchar unique not null
  name text not null
  created_at timestamptz default now()
}

Table with foreign keys

table posts {
  id uuid pk
  author_id uuid fk users.id not null
  title varchar not null
  content text
  published boolean default false
  created_at timestamptz default now()
}

Table with mixed nullability

table profiles {
  id uuid pk
  user_id uuid fk users.id not null
  bio text nullable
  avatar_url text nullable
  website varchar nullable
  updated_at timestamptz default now()
}

Project Structure

With postgres (default), migrations live in migrations/ at the project root. With supabase, they live in supabase/migrations/. Example for a Supabase-backed project:

your-project/
+-- schemaforge/
|   +-- schema.sf          # Your schema definition (edit this!)
|   +-- config.json        # Project configuration
|   \-- state.json         # State tracking (auto-generated)
\-- supabase/
  \-- migrations/        # Generated SQL migrations (when provider is supabase)
    +-- 20240101120000-initial.sql
    \-- 20240101120100-add-user-avatar.sql

For postgres, replace supabase/migrations/ with a top-level migrations/ directory.

Configuration

The schemaforge/config.json file contains project configuration. The provider and outputDir values are set by schema-forge init based on the provider you choose:

{
  "provider": "supabase",
  "outputDir": "supabase/migrations",
  "schemaFile": "schemaforge/schema.sf",
  "stateFile": "schemaforge/state.json",
  "sql": {
    "uuidDefault": "gen_random_uuid()",
    "timestampDefault": "now()"
  }
}

• postgres: provider: "postgres", outputDir: "migrations".
• supabase: provider: "supabase", outputDir: "supabase/migrations". Init also sets migrationFileNameFormat: "hyphen" so migrations are named timestamp-name.sql (Supabase CLI uses timestamp_name.sql by default; you can set migrationFileNameFormat: "underscore" in config or use --migration-format underscore to match).
• migrationFileNameFormat (optional): "hyphen" (default) or "underscore". Controls generated migration file names: YYYYMMDDHHmmss-name.sql vs YYYYMMDDHHmmss_name.sql. You can set it via schema-forge config migration-format <hyphen|underscore> instead of editing the file.

Supported Databases

Currently supports:

• PostgreSQL (postgres) – default; migrations in migrations/
• Supabase (supabase) – migrations in supabase/migrations/; choose at init with schema-forge init supabase

Development Workflow

A typical development workflow looks like this:

1. Initialize - schema-forge init or schema-forge init supabase (one time)
2. Edit schema - Modify schemaforge/schema.sf
3. Preview changes - schema-forge diff (optional)
4. Generate migration - schema-forge generate --name "description"
5. Apply migration - Run the generated SQL against your database
6. Repeat - Continue editing and generating migrations as needed

Tips

• Use descriptive migration names with --name to make your migration history readable
• Run diff before generate to preview what SQL will be created
• Commit your schema files and migrations to version control
• The state file tracks your schema evolution - don't edit it manually

Releasing

Schema Forge uses automated releases via GitHub Actions and Changesets.

When contributing changes, create a changeset:

npx changeset

Once your PR is merged to main, the release workflow automatically:

• Bumps the version
• Updates the CHANGELOG
• Creates a git tag
• Publishes to npm

No manual steps required! See docs/releasing.md for detailed documentation.

Publishing Manually

To publish a scoped package to npm:

npm publish --access public

Or use the convenience script:

npm run publish:public

For detailed guidelines on contributing and automated releases, see CONTRIBUTING.md and docs/releasing.md.

License

ISC