Computed Fields

A computed field holds a value the database produces: an SQL expression over the row, or a relation aggregate such as a count of children. You read, filter and sort it like any other field, and UQL never writes it: every insert and update leaves it out. Declare it readonly, so a write payload naming it fails to compile.

What backs the field depends on how you declare it. Only a stamp uses a trigger:

Declared as	What is behind it	Who writes it
computed: (p) => sql`...`	Nothing stored. The expression is pasted into each statement that reads the field.	Nobody: it is worked out on each read
sql`...` , stored: true	A generated column, GENERATED ALWAYS AS (...) STORED	The engine, on every insert and update
sql`...` , stored: ['update']	A plain column plus a trigger	The trigger, on each listed event
computed: (o) => o.items.count()	Nothing stored. A correlated subquery in each statement that reads it (a $lookup on MongoDB).	Nobody: it is worked out on each read

The first and last are computed on each read, so they are always current but cost their expression every time. The stored kinds are real columns: computed once per write, then read and indexed like any other.

import { Entity, Id, Field, sql } from 'uql-orm';

@Entity()
export class Product {
  @Id({ type: Number }) id?: number;
  @Field({ type: Number }) cost?: number | null;
  @Field({ type: Number }) salePrice?: number | null;

  @Field({
    type: Number,
    computed: (product) => sql`${product.salePrice} - ${product.cost}`,
  })
  readonly profit?: number | null;
}

Each ref (product.salePrice) renders as its column, qualified and escaped, so the expression works in joins and subqueries and follows a rename in your editor. A ref to another computed field pastes that field’s expression in turn. The field is read even when a query names no fields (eager: false leaves it out then), and wherever $where or $sort names it, the expression is repeated there, in parentheses, since an alias from the SELECT list is not visible in those clauses:

You write

const products = await pool.findMany(Product, {
  $select: { id: true, profit: true },
  $where: { profit: { $gte: 10 } },
});

PostgreSQL

SELECT "id", ("Product"."salePrice" - "Product"."cost") "profit" FROM "Product" WHERE ("Product"."salePrice" - "Product"."cost") >= $1

MongoDB runs no SQL, so it refuses a query naming an expression field. Relation aggregates work on every engine.

Storing the value: stored

stored: true turns the expression into a generated column the database keeps up to date, which you can index and constrain. Queries stay the same, so switching it on or off touches no call site.

@Field({
  type: Number,
  computed: (product) => sql`${product.salePrice} - ${product.cost}`,
  stored: true,
})
readonly profit?: number | null;

PostgreSQL

CREATE TABLE "Product" (
  ...
  "profit" BIGINT GENERATED ALWAYS AS ("salePrice" - "cost") STORED
)

SQL Server

CREATE TABLE "Product" (
  ...
  "profit" AS ("salePrice" - "cost") PERSISTED
)

The engine evaluates the expression whenever a row is inserted or updated, from any client, and a query reads "profit" as a plain column.

	Not stored (default)	stored: true	stored: ['update'] (a stamp)
Indexable	no	yes	yes
Expression	anything the engine accepts	deterministic, over the row only	anything, even currentTimestamp

A stored field needs a type. It works on Postgres 12+, CockroachDB, MySQL 5.7+, MariaDB 5.2+, SQLite 3.31+ and SQL Server (as PERSISTED). generate:from-db reads a stored generated column back as computed with stored: true. A column recomputed on each read (SQL Server’s non-persisted one, SQLite’s VIRTUAL) comes back as a plain field for you to adjust.

Sync and drift detection do not compare a stored column’s expression, so editing it changes nothing in the database: write a migration that drops the column and adds it again.

Stamps: stored on an event

A generated column cannot hold the current time. Give stored a list of events ('insert', 'update') instead, and uql installs a trigger that writes the column on each one:

import { currentTimestamp } from 'uql-orm';

@Field({ type: Date, computed: currentTimestamp, stored: ['update'] })
readonly updatedAt?: Date | null;

