Manual · API

`database, records & queries`

The data itself. database.customers is a table, a query is a chain that stays lazy until something asks, and a record is an object — the same row is always the same object. The badges say what each command means for the wire in a Client/Server pair — green: only an answer returns; red: rows or bytes travel — with measured times (localhost; an office LAN adds ~0.3–3 ms per round trip). Single-user applications have no wire and pay none of it.

Every command on this page carries a worked example.

database.<table> property

database.customers no traffic — a query is a description

Every table of the schema hangs off database by name. What comes back is a query over the whole table — nothing is read yet.

Worked example

// Nothing is loaded here — a query is a description, not a result.
const berliners = database.customers.where("town", "=", "Berlin");
// ...until something asks. THIS reads, and only what it needs:
const count = berliners.count();   // SELECT COUNT(*) — no rows travel

where function

query.where(path, comparison, value) no traffic — still a description

Narrows a query. Comparisons: =, ≠, <, >, contains, beginsWith, is (forgives capitals) and more. The path may cross a relationship: "customer.town".

Chain where twice for AND. Comparing against a record — where("customer", "=", someCustomer) — beats digging out its id. And "is" forgives capitals where = does not.

Worked example

// Unpaid invoices of one customer, newest first.
const open = database.invoices
    .where("customer", "=", form.record)   // compare against a record
    .where("isPaid", "=", false)
    .orderBy("date", "descending");
form.list.show(open);   // the list runs the query itself

orderBy function

query.orderBy(path, direction) no traffic — still a description

Sorts. Direction is "ascending" (the default) or "descending"; chain it twice for a second key.

Worked example

// Two keys: region first, inside a region the biggest first.
const ranked = database.customers
    .orderBy("region")
    .orderBy("revenue", "descending");

all function

query.all() every matching row travels 500 rows ≈ 6 ms

Really loads — every record the query describes, as an array. The one call that reads rows in bulk, which is why the editor warns when the table is measured large: often count() or first() was meant.

Reaching for all().length? That is count() — no rows travel. And the newest record is orderBy(…).first(), one row instead of the table.

Worked example

// Fine on a narrowed query — this loads a handful, not the table:
for (const overdue of database.invoices
        .where("isPaid", "=", false)
        .where("dueDate", "<", new Date())
        .all()) {
    overdue.reminderLevel = overdue.reminderLevel + 1;
    overdue.save();
}

first function

query.first() one row returns ≈ 0.15 ms

The first record of the query's order, or null — one row travels, however big the table is.

Pair it with orderBy and it answers questions like the latest invoice in one row — the database sorts, one record travels.

Worked example

const newest = database.invoices.orderBy("date", "descending").first();
if (newest === null) {
    messages.alert("No invoices yet.");
}

count function

query.count() one number returns ≈ 0.1 ms

How many — counted by the database. Four thousand invoices are never loaded to be counted; no rows travel at all.

Counting a relationship works the same: customer.invoices.count() is one COUNT, not a load.

Worked example

// A relationship is a query too, so this is one COUNT — no loading:
form.countLabel.value =
    form.record.invoices.where("isPaid", "=", false).count()
    + " open invoices";

sum function

query.sum(path) one number returns ≈ 0.2 ms

The total, computed by the database. On an Amount field the answer is exact — whole cents, never floating point.

Narrow first, then sum: where(…).sum("total") lets the database do both in one pass.

Worked example

const outstanding = database.invoices
    .where("isPaid", "=", false)
    .sum("total");   // exact cents — Amounts never touch floating point

average function

query.average(path) one number returns

The mean over the query's records, computed by the database.

Aggregates share one shape: the database computes, one number travels. Any of them beats loading rows to do the arithmetic yourself.

Worked example

const typical = database.invoices.average("total");
form.hintLabel.value = "A typical invoice is around "
    + Math.round(typical / 100) + " €.";

minimum function

query.minimum(path) one number returns

The smallest value, or null over nothing.

Worked example

const cheapest = database.products.where("stock", ">", 0).minimum("price");

maximum function

query.maximum(path) one number returns

The largest value, or null over nothing.

Worked example

const latest = database.invoices.maximum("date");
if (latest === null) { messages.alert("No invoices yet."); }

create function

database.<table>.create() no traffic — born locally, real at save()

A new record, with the table's defaults filled in. It exists for nobody else until its first save().

Assign the relationship, not the id: invoice.customer = chosenCustomer. And for a whole batch, wrap the saves in database.transaction — one commit instead of hundreds.

Worked example

const customer = database.customers.create();
customer.name = "Blackwood & Sons";
customer.town = "Hull";
customer.save();   // now it is real — and every open list learns of it

record.save function

record.save() changed fields go, the saved row returns ≈ 0.2 ms

Writes the record — through the same funnel every writer uses, so the table's rules run whoever is saving. On a client/server pair the save happens on the server, guarded by the record's revision.

Nothing changed, nothing happens — saving an untouched record is free, so a defensive save() at the end of a script costs nothing.

Worked example

// tables/products/onSaving.js sees this save too — rules run for every writer.
const product = database.products.where("code", "=", "RO-12").first();
product.stock = product.stock - quantity;
product.save();

record.delete function

record.delete() an id goes, a confirmation returns ≈ 0.15 ms

Deletes, honouring the relationship's delete rule — prevent refuses while linked records exist.

Deleting many? Narrow a query and loop its all() inside one transaction — or reconsider: a flag (isArchived) keeps history and costs one column.

Worked example

const answer = messages.confirm("Delete " + form.record.name + "?",
                               ["Delete", "Cancel"]);
if (answer === "Delete") {
    form.record.delete();   // "prevent" refuses while invoices still point here
    form.close();
}

record.<relationship> property

customer.invoices · invoice.customer no traffic until asked — then exactly what you ask of it

Relationships read like properties. To-many gives a query (lazy, chainable); to-one gives the record or null.

Treat it as the query it is: customer.invoices.count() and .where(…) run in the database. Looping a relationship's all() per row of a list is the classic N+1 — the query log shows it as a column of tiny queries.

Worked example

// A relationship is a query until somebody treats it as a list:
const heavy = form.record.invoices.where("total", ">", 10000).count();
// .count() did NOT load the invoices — the database counted.

transaction function

database.transaction(work) each command inside travels on its own

Everything inside happens or nothing does. Throwing rolls back.

The tool for imports and mass changes: hundreds of saves become one commit, and a thrown error rolls all of them back instead of leaving half.

Worked example

// All or nothing: the transfer happens whole, or not at all.
database.transaction(function () {
    from.balance = from.balance - amount;
    from.save();
    to.balance = to.balance + amount;
    to.save();   // a throw anywhere in here rolls BOTH back
});

showBackups function

database.showBackups()

Opens the backup window — the same one the standard menu offers, so a button of yours and the menu cannot disagree.

Worked example

// A "Backups…" button on the settings window:
database.showBackups();

restoreFrom function

database.restoreFrom(path, options) the whole database is replaced — a restart follows

A scripted restore. A restore is a restart — a safety copy is taken first, then the application ends and begins on the restored data.

Put a messages.confirm in front — a restore replaces the present, and read-before-write is this product's rule everywhere else too.

Worked example

const path = files.chooseOpen();
if (path !== null &&
    messages.confirm("Replace the current data with this backup?",
                     ["Restore", "Cancel"]) === "Restore") {
    database.restoreFrom(path);   // takes a safety copy, then restarts
}