Organizing SQL Files

How you structure your SQL files determines how they execute. noorm processes files in alphabetical order by path, so a well-organized directory structure gives you predictable, repeatable builds.

Recommended Directory Structure

Start with this layout:

sql/
├── 01_tables/
│   ├── 001_users.sql
│   ├── 002_posts.sql
│   └── 003_comments.sql
├── 02_views/
│   ├── 001_active_users.sql
│   └── 002_recent_posts.sql
├── 03_functions/
│   └── 001_calculate_score.sql
└── 04_seeds/
    └── 001_default_roles.sql

This structure works because:

• Numbered folder prefixes - Guarantee execution order between folders (01_ before 02_)
• Numbered file prefixes - Guarantee execution order within each folder
• Logical grouping - Easy to find and maintain related files

Naming Conventions

noorm doesn't enforce naming rules, but consistent naming makes life easier.

Use Numeric Prefixes for Order

Files execute alphabetically. Without prefixes, accounts.sql runs before users.sql—even if users should exist first.

# Without prefixes (unpredictable)
accounts.sql      # Runs first (a < u)
users.sql         # Runs second

# With prefixes (explicit order)
001_users.sql     # Runs first
002_accounts.sql  # Runs second

Recommended formats:

Format	Example	Best For
001_	001_users.sql	Most projects (up to 999 files per folder)
01_	01_users.sql	Smaller projects (up to 99 files)
0001_	0001_users.sql	Large projects with many files

Use Descriptive Names

The name should tell you what the file does without opening it:

# Good
001_create_users.sql
002_add_user_indexes.sql
003_create_posts.sql

# Avoid
001.sql
002_update.sql
users_v2_final_FINAL.sql

Template Files

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

seeds/
├── 001_default_roles.sql          # Static SQL
└── 002_environment_config.sql.tmpl # Dynamic SQL with variables

Execution Order

noorm walks your schema directory recursively, collects every .sql and .sql.tmpl file, and sorts the full paths as strings. That sorted list is the execution order. This is the critical rule for understanding how your schema executes. It means:

1. The directory part of the path decides the order first
2. File names break ties inside the same directory
3. The comparison is by character code, not dictionary order, so every uppercase letter sorts before every lowercase one: Users.sql runs before accounts.sql

Without numeric folder prefixes, alphabetical order causes problems:

sql/
├── functions/          # f comes before t—runs first!
│   └── 001_helpers.sql
├── tables/             # t comes after f—runs second
│   ├── 001_users.sql
│   └── 002_posts.sql
└── views/              # v is last
    └── 001_summary.sql

This is wrong—functions often depend on tables. Use numeric prefixes on folders:

sql/
├── 01_tables/          # runs first
│   ├── 001_users.sql
│   └── 002_posts.sql
├── 02_views/           # runs second
│   └── 001_summary.sql
└── 03_functions/       # runs third (can reference tables/views)
    └── 001_helpers.sql

Execution order:

1. sql/01_tables/001_users.sql
2. sql/01_tables/002_posts.sql
3. sql/02_views/001_summary.sql
4. sql/03_functions/001_helpers.sql

Using Settings for Build Order

The settings.yml file lets you control which folders run and in what order:

build:
    include:
        - 01_tables
        - 02_views
        - 03_functions
        - 04_seeds
    exclude:
        - archive         # Never runs

Folder names are relative to your paths.sql directory, not the project root. With the default paths.sql: ./sql, an entry of 01_tables means ./sql/01_tables — so you do not repeat the sql/ prefix.

A wrong include path still exits 0

sql/01_tables resolves to ./sql/sql/01_tables and matches nothing. noorm run build names the offending entries in a warning:

Ignored 1 build.include entry that matched no files: sql/01_tables
Include paths are relative to paths.sql — use `01_tables`, not `sql/01_tables`.

The build still reports success and exits 0, so a pipeline that only checks the exit code will not catch it. Read the warnings.

A mistyped exclude entry gets the same treatment and is the more dangerous half: nothing is excluded, so the files you meant to fence off run against the target database.

