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. Everything 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 file:

Compute SHA-256 checksum of file contents
Check tracking database for previous execution
Skip if unchanged, run if new/changed/failed
Render template if .sql.tmpl file
Execute SQL against database
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 file contents
`status`	`'success'`, `'failed'`, `'skipped'`
`skip_reason`	`'unchanged'` 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: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`)
})

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 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'

// Prepare file inputs with pre-computed checksums
const files: FileInput[] = [
    { path: '/project/sql/001_users.sql', type: 'sql', checksum: 'abc123' },
    { path: '/project/sql/002_posts.sql', type: 'sql', checksum: 'def456' },
]

// 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'
    checksum?: string     // Pre-computed SHA-256; computed if omitted
}

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
Checksum computation for all files upfront
Full batch visibility via createFileRecords()

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 } 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 file needs to run (by filepath). Returns NeedsRunResult:
// { needsRun, reason?, skipReason?, previousChecksum? }
const { needsRun, reason, skipReason } = await tracker.needsRun(filepath, checksum, force)

// Inside a running operation, exclude that operation's own pending rows or
// every file reads as 'new' forever
const check = await tracker.needsRun(filepath, checksum, force, operationId)

// 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 upfront (for full batch visibility) — 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?, checksum?)
await tracker.updateFileExecution(operationId, filepath, 'success', 45, undefined, undefined, 'abc123')

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

// Legacy: Record a file execution (still supported). Note the field is
// `changeId`, not `operationId`.
await tracker.recordExecution({
    changeId: operationId,
    filepath,
    checksum,
    status: 'success',
    durationMs: 45,
})

// 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

Creating file records upfront provides complete audit trails:

typescript

// All files are visible immediately as 'pending'
await tracker.createFileRecords(operationId, files)

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

// 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
}

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 ​

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

Additional Utilities

File Filtering

Unified executeFiles

Tracker Class

Direction and ChangeType

Batch Visibility

Template Checksums

Run Context

Best Practices