Sweet! Thanks Derek!

In the '90s I was doing web development using Microsoft's ASP and MSSQL.
We did EVERYTHING with stored procedures, triggers and constraints.

I guess mass adoption of MySQL, Postgres, etc. turned devs away from that, because the software didn't offer the features. And in my experience, you don't switch language/practices all that often (we tinkerers notwithstanding). Once you arrive to a solution, you maintain it instead of rewriting it.

Thank you Derek for sharing! I will use it in a project and let you know (im also watching your github activity)

Great come back from 90s... We used to do stored procedures for SQL server, but they were really hard to develop and debug... Some of stored procedures where several pages long and it was almost impossible to find a bug :)

How is the situation after 15 years? Is it easier these days to work with SP?

Wow, Derek - what a brilliant application of the insight and a great set of examples! And, as with so many brilliant ideas, obvious in hindsight...

Very timely for me - thank you so much for sharing!

Gary

This is great! It makes so much sense and I really appreciate the fresh perspective on OOP. Personally, it was a practice that nearly killed any affection I had for programming. i still loathe it till this day. It has become a "necessary evil" of sorts. This method s much more dynamic IMO. Kudos!

"Simplify, simplify, simplify."~Thoreau

Superb. Great write up, fantastic examples. Thank you

In regards to Andres comment. I would say mass adoption of MySQL, not Postgres.

Postgres has had a very robust stored procedure language pretty much since the beginning. ArsDigita pretty much used Oracle for it's web layer and when OpenACS came out, it supported both Postgres and Oracle, still does.

The framework that changed the database layer being used for business logic was Ruby On Rails. I got tired of arguing with them about their fragile apps depending on middle ware to run logic.

They didn't get the memo and still feel the database is just a big file storage system. They didn't get that enterprise software might just access the data without using their precious code :)

This is why “History Repeatig” from the Proppellerheads, feat. Shirley Bassey, is my all time favourite song :)

Like others mentioned above, I distinctly remember this database-centric view of the world being a common practice back in the 90s.

I also remember the maintainability nightmares this approach caused in huge code bases with many people trying and failing to collaborate together.

Moving all that logic out of the database was a bliss in terms of code management, collaboration, testing, etc.

Having said that, 10+ years in tech is an eternity and dbs must have evolved a lot, so you got me quite intrigued in figuring out what new development patterns they allow for nowadays (hopefully without the pitfalls of old).

Thanks for shinning a new light into an old topic… it’s funny how our past can so easily become a blind spot: I would never had thought to consider such an approach again before reading this.

I have a few concerns here:

- How do you revision control a database schema? How do you do rollback if a regular expression is borked and tells the database that existing data is invalid?

- How do you unit test?

- How do you scale? If the web tier becomes CPU-bound, you can add more webservers and put them all behind a load balancer. If the database tier becomes CPU-bound... then what? Setting up (and maintaining) replication is non-trivial. Sharding tables across multiple machines is non-trivial.

I've personally worked in situations like these before, where some programmer on the team thinks he is going to be clever and "offload all of this work int he database". End result? The rest of us have a product that's harder to maintain and hard

Enforcing things like unique keys and foreign keys? Sure, that makes sense. It's low-level and fits well with third-normal form. But getting into all sorts of regular expressions and application logic? You're asking for trouble. Please don't do it.
Thanks for the thoughts. It's only me, not a team, and you can see code that's working well for me here (with tests, rollbacks, etc). — Derek

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

True, but maybe that's precisely because it was containing only data.

Once you start putting more work in the database side, you open the door to entropy. Soon maintaining it will be much harder than before.

I guess you're going to figure out that by yourself in a few years. :)

Hi Derek,

Curious about some other aspects of software development that are easy in code but harder in stored procedures:

Logging
Debugging
Versioning
Testing

Any comments on how you handle these?

Thanks,
Dan
Examples of my tests here and here. Debugging macro here, included in most functions. — Derek

Thanks for sharing your approach with us, Derek! This discussion interests me greatly, and I'd really like to hear what you have to say to commenters asking for details - Douglas Muth (#12) and Dan (#14).

I learned to use Postgres from a university DB class, but by the time I got into web development, NoSQL was the new hotness, especially for Node applications. We did a quick speed comparison between MySQL and MongoDB, and Mongo won outright (40x-200x). You've made me realize that a complete speed test would include the backend model code we've wrapped around Mongo, versus the inline constraints you show above. (I'm pretty confident that Mongo + Node will still win the race against Postgres, but that's a hypothesis to be checked).

Providing a way for scripts and other non-server programs to access the database is something we've struggled with. At the least, I think we should break our model code out into a separate database API module, decoupling it from code that only applies to the web server.
I've only heard PostgreSQL is faster than MongoDB but speed isn't my reason. I just prefer the relational table structure. — Derek

almost from the get go Oracle RDBMS offered stored procedures, trigger, views etc.

