Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Wrong ways to use the databases, when the pendulum swung too far

luu.io

Post reply on HN
Print view View original

71–80 of 110 posts

Re: Wrong ways to use the databases, when the pendulum swung too far

#71

Post by bob1029 »

t43562 wrote: post #55

So far in my career, such as it is, I have been on lots of rewrite projects and not one of them was a good idea. There were one or two outright failures (new code abandoned) but the more subtle ones were just efforts that took so long to deliver value that the whole market opportunity was lost. In every single case it was possible to take the existing system and gradually morph it towards something better - but the d…

I've got the opposite experience. Most rewrites I was on had substantial uplift in the product value.

The trick is to engage with the customer early, directly and often. If the customer isn't willing to pay more money for something, you should probably not do it.

Re: Wrong ways to use the databases, when the pendulum swung too far

#72

Post by roenxi »

mickeyp wrote: post #70
roenxi wrote: post #61

Earlier quoted context omitted.

How many DBAs have you dealt with that only give you SELECT and EXECUTE? That seems somewhat crazy. It doesn't cut down the surface area for bugs at all (the same amount of SQL is still required and it'd be the same code as the app was going to execute anyway as a normal query). What scenarios are they worried about here?

Amending data obviously. Building a dashboard using payroll data and having the power to change it are two wildly different things, and no sane large corporation would allow a dashboard's user account to change that data . You're coming at this from the idea that one user has full access to everything when that is likely the biggest security lapse you'll find in modern apps that use databases: a Web app that has full…

Yeah, that was a very confused question from me. I thought for a moment EXECUTE permissions were a roundabout way of granting restricted write access. Let me try again: how is "How am I going to work here without Stored Procedures?" supposed to be interpreted in that comment? The application could just run the queries without using stored procedures. Stored procedures don't change the permission requirements; they're a moderate performance optimisation. They're almost always optional.

bytefish's comment seems to be linking stored procedures and writing to columns, which doesn't make sense because the scenario doesn't have any write permissions.

Re: Wrong ways to use the databases, when the pendulum swung too far

#73

Post by bytefish »

roenxi wrote: post #61
bytefish wrote: post #42

Earlier quoted context omitted.

I am not going to dismiss your experience here. Stored Procedures can turn into wild monsters. Pair it with Triggers and you are in for chasing a noodle. But it's also a reality, that relational databases often become the main integration point in companies. In those environments it’s hard (next to impossible) and dangerous to use something like ORMs. Often enough I don't "own the tables" and I don't "own the columns…

How many DBAs have you dealt with that only give you SELECT and EXECUTE? That seems somewhat crazy. It doesn't cut down the surface area for bugs at all (the same amount of SQL is still required and it'd be the same code as the app was going to execute anyway as a normal query). What scenarios are they worried about here?

I have seen many actually.

Imagine having a huge amount of applications all using the same database, which is, like I said, often enough the main integration point in a company. The applications you are maintaining are written in, let’s say Java, C#, Python and C++. They are too important, too large and a rewrite is way too risky.

How would you start to get this under your control? By moving common logic in a shared C++ DLL probably? Rewrite all those applications at once, in a heroic effort? I for one take the pragmatic approach and extract common business logic shared between all them into a Stored Procedure.

The Stored Procedures are invoked by all applications, so instead of maintaining 10 implementations, I maintain 1. Suddenly you have a common data access path! The 1 implementation will be drowned in a shitload integration tests, because this is what really matters.

Of course, a Stored Procedure doesn’t prevent you from shooting yourself in the foot! But in my experience, it’s way harder to shoot yourself in the foot using a Stored Procedure, that you can inspect the Query Planner for… than hunting down Monster-Queries for weeks, generated by a LINQ provider.

As for the INSERT, UPDATE and DELETE: With SPs, you’ll have fine-grained control, which columns are updated instead of needing to expose all properties in your application code and praying for an ORM to not accidentally update “columns I don’t own”, because of some convention applied by the ORM.

If you are in the lucky position of owning the database and don’t have to maintain legacy applications, I see resistance to Stored Procedures. Then go ahead and build an API all applications are going to use.

But if you don’t own the database and need to maintain dozens of mission-critical applications, Stored Procedures are a tool worth knowing.

Re: Wrong ways to use the databases, when the pendulum swung too far

#74

Post by t43562 »

jpgvm wrote: post #66
t43562 wrote: post #55

So far in my career, such as it is, I have been on lots of rewrite projects and not one of them was a good idea. There were one or two outright failures (new code abandoned) but the more subtle ones were just efforts that took so long to deliver value that the whole market opportunity was lost. In every single case it was possible to take the existing system and gradually morph it towards something better - but the d…

It really depends on the origin of the system to be replaced. If it was never designed to be replaceable you are usually in for a bad time. Similarly if the drive to replace it comes from new folk that simply don't understand the original system you are probably completely doomed. I often write code with full intention of replacing it, generally by writing it in such a way it's replacement is forced. That latter bit…

Rewriting something you wrote yourself makes sense. i.e. who knows better than you what the bash script does and why? You did it with bash - probably quite quickly but now you understand what's needed you can do it better the second time in a language which might be a bit less flexible but is much faster.

As for writing code to be replaced - well I can understand doing it but generally I've been in companies where you never get a chance to go back to anything that doesn't seem to be critical to the next requirement from the business - and they always have more than you can do.

The big rewrites are almost a response to this - no improvement can be justified unless it enables so many new features that the business decides to sign off. But because it's such a big change it is also much much more risky.

Re: Wrong ways to use the databases, when the pendulum swung too far

