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.

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.

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.

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:

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.