4 ms·

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

ako 5y ago
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.
- deleted 5y ago
  [deleted]
- mdavidn 5y ago
  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?
  - throw1234651234 5y ago
    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.
    - mdavidn 5y ago
      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.
- munk-a 5y ago
  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.
- Thaxll 5y ago
  "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.
  - ako 5y ago
    How is this not code: https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#insertedID8 https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#in... ? What is your definition of code?
  - mousepilot 5y ago
    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.
mdavidn 5y ago
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.
- throw1234651234 5y ago
  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.

ako 5y ago

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.

deleted 5y ago
[deleted]
mdavidn 5y ago
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?
- throw1234651234 5y ago
  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.
  - mdavidn 5y ago
    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.
munk-a 5y ago
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.
Thaxll 5y ago
"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.
- ako 5y ago
  How is this not code: https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#insertedID8 https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#in... ? What is your definition of code?
- mousepilot 5y ago
  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.

mdavidn 5y ago

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.

throw1234651234 5y ago
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.