SQL Queries in Separate Files

When to use

Store SQL queries in separate .sql files instead of embedding them as strings in JavaScript. This gives you full editor syntax highlighting, SQL linting and formatting tool support, and better readability for complex queries.

The pattern

Each database operation gets its own .sql file in a queries/ directory alongside the model module that uses it. Files are loaded at module initialization time using a synchronous file reader and stored in a query object.

Project structure

Each model lives in its own directory with an index.js entry point and a queries/ subdirectory holding one .sql file per database operation:

models/
└── account/
    ├── index.js
    └── queries/
        ├── account-list.sql
        ├── account-create.sql
        ├── account-get.sql
        ├── account-update.sql
        └── account-delete.sql

A query file

Each .sql file contains a single SQL statement with a leading comment that describes its purpose:

-- Return account by ID
select
  *
from
  `Account`
where
  `Account`.`id` = :id

Loading query files

Use a synchronous file reader like file() (see the nodejs-files skill) to load .sql files at startup. Group related queries into an object for easy discovery:

import { file } from "@chriscalo/file";

const AccountQuery = {
  list: file("./queries/account-list.sql"),
  create: file("./queries/account-create.sql"),
  get: file("./queries/account-get.sql"),
  update: file("./queries/account-update.sql"),
  delete: file("./queries/account-delete.sql"),
};

The file() function resolves paths relative to the calling module. SQL strings are loaded once at import time and cached in the query object.

Defining a CRUD query set

Each model gets its own set of query files following a consistent naming convention:

queries/
├── expense-create.sql
├── expense-delete.sql
├── expense-get.sql
├── expense-list.sql
└── expense-update.sql

Writing complex queries

Complex queries with CTEs and subqueries are far easier to read and edit in a .sql file than in a template literal:

-- Expenses with calculated spending averages

select
  *,
  (
    select
      abs(sum(`amount`)) / `Expense`.`moving_avg_days`
    from
      `Transactions` as `T`
    where
      `T`.`category` = `Expense`.`name`
      and to_days(now()) - to_days(`T`.`date`)
        < `Expense`.`moving_avg_days`
  ) as `avg_daily_spend`
from
  `Expense`
order by
  `Expense`.`name`

Trade-offs

• Full SQL tooling support. Editors provide syntax highlighting, autocomplete, and linting for .sql files. Embedded strings get none of this.
• One file per operation. This is intentional — it keeps each query focused and makes diffs easy to review. However, a model with many operations produces many small files.
• File names must be discoverable. A consistent naming convention (model-operation.sql) prevents confusion when the queries/ directory grows.
• Synchronous load at startup. Loading files during module initialization blocks the event loop briefly. This is acceptable for a small number of static queries but not for dynamic or user-provided SQL.
• No parameterization in the file itself. The .sql file contains a static query string with parameter placeholders. Parameter binding happens at execution time in the database driver. See sql-named-params.md for the parameter convention.