Query Objects and Specifications

The repository pattern closed the boundary, but it bloated the interface. Every new question asked for a new method: open records, a member’s open records, records within a date range, a member’s open records within a specific date range. Because criteria can be combined, the method count grows with the combination of criteria.

The problem is not in the criteria; it is in the criteria being embedded in a method name. Inside the name membersOpenRecords, two criteria and a combinator are hidden; none of them can be used on its own. This lesson takes the criterion out of the name and makes it an object. A criterion that is an object can be combined; a combined criterion produces a single query.

The Criterion Becomes an Object

A specification carries three things: an SQL fragment, the values that correspond to the placeholders in that fragment, and a predicate that is the same criterion’s in-memory counterpart.

// specification.mjs — every specification carries both an SQL fragment and an in-memory predicate
const specification = (sql, values, predicate) => ({ sql, values, predicate });

export const open = () =>
  specification("return_date IS NULL", [], (k) => k.returnDate === null);

export const byMember = (memberId) =>
  specification("member_id = ?", [memberId], (k) => k.memberId === memberId);

export const byBook = (bookId) =>
  specification("book_id = ?", [bookId], (k) => k.bookId === bookId);

export const pickedUpBetween = (start, end) =>
  specification("pickup_date BETWEEN ? AND ?", [start, end],
           (k) => k.pickupDate >= start && k.pickupDate <= end);

export const and = (...specs) =>
  specification(`(${specs.map((x) => x.sql).join(" AND ")})`,
           specs.flatMap((x) => x.values),
           (k) => specs.every((x) => x.predicate(k)));

export const or = (...specs) =>
  specification(`(${specs.map((x) => x.sql).join(" OR ")})`,
           specs.flatMap((x) => x.values),
           (k) => specs.some((x) => x.predicate(k)));

export const not = (spec) =>
  specification(`NOT ${spec.sql}`, spec.values, (k) => !spec.predicate(k));

The combinators’ work is in three steps: they join the sub-specifications’ SQL fragments inside parentheses, concatenate the value arrays end to end in the same order, and connect the predicates logically. The second step is critical. Whatever order the placeholders appear in the text, the values array must follow the same order; flatMap preserves this order because it keeps the same order as the map call inside it.

Making the Generated Query Visible

A specification’s correctness is checked by looking at the text it produces.

// specification-print.mjs — the SQL and bound values a composite specification produces
import { open, byMember, byBook, pickedUpBetween, and, or, not } from "./specification.mjs";

const examples = {
  "open": open(),
  "member's open records": and(byMember(4), open()),
  "not open, first half of 2025": and(not(open()), pickedUpBetween("2025-01-01", "2025-06-30")),
  "member 1 or member 2, book 1": and(or(byMember(1), byMember(2)), byBook(1)),
};

for (const [name, b] of Object.entries(examples)) {
  console.log(name);
  console.log("  sql   :", b.sql);
  console.log("  values:", JSON.stringify(b.values));
}

node specification-print.mjs

open
  sql   : return_date IS NULL
  values: []
member's open records
  sql   : (member_id = ? AND return_date IS NULL)
  values: [4]
not open, first half of 2025
  sql   : (NOT return_date IS NULL AND pickup_date BETWEEN ? AND ?)
  values: ["2025-01-01","2025-06-30"]
member 1 or member 2, book 1
  sql   : ((member_id = ? OR member_id = ?) AND book_id = ?)
  values: [1,2,1]

The last example shows how the order is preserved: three placeholders, three values, the first two from the inner or specification, the third from the outer byBook specification. In the third example, the not combinator carries no value at all, because the specification it wraps carried none either; the values array therefore holds only the two dates.

If the parenthesization is skipped, the error stays silent. If or’s output had no parentheses, (A OR B) AND C would read as A OR (B AND C); the SQL would still be valid, the result would still be non-empty, but it would be wrong.

Single-Method Repository

Once a criterion becomes an object, the repository’s interface shrinks. Together with requests other than filtering — sorting, limit, offset — the criterion is gathered into a single record. This record is called a query object.

// query-store.mjs — a single search method; the criterion arrives from outside
const TO_RECORD = (s) => ({ loanId: s.loan_id, bookId: s.book_id, memberId: s.member_id,
                        pickupDate: s.pickup_date, returnDate: s.return_date });

export class QueryRepository {
  constructor(db) { this.db = db; this.generated = []; }

