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:
- 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.
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.
Geeky Sivers posts. Not common but always welcomed!
So, what’s the argument against this style? Why wouldn’t you want to build this way?
This is actually quite an old debate.
Some in-depth pro/con lists can be found here and on many other places online: softwareengineering.stackexchange.com/questions/158534/pros-and-cons-of-holding-all-the-business-logic-in-stored-procedures-in-web-appl
My take: is exchanging the program‘s language (e.g. php to ruby) really so much likelier for a grown App compared to exchanging the database / backend technolog? What if you kept your php, but went cloud, big data, made use of key/value stores or a nosql database (think mongodb, Hbase) You get to move your original Problem and need to recreate these functions, but this time on the database layer. Even if you only switch from one rdbms to another (e.g. Postgres to mariaDB) you will likely need to rewrite your sql functions because often times they rely on some rdbms-specific functionality/syntax.
Also if you take a micro-service approach to your application design, it would not really matter, because your apps don’t work with a direct database interface anymore, but with real (http) apis. Now each of your services can choose its own programming language and storage/database solution while you get to keep being master of your own implementation.
Best
Florian
This is a very nuanced topic. This advice, by itself, is bad. There are a lot of things to consider. You need to consider your application, how often you hit the database, whether the function needs to trigger non database related actions, ie how it works in the greater context. Decisions like this can start forcing you to have to implement more and more inside the database and you can totally do it like that, but this advice as it is bad, the reason "because I want to switch language" is bad. There ARE good reasons to do this, but not that so much.
I remember back in the day when people would advise against this because you might switch flavors of SQL and you’d have to rewrite all your SQL functions. Nowadays everyone thinks they need data access layers and that they will migrate from SQL to NoSQL, or between different types of NoSQL databases, so they are doubly against this. But based on my humble experience I’d say that SQL is more than enough for most applications and you’ll probably never need to change the database layer. Putting as much as you can in database functions is how I’ve designed my most successful and longest lasting systems. So I fully endorse this approach for most projects.
I remember you talking about this approach a few years ago. I like it a lot.
It's starting to catch on and I can recommend supabase.com/ - all open source with a nice hosted solution. Put as much as possible into PostgreSQL and get a very nice API for free (they use postgrest.org/en/stable/). They also provide authentication and storage.
Forgot to mention - it's also possible to write PostgreSQL functions in other languages - e.g:
- Python: www.postgresql.org/docs/10/plpython.html
- JavaScript: pgxn.org/dist/plv8/doc/plv8.html
Hi Derek. You should also look into Hasura and Postgraphile- since you like this approach. I think, these tools extend this approach.