---
title: 'Query Objects and Specifications'
source: 'https://academia.sh/en/courses/data-access-layer/query-objects-and-specifications'
course: 'The Data Access Layer and Business Logic'
language: en
updated: '2026-08-19T05:19:34+00:00'
license: 'CC BY-SA 4.0'
---

# Query Objects and Specifications

Turning a criterion into an object: each specification produces its own SQL fragment and bound values, combines with and-or-not, the same criterion works as an in-memory predicate, and identifiers are limited with an allowlist.

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.

```js
// 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.

```js
// 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));
}
```

```sh
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**.

```js
// 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.

```js
// 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.

```sh
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.

```js
// 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);
```

```sh
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.

```js
// 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);
```

```sh
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.

```js
// 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);
```

```sh
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.