Computed Fields

A computed field holds a value the database produces. You read it like any other field, and uql never writes it. Declare it readonly, so a write payload naming it fails to compile.

It is either an SQL expression over the row, or a relation aggregate such as a count of children.

import { Entity, Id, Field, raw } 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) => raw`${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. You select, filter and sort the field like any other, and the expression goes into the statement:

You write

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

PostgreSQL

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

MongoDB does not run SQL, so a query naming an expression field there is refused. 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 do not change, so you can switch it on or off without touching a call site.

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

	Not stored (default)	stored: true	stored: ['update'] (a stamp)
Value lives in	each statement	a column the engine writes	a column a trigger writes
DDL	none	GENERATED ALWAYS AS (...) STORED	a plain column plus a trigger
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.

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 computes it in the database, but only on writes uql makes. A stamp covers every write, including a console UPDATE, a data migration or another service, so use it 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 instead, skipping rows whose stamp already matches so the trigger does not fire itself forever. 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;
}

count and sum read 0 with no related rows; 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 alone, 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. It cannot be stored.