I cannot remember how many years I spent trying to convince java jockeys that having as much code as possible on the db server was not just easier, more secure, much much much more maintainable, but was massively more efficient and speedy than the usual spaghetti that they turned out.

Hopefully the world is finally understanding this.

Geoffrey

Niiice

Greatings to you from city Mierlo ( 20 km under city Eindhoven) in the Netherlands.

Jan

Is there something similar for no sql, mongo, amazon dynamicDb etc?

Also how have you built this site?
I don't know about those others. But the code to sive.rs is here. — Derek

Ha! Even when you intend something for programmers, which I am not, I take away yet another gem from your brilliant mind: "Simple is unrelated to easy. It is easy to bind yourself to something very complex, and can be hard to build something simple." How inspiring and true for any situation! Thanks

Derek, you on Hacker News :)
https://news.ycombinator.com/item?id=9481211

Nice article, Derek.

While this might not suite many systems, if you are doing something simple, why complicate it.

I like you approach for simple systems, for services (web services) and API's.

Kids please never do it

YES!!

I have two concerns with this and am not convinced:

1) You are now reliant on that specific database. When the next great new DB comes out you have to convert all that code into a new proprietary DB language. If you keep most of the logic in the application level, then switching DB's keeps the headaches mostly to switching to the new DB / application interface.

We ran into this problem which extended our reliance on Oracle for years more than we wanted because of the major code rewrite needed. We now have a policy of no complex institutional logic within databases but still have lightweight triggers, db jobs, etc.

2) You now are increasing load on the databases which are much less scalable. It's much easier to just spool up another application server instance to address higher usage.
Yes this was definitely a decision to “double down” on PostgreSQL, which I've been using for 15+ years and I believe in long-term. I agree with you about the db server load. I'll see what happens if that happens. — Derek

Looong time ago I made applications that used MS SQL Server as the database.

We used to put a lot of logic in the db itself, and had numerous stored procedures that could become quite lengthy.

Downside was that they were hard to debug, and putting changes in version control was fully manual and thus often forgotten.

So eventually we used a tool called 'sqlcompare' that could diff 2 databases and script the differences. Ahhh... memories...

Wow...this is brilliant.

Derek,
As always you're awesome in the fact that you care about others! Speaking of awesome, I wanted to share this awesome Band I found out of Germany:
http://www.cdbaby.com/cd/kingsxecutioner
Thanks again Derek, you ROCK!

A word of warning on database triggers.
Sometimes people disable them, for testing or some other reason, and forget to re-enable them.
I discovered a major blunder of this variety when I worked for Acxiom before.

It is better to explicitly call PL/SQL functions and check the return code.

Yes. I completely agree with your approach. My background is in SQL Server and postgresql. But I work in a python/django environment.

Hi Derek ☺

Good to see you are still programming! You raise valid points, and the comments raise most of the counter points I could make.

I dislike the tendency, post Rails, to treat the database as dumb storage. Especially, ignoring table constraints and indexes, leads to train wrecks. Expecting the application to enforce uniqueness, for example, is dangerous.

Your post invites me to reconsider stored procedures. Thank you, I will. ☺

You have a bottleneck issue with scaling. Eliminated the ability to add caching. Unable to do Unit Testing. Stuck with this technology. Created an environment that is harder to debug.

This is a pattern that we've been fighting to get away from for years. Cloud technologies are the result of getting away from exactly this type of architecture. I understand that you're comfortable with this, can make it work, and were able to create nice patterns for yourself, but I can't help but feel that you're doing a disservice for yourself.

yep, as people have said this is how I used to do things back in the late 90s / early 00s with classic ASP, VB and SQL Server. Stored Procedures, Triggers and User Defined Functions were amazing. For example, I used to write systems that sent out automated mass mails and generated huge amounts of XML data. Without SQL Server's stored procedures and in-built mailer these tasks would have been even more nightmarish than they were.
Since I moved over to .NET MVC everything is done code-side with things like the Entity Framework, LINQ etc.

Sorry, I'm not convinced...

I feel that this only works for simple use cases like you've described, but in that case it is trivial to port such logic from PHP to ruby or whatever else.

By moving logic into your database you will run into another problem – now you can't do some things in your code without talking to the database, e.g. you can't tell whether a given employee entry is valid, or even whether a string is a "valid" email (for your definition of "valid"). Unless you duplicate some of your code back to PHP, which I think we can agree is undesired.

And then there's another problem of interaction with third parties. Say you need to make an authenticated API call to a third party to validate a username. You can't reasonably do that in PL SQL, so you'll need to keep that logic in PHP, and we're back to where we started, except now we have another problem: our validation logic is now split between PHP and the database.

Which brings me to the next point – ultimately, you WILL have SOME logic in PHP (or the language of your choice). You aren't going to write the whole web application in PL SQL. I think having ALL of your logic in one place (PHP) is more Simple than having it split in two.

Lastly, if the portability of data layer logic is a problem you want solved, you can always write the web application in something as trusty and reliable as Postgresql itself, say C++. Or just write the data layer in C++ that talks to the database, and call it instead Postgresql from PHP.

