Stored Procedures as a backend

  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

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

5 Python packages you need to know about

Five No-Code Platforms You Should Know About

Every buck counts when it comes to IT infrastructure costs for Apps — Container is the key

How to setup Mendix Studio Pro on Mac

Configuring HTTPD Server on Docker Container

How to Set up Python and Visual Studio Code IDE for Data Science

Anatomy of an Oracle

Infrastructure as Code: The New Normal

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Oleg Zech

Oleg Zech

More from Medium

Pros & Cons of TRUNCATE vs DELETE (PostgreSQL)

NOSQL VS SQL MISCONCEPTIONS

How to deal with a cyclic foreign key constraint using PostgreSQL?

Query Plan in Postgres