Birbla

Join the discussion

Write your take first — we'll ask for email only when you're ready to publish.

Sign in
  • Hacker News
  • (In our on-prem SQL server environment) We expose stored procedures to web developers like an API contract to ensure a level of integrity and robustness in how they interact with the database. We find SQL knowledge among web developers these days is a toss up and I'd rather have the DBA design the schema and provide stored procedures.
    by noddingham
  • I am uneasy about databases being used in general how they are today. Nobody writes SQL to read and write records directly. it's all pumped through business logic. Then you write a stored procedure to update x-on-y and the business logic breaks, in a scenario that is impossible for the business logic to produce.

    additionally the logic is very far away from the data in many cases, obfuscated through layers of data modelling.

    If there was a way to bring these closer, that would be nice.

    by calvinmorrison
  • Just learn how to use Liquibase (or flyway, or... ) in your git repo and CICD pipeline deployments alongside your code.

    I did it with production Scala apps over a decade ago. I even built sproc TDD test suites.

    by gregw2
  • I used to be pro-sproc, but I agree that making queries part of the database schema can be burdensome and creates some development friction that I’d rather avoid.

    I’ve mostly moved on to storing queries as *.sql files in the application’s repository. You still get the good query caching and predictable plans. But you also get some other nice perks, like queries and the code that uses it being versioned together, and making it easy for developers to test queries in a SQL console. Most editors even have plugins that give you autocompletion and basic error checking in exchange for a connection string to the dev database.

    That latter bit is why I don’t love inline SQL in string literals or ORMs’ querying DSLs. Both discourage tinkering with queries to observe how they work. I believe that’s a major reason why it’s so common for developers to commit awful performance sins like computing aggregations on the client side. It’s hard to expect people to get comfortable with SQL window functions when the project’s database interaction is set up in a way that actively discourages doing so.

    by bunderbunder
  • This is an opinion where the author is obviously coming from an application developer’s bias. If you’re a data engineer you love the idea of stored procedures. A data engineer is often more comfortable putting logic closer to the data, especially for bulk transformations, set-based processing, ETL/ELT, data quality rules, and involving large volumes of data. Doing this in an application just to process them would be inefficient.
    by hbarka
  • I think stored procedures are just strong typed "serverless lambdas" which runs really, really close do your data storage.
    by est
  • > our application code and DB access are separately versioned, our sprocs can change right under our feet from aberrant (i.e. extremely rare, insane) DBAs

    Your database (and its migrations) should be source controlled along with the rest of your code. Problem solved, now you can take advantage of some of the legitimate benefits stored procedures have to offer!

    by JeffRosenberg
  • This is very poorly written and is wrong on basic facts.

    > So what does a stored procedure get us? > Absolutely nothing! Well, I mean, headache for one.

    > ... we have to deploy migrations to update our queries, and we have to run diff migration_for_my_sproc migration_for_my_sproc_n to see how things changed

    1) You can version sql functions in your repo alongside your code and deploy sql function alongside your db migrations(even in the same transaction).

    Have one file per sql function and you can also compute checksums to speed it up if like me you have a repo with 600 stored procedures.

    2) With stored procedures you have no need to to db.startTransaction on server when executing multiple statements and wait for db round trips. That's often the biggest reason for preferring stored procedures.

    3) I have seen systems in healthcare/finance where different teams have no access to underlying tables and the db only exposes sql procedures. Every-time a procedure is called it also adds a log entry to an audit table.

    Databases are a great piece of technology! Learning how to use them properly can have huge payoff in terms of business value generation.

    EDIT: not mentioned in this article but people often mention testing difficulties with stored procedures.

    You can have normal vitest tests testing your postgres functions with in-memory pglite.

    by saxenaabhi

Explore Birbla archives