Very nice presentation of idea Derek!

This concept is not new. We had this kind of setup in our school (msuiit.edu.ph) apps since 2002.

This is only applicable of your app doesn't support multi-database setup.

Moodle (moodle.org/) for example, is an app that supports multiple databases namely PostgreSQL, MariaDB, MySQL, MSSQL and Oracle. Moving the logic to the DB would mean maintaining a code-base for each DB.

Thanks for sharing Derek, that is quite thought provoking!

Not so fond of database procedures and functions, but this is true that it reduces the repetition in the code.

Sylvain

At past, I asked my brother "Should we use database?"
My brother replied "Of course we should. How can we build 'internet software' without database!"
Then I said to him "I didn't use that one. Simplicity is for customers, not for us as engineers n scientists."

Hi everybody,
I'd like to share some high level experiences I had on this topic.

Things usually have many faces or angles from which to approach; and in most cases, I've found that the truth is a matter of perspective.
Technology evolves, sometimes to solve unsolved (or very complex) problems, and sometimes just because it can or is simply pushed by commercial interests.
Take for example Object Oriented Architecture. It was born to model things that could not be modelled (or were too complex to model) with the available
paradigms, and also to simplify things, made them reusable, etc. etc. etc.
Nice, in theory.
After a while and some reality tests, it started having different issues and had to be simplified and improved, and started evolving itself into a different
kind of beast.
The same happened with decentralization of hardware resources, going from mainframes to networks, to distributed layers, and now, at the highest technology end, starting the return to huge and insanely powerful individual machines, running everything inside.

In some cases (sometimes core issues) technology is like clothing, and so through the years, as we have seen Oxford pants comming back againg and again ;-), we will see old technology trends say "hello".

From a little bit more technical point of view:

Talking about performance there is little doubt about the fact that executing app logic inside the database is better; it saves roundtrips, bandwitdh, and intermediate layers that in some cases do almost nothing (but add complexity) or are redundant. Also sometimes propietary SQL and embedded database languages have built-in functions that optimize the gathering of information, making them very powerful tools when talking performance.

Flexibility/Portability has been a long lasting argument. The fact is that (as has already being said in previous comments of this thread) if you stick to one programming language/framework you may have a decent amount of portability, in case you want to change for example your database; otherwise you will have to rewrite almost everything.
However, even when staying with the same programming language/framework, as the application/middle tier layer nowadays is very complex, transparency and portability, as sold, don't exist, and you will end having to do lots of stuff to make your systems work once you change the environment (say,the database, middleware, even sometimes the hardware).
The more complex the application/environment the more work you will be faced to do. There might be exceptions, but that's just what they are, exceptions.
More or less the same thoughts apply when living for a while in a monogamic relationship with a single database, and then trying to move to another one.

One "almost truth": nobody uses pure/standard things, (standard SQL, standard Java, etc), because it is impractical, and add-ons are needed to make them
efficient, and competitive with other proprietary technologies that perform better. So, goodbye 100% transparency, portability, reusability, etc.

