In-memory query builder with typed columns and where operators
A team writes its server code against a typed SQL query builder. For fast unit tests they want an in-memory stand-in with the same feel: tables are declared with column helpers, filters are built from small operator functions instead of callbacks, and queries are chained.
Implement createQueryKit(). Each call returns a new, empty in-memory database with these helpers:
const {
text, integer, boolean, // column helpers
table, // declare a table
insert, select, // write and read
eq, ne, gt, gte, lt, lte, inArray, isNull, and, or, not, // conditions
asc, desc, // sort directions
} = createQueryKit();
text(), integer() and boolean() return a column definition. A definition has two chainable methods that return the definition: .notNull() and .default(value).table(name, columns) declares a table. columns maps column names to definitions. The returned table object has one property per column (users.email), which is a column reference you pass to conditions and sorts. Column order is the order of the keys in columns.insert(table, values)values is one object or an array of objects. Returns an array with a copy of every stored row.undefined) takes the column’s default if it has one, otherwise null.Error for a key that is not a column, and for a null value in a notNull column.TypeError when a non-null value has the wrong type: text needs a string, integer an integer number (Number.isInteger), boolean a boolean.select(table)Returns a query with chainable methods. Each returns a query, so calls chain.
.where(condition): keep rows that match. A later where replaces an earlier one. where(undefined) means no filter..orderBy(...sorts): each sort is a column reference (ascending), asc(column), or desc(column). The first sort is the primary one and later sorts break ties. A later orderBy replaces an earlier one. Rows that tie on every sort keep insertion order..limit(n) and .offset(n): non-negative integers (throw a RangeError otherwise). Calling either again replaces the earlier value..all(): runs the query against the table’s current rows and returns copies. Steps: filter, then sort, then offset, then limit. Without orderBy, rows come back in insertion order.eq(col, v), ne(col, v), gt, gte, lt, lte: compare the cell with v using ===, !==, >, >=, <, <=. Like SQL, a null cell never matches any of these, not even ne.inArray(col, values): the cell is non-null and in values. An empty list matches nothing.isNull(col): the cell is null.and(...conditions) matches when all match; or(...conditions) when any matches. Both skip undefined arguments, which makes optional filters easy. and() with nothing left matches every row; or() with nothing left matches none.not(condition) inverts a condition. not(eq(col, 'x')) matches null cells.Compare cells with < and > (one column holds one type; false sorts before true). null counts as the smallest value: it comes first with asc and last with desc.
const { text, integer, boolean, table, insert, select, eq, gte, and, or, isNull, desc } = createQueryKit();
const tasks = table('tasks', {
id: integer().notNull(),
title: text().notNull(),
points: integer(),
done: boolean().notNull().default(false),
});
insert(tasks, { id: 1, title: 'Write spec', points: 3 });
// => [{ id: 1, title: 'Write spec', points: 3, done: false }]
insert(tasks, [
{ id: 2, title: 'Fix login', points: 8, done: true },
{ id: 3, title: 'Plan sprint' },
]);
insert(tasks, { id: 4, title: 99 }); // throws TypeError
insert(tasks, { id: 4, title: 'x', owner: 'ana' }); // throws Error (unknown column)
select(tasks).where(and(eq(tasks.done, false), gte(tasks.points, 1))).all();
// => [{ id: 1, title: 'Write spec', points: 3, done: false }]
select(tasks).where(or(isNull(tasks.points), eq(tasks.id, 2))).orderBy(desc(tasks.id)).all();
// => [{ id: 3, ... points: null ... }, { id: 2, ... }]
select(tasks).orderBy(desc(tasks.points)).limit(2).all().map((t) => t.id);
// => [2, 1] (null points sort last with desc)