An approach to writing database applications [closed]

Question

I'd like to share my approach to writing database applications for small businesses. It's possibly unconventional, but it's something I have been developing for nearly 20 years.

The approach I use is to place ALL the application logic in the database, with only a thin "dumb" layer which passes http requests to a SQL stored procedure (the procedure is selected based on the request path), and sends a response back to the client based on the result sets generated by the stored procedure.

The advantage of this approach is that changes to the application need only be made in one place - the database, and if I need to look for something, I only need to search the database code. I also only have to work in one language, the thin "dumb" layer very rarely needs to be modified, and is only a couple of hundred lines of code.

In addition, most of the handling SQL procedures are not coded by hand, instead they are compiled from a simple database specification, this handles adding, editing, listing, showing and finding database records.

My question is : does anyone else use this approach, is it a recognised technique with a name?

Answer 1

I started writing this as a comment to @candied_orange :) I like the expression being owned by your DB and that's what I'd call this approach, as soon as you trust your data stack I think it's fine.

It was a discouraged practice a long time ago due to the general prevalence of commercial db's with huge licenses fees, so there was a tendency at the opposite approach (concentrating all the business logic in another layer): to treat a database as only data, pure and simple. Countless of tools have been created aiming at creating a portable "transparent" layer that would render the switch from database A to B an easy task, in reality that switch would rarely happen.

Nowadays databases are becoming more and more open and able to outperform any business logic layer you would set up, projects like PostgREST will use the database's keys and constraints to build and serve a REST Api without writing code.