Simplify: move code into database functions | Derek Sivers

If you are a web or API developer, programming code that uses an SQL database, this is for you.

I’ve found a very different and useful way to structure code.<br>It’s made such a big difference for me that I had to share it here.

How things are

Most web development — whether custom or using frameworks like Rails, Django, Laravel, Sinatra, Flask, and Symfony — tends to work the same way:

At the core is a database , which is just the storage of data.

All intelligence is in Ruby/Python/PHP/JavaScript classes.

Why that’s bad

These norms have some dangerous implications:

Everything must go through these Ruby/Python/PHP/JavaScript classes — including shell scripts and other things not part of this application.

Nothing else may access the database directly, since doing so may break the rules defined by these surrounding classes.

The database is treated as dumb storage , even though the database is smart enough to have most of this logic built-in.

But if you add data logic into the database itself, it’s now duplicated , requiring changing in multiple places if the rules change.

These two systems — the database and its surrounding code — are coupled and dependent on each other.

If it’s ever advantageous to switch applications (say from a web app to mobile app, or Python to JavaScript), you’re going to have to re-write all of that data logic .

Simple vs complex

Please go watch this amazing 35-minute talk as soon as possible:<br>Simplicity Matters by Rich Hickey .

Here are his important points for this article:

“Complex ” is objective.<br>It means many things tied together .

“Simple ” is objective.<br>It means one ingredient — the opposite of complex.

These are unrelated to “easy”.<br>It is easy to install and bind yourself to something very complex (like ORM), and can be hard to build something simple.

Classes, models, and methods (OOP) are an unnecessary complication .

Information is simple, so don’t hide it behind a micro-language.

Work with values directly : hash/map of strings.

Since a JSON API — a hash/map of strings — is often the eventual interface, it’s even more reason to skip the abstractions and work with values directly.

Why this hit home for me

Databases outlive the applications that access them.

I’ve been using the same SQL database since 1997: same data, values, and SQL tables.<br>But the code around it has changed so many times.

In 1997, I started in Perl.<br>In 1998, I switched to PHP.<br>In 2004, a rewrite in Rails.<br>In 2007, back to PHP.<br>In 2009, minimalist Ruby.<br>In 2012, client-side JavaScript.

Each time I’d have to re-write all of the logic around the database :<br>how to add a new person into the database,<br>how to verify an invoice is correct,<br>how to mark an order as paid, etc.

But that whole time, my trusty PostgreSQL database stayed the same .

Since most of this is data logic, not business logic, it should be in the database.

So I’m putting this data logic directly into PostgreSQL, since I plan to stay with it for many more years, but plan to keep experimenting with programming languages.<br>(Nim, Elixir, Racket, Lua, whatever.)

How things could be

Web developers have been treating the database as dumb storage, but it’s actually quite smart.

It’s simple to have all of this intelligence in the database itself .

It’s complex to have it tied to surrounding outside code.

Once you put all of the intelligence directly into the database, then the outside code disappears!

Then the database is self-contained, and not tied to anything .

Your outside interface can switch to JavaScript, Haskell, Elixir or anything else with ease, because your core intelligence is all inside the database.

How to do it

Table constraints

The easiest place to start is constraints:

create table people (<br>id serial primary key,<br>name text not null constraint no_name check (length(name) > 0),<br>email text unique constraint valid_email check (email ~ '\A\S+@\S+\.\S+\Z')<br>);<br>create table tags (<br>person_id integer not null references people(id) on delete cascade,<br>tag varchar(16) constraint tag_format check (tag ~ '\A[a-z0-9._-]+\Z')<br>);

Define what is considered valid/invalid data here.

In my people example above, it says name can’t be empty, email must match that pattern with “@” and “.” and no whitespace.<br>Then it says tags.person_id has to exist in the people table, but if the person is deleted then delete the tags, too.<br>And the tag has to fit that regexp pattern of lowercase letters, numbers, dot, underscore, dash.

It helps to name your constraints for later use in error catching.

Triggers

For things that happen before or after you alter data, use triggers:

create function clean() returns trigger as $$<br>begin<br>new.name = btrim(regexp_replace(new.name, '\s+', ' ', 'g'));<br>new.email = lower(regexp_replace(new.email, '\s', '', 'g'));<br>end;<br>$$ language plpgsql;<br>create trigger clean before insert or update of name, email on people<br>for each row execute procedure...