> A lot of things people use ORMs for are rather easily solved with stored procedures

More like 1 thing. ORMs are meant to make interfacing through the object/relational impedance mismatch easier and through the regular code in your application. Stored procedures do not come anywhere close to this and are usually the same as just calling any other SQL query as you would when not using an ORM.

If you think any majority of what an ORM is used for can be replaced by stored procedures, then you're not really using an ORM for much at all.

I agree with this, but I'd also add that if your ORM is doing a lot beyond the capabilities of your database, it's an indicator of a hacky design in your application. If there's a large object/relational impedance mismatch, then either the objects or the relational DB are a poor fit for the problem you're trying to solve, and an ORM can't really fix that.

If your ORM is just providing a mapping between select/map, where/filter, join/zip, etc., you have a fairly list-of-records-ish functional application and your objects are only nominally objects. The ORM is only providing an interface that lines your types up (and the benefits of that are not to be underestimated--it allows, for example, running your app on different database engines).

But if your ORM is doing more than that, it's usually because your objects have a lot of complicated graph vertices that are poorly represented by tables. ORMs can reduce the difficulty of this object/relational impedance mismatch, but ultimately they can't provide a general solution for it, because the data structure they're sitting on top of doesn't have the capability. ORMs can make your code simpler, but they can't make SQL performant on, for example, directed graph or deep tree structures. Ultimately, they only mitigate the object/relational impedance mismatch, they don't solve it.

Again, as I parenthesized earlier, the benefits of ORMs still aren't to be underestimated. But I think a lot of those benefits can be realized with a simple wrapper around SQL (i.e. LINQ). Beyond that, all ORMs provide is a little fudge factor which lets you get away with some things that SQL doesn't support well, but ultimately ORMs aren't a general solution to those kinds of problems.

> If there's a large object/relational impedance mismatch, then either the objects or the relational DB are a poor fit for the problem you're trying to solve.

Why? You're just making that assumption but the fact is that relational databases and the normalized storage of data is completely different from the way OO languages deal with rich nested objects. And there's nothing wrong with that mismatch because there will always be a mismatch. It's just 2 different paradigms of handling data. All an ORM is doing is giving you a tool to make that translation easier, if you want it. If you really can't have the mismatch then there are document-stores available but in most cases, the O/R mapping is just not a big deal.

> But I think a lot of those benefits can be realized with a simple wrapper around SQL (i.e. LINQ).

That's basically still an ORM. Again why the assumption that just because you have an ORM that anything and everything must be piped through it? The modern ones let you use ORM methods in your code and chain them with custom raw SQL too. ORM usage is on a spectrum, it's not binary and there's definitely no "right" way to use them.

I suspect you're viewing my previous comment as a criticism of ORMs. I tried to make it clear that it was not a criticism of ORMs. It's more a criticism of people who try use ORMs to whitewash a bad design or get around a problem that relational databases can't solve. Relational databases lend themselves to a very specific way of structuring data, and if you don't structure your data that way or your data can't be structured that way, an ORM won't fix it. That doesn't mean ORMs are bad, it means that ORMs have to be used as intended and often people don't use them as intended. That's not the fault of the ORM any more than it's the fault of a screwdriver when it's used to saw wood.

Nobody uses a screwdriver to saw wood. But they're great as replacement paint stirrers and hole punches.

That's my bad ORM misuse analogy.

As a maintenance programmer, it really fries my bacon when I find all the screwdrivers next to the old paint cans.