To the fact of scaling solutions at the database level, the latest tendencies, specially when dealing with huge volumes of data (I'm taking about high end solutions) are to move app logic load to the database/storage layer, and optimize data gathering at that layer in order to return reduced data sets that are easier for the middle tier to handle.
At present you can have database clusters, that are scalable in a plug and play fashion, as well as database cache to speed up things even more; you need more power at the database level, you add a server, you have it. (all you need is just ... a lot of money ☺, but the technology is available).

Regarding team work and organization; well, as almost anything in life it is a matter of choice.
You will certainly find more tools and practice, in organizing developement team work in environments outside the database; and to replicate that when working
inside the database will take a considerable effort, but it is possible.
All you need to do is weigth the importance of each aspect (ie. organization/maintainance effort, vs performance/simplicity), and make a choice.
Sometimes you can have it all, and sometimes not.

I've been in all sides of this coin (like 5 or 6 ;-) ), and I have experienced all the problems I could have ever imagined, and the ones I haven't ever thought were possible.
I've seen (from very close) multi million dollar projects crash to the ground in legendary ways, and technology was never the cause of it.
At the end, in most cases, things could have been done in either way (inside or outside the database), and the real problems came from doing things wrong (no matter the tecnology they were using)
So despite the natural strengths and weaknesses of each technological trend, the natural and real enemies of any project are, poor management, poor application design (very common), poor programming, poor knowdlege of the underlying technologies, poor technical cooperation/communication among technology layers (developers, database admins, OS admins, storage admins, etc; also very common)

See the pattern? Human nature ☺

There is also a whole commercial aspect over all this highly technological world, which is frequently more influential on technology than technological issues themselves. But that's for another thread, I think.

Well, I hope this helped in anyway, and if not, at least I hope it was not too boring to read.

Best Regards
Ricardo
Thanks for the thoughts, Ricardo! — Derek

I'm not convinced that moving more logic from application code into a particular database is a good idea. In my experience a better approach is to create an API in some full featured programming language that interfaces with various different data-stores. Locking yourself into a particular data-store is more problematic in my opinion than duplicating code outside of the datastore in places where it may be required.

If a shell script or some other language needs to access the datastore in some way it would simply do so through the API and not directly through SQL. This has the advantage of centralizing your business domain logic and allowing any client that speaks the API's protocol to interface with the data. Additionally you will be able to quietly swap out the implementation of the API without impacting any clients of the API.

In my opinion it's a poor architecture decision to use the database layer as an integration layer and instead one should have an application layer on top of the database that removes the need for other applications to know the details of the data-store.

You are 100% correct

Derek,

You're great! I'm not a programmer, but I can do simple CSS and HTML. I love what I can glean from your post here.

It feels like a timely compliment to my current proof of concept project, which is essentially a database. I've been testing the Participants Database plugin for wordpress, and been communicating with the developer about enhancements. I think I'll forward this post to him to see if this sparks ideas for his approach.

If you're ever in the US, Bend, Oregon in particular, let me know and I'll buy you lunch. Would love to talk about music and possibilities for change, etc.

Thanks again!
John Kelly Hughes

Heresy, but I love it! Especially if it's all JS at the code level. You effectively have 2 things to learn: pgSQL and JS

I wouldn't wanna work on a huge codebase with 50 developers this way, but for smaller things, modern methods really feel like overkill sometimes.

Like most things with extremes, the truth is usually somewhere in the middle. I agree with you wholeheartedly about leveraging your database properly - especially with PostgreSQL because it's capable of so much more than dumb storage.

Using PostgreSQL for CRUD and letting the app do everything is like getting a Tesla Roadster to make grocery trips. It can do it but it's a waste.

At the same time, doing EVERYTHING in the database ends up creating a lot of the same problems that you'll run into by doing everything in the ORM (which are awful). Usually the bias comes down to this:

1. A web developer that knows and uses language X wants everything done in language X. Putting all of the data logic in language X ensures moving from language X is going to be a pain in the tail.

2. You'd like the see the same bias from database guys, but the unfortunate reality is that switching database is hard no matter what. Whether there's logic there or not, if you're switching databases it involves moving data, stopping data from coming in, having code to move that data, and if there is a problem having code to move that data back. That's without any logic in the database.

The above to 2 things are why database choice is the most critical decision for your application. Switching web languages can be EASY comparatively. Switching databases is going to be a pain no matter what.

The issue is that lots of parts of an application need to work on the same data so it's a much more natural function to lets multiple systems access a single database with different code. Without that you're ensuring yourself of eventual need to sync data between application parts. You're also guaranteeing race conditions in almost every case.

Likewise, development and team workflow for version control, etc are valid concerns. That's a main reason why you need SOME codebase to be primary owner of your database.

With PostgreSQL, Rails is an ideal candidate and I've actually broken down a lot of the reasons why here:

http://www.brightball.com/ruby-postgresql/rails-gems-to-unlock-advanced-postgresql-features

Rails will let you use migrations to version control the database AND the procedures / functions AND let the database be the source of truth for your data.

So, we do this with all of our applications and have for 25 years. The biggest benefit, which is not listed, is that you can change technologies without having to rewrite the business logic. However, if you do this, please, please, please keep in mind performance implications. We have noticed that developers often will forget that it is a database and will do crazy things like run functions in where clauses on tables containing 1million rows.

We've been slowly migrating over to REST services and separating the business logic out separately.

Another great post.... It's really complex to create something simple :)

What you describe is a solution for small applications, running under small loads, and implemented by small teams (or in the case of yourself, no team whatsoever).

Everyone else has mentioned the issues with this approach, but I'll add another one: brittleness.

Dealing directly with tables and relationships and with structs of field names/values ties you directly to those table names, those relationships, and those specific field names.

Change anything of significance on the backend, and you've busted your application. APIs and data layers exist to provide known interfaces to your data.

In fact, pretty much everything you deride (ORMs, etc.) exist because "doing it in the database" simply wasn't scalable, both from a physical and from a developmental standpoint.

Hei Derek,

I wrote a little blog post to comment on yours, here https://usingimho.wordpress.com/2015/05/04/design-space/

it would be interesting to know what are your thoughts about it.

Best,

Andrea
Very cool. Great points. — Derek

We went through a similar paths with PHP, Rails, PHP, then back to Ruby for some new projects, and most recently client-side JavaScript (designing a "SPA"). I've used relational DBs and pushed as much of the logic back to the DB (aka "stored procedures"). Most recently, I explored some noSQL (via Firebase) ..and, what a liberation.. (if you can afford it i.e. not a transaction-based or large-scale project). It's a beautiful way to store and grow your data, very organic and holistic. And you are so close to the actual data, no tables or rules or restrictions between you and your data. You should check it out, Derek.
Interesting! Thanks for the recommendation. — Derek

Hey! Thanks for this. I just finished a course at Udacity that glossed over a few of these concepts.

My question: is there ever a time when it's better to program for the database via another language/framework rather than program from the database?

For example, what are your thoughts on the extreme convenience of libraries like SQLAlchemy? I don't find it very complex. Is it just the speed of launching a product?

In any case, thanks for sharing your thoughts. I'm interested in hearing the pros/cons more since it looks like the topic is somewhat opinionated.
I think for most people, it's best doing things the normal way. I just love my PostgreSQL database, so I prefer this way. Either way can achieve the same goals, but the popular way will get more support. — Derek

This post made me think of what would really be ideal is something along the lines of micro services-style structure. That way, when you want to change languages, you keep the stuff that already works well and doesn't really need to be revamped.

I agree that the problem of embedding your code in the database is that if you change databases, which many companies do, you'll have to change all your code. So, to completely decouple you would need to do something like microservices. I'm not really sure how they work though. It will be nice to get to the point where I could understand it better. Of course, microservices have their own drawbacks.

In the real life the are a lot of issues by implementing all the business logic with stored procedures. The fact that relational databases have become so "smart" is due to historical and business reasons and not because that is the right way to do things.

When you implement your business logic in the client code outside the database, you are able to distribute your code, it's easier to version your code, no mention that you have a solid way to test and source control ... your code.

Also, the article refers to only one side of the coin (DB never change and programming language change every other year) ... mmm that could be the case for a few teams. But I believe that more than often the situation is all the opposite:
- Company X built a system that transparently talks to any Relational Database -
(and notice that I say Relational because they are not the only kind of DBs in the market anymore).

But, despite that, I think that still the point in the article is valid:
- If you are developing in a closed environment
- If you know (%200 sure) that the DB will never change
- If there's a DBA god* in your team** who is in charge of every little detail related to the DB or if your team is really small and anybody can put their hands in QA and Production instances

then YES, definitely implement the BL on the DB side

* and I don't like those
** if there's a DBA, he's usually never in your team

Thanks Derek and commenters for discussing the pros and cons of database coding. My approach is similar to Derek's, and I'm glad others pointed out the scalability issues on my horizon.

I'm probably better off moving my stored procedure logic to app code... than trying to duplicate my db server and getting everything to sync properly.

I have the same concerns like others in the comments.

This approach is OK for small projects that will never require any special attention.

Of course I agree with the logic of simplicity though.

But if you want to learn about or even build state of the art software this is a very good start: http://www.reactivemanifesto.org/

a good read, somewhat related to your post - http://thehelsinkideclaration.blogspot.com/2009/03/window-on-data-applications.html

Hope, you and curious readers of this blog will find it useful.

Thank you

Thanks god, there is one developer who is not hypocrite, and tell the true, everybody talks about layers of code (binaries) but all of them end up using all logic in DB, GOOD ARTICLE, THANKS for write about common sense

I disagree totally this time Derek. But this is a free world.

I think the biggest thing is Git (or other version control). I don't know how to do version control on code within a database? Do you use it? How do you go about it? Also, do you use PL/pgSQL or another language?
See my links above to my code examples, all kept in Git. — Derek

It's interesting, and certainly there are a lot of things that a database can do faster than scripting languages (mostly because they're written in C or so and know their data structures).

