We're implementing an e-commerce data model and struggle to decide between Postgres generated columns, Hasura computed fields, and Postgres views.
As a simplification, we have two tables:
items
------------
id
order_id
unit_price
quantity
orders
------------
id
We now want to add total_price to the items table (unit_price * quantity), and total_price to orders (sum of total_price of all items in the order).
In the first case, we used a Postgres generated column. The benefit here seems to be that it is generated once, and stored, rather than rerun on each query like a Hasura computed field would be.
Is this the right choice?
items
------------
id
order_id
unit_price
quantity
total_price : Postgres generated column : (unit_price * quantity)
CREATE TABLE orders (
...,
total_price integer GENERATED ALWAYS AS (unit_price * quantity) STORED
);
In the second case, we can't use a generated column, as it cannot reference fields from a different table.
Instead, we can solve this using a Hasura computed field:
orders
------------
id
total_price : Hasura computed field : SUM(items.total_price)
CREATE FUNCTION calculate_order_total_price(orders_row orders)
RETURNS INTEGER AS $$
SELECT CAST(SUM(total_price) AS INTEGER)
FROM items
WHERE order_id = orders_row.id
$$ LANGUAGE sql STABLE;
Alternatively, we can create a Postgres view:
vw_orders
------------
orders.id
total_price : SELECT SUM(items.total_price)
CREATE VIEW vw_orders AS
SELECT orders.id,
(SELECT sum(items.total_price) AS sum
FROM items
WHERE (items.order_id = orders.id)) AS total_price
FROM orders;
The Hasura computed field solution has the detriment that total_price is only exposed in GraphQL queries, so we could not use it in SQL.
The Postgres view solution does not seem to have any problems, at first sight.
Are we missing something?
Why would one ever use a Hasura computed field over Postgres generated fields or views?
Is there a comparison table or flow chart somewhere that would help us decide which approach is best in each particular situation?
Finally, for all of these, we could of course also use Postgres triggers, Hasura event triggers, and Hasura actions... when would those be the appropriate solutions?
Cheers!