A leading ./ or a trailing / matches nothing for the same reason.

The include array is a filter—it controls which folders are included, not their order. Execution order is always alphanumeric. Use numeric prefixes to control the sequence:

sql/
├── 01_tables/    ← runs first
├── 02_views/     ← runs second
├── 03_functions/ ← runs third
├── 04_seeds/     ← runs last
└── archive/      ← excluded, never runs

This keeps the filesystem self-documenting. Anyone can see the execution order without consulting settings.

Conditional Includes with Rules

Different environments often need different files. Use rules to include or exclude folders based on config properties:

rules:
    # Only run seeds in test environments
    - match:
          isTest: true
      include:
          - 04_seeds
          - 05_test-fixtures

    # Skip heavy seeds for CI
    - match:
          name: ci-test
      exclude:
          - 04_seeds/003_large-dataset.sql

This means:

• Production builds skip test data entirely
• CI builds skip slow seed files
• Development gets everything

A rule include narrows the whole build

An empty build.include means "every folder under paths.sql". A matching rule does not add to that set, it replaces it: its include entries are merged into build.include, and the moment that list is non-empty only the listed folders run.

So on a project with no build.include, the rule above turns a test build into 04_seeds and 05_test-fixtures only. Your tables never get created. If you use rule include at all, list every folder you want in build.include too, and use rule exclude for the conditional part.

Organizing by Feature vs by Type

There are two common approaches to organizing SQL files.

By Type (Recommended for Most Projects)

Group files by what they are:

sql/
├── 01_tables/
├── 02_views/
├── 03_functions/
├── 04_procedures/
└── 05_seeds/

Pros:

• Clear execution order from numeric prefixes
• Filesystem is self-documenting
• Matches how databases organize objects

Cons:

• Related files are spread across folders

By Domain (For Projects with SDK)

If you're building a typed SDK, organize SQL to mirror your domain classes:

sql/
├── 01_core/
│   ├── tables/           # Base entities (users, accounts)
│   └── functions/        # Shared utilities
├── 02_auth/
│   ├── tables/           # Sessions, tokens, permissions
│   ├── views/            # Active sessions, user permissions
│   └── functions/        # Authentication helpers
├── 03_billing/
│   ├── tables/           # Invoices, payments, subscriptions
│   ├── views/            # Revenue reports, aging
│   └── procedures/       # Billing transactions
└── 04_seeds/
    └── defaults.sql      # Reference data

This aligns your database layer with your application layer. The SDK's auth domain class maps to sql/02_auth/, making it easy to find related code.

Pros:

• SQL and SDK stay in sync
• Clear ownership boundaries
• Domain changes are localized

Cons:

• Need numbered folders to control cross-domain dependencies
• More complex include configuration

Hybrid Approach

Start simple and add domains as needed:

sql/
├── 01_tables/            # All tables (simple start)
├── 02_views/
├── 03_functions/
└── 04_seeds/

When a domain grows complex, extract it:

sql/
├── 01_core/              # Moved tables, views, functions
├── 02_billing/           # Extracted billing domain
├── 03_functions/         # Shared functions
└── 04_seeds/

Common Patterns

Environment-Specific Files

Use rules to include environment-specific folders:

sql/
├── 01_tables/
├── 02_views/
├── 03_seeds-dev/        # Development data
├── 03_seeds-staging/    # Staging data
└── 03_seeds-prod/       # Production defaults only

rules:
    - match:
          name: dev
      include:
          - 03_seeds-dev

    - match:
          name: staging
      include:
          - 03_seeds-staging

    - match:
          name: prod
      include:
          - 03_seeds-prod

Archive Pattern

Keep old files without running them:

sql/
├── 01_tables/
├── 02_views/
└── archive/          # Old files, excluded from builds
    └── deprecated_table.sql

build:
    exclude:
        - archive

What's Next?

Now that your files are organized:

• Templates - Add dynamic content to your SQL
• Execution - Understand builds and change detection
• Settings - Configure build order with settings.yml