However, as I see it it's easier to switch database systems than programming languages. As you say, if you decide to use a different programming language, you have to rewrite everything from scratch. Same if you use a different database system and have your code in the DB.

We've used 4 different database systems for our application over the last 10+ years (and investigated and deemed it possible to use a 5th one). We've though never switched away from Java - iterated from 1.4 up to Java 8, yes. Changed from-end and back-end techs (EJB1, 2, 3; custom framework, Spring, JSP, JSF, ...). And we've never had to rewrite _from scratch_. We've rewritten, refactored and more, but the core logic was always preserved.

We're also using H2 (previously HSQLDB) for integrated testing. Do I want to make an effort to make sure my trigger, procedures and more are compatible with 2 (or more) database systems? I don't think so. Has someone made abstraction layers to shield me from the sublte differences between database systems (when using it as dumb storage)? Oh yess! Do we have the freedom to use a traditional SQL system and a NoSQL system? Yes, because our business logic is implemented in a language that was made for business logic.

And about testing: can I fire up a private (or better: in-memory) PostgreSQL instance? How long does it take to create all the trigger and procedures to start testing? Starting H2 is so fast it's not even noticable.

Just my 2ct, hope you understand.

You are SO amazing, Derek.

Nice Write up.

I... think... that this is satire? It's almost point for inverted point the argument the .net world ran about getting people to stop putting all logic into stored procedures ten years ago.

If the idea is that sometimes that makes perfect sense, it's a point we'll taken.

I'm not sure what to do with this if it's serious. There exists literally thousands of pages of wiring on why this is a bad idea.
You don't have to do anything with this. I wasn't telling anyone to do this. It works well for me. — Derek

