0

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?

George Barwood
  • 489
  • 4
  • 11
  • What kind of functionality you usually support with this design ? – latusaki Mar 15 '19 at 11:17
  • 3
    I used to do this. But we just called it "the office DB" because it managed our office. So long as you don't mind being owned by your DB vender it's fine. – candied_orange Mar 15 '19 at 11:31
  • @latusaki an example would be a system for an insurance broker, it allows customers to get a quote online, pay, the system then creates emails and documents the customer can view on a portal. It also handles renewals. I also did a system for an exam board ( managing exams, issuing results etc. ). – George Barwood Mar 15 '19 at 13:07
  • can you tell us what db do you use? – Edoardo Mar 15 '19 at 14:20
  • @Edoardo I use Microsoft SQL server, the thin "dumb" layer is written in C#. I pass the http context to the stored procedure as a table of name/value pairs ( and a code which says whether it's a query param, a form value, a cookie etc. ) – George Barwood Mar 15 '19 at 15:10
  • 1
    Related: [How much business logic should the database implement?](https://softwareengineering.stackexchange.com/q/194446/118878) – Greg Burghardt Mar 15 '19 at 15:35
  • 2
    Also related: [Pros and Cons of holding all the business logic in stored procedures in web application](https://softwareengineering.stackexchange.com/q/158534/118878) – Greg Burghardt Mar 15 '19 at 15:35
  • 1
    I'm voting to close this question as off-topic because it reads like a polling question which is specifically discouraged in the help center. (Does anyone else do this?) – Becuzz Mar 15 '19 at 15:36
  • 1
    How do you handle version control on the logic inside the various sprocs? – Graham Mar 15 '19 at 15:39
  • 1
    @Graham I do regular database backups, and that's usually it. To be frank, the code is mostly extremely simple, although I have an unfortunate habit of getting date comparisons the wrong way round occasionally ( perhaps due to senility... I'm over 60, have been doing this stuff far too long! ). – George Barwood Mar 15 '19 at 18:10

1 Answers1

5

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.

Edoardo
  • 236
  • 1
  • 7
  • There is a free version of Microsoft SQL server which runs fine. I switched to this approach due to feelings of disgust at the complexity of other approaches. I can build a 20 table application in a matter of a few days, and it's easy to maintain. – George Barwood Mar 15 '19 at 15:53