Hacker News Reader

Comment by throw1234651234 | Hacker News Reader

Is this a joke? SPs and SQL Functions are far less maintainable than .NET/Java/Node/Python.

Why are they less maintainable? It’s just code you can write in a code editor, version control in a versioning system, have automatic tests for, have automatic deployment scripts for, etc. In the end the database is just an application server executing your code close to the data. Only thing you have to ask yourself is how much your code needs to scale. But it can be much more efficient to run business logic in the database as stored procedures.

Using a migration utility like ActiveRecord, Flyway, or go-pg, SQL definitions can also live in version control, have automatic tests, and deploy automatically. Who has a database in production without these things?

Unrelated, but which ones of these do you recommend (also factor in LiquiBase) that's free?

As far as automatic tests for dbs - I have no idea what you are talking about unless you mean integration testing through a backend.

I’d recommend whichever one is available in a language the project uses already.

Automatic tests for a database could simply be writes followed by reads with expectations for the returned rows. This is how Postgres tests itself.

My personal go-to right now is ActiveRecord and RSpec. The examples could just as easily run raw SQL directly using the Ruby pg driver.

I personally have found it's much more difficult to partially mock out concepts when writing logic in the DB layer though my tooling may have been the weakpoint. In a nice application language I can easily isolate specific units of code for direct testing - while every test I write over complex database logic feels like composing a symphony where I need to make sure the horns are coming in on the right pitch and the wind section isn't too early to properly simulate a relatively easy error scenario.

Since the application layer is much more naturally aligned with small well isolated chunks of logic it is easier to minimize the volume of code and logic in scope when testing a particular attribute - when that goes into the DB I've always seen things get more complex rapidly.

"But it can be much more efficient to run business logic in the database as stored procedures."

But this is not code, this SQL or equivalent, if SQL was known to be better than code to do busines logic everyone would do that.

How is this not code: https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#in... ?

What is your definition of code?

There's all sorts of programming styles in use and calling stored procedures from dot net programs for instance is at least a thing.

Plus, lots of those ORM things get plenty of hate from DBAs, I mean I like them but then again I'm not a very good programmer.

That's perhaps true of stored procedures, but SQL views, constraints, and triggers written in a declarative manner are far more maintainable and far less buggy than imperative application code, in my experience.

I have no words for "triggers" and "maintainable" in the same sentence - enjoy your deadlocks and having no clue why a single write says "UPDATED x records" y times without having to dig down through an ancient UI.

I am actually "good" at this stuff - writing logic in SQL through triggers, which call functions (at which SQL Server is way better than PG is) and stored procs, etc, and it's just a horrible, horrible practice.

Reply on news.ycombinator.com