currentTimestamp is the database clock. @Field({ onUpdate: currentTimestamp }) also reads it, but only on writes uql makes. A stamp covers every write, including a console UPDATE, a data migration or another service, so use one when writes can come from outside uql.

PostgreSQL

CREATE OR REPLACE FUNCTION "_uql_Product__updatedAt_update_048f80"() RETURNS trigger AS $uql$
BEGIN
  NEW."updatedAt" := CURRENT_TIMESTAMP;
  RETURN NEW;
END $uql$ LANGUAGE plpgsql;

CREATE TRIGGER "_uql_Product__updatedAt_update_048f80"
BEFORE UPDATE ON "Product"
FOR EACH ROW
EXECUTE FUNCTION "_uql_Product__updatedAt_update_048f80"()

SQLite

CREATE TRIGGER `_uql_Product__updatedAt_update_a3e1d4`
AFTER UPDATE ON `Product`
FOR EACH ROW
BEGIN
  UPDATE `Product` SET `updatedAt` = (strftime('%Y-%m-%d %H:%M:%f', 'now')) WHERE `Product`.`id` = NEW.`id`
    AND `Product`.`updatedAt` IS NOT (strftime('%Y-%m-%d %H:%M:%f', 'now'));
END

Postgres and the MySQL family set the value on the incoming row. SQLite and SQL Server run an UPDATE right after the write, skipping rows whose stamp already matches so the trigger does not keep firing itself. Either way, the next query reads the new value.

A stamp is installed, synced and dropped like any trigger. It cannot read another table; for that, write the trigger yourself.

Relation aggregates

A relation aggregate reads a to-many relation: count(), sum(), min(), max() or avg(). It needs no type.

import { Entity, Id, Field, ManyToOne, OneToMany } from 'uql-orm';

@Entity()
export class Order {
  @Id({ type: Number }) id?: number;

  @OneToMany({ entity: () => OrderItem, mappedBy: (item) => item.order })
  items?: OrderItem[];

  @Field({ computed: (order) => order.items.count() })
  readonly itemCount?: number;

  @Field({ computed: (order) => order.items.sum((item) => item.amount) })
  readonly total?: number;

  @Field({
    computed: (order) => order.items.count({ $where: { refunded: false } }),
  })
  readonly paidCount?: number;

  @Field({
    computed: (order) =>
      order.items.sum((item) => item.amount, {
        $sort: { amount: -1 },
        $limit: 5,
      }),
  })
  readonly topFiveTotal?: number;
}

@Entity()
export class OrderItem {
  @Id({ type: Number }) id?: number;
  @Field({ references: () => Order, type: Number }) orderId?: number | null;
  @ManyToOne({ entity: () => Order, references: (item) => item.orderId })
  order?: Order;
  @Field({ type: Number }) amount?: number | null;
  @Field({ type: Boolean }) refunded?: boolean | null;
}

With no related rows, count and sum read 0, while min, max and avg read null, so include null in their type. sum and avg take only a numeric column. On a many-to-many, count counts the links, and an aggregate over a target column reads each target once.

$where picks which related rows count. A value aggregate over only some rows needs a $sort with its $limit, as in topFiveTotal. A count takes $limit (and $skip) without a $sort, which caps the count: it answers “at least 500?” without counting a million rows.

Each aggregate is a correlated subquery in the parent’s statement, so no related row is loaded:

You write

const orders = await pool.findMany(Order, {
  $select: { id: true, total: true },
  $where: { itemCount: { $gte: 2 } },
});

PostgreSQL

SELECT "id", (SELECT COALESCE(SUM("items"."amount"), 0) FROM "OrderItem" "items"
  WHERE "items"."orderId" = "Order"."id") "total"
FROM "Order"
WHERE (SELECT COUNT(*) FROM "OrderItem" "items_2"
  WHERE "items_2"."orderId" = "Order"."id") >= $1

On MongoDB it renders as a $lookup ending in $count or $group. Like a relation, an aggregate is read only when a query names it in $select, $where or $sort; eager: true reads it on every query. An aggregate cannot be stored.