Hacker News Reader

Comment by guggle | Hacker News Reader

> but by using stored procedures in your database.

This used to be the standard when clients used to connect directly to the database. Now that the world depends on web services, things are a little different and there's less incentive to maintain stored procs as an interface to your database.

It's still a good idea though, with unparalleled performance. I suppose developers don't do it for a variety of reasons: They may lack the SQL knowledge, they may not want to maintain the extra code, they may fantasize about database portability, etc.

View on HN

Well as someone with 25 years SQL experience, I generally will still use LINQ for 90% of db access code in business style applications, for very important reasons beyond those mentioned, like

- They may realise value in having a compiler provide guarantees, as a bonus immediately in a tight feedback look while editing code as well as on compilation

- They have favour expressive languages with robust error handling, integrated IDE and VC etc

- They may want to reduce the number of moving parts

These are all along the themes of a) delivering results faster, and b) improving the maintenance lifecycle of systems - unlike those parts of the industry that don't really plan to maintain their systems and instead just wanna do rewrites in the latest hotness which is usually a hot mess from a long term maintainability perspective.

But then LINQ is in many ways conceptually closer to directly using SQL than to using a classical ORM.

Sorry my wording could have been more explicit!

Generally, use of LINQ in the context of DB access is done hand in hand with an ORM, such as Entity Framework, NHibernate etc, and this is how I meant it.

I'm not a fan of stored procedures because 1) It's more maintainable to keep all the logic unified in the one language and system with which developers will be more familiar with 2) The programming tools are normally much better for app code 3) You can write unit tests for app code (does anybody write unit tests for sql?) 4) you can usually avoid transferring too much unnecessary data or doing too many unnecessary network requests, remember to avoid premature performance optimization

On the other hand, one of the highly specialized stored procedure I wrote in PostgreSQL have being successfully and flawlessly re-used/tested on multiple out-of shelf clients app (BI related all developed in different languages) simply because they all support SQL.

I agree this only apply for data-model related functions. But this has proven really low maintenance, and improvements of this "API" are automatically available in all clients.

I'd bet that accessing this stored procedure through an ORM raw SQL query would provide more benefit than rewriting it from scratch in ORM's language.

PS: Also yeah people do write unit test for SQL (never enough but that's another story). https://pgtap.org/

1) One language and one system: SQL on a database that will last much more than the applications 2) SQL has a limited scope, and so do SQL tools. You need advanced tools if you have "app code": it's a necessary evil. 3) At worst, you can write unit tests for app code that calls SQL queries and stored procedures. 4) "Usually" isn't enough, moving logic to the client requires moving data to the client.

The argument against it I usually see is "you can't version control SQL!"

Where I work we have a custom system which manages releasing immutable SQL snapshots between environments and they get merged to master once in prod. The only thing needed is a process, and then version control is easy.

Yeah you can just use a migration system and store your functions in text files checked into the version control. Nothing but the migrations are allowed in staging and production so everyone is forced to use it.

As a front-end dev, I've been interested in learning about such a process but don't know where to look. What kind of tooling would you use to manage your migrations in a CI environment?

ive used liquibase in the past (not affiliated) with great results https://www.liquibase.org

Flyway or Liquibase.

Editing stored procedures and deploying them on a database is the same as editing code deploying it on a server. There is no difficulty in versioning it

> you can't version control SQL

Aren't stored procedures part of the database schema? Which can be version controlled?

I'm not sure about other DBs, but SQL Server has (really good) tools for this (DACPACs). As an added bonus you can then use tSQLt to unit test the SQL.

On high volume applications I would avoid it since it's usually easier to horizontally scale web servers than sql servers, and it makes cache strategies more difficult depending on how and what you're querying.

It is a common misconception that more data processing in SQL puts a higher load on the database. A typical database spends 96% of its CPU time on logging, locking, latching and marshalling [1][2] rather than processing SQL. By sending less data to the middle tier and performing fewer round trips, the use of stored procedures means that the database can actually do more real work.

[1] https://dzone.com/articles/mit-prof-stonebraker-%E2%80%9C

[2] https://drive.google.com/file/d/0B7jyeB8kxFPjU0VySkF3UHhoVnM...

It depends. Regardless there is limited CPU, and so any scenario in which a stored procedure uses more CPU than a simple query will cause you to hit that saturation point sooner.

I generally have layers of caching on top of the sql server so the majority of queries will be integer equivalency or range checks, if not get by primary key queries. So I am not generally operating in a scenario where a stored procedure would reduce record scans, etc.

I generally also don't run on transactions out side of limited scenarios, since throughput is usually more important to me than data consistency.

sorry, I don't get this at all. How is using stored procs instead of an ORM going to adversely affect your cache strategy?

Imagine for example that you have per record caches. You can run a query to get ids with out joining to the table and then simply fill in any gaps in your cache with a follow up query.

In most cases, it is sufficient if your database runs on the DB in production and sqlite for local development. If it's too big to be able to run on the dev computer, then it should use one db across the board.

Portability across databases rarely buys you anything but hard to reproduce bugs.

Reply on news.ycombinator.com