Runner

The Problem

You have SQL files that need to run against a database. But running them manually is error-prone:

Which files have already run?
Did that file change since last execution?
What happens if execution fails halfway through?
How do you preview what will run before committing?

noorm's runner solves this with checksum-based change detection and execution tracking. Files that haven't changed are skipped. Failed files are automatically retried. Every execution is logged to the database for auditability.

How It Works

The runner provides four execution modes:

Mode	Purpose	Input
Build	Execute all files in schema directory	Schema path from config
File	Execute a single SQL file	File path
Dir	Execute all files in a directory	Directory path
Files	Execute specific files selectively	Array of file paths

When you run a batch (a single file is a batch of one):

Render every .sql.tmpl file, then compute the SHA-256 checksum of the SQL that will execute
Read the newest execution row per file from the tracking database, in one query for the whole batch
Skip if unchanged, run if new/changed/failed. A skipped file writes no tracking row
If no file is left to run, return here without writing an operation row
Create the operation and a pending row for each file about to run
Execute SQL against database, in discovery order
Record result in tracking tables

Change Detection

Files are re-executed only when necessary:

Condition	`RunReason`	Action
New file (no previous record)	`new`	Run
Checksum changed	`changed`	Run
Previous execution failed	`failed`	Run
Parent operation marked stale by teardown	`stale`	Run
`--force` flag	`force`	Run
Unchanged and successful	—	Skip (`skipReason: 'unchanged'`)

This makes builds idempotent—run the same build command twice and unchanged files won't re-execute.

Run Options

Option	Default	Description
`force`	`false`	Re-run even if unchanged
`abortOnError`	`true`	Stop on first failure
`dryRun`	`false`	Render and write to `tmp/` without executing
`preview`	`false`	Output rendered SQL to stdout/file
`output`	`null`	File path for preview output
`concurrency`	`1`	Reserved; see the note below

Note: Files are always executed sequentially for DDL safety. concurrency exists on RunOptions and defaults to 1, but parallel execution is not currently implemented.

Dry Run Mode

The --dry-run flag renders all SQL files and writes them to a local tmp/ directory that mirrors the source structure. This lets you inspect the exact SQL that would be executed without actually running it.

Source:                                     Dry run output:
sql/02_views/001_my_view.sql           →    tmp/sql/02_views/001_my_view.sql
sql/03_seeds/001_users.sql.tmpl        →    tmp/sql/03_seeds/001_users.sql
sql/04_auth/002_permissions.sql.tmpl   →    tmp/sql/04_auth/002_permissions.sql

Templates are fully rendered with the current config context. The .tmpl extension is stripped from output files.

Use cases:

Scenario	Command
Inspect rendered templates	`noorm run build --dry-run`
Review before production deploy	`noorm run build --dry-run -c production`
Debug template variables	`noorm run file seed.sql.tmpl --dry-run`
CI/CD validation step	`noorm run build --dry-run && git diff tmp/`

Preview Mode

The --preview flag outputs rendered SQL to stdout or a single file. Unlike dry run, it doesn't record anything in the tracker.

bash

# Preview to stdout
noorm run build --preview

# Preview to file
noorm run build --preview --output build.sql

Dry run vs Preview:

Aspect	Dry Run	Preview
Output location	`tmp/` directory (mirrored structure)	stdout or single file
Records in tracker	No	No
Change detection	No (processes all files)	No (processes all files)
Use case	Pre-execution inspection	Quick SQL review

Template Integration

Files ending in .sql.tmpl are processed through the template engine before execution:

sql

-- sql/03_seeds/001_users.sql.tmpl
{% for (const user of $.users) { %}
INSERT INTO users (email, name) VALUES ({%~ $.quote(user.email) %}, {%~ $.quote(user.name) %});
{% } %}

The runner passes config, secrets, and project root to the template context. See Template for full documentation.

Tracking Tables

Every execution is recorded in two tables. Names below are the MySQL/SQLite prefixed form; PostgreSQL and SQL Server use noorm.change and noorm.executions in a dedicated schema instead.

__noorm_change__ - Parent operation record:

Field	Description
`name`	Operation identifier (e.g., `build:2024-01-15T10:30:00`)
`change_type`	`'build'` or `'run'`
`executed_by`	Identity string
`config_name`	Which config was used
`status`	`'pending'`, `'success'`, `'failed'` — teardown also rewrites forward rows to `'stale'`

__noorm_executions__ - Individual file records:

