This program is written almost entirely using PostgreSQL stored procedures. These replace all of the controller, model, and serializer logic that you might find in a typical web app.
The part that’s implemented in Ruby amounts to handling results and passing parameters:
ok, res = db.call('invoice_paid', 4, 'cash')
if ok
puts 'paid'
else
puts 'error'
puts res.inspect
endThe rest — including serializing to JSON, setting status codes, and building error objects — is done entirely in PostgreSQL’s stored procedure language, plpgsql. This is a superset of the query language that looks a lot like bash.
Here’s an example, from invoice_get:
js := row_to_json(r) from (
select * from store.invoice_view where id = $1
) r;
status := 200;
if js is null then
js := '{}';
status := 404;
end if;Validation is done with very specific constraints, like this email regex:
CREATE TABLE peeps.people (
id serial primary key,
email text UNIQUE CHECK (email ~ '\A\S+@\S+\.\S+\Z'),
name text NOT NULL CHECK (LENGTH(name) > 0),
city text,
state text,
country char(2) REFERENCES peeps.countries(code)
);There’s also some more complicated logic, like finding the minimum cost of a ship with at least the given weight:
create or replace function store.shipcost(char(2), numeric) returns numeric as $$
declare
c numeric;
begin
if $2 = 0 then
return 0;
end if;
if ($2 < 0) or ($2 > 999) then
return 1000;
end if;
select cost into c from store.shipchart
where country = $1
and weight >= $2
order by cost asc limit 1;
if c is not null then
return c;
end if;
select cost into c from store.shipchart
where country is null
and weight >= $2
order by cost asc limit 1;
if c is not null then
return c;
end if;
return 1001;
end;There are triggers to update related records when other records are updated. For example, lineitem_calc.sql calculates the total price of the items sold based on the item’s price and the quantity sold:
if (tg_op != 'DELETE') then
update store.lineitems l
set price = l.quantity * i.price
from store.items i
where l.item_id = i.id
and l.id = NEW.id;
end if;Tests on the stored procedures are done in ruby, using a set of fixtures:
res = DB.exec("SELECT * FROM store.invoice_shipcost(1)")
assert_equal '6', res[0]['cost']There are a couple potential benefits to this approach:
- Enables easily moving between web frameworks, or using something like http://postgrest.org/en/v6.0/ to generate an API from your schema
- Puts all your data logic in one place. Some logic — eg constraints — is normally handled by the DB anyway. Why not do all of it here?
Potential drawbacks:
- Ties you very heavily to a particular database implementation, which an ORM gives you relative freedom to change
- The tooling — eg, for testing or debugging — doesn’t seem great
But probably the most compelling reason — to those who seek this sort of solution — is the minimalism that’s common to other types of framework avoidance. There are still those who prefer writing vanilla Javascript or C to their more modern, complex cousins, and I see an approach in common with that here. But most will still prefer the power of frameworks over the elegance and control of simple solutions like this.