What does a SurrealDB table view cost on an existing table?
Table view over an existing table
Note: this works, but it is a documented trap or a cost the author may not have meant.
What happens
Observed on SurrealDB 3.0.2: DEFINE TABLE revenue AS SELECT math::sum(total) ... GROUP ALL over two stored purchases returns revenue = 12 immediately: the view is computed from every record when it is defined.
Why it is dangerous on a populated table
SurrealDB checks a schema change against new writes, not the records already stored, so an empty table hides it: on a populated one every old record is a write waiting to fail, and whole-table work runs for the length of the table in one transaction.
Fires on
DEFINE TABLE revenue_by_customer AS SELECT customer, math::sum(total) AS revenue FROM purchase GROUP BY customer;The safe pattern
Accept the cost knowingly for small or rarely written tables; for large, write-heavy ones compute the aggregate in a scheduled job instead. Suppress the finding with the reason once the source table's size is known.
-- bolvrk-ignore SR025: purchase holds about 2k records and is written a few times an hour
DEFINE TABLE revenue_by_customer AS SELECT customer, math::sum(total) AS revenue FROM purchase GROUP BY customer;Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (1)
DEFINE TABLE revenue_by_customer AS SELECT customer, math::sum(total) AS revenue FROM purchase GROUP BY customer;Stays silent (1)
DEFINE TABLE page_view DROP;
DEFINE TABLE page_view_daily AS SELECT count() AS views FROM page_view GROUP ALL;How to check locally
SurrealDB support is in beta: this rule runs locally in the free CLI over .surql migrations, static only, and not in the hosted service yet. No install, nothing leaves your machine:
npx bolvrk check migration.surql --engine=surrealdb