Rewriting your web app in PostgreSQL stored procedures

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 ='invoice_paid', 4, 'cash')
    if ok
    	puts 'paid'
    	puts 'error'
    	puts res.inspect

The 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 $$
    	c numeric;
    	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;

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 =
    			and =;
    	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 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.