  search(query) {
    const { specification, orderBy = "loan_id", direction = "ASC", limit = null, offset = 0 } = query;
    let sql = `SELECT * FROM loan WHERE ${specification.sql} ORDER BY ${orderBy} ${direction}`;
    const values = [...specification.values];
    if (limit !== null) { sql += " LIMIT ? OFFSET ?"; values.push(limit, offset); }
    this.generated.push({ sql, values });
    return this.db.prepare(sql).all(...values).map(TO_RECORD);
  }
}

Four separate requests pass through a single method.

// repository-run.mjs — the same repository method meets three different criteria
import { DatabaseSync } from "node:sqlite";
import { QueryRepository } from "./query-store.mjs";
import { open, byMember, byBook, pickedUpBetween, and, or, not } from "./specification.mjs";

const repository = new QueryRepository(new DatabaseSync("library.db"));

const requests = [
  ["all open records", { specification: open() }],
  ["member 4's open records", { specification: and(byMember(4), open()) }],
  ["closed, first half of 2025, most recent",
   { specification: and(not(open()), pickedUpBetween("2025-01-01", "2025-06-30")),
     orderBy: "pickup_date", direction: "DESC", limit: 2 }],
  ["book 1 picked up by member 1 or 2", { specification: and(or(byMember(1), byMember(2)), byBook(1)) }],
];

for (const [name, query] of requests) {
  const result = repository.search(query);
  console.log(`${name}: ${result.map((k) => k.loanId).join(", ") || "(empty)"}`);
}

console.log("\ngenerated queries:");
for (const u of repository.generated) console.log(`  ${u.sql}\n    <- ${JSON.stringify(u.values)}`);

The command below first sets up the loan relation of the library schema, then runs the script.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL
node repository-run.mjs

all open records: 3, 7, 10
member 4's open records: 7
closed, first half of 2025, most recent: 12, 11
book 1 picked up by member 1 or 2: 1, 3

generated queries:
  SELECT * FROM loan WHERE return_date IS NULL ORDER BY loan_id ASC
    <- []
  SELECT * FROM loan WHERE (member_id = ? AND return_date IS NULL) ORDER BY loan_id ASC
    <- [4]
  SELECT * FROM loan WHERE (NOT return_date IS NULL AND pickup_date BETWEEN ? AND ?) ORDER BY pickup_date DESC LIMIT ? OFFSET ?
    <- ["2025-01-01","2025-06-30",2,0]
  SELECT * FROM loan WHERE ((member_id = ? OR member_id = ?) AND book_id = ?) ORDER BY loan_id ASC
    <- [1,2,1]

The third query shows that the limit and the offset are also added as bound values: after the criterion’s two dates come 2 and 0. The generated array is this layer’s counterpart to the counting hook built in the first lesson; which criterion turned into which text can be seen at run time.

The Same Criterion, Two Places

The specification’s second field — the predicate — has not been used yet. Its value is that the criterion can be asked without going to the database. Whether an unsaved candidate record satisfies the rule is tested in memory.

// validate-in-memory.mjs — the same specification applied to a candidate record without touching the database
import { open, byMember, pickedUpBetween, and } from "./specification.mjs";

const rule = and(byMember(4), open(), pickedUpBetween("2025-01-01", "2025-12-31"));

const candidates = [
  { loanId: null, memberId: 4, bookId: 7, pickupDate: "2025-07-15", returnDate: null },
  { loanId: null, memberId: 5, bookId: 7, pickupDate: "2025-07-15", returnDate: null },
  { loanId: null, memberId: 4, bookId: 7, pickupDate: "2024-12-30", returnDate: null },
];

for (const a of candidates) {
  console.log(`member=${a.memberId} pickup=${a.pickupDate} -> ${rule.predicate(a)}`);
}
console.log("the same specification's SQL counterpart:", rule.sql);

node validate-in-memory.mjs

member=4 pickup=2025-07-15 -> true
member=5 pickup=2025-07-15 -> false
member=4 pickup=2024-12-30 -> false
the same specification's SQL counterpart: (member_id = ? AND return_date IS NULL AND pickup_date BETWEEN ? AND ?)

None of the three candidates is in the database; their identifiers being null shows this. The rule was applied anyway. Defining the rule in a single place prevents the query and the check from drifting apart: if two definitions are written, one goes silently stale when the other changes.

The cost of this duality is that the two definitions must stay consistent. In the pickedUpBetween specification, the SQL side uses BETWEEN, and the predicate side does two separate comparisons; both must include the boundary values. Every time such a pair is written, it must be tested that the two sides give the same result.