I think when people talk about using an ORM or not they're mostly talking about using a LINQ-like wrapper or not. There's maybe a conversation to be had about some kind of fancier features on top of your ORM and whether they're worth it, but I don't think those kind of features are what people usually mean when they talk about ORMs (whether they're technically what the acronym "should" mean or not).

> interfacing through the object/relational impedance mismatch

Do we really need the regular code with it's traditional OOP and fat application server to convert result set to JSON and spit it out - that most of the time is all that code is doing actually? I suppose these typical tasks might be pretty much covered not only by PostgREST [1], but with just a sweet combination of two PostgreSQL functions - array_to_json() and array_agg().

[1] http://postgrest.com/

> you're not really using an ORM for much at all.

The ORM can do transient fault recovery (including other cloud patterns) as well as modernise the interface (Micro-ORMs in general).

One problem with the stored procedure approach is that it scales terribly.

If your logic is in app servers and your state in a DB, you can add more app servers, and it will be a long time until your DB get overwhelmed.

When your DB is doing both, the choking point is much earlier.

Look, this is just wrong. All the best-performing research databases use stored procedures because they perform much better than other approaches. Your bottleneck when it comes to validating your data is, generally speaking, your database, so you're not going to scale better by moving the logic farther away from it (and introducing a bunch of round-trip latency) and this is empirically confirmed in benchmarks of stuff like TPC-C. It's a really tired argument, especially when virtually every aspect of stored procedures but their performance is irritating.

That stored procedures scale badly is different from if they perform badly.

With application logic on separate stateless app servers, you can quickly spin up as many of those as you need when your site goes viral. If it's in your DB, you can't.

If the stored procedures are fast or slow doesn't really enter into that equation. It's about the difference between 1 and N.

I actually did work at a startup that got into serious trouble because much of their application logic was in the DB.

Were you serving something that had to live in the database, or were you serving cached copies that didn't actually need to be updated or read within a transaction? There's a crucial difference there. The moment you are required to access the database at all, the equation changes (which is usually true for data validation, inserts, updates or deletes, and constraint management). If all you're doing is reading static data, there's no need to touch the database more than once and you can do much better. Moreover, if all you need is causal or eventual consistency (for application-specific reasons), you can use databases that provide those semantics. In pretty much all cases, once you've chosen the correct database / semantics for your use case, you'll be better off from a performance perspective with stored procedures; 1:N doesn't really enter into it because databases can run with their own isolated state on multiple nodes just as well as applications can (indeed, there are some caching systems built around this observation)!

IME, the win in the application layer is when you can have faster copies pulled from a cache or validate rules that don't relate to other records.

I've yet to see empirical proof that stored procedures don't scale well. If you write an application in your SQL dialect of choice, that likely won't scale well, but putting data access behind an API should scale well no matter if that API is in Rails, Spring, or a stored procedure.

I don't know what you consider "empirical proof", but it seems obvious to me. And should be obvious to anyone competent who has ever had to scale stuff. A system fails to scale when it has a bottleneck, and that bottleneck gets overwhelmed. You make it scale better by scaling the bottleneck, which can be done by moving work out of the bottleneck, or by parallelizing it in some way.

The natural bottleneck for any system that has to synchronize data is the locking around synchronizing that data. That is because things that do not need synchronization can easily be parallelized. You therefore scale that bottleneck until the more fundamental one emerges.

In a standard database driven website, that bottleneck is always in the database. And therefore your scaling limit is the capacity of your database. As follows normal scaling advice, you need to move work out of the database, or remove the database as a scaling limit.

Moving work from stored procedures to the application is an example of moving work out of the database. So is having queries run against read-only replicas instead of the read/write master. Sharding your database and moving to a distributed NoSQL architecture are examples of removing the bottleneck.

Of the two approaches, the much simpler and safer one is to move work out of the database. Going NoSQL is cool, but unless you really know what you're doing, it is both unlikely to buy you what you wanted, and leaves you open to obscure data consistency problems.

When it comes to data validation, none of what you just said applies, because you need to perform your work inside a transaction. You can validate the data outside your database and then confirm that nothing's changed (optimistic concurrency control) but you can do that just as easily inside the database, with lower latency and greater throughput (and in situations with lots of contention this can lead to many more aborts than other concurrency control mechanisms, so be careful!) because many databases have OCC built in. If you can afford to relax consistency due to aspects of your data model, you can use a database with a relaxed consistency model and--again--get far better performance than an ad-hoc solution in your application.

It's hugely unclear to me why you think you skirting transactional requirements by performing work in your application is less complex than using a NoSQL database (or using a database that utilizes MVCC or can otherwise provide long-lived read snapshots).

Frankly, I also disagree that for most websites the bottleneck is the database. For many websites, database latency / throughput constraints don't ever become the dominant factor in end-to-end requests because of all the layers they have to get through in order to get to the database in the first place, combined with a relatively low number of requests per second (commodity relational databases on commodity hardware can easily handle many thousands per second, and IIRC Google Search only had to handle 40k rps from real clients in a recent press release) and inefficient code elsewhere in the stack.

Just put transactions in the application. With row level locking, the odds of running into contention problems are low. If you've been careful to always lock tables in the same order, then deadlocks are a non-issue. At that point, having longer running transactions does not matter. What matters is contention for the internal locking mechanisms inside of the database. And limiting how much extraneous stuff you have improves that.

Now it is easy to screw up an application. It is easy to screw up a database design. It is easy to screw up queries and query plans. But all of those are fixable in relatively straightforward ways. And once you do that, you will wind up with database throughput as your bottleneck.

As for NoSQL, the problem is this. Moving to that architecture requires taking an up front hit on transactional complexity, usually requires several times the hardware (data needs to be stored multiple times for hardware failure), puts a lot of stress on your network and latency, and is really easy to screw up. Just using a popular out of the box solution is not enough - see https://aphyr.com/tags/jepsen for a list of real failure modes on stuff that will look perfectly fine in testing.

It is a necessary challenge to accept if you want to go beyond a certain scale. But you should not accept that challenge unless you have good reason to do so.

The thing you're missing is that unless your validation logic is really complicated or you have a really fast network (think Infiniband, which is still not commodity), it will take less time and fewer resources to perform the validation logic (once the locks are taken, which has to happen regardless) than it does to marshall the data over the network to your application for processing, often by orders of magnitude. That is why more or less everyone trying to win benchmarks at transaction processing uses stored procedures.

FWIW, there's lots of concrete evidence of this beyond the fact that benchmarks almost always use stored procedures. If you look at the very best-performing storage systems, like MICA or FaRM, which achieve 100 million tps or more, you'll see that a huge amount of their optimization comes from circumventing the OS networking stack, taking advantage of built-in queueing mechanisms in NICs, and offloading work to avoid taking up cache lines and cores from the processing CPUs. Many extremely recent database designs also take this approach (separating the transaction processors from the executors) for similar reasons, like Bohm, Deuteronomy, and the SAP HANA Scale-out Extension, as well as deterministic database systems like Calvin. Given that these are literally the best-performing database systems out there, I have a hard time accepting that reducing network latency and doing as much processing work as possible in the database isn't the correct way to achieve high performance.

Using stored procedures also allows for (in theory) analysis of the allowed transactions and conflicts between them, which can boost performance further (e.g., if you can guarantee that they can't conflict with each other ahead of time, or if you can reuse cached data because nothing could have changed, or you might be able to order data in epochs and guarantee that there are no deadlocks, or you might be able to periodically check to see if a record was particularly highly contended and rebalance or split, if commutativity is possible--all of these options and more have been explored in recent database research). It's often difficult for an application to guarantee this because several web servers may be contacting the database at once, and they don't all know what the other web servers are doing, and this is doubly true if you allow ad-hoc queries. Not knowing whether a transaction will finish quickly (as is the case if an application is allowed to hold locks) also greatly increases chances of contention and/or disconnection, which can lead to further issues.

To be doubly clear--I think stored procedures are a massive pain in the ass and there are lots of good reasons to do exactly what you're proposing above. But performance is not one of them.

I wrote https://news.ycombinator.com/item?id=11989138 while you were writing that, and addressed your point there.

Performance and scalability are very different things. Stored procedures are great for performance, but bad for scalability.

Please see my response there. It's not as simple as you're implying; often your throughput suffers if you can't handle requests fast enough. MICA, for instance, injects packets directly into L3 to avoid cache misses; it's important that MICA handle these requests as fast as possible because otherwise the L3 cache will fill up, the queues will start to back up, and ultimately they'll put backpressure on incoming packets and slow down the whole system (greatly decreasing throughput). The fact that it has tail latencies measured in microseconds is good on its own, but its real benefit is its effect on throughput!

I believe the poor performance of database is often caused by people who are new to the subject writing applications in such way that they do N+1 problem.

I think we're in agreement writing applications in the database isn't a good idea.

By "empirical proof", I mean that I have never seen anyone conclusively prove that switching data access from stored procedures to ad hoc SQL through an application was the cause of performance improvement. I've seen proof that removing complex application logic from the database fixed a problem, or that better indexing improved performance, but I've never seen anyone prove that removing stored procedures alone fixed the problem.

Further, there's nothing in your proposed solution of scaling out with readable replicas that precludes the use of stored procedures for data access. As a huge fan of both databases and separation of concerns, stored procedures make tremendous sense. They let a domain expert tune the database as needed. Even going so far as allowing that expert to re-write queries, provide optimizer hints, or even change the physical data model for better performance without ever changing application code.

Although I may or may not be competent, I have scaled "stuff" in a database. But I do appreciate you getting a vague ad hominem into the first sentence. Bravo.

The typical bottlenecks that I found were almost always IO - either through crappy storage, crappy indexing, or some combination of the two. Modern databases typically aren't bottlenecked by the lock manager.

I'd also agree that moving work out to a NoSQL database is particularly tricky. For three years I maintained the .NET Riak client and helped developers make better decisions when they were considering moving away from an RDMBS.

Scalability IS NOT performance.

I like to say that scalability is like a Mac truck. Good if you have to move a lot of stuff, but not necessarily the best tool for getting your groceries. Moving logic from stored procedures to the application adds latency and network traffic. This is never going to be good for how fast you process an individual request. However it can let your system handle more requests per second.

That's only true if there aren't resources taken up by your in-flight transactions, and if those resources don't exceed the cost of just running the logic in-place. Otherwise, you're not sacrificing latency for throughput, you're just sacrificing latency and throughput. Unless you have a very heavy application-level thing to do that doesn't require periodic new access to data, that's usually not the case, especially if you're trying to process requests at line rate (NIC queues will get exhausted just trying to hold all the pending requests in memory, and you'll start to suffer from lock manager contention, page buffer swapping, etc.).

True.

But my experience with Oracle specifically is that handling connections is just general overhead, while the real failure modes have to do with overloading random latches somewhere deep inside of the system. I also found that logic in stored procedures caused us to hit limits faster than leaving it in the application.

The specific case I saw this in was a simple set of queries to test if you were in an A/B test, and if not to assign you to a random variant. After I left my application logic was moved into a complex stored procedure, and they then had scalability limitations that they didn't have before. For political reasons they declared success and ran fewer A/B tests...

(I've used a lot of other databases as well, but Oracle is the only one I've really pushed to its scalability limits.)

The database is usually the bottleneck because of IO, not because of load on the actual database in my experience. Assuming you know how to design and index a database.

That's a valid point. Seeing as I don't build anything that has to scale well I can't really comment on this, but I don't think it scales too badly. With stored procedures it's more or less just your model in the database, not your whole application logic. Using a database to replace your controllers wouldn't scale so well, but replacing fat models would still scale fine.

It significantly depends on the application, but it shouldn't be more than a factor of 5-10 or so...

A factor of 5-10 can matter. A lot.

Many applications don't need Facebook/Twitter scale and will never have this problem.

How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?

That's the way I've done things in the past -- save stored procs in a file in a project repo, along with deploy and rollback scripts.

In Microsoft land we have SSDT and stored procedures live in source control right next to the schema and any CLR functions. The whole thing is built, compile-time checked, and deployed. It's all pretty wonderful.

I myself am wary of stored procedures except in very specific and uncommon circumstances. That said, you could absolutely version control your stored procedures by creating them from within database migration files that are version controlled by default.

What is the source of the wariness?

Don't know about the parent, but I always felt that stored procedures end up by incorporating a good share of the business logic, extracting it from the main code of the application. This creates a messy situation in which you have your business logic split up between two completely separate and different systems, one of which (the stored procedures) is much harder to read, write, maintain and test. That said, I also deeply hate ORMs, I much prefer to use thinner layer to interface with a database (query builders and row mappers).

That is normally considered a good thing other wise different applications accessing the same data have to roll there own - do you really want multiple versions of biz logic.

eg a large organizations like a bit telco may have multiple applications that update customer records.

Should they do that through a single service api instead of directly hitting the database?

Yeah, the proper way to be doing that is via a middleware api that can be maintained and versioned by it's own team, rather than coupling all these applications to a very specific database. If you have 4 apps talking to the same oracle database, and you decide to ditch the license, that's going to be way harder to deal with than if you just had one api being maintained by a team who's job is to know databases.

Midleware is the normal term

I also do think putting too much logic in stored procs is a danger, and was/is a scourge for some shops. But was curious if that is the primary objection.

I try to avoid anything but mapping or querying data in sprocs, but sometimes they are a better place to put logic that is needed to ensure consistency. Your database will outlive your application, or you will eventually need to integrate data from a third party system - as a result your database engine is always the last line of defense to protect your data, and if that means using a sproc than so be it. I'll admit, I've written some business logic into stored functions in PostgreSQL, but it's mostly a background job to update cached data because it is much faster to do it in the database than pulling a bunch of records down to a service and sending them back up.

It's fat models vs thin models, just with fat database vs thin database. You have to be careful.

I am working on an app where the original developer has followed a "thin models fat controllers philosophy". I have never seen this referred to as a good thing anywhere else (and Django works far better the other way around). Is there any information on why this might be a good idea / benefits to this approach?

How about the fact that in mysql you need root access to add / drop stored procedures? So my app's installer requires the regular user to have admin credentials to mysql instead of just creating a database for them?

There is hardly any reason to use stored procedures anyway, when your favorite mysql library supports multiquery.

Yes, there are tools to do this. It varies by database server and may need custom integration with your deployment system but they definitely exist.

The alternative is the old StoredProc_v1, StoredProc_v2 etc in the database and have the application check what's available. This allows you to roll the parts independently.

The big advantage with stored procedures is they can benefit from the DBMS caching and applying additional optimisations (depending on the DBMS of course).

Yes. Data warehousing projects I have worked on have lots of stored procedures in with the code.

One way that works is make sure you make a simultaneous tag of your code and stored procedure that works with it. You really want development to run hand in hand with stored procedure development, not as two parallel processes.

I have found Sqitch (http://sqitch.org) to be a useful solution.

> 30 lines of Perl to wrap DBI

In Java it's even easier with JPA @NamedStoredProcedureQuery annotations

http://www.thoughts-on-java.org/call-stored-procedures-jpa/

But once you have run the stored proc how do you display it to the user? How do you get the data from the UI to the stored proc to execute? You write some code or it happens by magic? If you write some code, then you have just written an ORM.

It's overly reductive to consider any code that interacts with a database an ORM. Broadly speaking, the big claims ORMs have made are 1) the ability to automatically map between an object model and a relational data model and 2) portability between database vendors. #1 falls apart when the object model becomes complex enough to suffer from object-relational impedance mismatch. #2 may work for smaller or simpler applications, but you're generally going to have to take advantage of vendor-specific database features to achieve optimal performance with applications that have anything more than trivial scale or performance requirements. Going with stored procedures generally means giving up on both #1 and #2 in exchange for a more flexible design (relational model can be very different from your object model) and higher performance (at the cost of having to hand-write all your own SQL, of course).

> If you write some code, then you have just written an ORM.

What? No, I've written code to query a database. Not an Object-Relational Mapper.

  my $q = $db->prepare("get_my_stuff_by_name(?);");
  $q->execute($name);

or with fancy metaprogramming:

  use DBMagicStuff 'postgres://…';
  get_my_stuff_by_name($name);

That's not really an ORM.

You are both partially right. An ORM deals with the black magic of connections, parameters, transactions, etc. If you don't use an ORM, then you still have to deal with those semantics. But I do agree that you aren't writing an ORM.

> connections, parameters

The database driver takes care of that, not the ORM.

(It's also not black magic.)

> transactions

Huh? That's a database feature. BEGIN/COMMIT/ROLLBACK are ANSI SQL.

ORM is an _Object Relational Mapper_. You're confusing it with a database driver (or, in the case of transactions, just a database in general).

In Go I only need to annotate the column name of the struct variable to have it load automagically from DB using just SQL and a little bit of tooling (sqlx). 95% of the convenience of an ORM, none of the problems.

That's because sqlx (and the go foundation) are already a basic ORM.

You could argue that anything that interfaces with a database is an ORM, but it is certainly no ORM in the Hibernate/ActiveRecord/EntityFramework sense.

It does very little "magic" and you're in full control of the SQL from the start.

Sure, let's say an ORM is anything capable of turning normal app code into SQL statements by itself.

What do ORMs do that's magic? What are the problems? Are you not in full control of them? It's your code after all that's using them.

I really don't get how they suddenly force any issues on you that you don't create yourself. The output SQL doesn't really matter if it gets the job done, and in cases it does you still have full control to write it yourself, and even use the same ORM to save you time in executing that.

>What do ORMs do that's magic? What are the problems? Are you not in full control of them? It's your code after all that's using them.

http://www.joelonsoftware.com/articles/LeakyAbstractions.htm...

Thanks for these questions. Initially it wasn't clear to me what the difference was either, but now I understand the distinction a little more.

The ORMs discussed in the article map a whole object model to a whole relational model. The idea is that a single piece of primitive data, say a bool flag on an entity, will have one location in the relational model, and one location in the object model, and the mapper's job is to get the data from one to the other and back again.

Hibernate will guarantee things about the object model, for example within a particular session, the entity A which maps to a record with id 1 in the database will be represented by the same object instance, so even if A is referenced indirectly through two different routes, eg, obj.foo.a and obj.bar.a, it would reference the same instance of A. The instance will only be fetched once, can be mutated through either route, and then saved back to the database using the mapping.

That's the sort of thing which is possible with a single mapping, but the abstraction breaks down outside that mapping. For example, questions like this on StackOverflow:

http://stackoverflow.com/questions/2470129/how-can-one-fetch...

He wants to select part of a database record, not the whole record. The mapping will only be defined for whole entities, so he has to use the complex projection syntax instead of a normal fetch. The top answer suggests mapping the data into a User object anyway, which will result in his code having some User objects will all their properties set, and some with just a subset.

This puts pressure on the programmer to stick to the single mapping. Partial fetches like this could lead to an object which represents a particular user which may or may not still be the same instance, and it may or may not support being saved back into the database.

> Are you not in full control of them?

So, yes, you are in full control, but the benefits come when there is a single mapping between the relational model and the object model. In practice, there isn't a single object model, let alone a single mapping.

The issue with it is that SQL is a bit different kind of language. When using it we don't tell the database what to do, but instead we say what we want. That does not translate well.

Second issue is that tables and their relations don't map too well to objects and their relations.