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
    }