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.
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.
Here’s a PostgreSQL example, from my previous post:
First, make three simple tables:
- Items with prices.
- Lineitems with quantities.
- 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.
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.