Field	Description
`change_id`	FK to parent operation
`filepath`	File that was executed
`checksum`	SHA-256 of the executed SQL (the rendered output, for a template)
`status`	`'pending'`, `'success'`, `'failed'`, `'skipped'` (a file the batch never reached after an earlier failure)
`skip_reason`	Which failure stopped the batch, if skipped
`duration_ms`	Execution time

Basic Usage

typescript

import { runBuild, runFile, runDir, runFiles, preview } from './core/runner'

// Execute all files in schema directory
const result = await runBuild(context, '/project/sql', {
    force: false,
    abortOnError: true,
})

console.log(`Ran ${result.filesRun} files in ${result.durationMs}ms`)
console.log(`Skipped ${result.filesSkipped} unchanged files`)

// Execute a single file
const fileResult = await runFile(context, '/project/sql/001_users.sql')

// Execute all files in a directory
const dirResult = await runDir(context, '/project/sql/02_views')

// Execute specific files (selective execution)
const filesResult = await runFiles(context, [
    '/project/sql/01_tables/001_users.sql',
    '/project/sql/01_tables/002_posts.sql',
    '/project/sql/02_views/001_active_users.sql',
])

// Dry run - render to tmp/ without executing
const dryResult = await runBuild(context, '/project/sql', {
    dryRun: true,
})

// Preview rendered SQL
const previews = await preview(context, [
    '/project/sql/seed.sql.tmpl',
])
console.log(previews[0].renderedSql)

// Preview to a file (optional 3rd parameter)
const previewsToFile = await preview(context, [
    '/project/sql/seed.sql.tmpl',
], '/project/output.sql')

Errors and Skips

Every file the runner touches comes back as a FileResult. Two optional fields on that result are the ones callers usually care about when something goes wrong:

Field	Set when	What it carries
`error`	`status === 'failed'`	The SQL or load error as a string. For SQL failures this is the dialect's error message (already passed through `getSqlErrorMessage`), so it reads the same as you'd see in `psql` or SSMS.
`skipReason`	`status === 'skipped'`	Why the file did not run. Currently one of `'unchanged'` (checksum matches a previous successful execution) or `'already-run'` (used for change-level checks).

Batch results (BatchResult, returned by build, dir, and files) carry the same shape. Each entry in BatchResult.files is a FileResult, so files[].error and files[].skipReason are how you find out which file in a 200-file build is the one that failed. BatchResult.error is reserved for failures that happen before any file executes — e.g. the tracking table cannot be created.

typescript

const result = await ctx.noorm.run.build();

if (result.status !== 'success') {

    for (const file of result.files) {

        if (file.status === 'failed') {

            console.error(`${file.filepath}: ${file.error}`);

        }
        else if (file.status === 'skipped') {

            console.log(`${file.filepath} (skipped: ${file.skipReason})`);

        }

    }

}

The CLI display layer surfaces both fields automatically. noorm run build, noorm run file, noorm run dir, and noorm run files print the failing file's error inline beneath the status line, and skipped files include the skipReason in parentheses. --json returns the full FileResult/BatchResult object verbatim — no fields are stripped. The same applies to noorm change run, noorm change ff, noorm change revert, and noorm change rewind for change-level operations.

MSSQL Batch Handling

T-SQL has a separator that is not a semicolon. GO ends a batch — and a handful of DDL statements (CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, CREATE VIEW, CREATE TYPE for table-valued parameters) must be the only statement in their batch. Without batch splitting you can fit exactly one of those per file. A natural domain grouping of 13 procedures explodes into 73 files.

GO is not SQL. It is a sqlcmd / SSMS client-side directive — the database engine never sees it. So when a runner feeds raw file content to the driver, GO reaches tedious as a token and the driver answers with Incorrect syntax near 'GO'.

When context.dialect === 'mssql', the runner splits SQL content on ^\s*GO\s*$ (case-insensitive, multiline) before execution. Each batch runs sequentially via sql.raw(batch).execute(db). If batch N fails, the runner short-circuits — batches N+1..M are NOT executed. The failing batch index is prefixed onto the error so the offending statement is easy to locate:

typescript

const result = await ctx.noorm.run.file('sql/procs/checkout.sql');

if (result.status === 'failed') {

    console.error(result.error);
    // "[batch 3 of 5] Incorrect syntax near 'WHERE'."

}

Non-MSSQL dialects bypass the splitter entirely — the full file is executed in one sql.raw(...).execute(...) call exactly as before, so PostgreSQL / MySQL / SQLite behavior is unchanged.

Known limitation: the splitter is line-oriented. It does not parse SQL. A GO on its own line inside a /* ... */ block comment or a '...' string literal will still be treated as a separator. Keep GO tokens out of strings and block comments. This matches sqlcmd behavior — if it would break sqlcmd, it breaks here too.

