Yet another Hacker News

8 points snikolaev 3 days ago 11 comments

gregw2 3 days ago | parent

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.

joeblubaugh 23 minutes ago | parent

Besides CI, what benefits were you getting from writing the logic as a stored procedure?

murphomatic 8 minutes ago | parent

I have this question as well .. granted there are a few targeted cases where SPs make sense, I generally agree with the headache perspective of SPs - such as: the business logic that inevitably ends up in SPs - how do you go about unit testing that in isolation, and particularly from a behavioral standpoint?

joeblubaugh 24 minutes ago | parent

Maybe they’re not properly called sprocs, but there’s at least one good use for logic stored in the database: data retention. I like CREATE EVENT / pg_cron for trimming append-only tables when the data has aged out.

saxenaabhi 16 minutes ago | parent

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.

jeremyjh 10 minutes ago | parent

> 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.

For a large system that has many different teams working on it, it is better to have a core API layer with clear ownership, than to not have one. But why would you choose to build that with database stored procedures? If this was built 25+ years ago, then that is all the answer that is needed.

saxenaabhi 2 minutes ago | parent

Why wouldn't you use stored procedures for it? For internal teams why is it better to have a API?

I can see usecases in which API could make sense, but it doesn't matter in most cases.

SQL already has authorization/authentication built in. For rate limiting you can use something like planetscale's traffic control.

jeremyjh 8 minutes ago | parent

Honestly it is a bit surreal seeing this. I thought this practice all but died with VB6 and PowerBuilder. There are certainly uses for the technology, but I really thought we'd all moved on from the idea that business applications should do all database operations through stored procedures a long time ago.

calvinmorrison 7 minutes ago | parent

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.

est 5 minutes ago | parent

I think stored procedures are just strong typed serverless lambdas which runs really, really close do your data storage.