| ▲ | HillRat 7 hours ago |
| The cargo-cult shibboleth of "never put business logic in your database" certainly didn't help, since a lot of developers just turned that into "never use stored procedures or views, your database is a dumb store with indexes." |
|
| ▲ | 0x457 5 hours ago | parent | next [-] |
| A lot of people probably think it's better to keep database "easy to swap". Which is silly, its MUCH easier to change your application layer, than database. |
|
| ▲ | forgetfreeman 7 hours ago | parent | prev | next [-] |
| There's value in not having to hunt in several places for business logic, having it all in one language, etc. I was ambivalent on the topic until I encountered an 12 page query that contained a naive implementation of the knapsack problem. As with most things dogma comes with a whole host of issues, but in this case I think it's largely benign and likely did more good than harm. |
| |
| ▲ | yobbo 7 hours ago | parent | next [-] | | > hunt in several places for business logic But that is the result of having multiple applications needing to enforce valid states in the database. "Business logic" is a loose term. The database is the effective store for state so it must enforce states, eg by views, triggers, and procedures. Other "business logic" can happen outside of the db in different languages. When individual apps need to enforce valid states, then complexity, code, etc grows exponentially. | |
| ▲ | simonw 5 hours ago | parent | prev [-] | | Did that 12 page query have any automated tests? | | |
|
|
| ▲ | iamsomewalrus 6 hours ago | parent | prev [-] |
| genuinely curious, can you steel man stored procedures? views make intuitive sense to me, but stored procedures, much like meta-programming, needs to be sparingly used IMO. At my new company, the use of stored procedures unchecked has really hurt part of the companies ability to build new features so I'm surprised to see what seems like sound advice, "don't use stored procedures", called out as a cargo cult. |
| |
| ▲ | simonw 5 hours ago | parent | next [-] | | My hunch is that the problems with stored procedures actually come down to version control, change management and automated tests. If you don't have a good way to keep stored procedures in version control, test them and have them applied consistently across different environments (dev, staging, production) you quickly find yourself in a situation where only the high priests of the database know how anything works, and making changes is painful. Once you have that stuff in git, with the ability to run automated tests and robust scripting to apply changes to all of your environments (I still think Django's migration system is the gold standard for this, though I've not seen that specifically used with stored procedures myself) their drawbacks are a lot less notable. | | |
| ▲ | tete 5 hours ago | parent [-] | | > My hunch is that the problems with stored procedures actually come down to
> version control Git? (and migrations) > change management Again. Just like any other code. > and automated tests. Just write an automated test like you write any other kind of test? | | |
| ▲ | simonw 4 hours ago | parent | next [-] | | That's exactly what I'm saying. If you do those things stored procedures stop sucking. | |
| ▲ | ChromaticPanic 4 hours ago | parent | prev [-] | | It's also about separately scaling your business logic from the data layer |
|
| |
| ▲ | saxenaabhi 5 hours ago | parent | prev | next [-] | | You give no reasons why you think it's a sound advice. My experience is following 1) Tx are faster when they are executed a sql function since you cut down on network roundtrip between statements. Also prevents users from doing fancy shenanigans with network after calling startTransaction. 2) It keeps your business logic separated from your other code that does caching/authorization/etc. 3) Some people say it's hard to test sql functions, but since pglite it's a non issue IMO. 4) Logging is a little worse, but `raise notice` is your friend. > At my new company, the use of stored procedures unchecked has really hurt part of the companies ability to build new features Isn't it just because most engineers aren't as well versed in SQL as they are in other programming languages. | |
| ▲ | IanCal 5 hours ago | parent | prev [-] | | It’s about what you want to tie to which system. Let’s say you keep some data in memory in your backend, would you forbid engineers from putting code there too, and force it a layer out to the front end - or make up a new layer in between the front end and this backend just because some blogs tell you to? If not, why would you then avoid putting code alongside your data at the database layer? There are definitely valid reasons to not do it for some cases, but as a blanket statement it feels odd. Stored procedures can do things like smooth over transitions by having a query not actually know or care about an underlying structure. They can cut down on duplication or round trips to the database. They can also be a nightmare like most cases where logic lives in the wrong place. |
|
