[Illniyar]

Logic in the Database has the following issues:

1. it doesn't work well with source control

2. deployments, rollback, replication, synchornization - they don't work very well with db procedures

3. unless you connect directly to the db, then you must have some logic on the serverside, usually you end up replicating logic from the db to the serverside

4. Databases languages (even advanced ones like PL/SQL) are not expressive enough

5.It's much easier to scale out the server then the database (and if you are using Oracle/Sql-server etc... also cheaper), and you don't want your database's cpu to be clogged with logic code execution

6.Unit testing (or any testing) is extremely difficult

7.Debugging is hard and convoluted (also it doesn't usually work inside your IDE)

and a whole lot more.

Nothing is absolute or completely obsolete, but it is considered a bad practice for a long time by most industry professionals.

As far as I know the most popular article about it is:

https://blog.codinghorror.com/who-needs-stored-procedures-an...

[waldfee]

4, 6 and 7 are my main problems with this.

i occasionally have to work with a big application which is essentially written 100% in sql. you simply can't easily change parts without testing the whole thing from start to finish because automated testing at a granular level is horrible. and sql does not lend itself to encapsulation, it does everything to make it hard to break stuff down to manageable pieces

and in sql everything you do is just so complicated, tons of boilerplate stuff you would not have in a real programming language. it's called "query language" after all, not "programming language". so don't use it for that

[collyw]

Its not that the logic goes into SQL that is the problem (that I usually see), its usually a poor database design and a load of code at the application level to compensate for the poor database design.

[amorphid]

If "put your code in the database" becomes a trending fad into which I am dragged, I'm going to write a MyFavoriteLanguage-to-SQL transpiler.

[lostboys67]

All of that apart from 5 is totaly wrong

[mmoroz]

Care to elaborate a bit? Especially, why do you think unit testing and debugging of SQL is not hard?

[coldtea]

>Care to elaborate a bit? Especially, why do you think unit testing and debugging of SQL is not hard?

An SQL returns results directly in tables which you can check in all kinds of ways. You can create any number of temporary tables, with the same schema as your business tables, and check all kinds of invariants.

There's absolutely no reason why unit testing SQL should be harder than anything else, considering a single query as the "unit" of testing.

In fact, that's to the built-in checks, constraints and types a RDBMS has, you are freed from having to unit test all kinds of crap too (similar to having less to unit test in Haskell vs Ruby).

[lagadu]

Well, you'll have to store your mock data somewhere, which in this case means more databases, often on other servers; so you'll have your SPs connecting to another DB in order to access their data.

[jholman]

Care to elaborate a bit? What's hard about it?

[rejschaap]

It's not very hard, it's just impractical. Mainly because a database is a giant bag of statefulness (to put it scientifically).

You need to prepare test data, you need to update and maintain the test data. That is already a big barrier to entry.

The actual testing involves three simple steps: setting the initial state of the database, run your queries/procs, verify the results. This will be unbearably slow even for a small test set. So you start to make things complicated by trying to be smart, like only revert the state you modified, or using SQLite for tests and Postgres for production, or by running the database server on a RAM filesystem, etc, etc.

I've seen a few people go down the rabbit hole and noone came up with a solution I could be happy with.

[lostboys67]

I don't see that writing code to test SPROCs is that hard just have a set of inputs that match all of the use cases including all the edge ones run that through and check that the results on the db are as expected.

Debugging mm possibly slightly harder in that you might have to have a 3rd monitor for Toad or work Manager - but you code your sprocs properly in the first place you should not have that many problems that jump between code and sql .

Just saying its hard doesn't help in that case we ought to still be coding in GWBASIC

[m_t]

What's the solution for source controlled database logic?

[coldtea]

What's the problem with source controlled database logic?

Have your statements (including those that create stored procedures on setup, migrations, etc) on text files, and just load those into your Git or whatever.

[lmm]

The problem is ensuring deployment matches up. It's very easy to end up with subtle differences between a newly deployed database instance and a database instance that was deployed with an old version and then updated. For code you would think very hard before deploying each version as a patch to the previous version - it's easy and effective to just deploy the code afresh each time, with a complete artifact built from a specific VCS tag. It's much harder to do that with databases; the tooling just isn't there and it's hard to ensure that you wipe out previous logic but retain previous data, because data and logic are commingled. You could possibly build a system for this kind of thing, but the standardized tooling just isn't there.

[jholman]

I don't understand what's hard about mass-overwriting your previous stored procedures with new ones.

You're right about non-SProc code; just deploy all of it. Do the same thing with SProc code!

What's tough about keeping all your code in files that start with "CREATE OR REPLACE FUNCTION <funcname>", and just firing them all at the DB (within a transaction, if you like)?

I don't actively advocate putting all the code in sprocs, but I can see advantages. I also don't advocate using PHP, and yet people demonstrably build some great websites with it.

[rejschaap]

Your approach is a bit naive. You will accumulate a lot of crud if you don't drop any function you deleted or renamed. This crud could even set people up for making mistakes, like using a function that shouldn't exist and does stuff that harms the integrity of the data.

[zeveb]

Back when I was writing Python and using Django, I found the migrations system provided by django-south was really good for exactly this.

A migration was a way to roll a database forwards or backwards; there were tools to create simple ones, and one was able to write whatever Python & SQL one wished in order to handle more complex cases. One might even archive off a column somewhere when deleting it, and load it up back when restoring it, if one wished.

Since the migrations were all just source code, they were perfectly well-suited to source control.

It was a really powerful system; I'm surprised that it hasn't seen wider acceptance.

[throwaway1892]

You have an export file in your repo, containing all your stored procedures and part of the deployement process is to export the procedures to the db, updating as needed.

[lostboys67]

scss was what we used I am sure any decent source control system can handle your Pl/SQl or what have you.