What ORMs have taught me: just learn SQL (2014)
wozniak.ca
wozniak.ca
In 2016, this sentiment is outdated. A few points:
1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at your application layer, and typically better performance (unless you plan on hand-rolling your own multi-level cache system). But you still must understand relational database fundamentals.
2. If you're not using an ORM, then you ultimately end up writing one. And doing a far worse job than the people who focus on that for a living. It's no different from people who "don't need a web framework", and then go on to re-implement half of Rails or Spring (without accounting for any CSRF protection). Learning any framework at a professional level is a serious time investment, and many student beginners or quasi-professional cowboys don't want to do that. So they act like their hand-rolled crap is a badge of honor.
3. Ten years ago, it was a valid complaint that ORM's made custom or complex SQL impossible, didn't play well with stored procedures, etc. But it's 2016 now, and this is as obsolete as criticizing Java by pointing to the EJB 2.x spec. I can't think of a single major ORM framework today that doesn't make it easy to drop down to custom SQL when necessary.
I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from your framework with an ORM). For convenient access you can roll 30 lines of Perl to wrap DBI or whatever (I use Perl for most web backends these days) and call your stored procedures in normal syntax with a little metaprogramming.
Maybe this sort of scheme (heavy usage of stored procedures and offload almost everything to the DB) doesn't work for everyone, but I like databases and it works for me.
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).
eg a large organizations like a bit telco may have multiple applications that update customer records.
There is hardly any reason to use stored procedures anyway, when your favorite mysql library supports multiquery.
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.
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);
use DBMagicStuff 'postgres://…';
get_my_stuff_by_name($name);
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).
It does very little "magic" and you're in full control of the SQL from the start.
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.
http://www.joelonsoftware.com/articles/LeakyAbstractions.htm...
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.
Second issue is that tables and their relations don't map too well to objects and their relations.
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.
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.
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.
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.
The ORM can do transient fault recovery (including other cloud patterns) as well as modernise the interface (Micro-ORMs in general).
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().
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.
A factor of 5-10 can matter. A lot.
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.
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.
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.
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.
Performance and scalability are very different things. Stored procedures are great for performance, but bad for scalability.
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.
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.
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.)
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.
In Java it's even easier with JPA @NamedStoredProcedureQuery annotations
This. When composing complex queries, we really want type checking and SQL injection safety.
Anyway. Type safe queries like QueryDSL is extremely nice to work with.
But as was mentioned, it all boils down to this: There IS NO silver bullet.
You have to learn SQL, and then the ORM tool. And the abstractions will leak, and you will be pissed of sometimes, but It Is Worth It because you will save a lot of development time.
There are some pain points. Large joins where you'd need to eager-fetch a few one-to-many "leaves" at the end of a huge and complex join is a bit of a pain, as the ORM will need to split the joins for efficiency, and there is no obvious way of reusing the complex part between the calls available to the user of the ORM.
(Like how you could use a temporary table when using Oracle for instance. Nothing should stop a ORM to use that as a join-strategy though, when I come to think of it...)
The other obstruction is mindset. To use a ORM efficiently, the developer need to step away from the data-layer model.
There is no separate data access layer when working with persistent objects. The objects represent the model, and are simply persistent. If they are changed, the change stays.
Preferably, the model should be available to the whole application, and the objects should be changed and used where it is suitable, not restricting access based on that they will trigger a database access. (The important thing should be to keep the model and it's rules together - in some sort of abstraction, not that some things happen to write to a database.)
Only on some languages. As I mentioned on another comment, on Go I'm very productive using simple database/sql + sqlx. On C# I could die writing mapping boilerplate before getting any business logic done.
Type-safe ORM's don't preclude integration testing, end of story.
I wish... Drupal had an issue in ORM itself. https://www.drupal.org/SA-CORE-2014-005
Sure they do, with a proper library. Haskell's persistent library does this very well.
delete $ from $ \t -> do
where_ $ (t ^. TutorialAuthor) ==.
(sub_select $ from $ \a -> do
where_ (a ^. AuthorEmail ==. val "anne@example.com")
return (a ^. AuthorId)){-/hi-}
tuts <- select $ from $ \t -> do
where_ (t ^. TutorialSchool !=. val True)
return (t ^. TutorialTitle)
The library in question is modeling relational theory at the language/type level. That's not remotely like an ORM.
That said, persistent doesn't do OO.
If I can model your type/language model as a higher-kinded dependently typed lambda calculus (e.g. using Boehm-Berarducci encoding for non-turing complete recursive ADT definitions), then it's not OO -- it's functional programming, rediscovered.
Can't say I care for the syntax much (`^.`, `where_`, etc.) but I guess that's par for the course in Haskell given the open world (i.e. module system leaves something to be desired relative to OCaml and SML).
[1] http://www.yesodweb.com/book/sql-joins#sql-joins_esqueleto
Ironically, this means that OCaml is probably the best [reasonably popular] language to use an ORM in.
Only if you assume that Object Relational Mapping is the only way to express type checked database access at the application layer.
ORMs are broken by design; there's no lossless bridge between relational theory and OO -- only inconsistent, error-prone, complex approximations.
Instead of bringing relational theory to the programming language, ORMs try to emulate OO behavior/method dispatch/etc in a world of transactions, deadlocks requiring retry of previous operations, on-disk serialization of data, etc.
In 2016, it's ORMs that are outdated.
A project with nontrivial data relationships should start with the data model, not with a code model superimposed on data. ORM leads to bad data models and performance problems that are unfixable.
The relational model is more flexible -- you can slice data up in any number of ways, rather than through a fixed hierarchy.
The relational model was developed by Date/Codd to address the needless complexity and brittle nature applications built around network or hierarchical databases of the 60s/70s, which are in many ways analogous to today's OO relationships.
I'm not impressed with NoSQL; they have no answer to the limitations of relational theory beyond simply abandoning formalism entirely.
Marshalling in general needs more compile-time support. Kludges such as Google protocol buffer preprocessors are a fast but clunky way to do it. It would be useful if languages could be given a reference to an SQL CREATE TABLE and could use that information usefully. Field names, type information, and enum values should come from the CREATE TABLE info.
Check it out: https://github.com/knq/xo
As a professional cowboy -- I detest layers; if it's running on my server, I need to support it, even if i didn't write it. An ORM or a web framework adds thousands of lines of codes and usually two or three layers of indirection. It's way less effort to just output some HTML, or just make a database query, than to learn a framework or ORM, and as a bonus it's usually faster too. Also, it's a lot easier to fix slow queries when there's one place that has the whole query (with placeholders where possible -- I may be crazy, but I'm not insane).
No tool is ever a substitute for discipline.
My experience is exactly the opposite. Humans are too fallible for discipline to ever be reliable. If you want something done, automate it.
The enterprise wants everyone to be a jack of all trades and to understand everything. Part of the reason for the massive layering that takes place is so that things are compartmentalized and easier for an individual to pickup and put down as they're shuffled around from project to project. Standardization, documentation, consistency, and discipline are essential for enterprise developers to be able to quickly grok a project and make the necessary changes.
Automation also plays an important role because it allows less skilled and knowledgeable individuals of lower pay grades monitor the blinky lights and only call on the big boys when things fail.
However, if your application is more complex, you should be careful. Chances are, you will add your own layers. And chances are, they will suck more than some popular framework or ORM. It's not that you make a conscious decision, a plan to do so, usually it's bit by bit. And having seen all the custom frameworks and shitty ORMs (because it seemed easier at some point early in the project), don't write your own ORMs for the sake of the maintainers mental health if not your own.
Do you work in a team? Or do you plan to hand over that code to someone else for maintenance at some point in time? If not, then by all means, go ahead. Another advantage of ORM/web frameworks is that they are properly documented and only have to be learned once. I once had the displeasure of maintaining someone elses custom 'framework' with no documentation.
Did anything of what the parent wrote sounded like it was worse for teams?
At least that's why I would consider working on a team like that much worse.
They wouldn't know vanilla SQL, HTML, WSGL, etc, --decade old standards-- etc, but they would know some random framework like Flask?
Well, Flask is 95% just two persons [1] and 5% among 10 others. And the one responsible for creating it and writing 60% of it doesn't even have it as his first priority (has multiple other libs, codes Go, etc).
A lot of widely used Javascript libs are not even that lucky. Heck, GTK+ itself, one of the pillars of Gnome and used by millions, was down to 1 maintainer a few years ago, who openly complained about it (not sure if things improved since).
Unless it's some large, focused project with bit traction, tons of books and courses on it, etc., like React, Angular etc (usually with some corporate backing too), a lot of the things that pass for serious frameworks and libraries are anything but, and a lot is hardly a "community" project either.
Some person slapped together something and thousands adopted it because "frameworks are better than writing your own code". Then the person moves on, or some small team that leads the project decides to rewrite it from scratch to try new stuff because they have short attention spans, and you're left with an abandoned code in your hands, which you haven't wrote yourself, and don't have time to understand fully.
A framework can be good to start with if you're doing stuff close to its confines, but otherwise its how Joe Armstrong described OOP: "You wanted a banana but what you got was a gorilla holding the banana and the entire jungle".
Notice how all major companies, from Facebook and Google, to Twitter and Microsoft and Apple etc have written their own frameworks.
Perhaps their programmers didn't know that already existing frameworks are better? Or were they focused on their actual problems and pain points, instead of lowest common denominator solutions?
http://php.net/manual/en/function.setcookie.php
grep -r setcookie in my code will let you find out if i sent any cookies. Incidentally, this is the same grep I would have to do on a framework, but the framework (pick one) is probably bigger than all of my code.
As an example -- we used to have a WordPress blog; now we have a custom blog that's a total of 3628 lines of php that lives on the frontend and I included the Makefile for deployment and some extra include files cause I don't want to spend the time to consider which ones aren't needed. Content is from text files pushed with another process.
By comparison Wordpress includes 298,643 lines of php. Wouldn't you rather dig through all of my code than all of theirs? (Yes, Wordpress has a bunch of features -- but even when you turn them off, the code is there lurking, and sometimes it still runs)
Someone else from my team fixed up the mobile support and handles the css/javascript -- they were able to just look at the code and do what needed to be done. (The mobile page was broken in our install of Wordpress too, so I got it to parity anyway)
It took me about a week to write it and polish it (three days dedicated, including exporting the content from mysql, two days I was doing other things too), but on the plus side, I don't need to spend half a day to figure out how to deploy a Wordpress upgrade every time they have a security release; there have been 12 security releases since then, so I've earned a day of my life back.
Maybe read the article first? The headline isn't a complete summary. You wouldn't have written point 2-3 otherwise - have you read what he wrote about DB schema in the article?
Hand-rolled crap is at least crap that was rolled to suit the problem you're facing, and you'll do it using tools you understand. You're not learning "any" framework, you're learning a bit of them all and hoping that's enough to point you in the right direction. Sometimes it's not, and then you're stuck with a hammer for screws and someone else's code that you don't understand.
There are other ways, like when using "event sourcing", or servers like postgrest [0] that give you a REST-api on top of your database.
ORMs are mainly useful if you have objects to create, read, update and delete.
I don't understand why people keep repeating this myth. I do not use an ORM. I did not write one, or anything resembling one. I have absolutely no problems with accessing my database from my applications.
Why? That presumes that you are using objects, doesn't it?
What i mean by that is, any simple CRUD operations are much easier in ORM. The hard things, i mean any complex queries that need more than one join you are probably better of writing yourself.
In the end i prefer to do inserts, updates and deletes with ORM (or some other database abstraction tools) but most SELECTs i write myself, fetching exactly what i need and mapping result to objects if needed manually.
ORMs are a tool, that's it. The relational operations of SQL and the object-oriented (or functional) logic of your application code are usually very different and it's nice to have a mapper that lets you interact with your app's language while it takes cares of automatically mapping it to SQL.
For 99% of database ops where it's pulling some stuff out, editing some fields and then saving it back, an ORM will save you a massive amount of time with better safety, security and performance. Even complicated queries/mappings/procedures run pretty well and you can always write raw SQL when you need it. Modern ORMs will even let you execute custom SQL and still give you conveniences like easy parameterization and hydrating the results back as objects.
Maybe I've been spoiled with the .NET ecosystem with great ORMs like NHibernate, EntityFramework, and Dapper (along with C# features like Linq) but it seems like most of these complaints are from people who use shitty ORMs or can't comprehend that it's just a tool that they can choose to use or skip, at very granular method by method level. There's absolutely no reason for any extreme here.
(I might add, Dapper in combination with C#6 even gives me enough type saftey to be happy. String interpolation and the nameof operator complements dappers DTO approach nicely)
Since you seem to have similar experiences Im curious. When would you ditch dapper for the others?
That someone would say "Dapper is all they need" leads me to think they only work on very small projects. You will drown in large projects if you use Dapper everywhere. Better to use a high level ORM like EntityFramework and sprinkle in some Dapper in performance critical code.
The only time EF creates performance problems is when the developer doesn't understand the concepts behind SQL. This isn't an ORM problem, it's a training problem.
And, I don't use dapper for performance, I actually like its transparency and friction free interface to the db. Let's me get things done without workarounds. And most importantly, lets me use ssms with a repl flow, copying sql verbatim between vs and ssms.
Same here. And I don't know anybody who has worked on large enterprise EF systems that hasn't come to roughly the same conclusion. The type safety EF offers is extremely nice to have, no doubt, but the problem is it makes it so easy to create performance problems that _every_ system ends up with them. Probably half of my billable hours in the last few years has been addressing EF performance problems.
Dapper, with a few extensions, can give you type safety for 80% of your typical queries, and the rest can easily be done in stored procedures or with (my preference) Dapper's SqlBuilder. And using SSDT instead of EF code-first, you can easily and in a version-controlled way manage your schema, views, stored procedures, indices, etc., simplifying performance management and getting static analysis in your sql while still not losing straightforward and configurable migrations.
Functional and relational models work really well together.
At Standard Chartered we even went so far as to add relations as a datatype to our Haskell-like language. It's a charm; and comparable for me to my experience first going from C-style arrays only to eg Python's dicts.
Only that this time the dict-style data organization was the `before'. Dicts are essentially equivalent to hierarchical databases, a model whose flaws relational databases were invented to address.
Have you written about this? It sounds really interesting.
I never understand why people want one or the other exclusively. Both have their place.
I think stored procedures would be very useful if they integrated better with source control and the app code. Maybe we need an ORM for stored procedures that automatically creates stored procedures from the project code?
That also sounds fun if you use the proc from more than one location...
It's not a version control scheme; once deployed the procs are never updated.
SQL is a terrible general-purpose programming language by the standards of today, which makes it a terrible way to express business logic.
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.
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.
90% of the queries in my app are no more complex than selecting from a table with a simple condition. I definitely find
users = User.where(has_foo: true).limit(10)
rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10")
users = rows.map { |row| User.build(row) }
Likewise, any app of sufficient size seems to end up with a handful of queries that really are a pain (or impossible) to cram into an ORM. Trying to do so would result in an unreadable mess, and using raw sql improves the situation immensely.
do you really think the first one is /that/ hard to understand?
Also note this is an observation of mine, not a logical argument, so trying to logically argue about why it shouldn't be harder would be arguing a point I'm not making. As much as I don't like people bashing strings together programmatically to generate SQL queries due to the ease of screwing it up, I observe that a lot more programmers are capable of this (even if they screw up the security) than seem to understand how to use things like ActiveRecord equally fluently. YMMV.
var users = Users.Where(u => u.HasFoo).Take(10);It's like regexps - imagine if there were 30-40 different implementations instead of 2 or 3.
Readability by maintenance programmers? How many Rails programmers will not know ActiveRecord? How many are going to be better at maintaining SQL than they are at maintaining ActiveRecord queries?
Readability by business people who don't know any programming languages? The first is a lot clearer than the second IMO.
Maybe, just maybe, it's possible that ORMs are useful for solving certain types of problems and less suitable for other types. If you work on web stuff or applications that are report-oriented where most of the time you're just fetching data (possibly with complex queries) and rendering it to display, then maybe ORMs aren't a good fit.
On the other hand if you work on client-side apps where your objects are backed by a database but are otherwise long-lived, then sometimes the other features (beyond SQL generation) that ORMs provide come in handy (tracking units of work over the object graph, maintaining an identity map for consistent access to objects, and providing change notifications when an object or collection of objects is manipulated).
If you've never needed any of these features that's perfectly fine. I've never to needed to use a bulldozer either. But I'm not going around declaring bulldozers useless just because I've only ever needed to use a shovel.
I do still use Hibernate, probably because my usual framework of choice makes it so easy to, but anything above medium complexity goes through jOOQ nowadays.
SELECT
posts.*,
(SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count
FROM posts;
Admittedly, that was not the best example. The last time I need something more intertwined than a simple COUNT in subquery, the answer was "give up and just use Arel." But at this point it is no longer quite ActiveRecord, but rather a SQL without strings.
This is my biggest gripe against the Active Record pattern in general, as it ties its model too tightly to the underlying database. It is convenient for a simple CRUD tasks, which may fit about 90% of use case, but that's not the only thing the database is capable of.
Post.objects.all().annotate(Count('comments'))
SELECT posts.*, COUNT(comments.id) AS comments_count
FROM posts
LEFT OUTER JOIN comments ON (posts.id = comments.post_id) GROUP BY posts.id
I'm surprised there isn't more explicit support for this in more ORMs (things like not having 'save' methods on the ORMed classes).
For example, let's say you have a query that gets search results, and depending on whether the visitor is logged in or not you also may want to know whether a given search result happens to be a favourited item. The way I understand it, I would have to do for example:
$query_text = 'blah';
if (user is logged in)
{
$query_text .= 'SQL pertaining to favourites';
}
$stmt = $dbh->prepare($query_text);
if (user is logged in)
{
$stmt->bindParam(':user_id', $user_id);
}
What really bugged me though was the idea of having to repeat the decision logic that determines what the query ends up being twice... though I just realized that putting name/value pairs into an array while building the query, and then using that when the query is built to bind them to it, is probably fine.
function getResultsForUser($DB, $user_id)
{
$query="search for user :user_id, SQL pertaining to favourites";
$stmt=$DB->prepare($query);
$stmt->execute(["user_id"=>$user_id]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
function getResultsForVisitor($DB)
{
$query="blah";
$stmt=$DB->prepare($query);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
- is the user logged in, and maybe even owner of the profile we're at?
- are we looking at all nodes, or the subnodes/subtree (don't ask) of a node?
- do we want to display tags/authors/sources?
- are we filtering by tag/author?
- what's the display mode: list or full?
But as I said in my other comment, I realized I can just remember the parameters/values as I build the query string, and then bind them all after creating the query. That way I can have my messy cake (that should feel so wrong but tastes so right) and eat it with a plate and a fork like a proper person.
Post.select("posts.*, count(comments.*) as comments_count").joins(:comments)Unfortunately, what I needed to do back then were more complex than that (say, I need to perform query on top of subquery, e.g. "SELECT ... FROM (SELECT ...) AS a HAVING ... GROUP BY ..." sort of thing) which seems to be something ActiveRecord wasn't designed for.
In the end, I solve the problem by using Arel with an ActiveModel and unwrap all the data manually.
Post.select("posts.*, COUNT(comments.*) AS comments_count").joins("LEFT JOIN comments ON comments.post_id = posts.id").group(:comments)I've worked with multiple ORM frameworks during my career. And I always reached the point where developer needs focus to framework internals instead of delivering new features.
Yes, usually most situation which developer didn't expected can be solved by configuring ORM framework, changing some configuration, providing additional parameters or calling some methods, but what that means? You need fully understand your framework if you want to be sure you application does exactly what you want and not misbehave.
So next time when you'll be thinking grab ORM to make things simpler, ask yourself if you really have enough time to fully learn the framework?
Partial objects, attribute creep, and foreign keys: defer the loading of columns [1] to suit your use-case. You can even have different mappings that deal with different subsets of columns depending on your situation.
Data retrieval: sqlalchemy is so good for querying that I've mostly stopped using sql for anything other than complex exploratory analysis work. Sure, you need to know sql to be effective, but whatevs - eg Window functions? Done [2]
Dual schema dangers: I understand the concern, but again, you can choose to map this as you please and I don't see what doing raw sql gains you here. You need to change the schema, if that affects your application, you need to change your application, if it doesn't, do you need to change your orm code? I routinely migrate my db and release changes to the orm code later.
Identities: I admit there are a couple of times I need to flush in my app outside of the normal lifecycle, which I don't like. With sqlalchemy, for the most part, you just connect objects and don't worry about the ids.
Transactions: Whatever happens you'll need some sort of transaction boundary in your code. Removing the orm doesn't gain you anything there, does it?
I've written about some of this before on here so I won't rehash it https://news.ycombinator.com/item?id=9180831
There's a time and a place for sql, orms and storedprocs. The more you know about each of them, the more effective you can be. As ever, learn the tools and never throw the baby out with the bathwater.
[1] http://docs.sqlalchemy.org/en/latest/orm/loading_columns.htm...
[2] http://docs.sqlalchemy.org/en/latest/core/tutorial.html#wind...
Since I develop in Scala both are very intuïtive to write.
Apparently Linq for F# is really good as well. As someone else mentioned in a comment.
I get a programming interface that doesn't attempt to hide sql and still my code is fully typed. And it's really easy to make your query logic modular: make the sql fragment a function that you can call. Like any other code you reuse.
I can't think of an advantage of ORM over this functional approach. Apart from ORM being more well known.
ORM / ActiveRecord is a great pattern imo, but if you want to make it do everything, like so many other techniques, you're gonna end up with a behemoth of a thing.
I've tried many different ORMs in the last couple of years, and while mine may not be the most complete or have a sexy API for joining custom query parameters into the results, I still feel my own provides the one and only syntax / workflow 'as it should be' in Javascript:
var serie = new Serie();
serie.name = 'Arrow';
serie.TVDB_ID = '257655';
serie.Persist().then(function(result) {
console.log("Serie persisted! ", result);
});
https://github.com/SchizoDuckie/CreateReadUpdateDelete.js
(browser that supports WebSQL required, obviously)
Narrowing the perspective is especially bad for beginners, because some options will simply never occur to them and the ORM will prevent learning by osmosis.
Let's say you need reliable, efficient cache invalidation (say, to re-render a static page when the data changes). Triggers and LISTEN/NOTIFY in postgres might save a huge amount of time (and even more in maintenance costs) over a custom solution that probably isn't right anyway.
Or maybe you need to prevent schedule conflicts. Postgres offers range types and exclusion constraints, which aren't supported in all ORMs.
Or to keep a remote system reliably in sync (even if it's a very different kind of system), logical decoding is a very powerful feature.
But how would you encounter any of these potential solutions if you are always behind an ORM?
Instead of using ORM I prefer moving data retrieval code to database layer with help of views. There is also updatable views that we can use to simplify database updates on code side and sometimes avoid using transactions. Separation of code and data logic is great concern when deciding to implement ORM. Nowdays database are very smart and convinient so there is no need to use ORM.
myobject, created = MyObject.objects.get_or_create(field1='1' , field2=field2)
That would take a number of lines. One query to check if the object exists, another to create it / retrieve it, plus application code to deal with that SQL.
That said, where is the academic problem in refactoring into the db and decomposing ORM queries into straight SQL where necessary/desired? I'm not a Fortune 500 Systems Architect, but I'm under the impression that this is a fairly straightforward process. Now, management of the systems will necessarily incur extra complexity, but that's a second order effect that should be assessed in the decision process as part of the cost estimations. Extra technical debt, for sure.
So, choosing one is technically cheaper and more simple and maintainable. Choosing multiple may be faster (or, in many cases, "academically satisfying"), but more complex with a steeper learning curve. Be sure to hire the best of the best!
The bottleneck in fixing technical things is never the technical stuff. After all, most of this stuff is 70s tech to begin with and the science has been worked out by much smarter people already. The social stuff on the other hand is a different matter entirely and once you're in an ORM quagmire it is impossible to get out.
Then again if you're a big enough business it probably doesn't matter as long as customers are paying you. All you have to do is plug-in another cog/programmer into the machine when the previous one wears out.
Beautiful code does exist in the software world. It's usually found in hobby, open-source, and research projects without profit motives, userbases, deadlines, and all those other complications that result in shitty hacks.
In the meantime, you could look at the crap that is most large-scale codebases as a barrier to entry that keeps demand for programmers high. Enjoy the money you get for cleaning up other peoples' messes, and then use that to buy time to make the beautiful, useless stuff.
Now you've got the ORM layer, which possibly translates to SQL correctly, to talk to a view, which is a front on the real untouched table. Its kind of a hassle.
Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application (or maybe thats the same as what you are saying). Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually it gets messy. Linux said something similar about bad programmers worrying about the code, and good ones worrying about the data and its relationships.
Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application. Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually things start to get messy. Linux said something similar about bad coders worrying about the code, and good ones worrying about the data and their relationships.
I created my own ORM-like access library which seems to work pretty well.
https://github.com/dieselpoint/norm
Comments welcome.
1. Using the DB Schema, generate stored procedures to load and save data together with code/validation in partial C# classes.
2. Transport data in XML format using single letter (or two) table/element and column/attribute names (auto generated). Use .Net to automatically build/ serialize the XML into objects. SQL Server/.Net have some nice XML features that make this simple.
This means that the only user access to the database is through authorized stored procedures. The users have no access to database views or tables.
I have implemented the means to load and save arbitrary hierarchical objects (e.g. an Order/Order Lines). A single request to the database can return a complex three level object which would otherwise take hundreds to round trips to load.
I agree with the observation that this would be hell to maintain. However, the people likely to maintain this system would be in just a different hell if they had to work with Hibernate or MS Entity Data Model.
Trying to write pure SQL leads to lots of manual unpacking of the result set which I generally dislike and is much harder to maintain and doesn't work well in practice compared to when LINQ actually works.
I think maybe what I've really learned is use scripting / loosely coupled type systems when working with SQL. In python, I usually just call sql directly rather than use sqlalchemy and its fine because of the loose typing and result set unpacking isn't terrible.
Unfortunately, it relies on property setters for doing the unpacking, so it doesn't interact super well with your code if you like to avoid unnecessary mutability.
The only publicly-available lightweight ORM I know of that does a good job with that is the SQL type provider in F#.Data. That one is head-and-shoulders above any other option I've found for working with databases. It does require that you write your data access layer in F#, though, which may make it a hard one to sell at work.
Edit: Looks like it actually does says it does support the enumerable but I'm questioning if it will do what I want. Will have to test out but I'm guessing there is a limit to the size of the array.
SELECT x, y FROM A WHERE z IN @ids
Works if you pass in a parameters object containing a property called ids that is a list/array of some sort (see https://github.com/StackExchange/dapper-dot-net#parameterize...).
On SQL 2016 this uses STRING_SPLIT across an nvarchar(MAX) which makes it effectively unbounded. Earlier versions and other platforms have restrictions on the number of elements in the array/list.
The IEnumerable support will generate (parameterized, I believe) inline arrays in the query. Not really my favorite, but it works.
The thing that I couldn't get over, and which ultimately led me to write my own micro-ORM at my last job, was the weak support for table-valued parameters. But I gather they've fixed that since then, so hopefully it's not a big deal anymore.
Django does a lot and does it well.
I don't think they have done a bad job with the ORM, but you really ought to know SQL as well.
That said, you're absolutely right that it is really bad at certain types of queries. There's no way to really influence joins. Subqueries are coming (https://github.com/django/django/pull/6478), and conditional aggregates became possible in Django 1.8 with Expressions support (which I authored). Queries that map over many to many relations that have .exclude() or .annotate() generally produce incorrect results. Most of the problems with the ORM are as a result of Django handling joins for the user - which is also one of its biggest strengths.
You should absolutely know SQL if you're using an ORM. If you're doing anything other than basic CRUD, you have to know SQL. An ORM should give you an escape hatch to write that custom SQL. Django's ORM does not save you from all the problems mentioned in the article.
- Extra conditions on a join (eg a prefetch filter): https://docs.djangoproject.com/en/1.9/ref/models/querysets/#...
- I'm not sure what you think you'd want explicit subqueries for, at least in a context that annotations, prefetch, etc don't already do. If it's that weird, just use `.extra(..)`
- Conditional aggregates: https://docs.djangoproject.com/en/1.9/ref/models/conditional...
- Not that you asked but also have a look at the new database functions stuff. http://stackoverflow.com/questions/38017076/annotate-a-comma...
And it keeps getting better. The list of "ORMs are rubbish because I can't do X" is evaporating. Yeah there's probably some stuff that never comes up but you can still run raw SQL through the ORM.
When you consider the development time saved and performance improvements (caching, good query writing, etc) an ORM delivers, I personally think it's hard to argue against them.
The author mentions Hibernate and SQLAlchemy, which are both DataMapper ORMs. But what about Active Record? It's true that AR will provide even more abstraction and distance from the database, but it also provides a lot more convenience, which for me in small CRUD projects (as most are) has been worth the downsides.
And as another posted mentioned, you can always optimize by replacing slow AR queries with custom SQL ones, and restructuring your database as your project scales.
I would caveat the performance issue, sometimes there is no better way, however i would always try to make things work in the ORM first before switching into native SQL to get the job done.
If you are going to do anything even remotely complex you're going to need to know that database technology otherwise your ORM is going to spit out queries that are dead stupid and a performance nightmare (pages taking 1 full minute to load). Then you end up fighting with the ORM to do what you want.
That's my experience.
I guess there are projects that are so simple the database layer can be abstracted away but I've never worked on one.
The secret to using an ORM is knowing exactly the SQL it will generate underneath. The advantage to using one is static typing as well as writing orders or magnitude less code than SQL. As well as orders of magnitude more understandable code than the equivalent SQL.
If you work on heavy enterprise applications the ORM can be your best friend or worst enemy. It comes down to knowing the tool you're using inside and out. It will make you much more productive in the long run. And sometimes yea, you need straight SQL or a stored proc to get the job done. That's OK.
False. To use an ORM you must know and understand data modelling and the Entity/Relationship model. I'd say, people who don't like ORMs are often those who don't understand that model.
If you do understand it, then you know that relationships can be represented 100% by code in an OO language, thus completely automated.
Why bother writing your own hydrators for each entity when you can model them directly in a library? it doesn't make a codebase more maintainable or readable.
Furthermore you could have an ORM that uses SQL directly for queries, and still eagerly resolve relationships. It's just not practical, that's why ORMs often come query builders.
That is the thing that abstracts SQL, not an ORM per say. ORMs only deal with relationships between entities. Query Builders build SQL queries.
Another important point is that it's, IMO, much easier to maintain ORM-based code, and another programmer coming along after me can more easily get started on understanding what I've implemented. Once place I worked in the past we had a 'SQL guru' who wrote these massively complicated SQL queries that could be understood by no-one by him.
More to your point, I think the problem is the vast number of developers using ORMs without knowing SQL and running into big problems with correctness and performance.
This isn't to say that one day we won't throw away django or use more SQL. We just had real world schedules to deal with.
Just write the SQL :) - relational algebra is not that hard...
Take a really thick client, like say a graphical diagramming tool that makes your machine's fan whir loudly when you start it up. Here an ORM can be a great tool to efficiently manage a constantly evolving, large cache of your hot objects, and keep them syched with the backing RDBMS.
Big batch programs can be another good use case.
Server side web apps and API servers though are the opposite of this. Web pages and API responses should be fast and small, so we don't normally build up a big cache, and in a stateless architecture we are normally throwing the cache away at the end of each request. In this case raw SQL is often easier than work with.
Let me backtrack a bit. There's a much better way to do things than the way we do it right now. A way that completely obviates the need for ORMs, or indeed any way to deal with the interface between application and database.
You see, there would be no object-relational Impedance Mismatch (and therefore no need for such clunky kludges like ORMs) if the database and the application were both written in languages of the same paradigm, either both OOP or both relational. Ideally, that would be a single language, that could handle both data and application logic.
There is such a language: Prolog [1]. Prolog is implemented as a relational database. So your program itself is the database. There is no separation between data and operation and therefore no ORIM and no need for ORMs, or stored procedures or anything, really.
And, yes, it's perfectly possible to do your web dev in Prolog. Here, see this explainer on the Swi-Prolog website, titled "Can I replace a LAMP stack with SWI-Prolog?":
http://www.swi-prolog.org/FAQ/PrologLAMP.txt
Hint: Yes. Yes, you can. You can replace LAMP (or LAM-whatever) with an LP stack, where all you need on top of your OS is Prolog itself. Its built-in Definite Clause Grammars notation [2] can be used to parse and generate javascript, html, xml, css, YAML, whatever you like. Swi-Prolog even offers translation to RDF, which is as natural as you can expect given RDF is also a relational language.
Here's a big fat tutorial to help you started:
www.pathwayslms.com/swipltuts/html/index.html
Can this be done for real? OMG yes it can. The Swi-Prolog website itself runs on an LP stack. There's a few more websites that do too:
www.pathwayslms.com/swipltuts/html/index.html
In short: you don't need to hurt yourself as badly as you 're currently doing it. There's no other need than of course, nobody wants to learn Prolog. I know that, I've made my peace with that and I've spent all of my so-far career hurting myself against the ORIM just like everyone else.
... but we could have had a better web.
___________________
Really, I don't see enough offered to counteract what little pain I feel using an ORM. The LP actually looks more painful (to me). Obviously I'm totally unfamiliar with this stack which makes it hard for me to see where the benefits lie.
In that case you'd probably find no benefit in giving up the use of one, regardless of what replaced it.
My advice is to avoid ORMs unless your project is big and its database schema is a good fit. And if it is, don't think, that ORM is easy, it's not. Learn how it works, read its source code, understand its inner workings. Or find someone who does.
I didn't have much experience with other ORMs, though.
The logical place for domain objects is in the Database. Yes, stored procedures, with object layer mappings of views. But the reality of the practice is that (a) the toolchain for the DB backend is quite firmly stuck in 80s and (b) most IT programmers lack SQL skills.
ORM's might do it 80% of the time. The other 20%, most of the mature ORMs provide a way to execute your own sql.
I like it, and it, IMO, keeps the API distinct from the access in the DB. You have to write your own SQL, which is good and bad. Huge amounts of control and performance, but higher portability costs.
Pretty much every other ORM I've worked with has been terrible by comparison.
I like to encapsulate inside of stored procedures, and write my code in such a way that it reads almost like a a procedural language. Heavy use of indentation, subqueries, and temp tables lets you carry results through the proc. I like to hang conditions off of joins, so related logic is in the same place.