Hacker News Reader

Comment by greydius | Hacker News Reader

In my limited experience with the enterprise world, the problem with using stored procedures was that the devs had no control over the database. We could have come up with some nicer solutions to certain problems, but no one wanted to deal with the bureaucracy necessary to create and maintain parts of our applications within the database.

View on HN

Even if devs had control, it's difficult to know which version is actually on the database without pulling it up (this is a timesink), especially if you have multiple environments/configurations.

AFAIK there's no way to diff/history of stored procs in the database (and certainly not against your VCS), so large companies usually do comment blocks at the top of each one.

> AFAIK there's no way to diff/history of stored procs in the database (and certainly not against your VCS), so large companies usually do comment blocks at the top of each one.

Sqitch, go check it out. Versioning database code with linear migrations always has this issue, you add a column and where's your diff on that without going through your list of migrations. Stored procedures are no different, and the sooner people use better tools the happier they will be with maintaining their database migrations.

Also Java and C# developers often produce bad SQL procedures because they tend to think procedurally about problems.

What would be the better thought process for writing sql procedures? It seems like PL/SQL is fairly procedural.

The rule of thumb would be, use SQL statements to model and execute business logic, not cursors/variables. While the execution is still procedural, the goal is to make all the decisions 'at once' rather than explicitly looping. Behind the scenes a similar set of steps are taken, but the dbms is free to perform the operations as it sees fit.

I think if they were better integrated with the IDE and easier to debug then they would probably write better stored procedures.

As a C# dev myself, fuck stored procedures, to be quite honest.

Please elaborate

1.They are a pain in the ass to keep track of in version control. 2. They are hard to debug, especially when lots of business logic gets dumped to them

Totally agree. It's hard to do code and stored procedures right with the same level of quality. The tools are different and the thinking is different.

And easier to unit-test, and profile, and had a better standard library available, and...

SQL is a terrible general-purpose programming language by the standards of today, which makes it a terrible way to express business logic.

This is a great point.

In a smaller company, those barriers aren't really meaningful. In an enterprise, you're easily adding a week or more to change control process to ship.

I think another factor towards why database focused solutions aren't popular in small companies is that MySQL historically hasn't been the best platform for that approach, and you need more expertise to scale the database.

Reply on news.ycombinator.com