Derek I salute you. I've long believed in what you're saying and you have just filled in the gaps for me.

I'm all for this except when you need to scale massively.

But how do you test this?
First I made “fixtures” (example data) to test against. Then I write a little test for every PostgreSQL function I create. To test my inner functions I use these tests. To test my API functions I use these tests. Browse my code at Github to see more examples. — Derek

Great commentary here. I recently played around with Postgrest (which throws a restful api around postgres tables/views) and managed to throw it into a hackweek project on an existing DB. It worked smoothly once we made the auth/CORS/csrf/etcetc work.

Postgrest is here: https://github.com/begriffs/postgrest it's heavily opinionated and I don't think I'd use it from scratch, it's really neat to throw onto an existing DB and bang have a rest api. Once I get more comfortable with postgres views, etc I might get back into it.

Another beauty of postgres / tables, etc is that it makes it easy to break out services where your JOINS or many to many tables are. That's a bit conceptual, but I think it makes sense if you know your keys you can move the tables to another service/server anywhere and work with the returned data there. Decomplecting (which I think is just a word for simplifying) that forward motion.
Yeah! Thanks for linking PostgREST. That's a really inspiring project. I really admire that guy and his writing, too. — Derek

I do love the idea of fully utilizing advanced database functionality like stored procedures, triggers, etc. Simplicity is always best.

Scalability becomes the issue here; the practices laid out here work best in a scenario where the IT professional had db admin privileges and is also the service or (web) application developer. At the enterprise level, these are typically different roles, and multiple teams (each with their own domain requirements) are requesting raw data from multiple shared data sources.

When we scale up to a many-to-many team-to-datasource scenario, db admins are having to tackle performance issues, lock contentions, backup and storage (and their failures), and a vast number of other issues. Add to this team requests for advanced functionality, or worse, teams learning and writing this functionality themselves, with no adequate testing/migration/integration strategies currently existing in the database world, and the db admin is suddenly facing a great nightmare.

If it's possible for a relational database (admin) to allow teams using said database to build and maintain triggers/functions/views/etc for their respective domains AND separate their domain from others, then it's ideal. But db admins responsible for setting this up seem to always have greater maintenance concerns.

The first step is to realize that your CS professors are not in industry because they can't or don't want to be productive. People have to unlearn what they learned in school, and that can be very, very difficult.

I do back end applications. I put as much as possible in the database. It does so much for you, and so much easier and better than you could ever do for yourself. Considering that billion dollar organizations stake their reputations on RDB code, I trust them more than I would trust myself to build the low level code necessary for database applications.

I’m fairly new to database and the code that interacts with it, so I find this topic very interesting, how to structure a database/software system. I agree about assuring data integrity by setting up the database to do so. But don't you usually have to push those rules into your external code since ultimately you have to make sure the end users enter good data? For instance, I am working on a project where I import data from XML files, and I have pushed as many database rules as possible into the schema files. This gives a way for users to check their data without having to deal with errors coming back from the database, a database that they do not have direct access to anyway. The rules in the database are primarily useful for continually validating that external code is sending good data. This is a maintenance issue in that changes to the database rules require changes to the external code. But at least if you miss anything,the database will kick back errors, and all external code will be consistent. Is this viewpoint reasonable, or am I missing something?

Hey Derek,

This looks a bit like an MVC framework, where you've implemented the Model part in SQL. Either way, separating the data Model from the View and Controller code seems to make sense.

How do you deal with giving useful feedback to invalid inputs? Presumably you'll still have to recode the input validation (e.g. email @ format) in the front-end, so you can tell the user why a value they provided is wrong rather than give a generic error?
Sorry I haven't thought about that yet because I haven't come to that yet. ☺ — Derek

After 9 years programming this hit my mind, i realize i have changed a software twice but the DB Sql.

Good article to develop durable systems.

Interesting approach, am going to try it out. BTW, there were some good comments here but I can no longer see them!

I think it depends, in general, but in your situation, it makes sense. It is a difficult decision if you are starting with new code and a new database. Essentially, you have to hedge your bet on which asset is going to be durable over time. This reminds me of the advice from Thomas Kyte who wrote one of the best Oracle books back in the 8i days - Expert Oracle: One on One from Wrox. In those days, our database was the durable asset until we replaced our custom database was replaced with a COTS solution. At that point, we didn't have any choice other than to put the logic in the integration code because we could no longer modify the database.

Like most issues of any complexity, there is a lot of nuance, and context determines the best answer at a given point in time.

I like the post. It is definitely not what you will here most people say these days.

So..... I know nothing about code, but I thought I would send your blog to a friend of mine who does it for a living. I was not prepared for the S&*tstorm response I got. I understood none of it, but thought it might amuse,annoy, or inform you so here it is:
It's a common philosophical argument as to whether the concept of tiers is eroding in web development.

The whole concept of a database is changing, but once you need or want a SQL/data back end, asking MySQL to act as a business logic tier is totally fail in my book.

