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

61–70 of 110 posts

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

#61

Post by roenxi »

bytefish wrote: post #42
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 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?

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

#62

Post by dottedmag »

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)

PostgreSQL server is a single process that starts in under 100ms on a developer's laptop.

In the company I work for we use real PostgreSQL in unit tests — it's cheap to start one at the beginning of a suite, load the schema and go, and then shut it down and discard its file store.

I keep thinking of moving that file store to tmpfs when run on Linux, but it's nowhere near the top of the performance improvements for the test suite.

So: no more mocks or subsitute databases with their tiny inconsistencies.

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

#63

Post by [deleted] »

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…

[deleted]

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

#64

Post by hyperman1 »

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…

Oh man. I've seen an org where the stored procedures were written by a different team, and friction was high.

Then one java dev found a glaring sql injection hole. The whole app quickly reorganized around using it as an API for everything. Management was pleased speed went up and bugs went down, DBAs were pleased for less work, and Java devs were pleased they could understand what was going on in the app. Everybody happy I guess?

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

#65

Post by zabzonk »

awesome_dude wrote: post #39
zabzonk wrote: post #31

Earlier quoted context omitted.

> you probably want to have the bulk of your business logic written in C# Perhaps, if the C# applications are the only ones accessing the database. But suppose multiple applications written in different languages (and for different purposes) need the database access? You can do this via stored procs or (better in my experience) by adding an intermediate server process which the applications use, via a publicly docume…

> Perhaps, if the C# applications are the only ones accessing the database. But suppose multiple applications written in different languages (and for different purposes) need the database access? Without wishing this to sound like a personal attack, YUCK A database that's accessed by multiple applications, regardless of the number of languages, is a really bad smell. If I have a user db, and hundreds of other applica…

Precisely why I said the applications should access the data via a server process - they never know the underlying database schema.

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

#66

Post by jpgvm »

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 is pretty important because systems generate inertia, you need to have some hard forcing function baked into prevent it ossifying.

i.e my current project is to replace a pretty awful pile of Bash I wrote last year and replace it with a much more sophisticated and automated system in Golang. The new system is already in production and the old tools are being phased out.

Writing the original in Bash was how I basically ensured it would get rewritten and not continually upgraded/morphed into something grotesque. There was a ton of pushback, lots of people saying "why not Go! why not Python! This is an awful decision, 3k LOC of Bash is an unmaintainable nightmare!". Literally all of those people missed the point.

Building simple systems that you 100% will replace (not intend, that really isn't the same thing) is a really good pattern to find a real understanding of the solution space before building something gold plated/spaceship-like.

It generally means once you implement the follow up it very rarely becomes "legacy" software without horrible mismanagement, i.e bringing in new management that wants to rewrite things for the sake of rewriting them, usually in some FoTM nonsense.

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

#67

Post by docandrew »

biorach wrote: post #41
TZubiri wrote: post #29

Earlier quoted context omitted.

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 find a lot of programmers don't know how to write an array of floats to disk what does that have to do with it?

I think his point is that ORMs (and maybe DBs in general) are used for data persistence by folks who just don’t know any alternative.

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

#68

Post by slooonz »

bob1029 wrote: post #56
tanelpoder wrote: post #52

The reluctance of using stored procedures where they’d be valuable is also a skill + will issue. I do get the non-database-developer view that if 98% of your app is gonna be written in some other language anyway, why complicate your app layers by adding a bit of PL/SQL code + testing infra here and there. But for processing (and validating) various data loads and incoming data streams, by looking up matching values i…

> But for processing (and validating) various data loads and incoming data streams, by looking up matching values in existing reference tables, stored procedures can increase performance/efficiency and reduce complexity Performing operations on data directly in the SQL provider is the peak of human enlightenment. It takes a lot of leadership or wisdom to push a modern team away from using crap like EF to process ever…

Operational concerns trumps raw performances most of the time. Stored procedures live in a different CI/CD environment, with a different testing framework (if there’s even one), on a different deployment lifecycle, using a different language than my main code. It is also essentially an un-pinnable dependency. Too much pain for the gain.

Now, give me ephemeral, per-connection procedures (call them unstored procedures for fun) that I can write in the language I want but that run on provider side, sure I’ll happily use them.

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

#69

Post by bob1029 »

slooonz wrote: post #68
bob1029 wrote: post #56

Earlier quoted context omitted.

> But for processing (and validating) various data loads and incoming data streams, by looking up matching values in existing reference tables, stored procedures can increase performance/efficiency and reduce complexity Performing operations on data directly in the SQL provider is the peak of human enlightenment. It takes a lot of leadership or wisdom to push a modern team away from using crap like EF to process ever…

Operational concerns trumps raw performances most of the time. Stored procedures live in a different CI/CD environment, with a different testing framework (if there’s even one), on a different deployment lifecycle, using a different language than my main code. It is also essentially an un-pinnable dependency. Too much pain for the gain. Now, give me ephemeral, per-connection procedures (call them unstored procedures…

> Stored procedures live in a different CI/CD environment

They don't have to. The procedural SQL commands can be source controlled along with the rest of the codebase. Transmitting the actual command text to the SQL server that does all the work is not the inefficient part.

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

#70

Post by mickeyp »

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?

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 ddl and even dml access when it shouldn't have.

Post reply on HN