An empty file (or one that contains only comments after stripping GOs) is treated as success with zero duration. The file ran; it just had nothing to execute. That mirrors sqlcmd -i /dev/null.

Observer Events

Event	Payload	Description
`build:start`	`{ sqlPath, fileCount }`	Build operation started
`build:complete`	`{ status, filesRun, filesSkipped, filesFailed, durationMs, error? }`	Build finished
`run:file`	`{ filepath, configName }`	Single file execution started
`run:dir`	`{ dirpath, fileCount, configName }`	Directory execution started
`run:files`	`{ fileCount, configName }`	Selective file execution started
`file:before`	`{ filepath, checksum, configName }`	About to execute a file
`file:after`	`{ filepath, status, durationMs, error? }`	File execution completed
`file:skip`	`{ filepath, reason }`	File skipped; `reason` is `'unchanged'` or `'already-run'`
`file:progress`	`{ filepath, elapsedMs, sessionId, status }`	File still running past the watch delay; repeats every watch interval. See Long-Running Statements
`file:dry-run`	`{ filepath, status, outputPath?, error? }`	File rendered to `tmp/` (or failed to render)

typescript

import { observer } from './core/observer'

observer.on('file:after', ({ filepath, status, durationMs }) => {

    console.log(`${status}: ${filepath} (${durationMs}ms)`)
})

observer.on('file:skip', ({ filepath, reason }) => {

    console.log(`Skipped: ${filepath} (${reason})`)
})

observer.on('build:complete', ({ filesRun, filesSkipped, durationMs }) => {

    console.log(`Build complete: ${filesRun} run, ${filesSkipped} skipped in ${durationMs}ms`)
})

Long-Running Statements

A file runs as one call, so from the client a 20-minute CREATE INDEX and a file queued behind another session's lock look the same. StatementWatcher (src/core/runner/statement-watcher.ts) tells them apart by asking the server. The runner routes every file's SQL through it with one watcher per run; the change executor uses one watcher per change.

A file that finishes inside 10 seconds costs one extra query (its session id) and nothing else. A file that runs longer gets a report every 10 seconds until it ends:

The file is pinned with db.connection() so the session id read first is the session the SQL runs on. A transaction executor (postgres changes) is already one connection and runs as is. The side connection comes from the same pool, is checked out only when a file first runs long, and is held until close() so later slow files reuse it.

status in the event is what the server reported:

Dialect	Source	Reports
postgres	`pg_stat_activity`, `pg_blocking_pids()`, `pg_stat_progress_{create_index,vacuum,cluster,copy,analyze}`	state, wait event, blockers with query and age, parallel workers, phase and done/total
mssql	`sys.dm_exec_requests`	status, wait type, blocking session, `percent_complete`
mysql	`information_schema.processlist`, `sys.innodb_lock_waits`, `sys.schema_table_lock_waits`, `performance_schema.events_stages_current`	thread state, blockers, stage done/estimated
sqlite	none	`status` is null; elapsed time only

Each part of a probe is attempted on its own. A missing privilege (VIEW SERVER STATE on mssql, the sys schema on mysql) drops that part of the report, not the report.

What bites:

Condition	Effect
Pool has no spare connection (`connection.pool.max: 1`)	The side checkout waits 5 seconds, then the watcher gives it up for the rest of the run: reports carry elapsed time only and cancel stops only between files. A checkout that arrives after its file ended, or after the watcher gave up, is returned to the pool at once
Transaction-mode pooler (PgBouncer `pool_mode = transaction`, RDS Proxy, Supabase port 6543)	The session-id read and the file can land on different backends. Reports can describe another client's session and a cancel can stop another client's statement. Point noorm at the database directly, or at a session-mode pooler
Postgres change on a transaction	The file already runs on the change's transaction, so no second pin happens; the side connection is still a separate pooled session

Cancellation

Aborting RunContext.signal stops the run. The runner starts no further file, marks the remaining ones skipped, and returns error: 'Run cancelled'. What happens to the file already running depends on the dialect:

Dialect	Running file
postgres	`pg_cancel_backend(pid)` from the side connection; the file fails and its implicit transaction rolls back
mysql	`KILL QUERY id` from the side connection
mssql	Runs to completion. Kysely's `MssqlDialect` never exposes tedious's `Request`, and `KILL` would end the session, not the request
sqlite	Runs to completion. It is in-process on one connection, so there is no second session to send a cancel from

A file the signal aborted before its SQL was sent does not start, and is skipped with the rest. The pinned connection goes back to the pool only after an in-flight cancel returns, so a cancel that lands late cannot hit the next query on that session. A last file that completes despite the abort leaves the run successful, because nothing was cut short.

