Hacker News Reader

Comment by simonw | Hacker News Reader

I think stored procedures can be perfectly safe provides you follow these rules:

- they live in source control

- they are covered by automated tests

- they are applied using some form of automatic database migration system (not by someone manually executing SQL against a database somewhere)

If you don't have the discipline to do these things then they are likely best avoided.

View on HN

> If you don't have the discipline to do these things then they are likely best avoided.

I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.

Aren’t those also the absolute bare minimum bar for any code in a production system?

Yeah, I think so. But my hunch is that the majority of people who tell you never to use stored procedures have been burned by these techniques not being used for them.

Your hunch is off. We version-control DDL/DML/DQL/etc. like any other software.

"But my hunch is that the majority of people" - I'm not saying no-one does this, I'm saying I expect a lot of people don't do it.

The realization that database procedures are code, not data, even though they reside on the database (where the data lives) is the difficult part.

That’s baffling to me. Who doesn’t realize that that thing which looks and behaves exactly like all other code isn't code?

Before the development of decent migration systems it was incredibly common for database structure - including stored procedures - to be treated independently of source code in a repository.

True, of course. There were also undoubtedly a lot of production systems that didn’t even use version control for non-database code. Industry practices certainly evolve over time. But it’s difficult to imagine a scenario where a team is aware of version control, uses it for the things they realize are code, but somehow doesn’t realize that stored procedures are code.

I know a place that operated like this for years, so I don’t have to imagine.

These sorts of places also tend to have database admins in one team and programmers in another team. All database changes go through the database team with tickets or whatever. It's a huge pain in the ass to navigate and enable quick changes.

The folks who treat databases as "that thing behind the ORM."

It's a common thing to miss. There's a reason SQL injections are (unless things have changed recently) among the most prevalent classes of web exploits.

> they live in source control

so that probably excludes 95% of legacy codebases out there from the 90s,00s

We tick all the boxes. Please contain your arrogant presumptions.

I was confused as to why you seemed to be taking offense here, then I realized that you posted the comment I was replying to.

For "you" in my comment, please read "one" instead:

> I think stored procedures can be perfectly safe provides one follows these rules:

> ...

> If one doesn't have the discipline to do these things then they are likely best avoided.

Reply on news.ycombinator.com