First Build

This tutorial takes about 5 minutes. By the end, you'll understand how noorm separates schema definition from schema evolution.

That is the whole loop: run build applies the SQL files, change ff walks an existing database forward, and change history records what ran. The rest of this page does it step by step.

What You'll Do

1. Initialize a project and configure a database
2. Create SQL files and build your schema
3. Evolve the schema with a change
4. Create a fresh test database to see you don't need migrations

Prerequisites

• noorm installed (Installation Guide)
• A PostgreSQL database to connect to

Step 1: Initialize Your Project

mkdir noorm-tutorial
cd noorm-tutorial
noorm init

noorm init sets up your identity (or reuses the one already in ~/.noorm/) and creates the project scaffolding. When it finishes, you'll have this layout:

noorm-tutorial/
├── .noorm/
│   ├── .gitignore             # ignores state/
│   ├── settings.yml           # Project settings (commit this)
│   └── state/
│       └── state.enc          # Encrypted state (don't commit)
├── sql/                       # Your SQL files go here
└── changes/                   # Versioned changes go here

Configs are added separately, from the TUI. That's the next step.

Step 2: Add a Database Config

Launch the TUI:

noorm ui

Press c for config, then a to add a new config. Fill out the form:

• Config Name: dev
• Database Type: PostgreSQL (use arrow keys to select)
• Host: localhost
• Port: 5432
• Database: noorm_dev
• Username: postgres
• Password: your password
• User Role (CLI/TUI access): leave at admin
• Agent Role (MCP/CLI access): leave at viewer
• Test Database: leave unchecked

Submit the form to save your config. SQL and changes paths are not part of a config. They live in .noorm/settings.yml under paths, and default to ./sql and ./changes.

Step 3: Create the Database

Back at the home screen, press d for Database. You'll see the Database Operations screen:

Database Operations

Config:      dev
Connection:  NOT CREATED

Available Actions

[c] Create - Create database and tracking tables
[d] Destroy - Drop all managed objects
[x] Explore - Browse database schema
[w] Wipe - Truncate table data (keep schema)
[t] Teardown - Drop user objects (keep noorm)
[r] Transfer - Copy data to another database

Press c to create. noorm will create the noorm_dev database on your PostgreSQL server and initialize its tracking tables.

No SQL Files Yet?

That's fine! At this point we're just creating the empty database and noorm's internal tracking tables. We'll add SQL files next.

Step 4: Configure Build Settings

Open .noorm/settings.yml and define which folders to include:

paths:
  sql: ./sql
  changes: ./changes

build:
  include:
    - 01_tables

The include array filters which folders are included in builds. Folder names are relative to your paths.sql directory.

Execution Order

Files and directories run in alphanumeric order. Without numeric prefixes:

sql/functions/   ← runs first (alphabetically)
sql/tables/      ← runs second
sql/views/       ← runs third

That's wrong—tables should exist before views reference them. Use numeric prefixes:

sql/01_tables/   ← runs first
sql/02_views/    ← runs second
sql/03_functions/ ← runs third

This rule applies everywhere: directories, files within directories, change scripts. One mental model for everything.

Step 5: Create Your First SQL File

Create the users table:

mkdir -p sql/01_tables

Create sql/01_tables/001_users.sql:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

Step 6: Run Build

noorm run build

Output:

• build:start     Starting schema build (1 files)
• file:after      Executed sql/01_tables/001_users.sql (12ms)
• build:complete  Build complete: 1 run, 0 skipped (18ms)
• log             Build completed  status=success filesRun=1 filesSkipped=0 filesFailed=0 durationMs=18

Your users table now exists.

Step 7: Evolve the Schema

Now you want to expand the schema: add a bio column to users, create a posts table, and add a view.

First, update your SQL files to reflect the new ideal state.

Edit sql/01_tables/001_users.sql:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    bio TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Create sql/01_tables/002_posts.sql:

CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title TEXT NOT NULL,
    body TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Create sql/02_views/001_recent_posts.sql:

mkdir -p sql/02_views

CREATE OR REPLACE VIEW recent_posts AS
SELECT p.*, u.name as author_name
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.created_at > NOW() - INTERVAL '7 days';

Since you added views, update your settings to include the new folder:

build:
  include:
    - 01_tables
    - 02_views

The 02_ prefix ensures views run after tables—important since views reference tables.

Your SQL files now represent the complete, ideal schema. But the dev database still only has the old users table.

Step 8: Create a Change

Use the TUI to create a change:

noorm ui

Press g for changes, then a to add a new change. Enter a name like add-posts-and-bio.

The TUI creates the folder structure and shows:

✓ Change "2024-01-15-add-posts-and-bio" created!
Created folder structure with template files.

[e] Edit in editor  [any] Back to list

Press e to open the change folder in your editor, or navigate to it manually. Add these files:

changes/2024-01-15-add-posts-and-bio/change/001_add_bio.sql:

ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT;

Changes should be idempotent—safe to run on any database state. PostgreSQL's IF NOT EXISTS ensures this won't error if the column already exists.

changes/2024-01-15-add-posts-and-bio/change/002_schema.txt:

01_tables/002_posts.sql
02_views/001_recent_posts.sql

The .txt file references your SQL files. When the change runs, it executes those files from your sql/ directory.

Step 9: Apply the Change

noorm change ff

Output:

• change:start     Starting change for 2024-01-15-add-posts-and-bio (3 files)
• change:complete  change 2024-01-15-add-posts-and-bio: success (29ms)
• log              Fast-forward success  executed=1 skipped=0 failed=0
• log                2024-01-15-add-posts-and-bio (success)

Three files, because the 002_schema.txt manifest expands to the two SQL files it names. Per-file lines are change:file events, which sit at debug level. Raise logging.level to verbose in .noorm/settings.yml to see them.

Your dev database now matches your SQL files.

Step 10: Fresh Database (No Migrations Needed)

Now create a test database to see the other path. In the TUI:

1. Press c for Configurations, then a to add a new config named test pointing to noorm_test
2. Use arrow keys to select the test config and press Enter to switch to it
3. Press d for Database, then c to create the database
4. Press r for Run SQL, then b for Build

If you already have a test config and database set up, the same flow runs from the non-interactive CLI:

noorm config use test
noorm run build

Output:

• build:start     Starting schema build (3 files)
• file:after      Executed sql/01_tables/001_users.sql (14ms)
• file:after      Executed sql/01_tables/002_posts.sql (11ms)
• file:after      Executed sql/02_views/001_recent_posts.sql (7ms)
• build:complete  Build complete: 3 run, 0 skipped (32ms)
• log             Build completed  status=success filesRun=3 filesSkipped=0 filesFailed=0 durationMs=32

The test database has the complete schema from your SQL files. No changes needed—they're only for evolving existing databases.

What Just Happened?

You experienced both paths:

Scenario	Command	What Runs
Fresh database	noorm run build	SQL files
Existing database	noorm change ff	Changes

Your SQL files are the source of truth. Changes are the changelog of how you evolved existing databases to match them.

The Workflow

1. Update your SQL files to reflect the ideal schema
2. Create a change to evolve existing databases
3. Fresh databases just run build—no migration history needed

What's Next?

• Concepts - Understand the full mental model
• Building Your SDK - Wrap noorm in a TypeScript SDK for your apps
• TUI Quick Reference - Navigate the terminal interface
• Changes Guide - Versioned changes in depth