Bound Value and Identifier

Specifications carrying their values through a placeholder is not a choice; it is a requirement. The difference between the same input entering a query through two different paths can be measured.

// binding.mjs — the difference a bound value and string concatenation make on the same input
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

const input = "2 OR 1=1";

const bound = db.prepare("SELECT count(*) AS n FROM loan WHERE member_id = ?").get(input).n;
const concatenated = db.prepare(`SELECT count(*) AS n FROM loan WHERE member_id = ${input}`).get().n;

console.log("rows returned with a bound value      :", bound);
console.log("rows returned with string concatenation:", concatenated);
console.log("total rows in the table                :",
  db.prepare("SELECT count(*) AS n FROM loan").get().n);

node binding.mjs

rows returned with a bound value      : 0
rows returned with string concatenation: 12
total rows in the table                : 12

The input sent as a bound value settled into the right-hand side of a comparison, not into the query’s structure; no row matched. With string concatenation, the same input became the OR 1=1 condition and returned the whole table. This is the application layer’s view of the SQL injection introduced in the Advanced SQL course.

Binding does not work everywhere. Table and column names — that is, identifiers — cannot be bound values; the query plan cannot be built without knowing them. The orderBy and direction fields in the QueryRepository class entered the text directly. If a value coming from outside reaches these fields, the check is done with a list.

// identifier-check.mjs — the sort column cannot be bound, so it is limited with an allowlist
import { DatabaseSync } from "node:sqlite";

const SORTABLE = new Set(["loan_id", "pickup_date", "return_date"]);
const DIRECTIONS = new Set(["ASC", "DESC"]);

function safeSort(db, orderBy, direction) {
  if (!SORTABLE.has(orderBy)) throw new Error(`cannot sort by column: ${orderBy}`);
  if (!DIRECTIONS.has(direction)) throw new Error(`invalid direction: ${direction}`);
  return db.prepare(`SELECT loan_id FROM loan ORDER BY ${orderBy} ${direction} LIMIT 3`).all()
           .map((r) => r.loan_id);
}

const db = new DatabaseSync("library.db");
console.log("pickup_date DESC:", safeSort(db, "pickup_date", "DESC").join(", "));
for (const bad of ["member_id", "loan_id; DROP TABLE loan"]) {
  try { safeSort(db, bad, "ASC"); }
  catch (h) { console.log("rejected:", h.message); }
}
console.log("the loan table is still in place, row count:",
  db.prepare("SELECT count(*) AS n FROM loan").get().n);

node identifier-check.mjs

pickup_date DESC: 12, 11, 10
rejected: cannot sort by column: member_id
rejected: cannot sort by column: loan_id; DROP TABLE loan
the loan table is still in place, row count: 12

The allowlist approach is safer than searching for escape characters, because it counts what is permitted; it does not try to guess what is forbidden. member_id, which is not on the list, was rejected too: even though this column is valid, it does not pass if it is not on the list.

The Pattern’s Limit

A specification makes a criterion portable but does not hide the schema. The member_id column name appears inside the specification; if this name changes in the schema, the specification changes too. This is the repository’s promise pulled back one step: the caller no longer writes a query, but it knows the field names. The trade-off is accepted knowingly; the alternative is writing a separate repository method for every combination of criteria.

The second limit is scope. These specifications work on a single relation. Criteria that need a join (filtering by branch, for instance) require the specification to affect not only the WHERE fragment but the FROM fragment too; this complicates the pattern quickly. Multi-table reports stay on the direct-query side of the split made in the first lesson.

Summary

• Making a criterion an object instead of embedding it in a method name lets criteria be combined with and-or-not and pass through a single repository method.
• Every specification produces its own SQL fragment and bound values; the combinators keep the value order the same as the placeholder order in the SQL text.
• Alongside the criterion, the query object also carries sorting, limit, and offset; the generated text and values can be verified by printing them.
• The same specification’s predicate field checks an unsaved candidate without going to the database; the rule stays defined in a single place.
• Values are bound, identifiers cannot be: the sort column and direction are limited with an allowlist.

Next Step

Queries are now generated from a single point, but they all need the same thing: a connection. So far, every script has opened its own connection and the process ended once the work was done. A continuously running service cannot behave this way; opening a connection is expensive, and the number of concurrent connections a database can accept is limited. The next lesson builds a pool that opens connections ahead of time and lends them out, measures the relationship between pool size and queue wait time with parallel calls by varying the pool size, and shows where the saturation point appears.