The session-id queries and the cancel table live in src/core/connection/session.ts, shared with the SQL terminal's cancel.

typescript

const controller = new AbortController();

const result = await runBuild({ ...context, signal: controller.signal }, sqlPath);
// elsewhere: controller.abort();

Additional Utilities

The runner module exports several utility functions:

typescript

import {
    computeChecksum,              // Compute SHA-256 checksum of a file
    computeChecksumFromContent,   // Compute SHA-256 checksum from string content
    computeCombinedChecksum,      // Combine multiple file checksums deterministically
} from './core/runner'

// Compute checksum from file
const checksum = await computeChecksum('/path/to/file.sql')

// Compute checksum from content string
const contentChecksum = computeChecksumFromContent('SELECT * FROM users')

// Combine multiple checksums (for change-level tracking)
const combined = computeCombinedChecksum([checksum1, checksum2, checksum3])

File Filtering

Filter files by include/exclude path patterns:

typescript

import { filterFilesByPaths } from './core/shared'

const files = [
    '/project/sql/01_tables/001_users.sql',
    '/project/sql/02_views/001_active.sql',
    '/project/sql/archive/old.sql',
]

const filtered = filterFilesByPaths(
    files,
    '/project',
    ['sql/01_tables', 'sql/02_views'],  // include
    ['sql/archive']                     // exclude
)
// ['/project/sql/01_tables/001_users.sql', '/project/sql/02_views/001_active.sql']

This utility is used internally by build operations when applying settings rules. The CLI passes the SQL directory (paths.sql) as the base directory, so settings patterns like 01_tables resolve relative to the SQL path, not the project root. Exclude patterns take precedence when both match.

Unified executeFiles

The executeFiles function provides low-level file execution with full tracking. runBuild/runDir/runFiles/runFile funnel through it. The change module does not — core/change/executor.ts has its own private executeFiles so it can emit change:file events and honour manifest resolution; it shares only computeChecksum, computeCombinedChecksum, and the Tracker base class with the runner.

typescript

import { executeFiles, type FileInput } from './core/runner'

const files: FileInput[] = [
    { path: '/project/sql/001_users.sql', type: 'sql' },
    { path: '/project/sql/002_posts.sql', type: 'sql' },
]

// Four arguments: run options and execution options are separate objects
const result = await executeFiles(
    context,
    files,
    { force: false, abortOnError: true, dryRun: false },   // RunOptions
    {                                                       // ExecuteFilesOptions
        changeType: 'build',
        direction: 'commit',        // default: 'commit'
        operationName: 'build:2024-01-15T10:30:00',
        checksum: combinedChecksum, // optional
        sqlPath: '/project/sql',    // optional, used in error messages
    },
)

The FileInput type:

typescript

interface FileInput {
    path: string
    type: 'sql' | 'txt'
}

ExecuteFilesOptions creates the operation record itself (or reuses a tracker you pass in) — there is no operationId parameter.

This design separates file discovery (external) from execution (internal), enabling:

Pre-validation before database operations begin
Rendering and hashing every file before any tracking write
One tracking query to decide skip-or-run for the whole batch

Tracker Class

The Tracker class handles execution history and change detection. It serves as the base for both runner and change operations.

typescript

import { Tracker, decideNeedsRun } from './core/runner'

// (db, configName, dialect) — dialect defaults to 'sqlite', which selects the
// prefixed table names. Pass the real dialect or pg/mssql queries hit the
// wrong identifiers.
const tracker = new Tracker(db, configName, 'postgres')

// Check if files need to run: the newest row per filepath in one query,
// decided in memory. Returns NeedsRunResult:
// { needsRun, reason?, skipReason?, previousChecksum? }
const [latest, latestErr] = await tracker.latestExecutions()
const { needsRun, reason, skipReason } = decideNeedsRun(latestErr ?? latest.get(filepath), checksum, force)

// Check if file needs to run (by name only - for changes)
const byName = await tracker.needsRunByName(name, checksum, force)

// Create an operation record
const operationId = await tracker.createOperation({
    name: 'build:2024-01-15T10:30:00',
    changeType: 'build',
    direction: 'commit',  // 'commit' or 'revert'
    configName: 'dev',
    executedBy: 'alice@example.com',
})

// Create file records for the files about to run — fileType required
await tracker.createFileRecords(operationId, [
    { filepath: '/path/to/file1.sql', fileType: 'sql', checksum: 'abc123' },
    { filepath: '/path/to/file2.sql', fileType: 'sql', checksum: 'def456' },
])

