Stored Procedures as a backend

or “How we got rid of Django and whole 3-Tier concept”

DISCLAIMER for the ones rushing to the comment section with epic rant:
I am FULLY aware how silly are all those hundreds of professionals building DB2, PostgreSQL, Oracle, SQL Server with everything that makes replacing the backend possible. Why would anyone sane use it, right?

OK, not stored procedures, as PostgreSQL has functions instead, but nevertheless we got rid of Django REST Framework and wrote whole logic just in the database. I immediately felt 15 years younger (I think that’s when this concept was declared dead and passe)
Also it would be a bit too long for the title, so we are utilizing
1. triggers (be careful here, most of the logic should rather be in the functions)
2. materialized views (now back in the game with CONCURRENT refresh!)
3. views (of course)
4. and functions :)

So far 3 projects has been migrated:
1. Forex CRM
2. Medical platform for schools
3. HR system for hotel employment

Spoiler alert: It was GREAT idea.

Concept:
1. Entire business logic in the database (PostgreSQL)
2. REST interface using PostgREST
3. Frontend: Vue or Angular/ionic (or Postman ;)
4. Look Ma, no backend! At least not in the traditional sense. Instead, several small do-one-thing-and-do-it-well (Go, Python) micro-apps listens on NOTIFY channels around the world. One of them sends emails, another is PDF converter, yet another sends SMS

I will not outline the positives of this approach. Too many — from excellent permissions per user (rows, columns) up to performance gain and everything in between.

Instead, let me address few elephants in the rather small room.

1. “Database should store data not logic!” — well, no. This is called data-base, not data-bag. Databases are specialized engines to deal with, you guessed it, data. And they are extremely efficient at it. Not to mention - majority of the job backends do, is dealing with data. This is good for prototyping. Unfortunately more often than not — prototypes lands on production with updated connection string and debug=False.
2. “Code is an unmaintainable mess” — Not having rules about what code belongs where is not a technology problem.
   Coding principles are there for a reason — we follow them in Java, we should in SQL.
   I understand : there are no clear recommendation how to write, store and maintain SQL code. I understand that there are no guidelines. But again — this is not a technology problem. It’s still people problem.
3. “Letting people connect directly to the database is a madness” — yes it is. PostgREST has nothing to do with connecting to the database.
4. Fantasizing about portability … Portable application is STILL CONNECTED to “un-portable” database, so you see where this is going.
5. “How is this better than ORM?”. Quick look at the code generated by ORM should suffice. Honoris causa goes to Entity Framework.
6. “On high volume applications I would avoid it since it’s usually easier to horizontally scale web servers than sql servers” — and those webservers are using what to store the data? Also typical database uses 96% of the time for logging, locking etc. and 4% percent for actual data operations
   Yes, thats how efficient it is. Killing database by multiple connections from single network port is simply impossible in real life scenario. You will long run out of free ports. Of course that strictly depends on the SP code quality.
   Performance hint: developers using cursors should have their hands cut off. This should do the trick.
7. “Crazy! Nobody is doing it! people do <framework_of_the_week> instead looooool” —Yyyyyyyyyhhhhhhhh…..
8. “No testing suite!” — we use pg_unit; it’s amazing
9. “No code versioning” : Let me use a small part of my project, just to illustrate how we tackled this. There are hundreds of files in the folders. Rebuilding the database is as simple as executing one line bash loop (I do it every few minutes)
   The loop:

for i in `ls [0-8]*/*.sql`; do psql -U<user> -h localhost -d <dbname> -f $i ; done

It has all thats needed: (1)tables, then (2)junction/lookup tables then (3)views, procedures, triggers and inserts (for rebuilding the database)

It needs to follow the flow, but you need to do the same using ORM models, at least in Django. You cant just randomly import models between applications within the same project.

Section 2 — Junctions is an equivalent of ManyToMany and let us avoid ALTER TABLE to insert references to tables that weren’t created yet during rebuilding.

This forces some discipline but makes entire codebase really easy to understand and follow.

I suggest to give it a shot with your next project :)
Next part in progress :)