#75

Post by t43562 »

bob1029 wrote: post #71
t43562 wrote: post #55

So far in my career, such as it is, I have been on lots of rewrite projects and not one of them was a good idea. There were one or two outright failures (new code abandoned) but the more subtle ones were just efforts that took so long to deliver value that the whole market opportunity was lost. In every single case it was possible to take the existing system and gradually morph it towards something better - but the d…

I've got the opposite experience. Most rewrites I was on had substantial uplift in the product value. The trick is to engage with the customer early, directly and often. If the customer isn't willing to pay more money for something, you should probably not do it.

If the effort is too large then you might take years to get to the end - always trying to match the old product (which evolves) and eventually realising that your new architecture isn't perfect either.

I think it can be extremely good to rewrite small things - you always know how to do it better after you've done it once.

Re: Wrong ways to use the databases, when the pendulum swung too far

#76

Post by random_savv »

sdeframond wrote: post #51
simonw wrote: post #30

Earlier quoted context omitted.

I'm convinced that the solution to stored procedures that people are afraid to update is automated tests . Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on. For some reason stored procedures and automated testing seem to often not go togethe…

In good resource on testing stored procedures ?

For Postgres, there’s pgTAP: https://pgtap.org/

Re: Wrong ways to use the databases, when the pendulum swung too far

#77

Post by pgwhalen »

samwillis wrote: post #43
simonw wrote: post #30

Earlier quoted context omitted.

I'm convinced that the solution to stored procedures that people are afraid to update is automated tests . Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on. For some reason stored procedures and automated testing seem to often not go togethe…

I think a lot of the problems come from the fact testing stored procedures ventures into e2e testing land. You have to stand up infra in order to test them. There's not really been a simple way to unit test stored procedures as part of your application codes testing framework. (Aside: I think this is something PGlite helps with if your in Postgres land)

My team has found a lot of success using testcontainers for testing Go and Java applications integrating with Postgres. They feel more unit-testy than e2e-testy.

Admittedly I’m only talking about selects, inserts, updates, views, etc. not stored procedures. But having worked in codebases with far too many stored procedures in the past, I think there might be a marginal improvement.

For what it’s worth, I fully agree that the main problem with using store procedures is testability and debugability.

Re: Wrong ways to use the databases, when the pendulum swung too far

#78

Post by Quarrelsome »

TZubiri wrote: post #29
Quarrelsome wrote: post #10

Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a…

I'm not hugely experimented, and SQL has always been enough for me, perhaps due to my simple requirements. But so far I hold the opinion that ORM is always a mistake. I find a lot of programmers don't know how to write an array of floats to disk, if you forced me to choose between an ORM or No DB at all, I would choose no DB all day. ORM feels like an abstraction on top of an abstraction. I don't trust that those who…

I know both and ORMs are fine. They save a lot of time at the cost of some inefficiency. However some of the newer ones are like Entity Framework Core for dotnet are significantly smarter than older models.

Re: Wrong ways to use the databases, when the pendulum swung too far

#79

Post by Quarrelsome »

gfody wrote: post #16
Quarrelsome wrote: post #10

Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a…

ORMs are basically the enemy of rdbms, by encouraging antipatterns and undermining language features they have easily done more harm than good. of course reasonable people can be trusted to use them reasonably but if you're arguing about it on the internet..

ye I think the guy I was arguing with was mostly a DBA so I get his perspective, but its just religious to be wholly on one or the other side of the argument.

Re: Wrong ways to use the databases, when the pendulum swung too far

#80

Post by physicles »

simonw wrote: post #30
freetime2 wrote: post #27

Anecdotally, the worst codebase I ever worked on made heavy use of stored procedures. Over the years people couldn’t be bothered or were afraid to update them - which really was the root of the problem. This led to all kinds of crazy patterns in the application code like calling things in a loop where a bulk operation was needed. Or stringing together several stored procedure calls to get the desired outcome, when re…

I'm convinced that the solution to stored procedures that people are afraid to update is automated tests . Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on. For some reason stored procedures and automated testing seem to often not go togethe…

I recently took a couple 100 line stored procedures that I wrote years ago, and replaced them with equivalent go code and a few inline SQL calls. We had extensive tests on those stored procedures too, or rather, tests on the go methods that called them. The tests all ran in a few seconds. The go code feels so much nicer to deal with, and I thought about why:

- Stored procedures are deployed separately from the code that calls them: one is a db migration, one is a k8s container deploy. And that binding isn’t strongly-typed, so there’s more thinking and double checking involved during deployment (and, heaven forbid, rollback).

- The SQL procedures duplicated some business logic and constants that are defined in some of our go core libraries. It felt good to remove that duplication.

- The procedures also had a few specific return codes. We needed to define some constants (like postgres itself does) that the go code could detect and translate into go errors. That’s more complexity.

- Maybe there’s a good SQL IDE out there, but I don’t have it, so I was writing these things in vscode with syntax highlighting. Plenty of errors that would get an immediate underline in go instead required 20 seconds to compile and run the test and interpret the error message.

- This lack of tooling, and the fact that I’m not great at SQL (I’m fairly good with queries, but not with stuff like variables and IF EXISTS that you only see in stored procedures), made it hard to use all the complexity-cutting techniques I’m used to applying in go and other languages. So the 100 line stored procedure stayed 100 lines, and everyone hated working on it.

Stored procedures are basically a cross-service API call, with all the complexities that entails. I won’t reach for them again unless there’s a technical problem that can’t be solved any other way. We don’t have a dedicated DBA so Conway’s Law doesn’t factor in.

Post reply on HN