Iterations, calculations, logic constructs (for, if, while) are all faster in the middle tier, your ability to cache and apply object methodology is generally stronger too. Databases do the following things really well:

Extract data from a source.

Transform data into desired formats and constructs of data. Monitor and record transactions on said data.

Load/Present that data on demand.

If you try to extend a native database's environment beyond this, you are asking a triathlete to play football, he might be decent at some of it, but chances are it's not exactly what he's designed to do.

I can prove this. Take basic iteration, storage of static meta-data and constants and apply something like, tracking a baseball game's progress.

Write a database-only engine, where every logical call (say do I need a pinch hitter now late in the game), generates a request to your end tier, your most vulnerable tier, the tier all hackers really want most of all: your data.

Now write the logic in a middle-tier component business logic element, or if you prefer a real WebAPI using oAuth2. You will blow the database layer out of the water AND your database layer overall improves because it is servicing less requests.

So for example, in our baseball analogy, your business tier doesn't both the database over the logic of determining who the starting pitcher should be, or if and when a reliever should be pulled, or whether a switch is necessary.

The database is granular enough however that it wants a physical record of what happened in every at bat. As your business tier determines how the at-bat will go and what the final result is, then it can inform the database of what happened.

The transaction can be now greatly tailored to. Because we know this physical element is a HIGH INSERT, LOW READ area we can optimize this in awesome ways that databases are designed to accommodate.

Your insert speed multiplies almost exponentially.

I change my mind, it's not philosophical, heavy business logic in native database environments beyond basic ETL needs is not unnecessary and does not scale well.

The only caveat to that is if the database environment generates compiled code and essentially operates like a middle-tier should, but then move it if you can, keep your database segregated and optimized for data.

The reason why NoSQL is such a big thing is because people realize databases are LOUSY at jagged structures and concepts. All formats of data be it OLTP, DW or OLAP demand some kind of structure, NoSQL essentially treats everything, any object or value in a configuration you decide as an indexed name/value pair and then maps and extracts what it can from those objects to match that index to various key words and search algorithms.

But even that isn't business logic. Finding data, sorting data, presenting data, archiving data, backing up data, making sure data is accurate and has integrity, is what databases do, deciding if you should kick dirt in the umpire's shoes to give your team a morale boost is a decision best left for a real business tier.

My humble opinion. MANY, MANY developers disagree with me, but that's certainly how I like to design what I work on.

But I am at 6.6 logged at-bats on just my junk hobby-code and it purrs like a kitten, because I don't both the poor baby with silly decisions like, "should I start a lefty or a righty for tomorrow nights game and should I take a defensive short stop because one of them is a ground ball pitcher?".

The database can get you all the statistics you need to help fuel that decision, but to iterate through it and analyze that data, please give me a properly designed object model in the middle tier.

The more your business logic doesn't care what database it works with and the more your database doesn't care what business logic it works with, the better your system is.

But again, many, many people disagree with this.

This sound intriguing!

It relates to the complexity I've witnessed myself with languages and structures that are suppose to make things simple...but don't.

I've been programming professionally for over 10 years, this is the first time I've seen an approach to this, and my brain just exploded!

It makes sooo much sense, I have felt the frustration of re-writing the same logic, over, and over, but I've avoided DBA style database programming because it is, quite frankly, "not sexy".

I thank you for this. I have a project that can benefit from this technique and it will make my life so much easier!

I also find it very moving to see that this concept of simple/complex vs easy/hard applies to a much broader life view - as you used it in a response to a question on Tim Ferriss' show.

Thank you!

Hi Derek,

do you have any recommendation what book to read to learn how to work with a SQL database such as the things you have described above? Like for someone that knows a just a bit about web programming (Html, CSS, Wordpress).
Try Head First SQL. Sample it with the free PDF downloads on the right-hand side of that page. If you've mastered SQL and want to dive into the stuff I describe above, try PostgreSQL Server Programming book, or really the PostgreSQL documentation is very good. — Derek

Yes Derek, this is effective and clean from the backend's (php, ruby, whatever) perspective. I recently took some time to learn it and I like it. When the queries get more complicated, database functions will eliminate a lot of roundtrips between the backend program and database.

The downside: if you ever wish to move away from postgres, it might be more complicated because each database management system tend to have it's own supported procedural languages instead of the PL/pgsql. Although they might be closely related, the standards are not quite the same.

Somewhere there's an article from a guy who got tired of re-writing his database logic every time he changed databases and realized he could just put that stuff in external code :)

There may be some situations where this would be fine. Your situation is probably one of them. The interesting thing about programming is there are not many hard design rules. You can find reasons to break any rule. However, a good analysis of the problem you are solving, will reveal a sound design. In many cases, storing business logic in the DB is not a sound design since it can be detrimental. Let me provide one example.

One company I worked for began placing more and more logic in the database. After years of it, the Oracle DB started to under perform. It was too busy "processing" instead of retrieving and storing. Eventually the hardware had to continually be updated to handle the load, which cost an arm and a leg since Oracle licensing is ridiculously expensive.

