Hacker News Reader

Comment by SulphurSmell | Hacker News Reader

>business logic is put into stored procedures

This is a double-edged sword. I have seen massive business logic baked into stored procedures...so much so, that the applications themselves are rather slim. If this stored procedure code is properly versioned and otherwise managed, this is not entirely bad. If the data model is sound, I don't worry that much...stored procs vs 100KLOC of Java code? I can tell you what is easier to migrate. The other side of it is that stored procedures can also serve as an API into the actual database. I built a system once (90's) where the developers never accessed the actual database tables directly. They always called stored procedures. The advantage here was that we could tune the snot out of the database without a code change/build/deploy. It also allowed the DBA some control over poorly written, runaway queries. YMMV. I think today I probably would try to keep the database as vanilla as possible.

View on HN

> YMMV. I think today I probably would try to keep the database as vanilla as possible.

Sure, YMMV.

In any non trivial dataset a lot of fields are effectively computed. For example merged entries: in order to get correct and whole data, one needs to consult some merge mapping, which can be easy to forget and tricky to get right - you have more than one relational identifier. This is not strictly business logic, but rather data assembly logic.

Similarly, a value can easily be spread over multiple fields (and tables) and it is crucial from data integrity standpoint to always update them in tandem if applicable. Again, this is very easy to screw up in client code, because the hidden relationship can be non-obvious. On one hand this is business logic, on the other hand violating this implicit relationship will result in non-agreeing data. This occurs for example when data represents parallel states.

Effectively, stored code acts as some kind of gateway API stored concurrently with the data. Sure, some peculiarities can be implemented with functions and triggers, but IMO those are just different sides of the same coin. In the end really depends on the dataset and what it represents.

Database stored code is a tool. Used appropriately it solves problems, used inappropriately it causes problems. If I were to design a database today, I would too try and make do without stored code, but would not try to twist data model so that it fits the relational model of RDBMSes.

Also an old timer. I’ve gone from mostly complex app and no DB logic to mostly heavily optimised DB and lots of procs. They protect the DB, make carefully crafted interfaces available and allow changes to the implementation.

Except for eg infinitely scalable cloud data stores like Google’s, or for ML where it’s just massive data and you need a dumb-ish store of many GB of parquet.

>protect the DB

I share this sentiment. The apps will come and go, the real value is in the data. If the database can cover its ass, I am less concerned about ham-fisted developers randomly messing it up with ill-conceived DML. It's not that they are malicious...it just happens. I have seen devs that code in Erlang, Haskell and even Assembly...run in terror at doing some SQL. It's weird. Trust but verify. And hire persnickity, passionate DBAs.

> and allow changes to the implementation

From my perspective this is a negative. You don’t want your queries to change after deployment.

Procedures are also quite hard to type.

If I publish a stored proc as an API, it leaves me free (within reason) to alter the actual SQL supporting it. Like modern API design, as long as you don't remove functionality, then change it as required.

A colleague of mine has a very strong opinion that any candidate who mentions using stored procedure in an interview without immediately disparaging it is an immediate no-hire. I sometimes wonder what kind of experience formed that opinion. (I personally never worked at a place with significant use of stored procedures.)

Some asshole DBA probably hurt his feelings way back.

If you have DBAs with organizational power and their shit together, they tend to ask awkward questions and tell you the baby is ugly. It’s easy for developers to do stupid shit and blame solar flares or whatever, but stored procedures are the DBAs realm, and the DBA knows who gets blamed when it blows up.

Like anything, there’s ups and downs. But if you can commit to a DBMS platform for the life of the app, there are compelling reasons to use it, even if it pisses off the devs.

The basic idea is the same as in the article: Data is more important that apps and stored procedures really are just app-code.

Stored procedures have the downside that they often work only in one vendor's database.

> Stored procedures have the downside that they often work only in one vendor's database.

Doesn't really matter, because no one is writing database-agnostic SQL (unless that's part of your product). Any non-trivial implementation is going to require the use of proprietary SQL.

It has always amused me when developers try to make their application's database layer vendor-agnostic. Not only does that restrict you to the most vanilla dialect of SQL imaginable, it's also a pointless exercise generally because it is orders of magnitude more likely that your app will be replaced and the database kept than the other way round.

It was much more important when software products were sold to companies that ran their own databases. You didn’t have the luxury of choosing which database your app would run on so half the job of a software dev was making running it on many different db so you didn’t lose the sale. It’s one of the many reasons saas became the dominant way to sell software.

Once you control the deployment environment it largely makes very little sense to spend much time on db agnosticism. But that’s also true of many other things that have left vestigial forms in our software today such as IOC containers, pluggable log libraries, configuration files instead of configuration code, etc.

it makes sense in a few cases. keycloak and other middleware are smart to support several RDBMS.

Even if you think you use database-agnostic SQL unless you actually run tests with multiple DBMS you cannot be sure. One can write code which depends on some DB specific behavior unintentionally, Hyrum's Law [1] works for databases too.

[1] https://www.hyrumslaw.com/

Exactly. Isn't it a sad state of affairs that you can not write plain pure standards-compliant SQL and be done with it?

It's a bit like if you wrote C and could only compile your program with Oracle's C-compiler. Where is progress in SQL standardization going?

Its not only about standards, but also about tools to check that you are actually following them. It is easy to write C code which will compile with one compiler but not with another. That's why if compatibility is desired it is better to compile code with different compilers in CI.

In theory we can use a tool which tells if you SQL is ANSI compliant, but this would not tell if your code expect only standard compliant behavior from the database. E. g. some RDBMs are flexible in which formats they accept datetime strings and some are not. SQL can be standards-compliant but code still can fail with one DB and work with another.

It is not to say that standards are useless. It much easier to port an application from one DB to another if a developer was trying to use only ANSI SQL. Or you can write an application which can work with multiple RDBMs without modification but like with almost everything else - if you haven't tested it you cannot be sure that it works.

Your colleague is dumb. Stored procedures have advantages and disadvantages. For certain data processing operations it is much better to perform everything inside the database than transfer large amount of data back and forth to some external application.

But I have also seen organizations with the policy that any operation touching base tables should be encapsulated in a stored procedure. This makes development extremely cumbersome, especially if some DBA is gatekeeper for the stored procedures. Something like this might have burned your colleague.

Reply on news.ycombinator.com