Database functions to wrap logic and SQL queries

When you make a database-backed app, you have some functions that need to run multiple database queries.

For example, to move money from one account to another, you have to insert a negative amount into account #1, and a positive amount into account #2.

Usually you do in your main code: your JavaScript, Python, Ruby, or whatever.

But what if some future code bypasses your crucial “business logic” functions? New code could access the database directly, without going through your existing functions.

Or what if you need to rewrite some code in a new language? You’ll have a lot of data to rewrite if all this data logic was kept in the surrounding code.

I felt the pain of this at my last company, when we converted some old PHP code to Ruby. I had to rewrite so much of the logic.

In hindsight, data logic should be in the database itself.

Simple logic that’s always needed to update the data (like the money-moving example) should be kept in database functions. Then your surrounding code - your JavaScript, Python, Ruby, or whatever - can just call these database functions, and never need to be rewritten if you change languages to Swift, Kotlin, Elixir, or whatever.

Here’s a PostgreSQL example, from my previous post:

First, make three simple tables:

1. Items with prices.
2. Lineitems with quantities.
3. Invoices with the total price.

Create two example items, a $5 and a $9 item. And create invoice #1 for testing.

create table items (
  id serial primary key,
  price int not null check (price > 0)
);

create table invoices (
  id serial primary key,
  total int
);

create table lineitems (
  invoice_id int not null references invoices(id),
  item_id int not null references items(id),
  quantity int not null check (quantity > 0),
  primary key (invoice_id, item_id)
);

-- example data:
insert into items (price) values (5);
insert into items (price) values (9);
insert into invoices (total) values (0);

If someone wants to add an item to their cart, you need to first see if it’s in their cart already. If it’s not in their cart, insert it. But if that Item is in their cart, you need to update it, to add the new quantity to their existing quantity.

So wrap all that logic in a simple function called cart_add.

create function cart_add(inv int, item int, quant int) returns void as $$
begin
  -- does this invoice + item combination already exist?
  perform 1 from lineitems
  where invoice_id = inv
  and item_id = item;
  if found then  -- yes? add this quantity
    update lineitems
    set quantity = quantity + quant
    where invoice_id = inv
    and item_id = item;
  else  -- no? insert
    insert into lineitems values (inv, item, quant);
  end if;
end;
$$ language plpgsql;

Someone updates their cart, to change the quantity of a Lineitem. If they change the quantity to 2, 5, or even 1, no problem, just update the quantity. But what if they change the quantity to 0? You don’t want a Lineitem hanging around their cart with a quantity of 0. No, if the quantity is 0 or below, you want to delete that Lineitem.

So wrap all that logic in a simple function called cart_set.

-- update the quantity of an item in the cart
create function cart_set(inv int, item int, quant int) returns void as $$
begin
  if quant > 0 then
    update lineitems
    set quantity = quant
    where invoice_id = inv
    and item_id = item;
  else  -- quantity 0 or below? delete
    delete from lineitems
    where invoice_id = inv
    and item_id = item;
  end if;
end;
$$ language plpgsql;

There, now this data logic is where it belongs: with the data itself.

Your JavaScript, Python, Ruby, or whatever can just call the functions, like this:

select cart_add(1, 1, 3);
select * from lineitems;

select cart_add(1, 2, 4);
select * from lineitems;

select cart_set(1, 2, 1);
select * from lineitems;

Imagine if you did it like this for all of the important things you need to do in your database?

Then any code, in any language, could just call those functions, knowing the database itself will handle the logic. Keeping the data-logic where it should be: with the data.

Download the final example file here: /code/api01.sql.

More on this in future posts. Or until then, see my example on Github.