This logic eventually had be stripped out as it was putting far too much load on the DB. There are more reasons than just putting load on your DB. Debugging is a nightmare on the DB. Also, most DB's have antiquated languages for their stored procedures causing you to jump through hoops (although Postgres is impressive in this area). It's also harder to search stored procedures, etc.

So, yes, in your situation, I can see it working, but be careful thinking this is a solution for larger applications with multiple programmers without a more careful analysis.
Thank you for the wise and thoughtful comment! Great points. — Derek

Food for thought wise this makes sense.
For a really small hobby level project this makes sense.

But as soon as you start thinking about apps and projects the idea breaks.
The reason no one puts all their business logic in database is because databases are not for running business logic.

Think of all things that you will miss out on.
1. django management commands
2. TDD
3. Logging
4. Service oriented architecture.
5. Any sql injections you missed you are dead.
6. Session level logics.. cookies, ui data caching etc etc..

The list goes on an on...

The paradigm proposed by the author if it gets accepted will re-do what oracle tried to do with oracle forms back in the day. You can have stored programs in db but it makes the whole application less flexible.

I just heard your interview on Software Engineering Daily (Oct 2015) and it was definitely mind-expanding. It also lead me to this article. THANK YOU!!

I don't necessarily agree that this is right for every project/use-case BUT I've seen enough terribly normalized, slow, corrupt, unreliable and failing business solutions to know that putting ALL business logic in the application layer is not necessarily the answer either.

I've been looking at postrest and postgraphql recently and they have been an eye opener in that I've been able to prototype 10-100x faster. Whilst pure development speed is obviously a big win, I've been treating data validity separately to business logic (business logic = 'given an input, what should happen and what should the result look like?'; data validity = 'does this result that I am being asked to store look like I would expect it to?').

I feel so much more confident in this approach since I have seen so many data related bugs created by naive programmers over the years - and of course the post-gres/rest/graphql stack is experimental for me - its nice to feel that there are some guarantees.

What am not quite happy about yet:

- testing. Either a team has to use postgres locally or integration tests have to be more extensive (is this a bad thing though?).
- continuity/change tracking. I've been using migrations but I've always felt these were limited and error prone, especially where data migrations and green/blue/zero-downtime-deploys are factored in.

I doubt there is any magic bullet but I'll be playing about with this approach - thanks for the insights!
Great point! Thanks Adrian. — Derek

Hey Derek, have you found this approach to work well with Elixir? I know one of Elixir's primary benefits is its Erlang/OTP underpinnings. Specifically, if you've used Phoenix, would moving code into database functions affect a Phoenix web app's ability to scale?

I'm excited about this concept - thanks for sharing!

Chip
Ah, sorry I haven't yet. I'm still so familiar and happy with Ruby + Sinatra, but if I do something that needs scaling past that, Elixir is where I'd go next. (Not sure if I'd use Phoenix - probably just Cowboy + Plug.) — Derek

Checkout PostgREST.

Iḿ surprised I did not read a single line in the comments about the NEWSQL initiative(CockROach DB for instance scales,and is much faster than PostGreSql);
Most of the Database bottlenecks are related to the way they do things, and not about what they do.
https://www.youtube.com/watch?v=uhDM4fcI2aI
Of course for no tabular data,noSQL is the way to go.
I havent been programming for 10 years,Iḿ thinking about doing more or less what you did,but in a way that some constraints are propagated to the client side,and so does meaningfull validation messages.
Iḿ also concernead about versioning,Iĺl read about you way of doing this.
In fact there is a lot of hipocrisy about the subject,people are trading performance for "Database freedom",but the vast majority of times costumers will pay for that(peoples time,high energy,cloud and so on bills), just because you the software producer can target more databases.

I always wanted to do smth like this but the database syntax is hard and every tutorial says its bad practice.

Also if you have not db logic it will make you have logic in 2 places, and so I understand why there was shift to treat db like dumb storage.


In next pet project I will consider making logic in db.

It's an interesting approach and has some advantages for really simple use cases. I personally really don't like coding in the database. First there are amazing code editors that make my life easier and coding directly in the database costs a lot of energy for me. Also I miss the overview and readability that languages like ruby in combination with rails give me. This makes it far easier and less error-prone to change existing code. Also managing code using git, having the possibility to run automated tests on my code before deploying, working in different branches, deploying a staging environment,... are really really helpful possibilities that make my life easier and prevent problems and a lot of headache ;-)

In my opinion OOP brings a lot of readability and makes it easier to understand code that you haven't seen for a while or that is completely new for you.

It took 5 years for this post to convince me to put business logic in a database.

Modern IDEs make working with database code easier. Check https://www.jetbrains.com/datagrip/ or any of their IDEs.

I've built a little tool to make my development similar to using modern ORM frameworks. I'm even more productive with it than I was with ORMs.

Here are some ideas how to simplify SQL development: https://postgresapps.com/builder
Very cool! — Derek