Query Objects and Resource Classes

When to use

Use these patterns when building a data access layer in Node.js. Query objects namespace related SQL queries and Resource classes encapsulate CRUD operations behind static async methods. Together they provide a clean, discoverable API for database operations without requiring instantiation.

The pattern

The pattern has two layers:

1. Query object — a plain object that maps operation names to SQL strings loaded from .sql files.
2. Resource class — a class with static async methods that execute queries, validate input, and transform results.

Consuming a Resource

Callers interact with the Resource class through static async methods. Each method name corresponds to a CRUD operation:

import AccountResource from "./models/account/index.js";

// List all accounts
app.get("/accounts", async (req, res) => {
  const accounts = await AccountResource.list();
  res.json(accounts);
});

// Get one account
app.get("/accounts/:id", async (req, res) => {
  const account = await AccountResource.get(
    req.params.id
  );
  res.json(account);
});

The calling code knows nothing about SQL files or query objects — it sees only the Resource class and its methods.

Defining a query object

Group all SQL queries for a model into a single object. Each property holds the SQL string for one operation:

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 query object acts as a namespace — autocomplete shows every available operation for the model, and IDE "find all references" works on each property.

Defining a Resource class

Wrap query execution in static methods on a class named after the model. Each method handles input validation, query execution, and result transformation:

class AccountResource {

  static async list() {
    const rows = await query(AccountQuery.list);
    return rows.map(toRecord);
  }
  
  static async create(input) {
    const { value } = await InputSchema.assert(input);
    return await query(AccountQuery.create, {
      ...value,
      include: true,
    });
  }
  
  static async get(id) {
    const [row] = await query(AccountQuery.get, { id });
    return toRecord(row);
  }
  
  static async update(id, input) {
    const { value } = await InputSchema.assert(input);
    return await query(AccountQuery.update, {
      ...value,
      id,
    });
  }
  
  static async delete(id) {
    if (!id) {
      throw new TypeError("ID required.");
    }
    return await query(AccountQuery.delete, { id });
  }

}

export default AccountResource;

Notice that every method is static — there is no instance state. The class serves purely as a namespace with a predictable API surface.

Defining result transformation

Keep a private toRecord() function alongside the class to convert raw database rows into the shape your application expects:

function toRecord(row) {
  const include = Boolean(row.include);
  return { ...row, include };
}

This keeps transformation logic centralized and out of the Resource methods themselves.

How the layers connect

The four layers stack cleanly — each one depends only on the layer below it, and callers interact only with the top:

┌───────────────────────────────────────┐
│ Route handler / Controller            │
│ await AccountResource.list()          │
├───────────────────────────────────────┤
│ AccountResource (static methods)      │
│ validates, queries, transforms        │
├───────────────────────────────────────┤
│ AccountQuery (query object)           │
│ maps operation names to SQL strings   │
├───────────────────────────────────────┤
│ .sql files                            │
│ one file per operation                │
└───────────────────────────────────────┘

Callers interact only with the Resource class. The query object and SQL files are implementation details hidden inside the model module.

Defining a complete model module

A complete model module brings together the query object, Resource class, and result transformation in a single file:

import { file } from "@chriscalo/file";
import { query } from "./db.js";

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

class ExpenseResource {

  static async list() {
    const expenses = await query(ExpenseQuery.list);
    return expenses.map(toRecord);
  }
  
  static async create(input) {
    return await query(ExpenseQuery.create, {
      ...input,
      include: true,
    });
  }
  
  static async get(id) {
    const [expense] = await query(
      ExpenseQuery.get, { id }
    );
    return toRecord(expense);
  }
  
  static async update(id, input) {
    return await query(ExpenseQuery.update, {
      id,
      ...input,
    });
  }
  
  static async delete(id) {
    if (!id) {
      throw new TypeError("ID required.");
    }
    return await query(ExpenseQuery.delete, { id });
  }

}

export default ExpenseResource;

function toRecord(row) {
  return { ...row, include: Boolean(row.include) };
}

Trade-offs

• Clean API surface. Callers use AccountResource.list() without knowing about SQL files or query objects. IDE autocomplete shows all operations.
• No instantiation needed. Static methods mean no constructor, no dependency injection, no lifecycle to manage. This keeps things simple when operations are stateless.
• Harder to test in isolation. Static methods cannot be easily replaced with mocks. Testing requires either a real database, a database stub, or dependency injection at the module level (e.g., passing query as a parameter).
• One class per model. Each model gets its own file and Resource class. This is clean for small to medium applications but can feel boilerplate-heavy at scale.
• Query object is an implementation detail. The query object exists to organize SQL strings, not as a public API. Keep it unexported unless other modules need raw query access.
• Compared to the Repository pattern. A Repository typically receives its database connection via constructor injection, making it easier to swap implementations or mock in tests. The Resource class pattern trades that flexibility for simplicity — no constructor, no wiring, no interfaces to satisfy.