// Update individual file after execution. Positional, not an options object:
// (operationId, filepath, status, durationMs, errorMessage?, skipReason?)
await tracker.updateFileExecution(operationId, filepath, 'success', 45)

// Skip remaining files (e.g., on error with abortOnError)
await tracker.skipRemainingFiles(operationId, 'aborted due to previous error')

// Finalize operation with optional checksum and error message
await tracker.finalizeOperation(operationId, 'success', 1234, checksum, errorMessage)

createFileRecords, updateFileExecution, skipRemainingFiles, and finalizeOperation return string | null — an error message on failure, null on success — rather than throwing. createOperation returns the new operation id.

Direction and ChangeType

Operations have both a changeType and direction:

ChangeType	Direction	Meaning
`build`	`commit`	Schema build operation
`run`	`commit`	Ad-hoc file/directory execution
`change`	`commit`	Forward change
`change`	`revert`	Rollback change

The API uses 'commit' | 'revert' for clarity, but the database stores 'change' | 'revert' for backwards compatibility. The mapping happens in createOperation().

Batch Visibility

Every file in the run set appears as pending before the first one executes. Skipped files leave no row.

typescript

// Every file in the run set is visible immediately as 'pending'
await tracker.createFileRecords(operationId, runSet)

// As each executes, status updates to 'success' or 'failed'
await tracker.updateFileExecution(operationId, filepath, 'success', 45)

// If aborted, remaining files marked as 'skipped'
await tracker.skipRemainingFiles(operationId, 'aborted due to error')

This means even if a build fails midway, users can see which files would have run.

Template Checksums

For template files (.sql.tmpl), checksums are computed from the rendered content, not the source file:

typescript

// Template source checksum (what's on disk)
const sourceChecksum = await computeChecksum('/path/to/seed.sql.tmpl')

// Rendered content checksum (what actually executes)
const renderedSql = await renderTemplate(templateContent, context)
const executionChecksum = computeChecksumFromContent(renderedSql)

This ensures change detection works correctly when template variables change (e.g., different secrets between environments) even if the template source is unchanged.

Run Context

The RunContext interface includes all execution parameters:

typescript

interface RunContext {
    db: Kysely<NoormDatabase>
    configName: string
    identity: Identity
    projectRoot: string
    access: ConfigAccess                    // the config's per-channel roles
    channel: Channel                        // 'user' (CLI/TUI/SDK) or 'agent' (MCP)
    dialect?: 'postgres' | 'mysql' | 'sqlite' | 'mssql'  // default: 'postgres'
    config?: Record<string, unknown>        // Config object for template context
    secrets?: Record<string, string>        // Config-scoped secrets
    globalSecrets?: Record<string, string>  // Global secrets from state
    signal?: AbortSignal                    // abort to cancel the run
}

access and channel are required. Every exported entrypoint (runBuild, runFile, runDir, runFiles, preview, checkFilesStatus) runs assertRunPolicy against them once, at the core seam, so SDK/TUI/CLI/MCP callers inherit one enforcement path.

dialect selects tracking-table naming (schema-qualified noorm.* on postgres/mssql, __noorm_*__ elsewhere) and drives MSSQL batch splitting.

The optional config field exposes the active configuration as a template variable, allowing templates to access config properties like database name, dialect, etc.

Best Practices

Number files for ordering - Use 001_, 002_ prefixes for deterministic execution order
One DDL per file - Complex statements (stored procedures, triggers) work better in separate files
Use templates for dynamic content - Seeds, environment-specific config, generated permissions
Dry run before production - Always --dry-run against production config to inspect what will execute
Don't modify executed files - If you need to change a table, create a new change instead of editing the old one

Runner ​

The Problem ​

How It Works ​

Change Detection ​

Run Options ​

Dry Run Mode ​

Preview Mode ​

Template Integration ​

Tracking Tables ​

Basic Usage ​

Errors and Skips ​

MSSQL Batch Handling ​

Observer Events ​

Long-Running Statements ​

Cancellation ​

Additional Utilities ​

File Filtering ​

Unified executeFiles ​

Tracker Class ​

Direction and ChangeType ​

Batch Visibility ​

Template Checksums ​

Run Context ​

Best Practices ​

Runner

The Problem

How It Works

Change Detection

Run Options

Dry Run Mode

Preview Mode

Template Integration

Tracking Tables

Basic Usage

Errors and Skips

MSSQL Batch Handling

Observer Events

Long-Running Statements

Cancellation

Additional Utilities

File Filtering

Unified executeFiles

Tracker Class

Direction and ChangeType

Batch Visibility

Template Checksums

Run Context

Best Practices