What ORMs have taught me: just learn SQL (2014)
woz.posthaven.com
woz.posthaven.com
If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks, providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL, so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). None of those things should be about "hiding SQL", and you need to learn SQL first before you work with an ORM.
> Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks
None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM.
> providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL
So this is the real ORM juice. And ya, without an ORM you have to do this by hand.
So here’s the question: How well can an ORM map to your data model out of the box? In a lot of cases this is where the mess comes from. You need to set up basically a low level AI that can figure out the “right” thing to do as call sites all over your codebase are making arbitrary queries on a huge API surface.
And so the argument against ORMs is that it will take less time and be less error-prone to write bespoke loaders that take rows and build your data model than it will be to configure that AI such that it can do the “right” thing with arbitrary queries.
(If you don’t like the word “AI” here, use “expert system” instead.)
What's the difference? To me, an ORM is largely a query builder.
Query Builder: You build a query, just not in SQL. So you can get around SQL's limitations (like composition)
Try this:
> ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query.
or possibly:
> ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case.
There's plenty of places for bottlenecks to hide and premature optimization is the root of at least some evils.
Ha. More like:
ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 months trying to get the damn ORM to generate performant SQL (you even call Oracle support, which promptly dispatches an "engineer" who tries to sell you on another $500k of crap you don't need that won't really address the problem). You eventually just start writing queries by hand wherever you find a bottleneck caused by the naive ORM, which you could have done at the outset, but no one who actually knew SQL well enough was on the team back then.
... that you understand and can be sure are sensible.
> while an ORM builds queries for you
... that you have to hope are sensible.
That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.
But of course, as the queries get more and more complex, the flexibility of the ORM syntax approaches the flexibility SQL. In the end, there are many situations one would rather just use SQL.
For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous design.
And when you have to do a query that returns results from multiple tables? Guess what, you just use the normal SQL module from the standard library.
https://github.com/bgard6977/sqorm
Flyway & jOOq take a similar approach:
Taking the SQL-first approach also allows you to serialize without circular reference problems, since you don't just load the data, you also define a path to decompose the graph into a DAG.
1. provide idiomatic domain-object oriented query interface which it in turn translates to SQL
2. provide CRUD sql generation
3. provide some sort of session-based object lifecycle change tracking and management
#1 - The generated SQL is important on many levels. As things like HQL/linq/<your QL here> deviates further from the generated sql transparency is lost. SQL is normally brittle compared to your domain language which has better testability and type safety but still you have a handful of queries where it feels more sensible to write the SQL yourself.
#2 - Code which reflects on a type and generates basic insert/select/update/delete is usually pretty naive and easily done. With the exception of complicated legacy databases and iBatis-style tools ORMs which only support #2 arent really worth bothering.
#3 - I've found this to be the real benefit of an ORM. Being able to scope object lifecycles into clear units of work, buffer pending changes until a discrete point and get scoped caches for "free" have been hugely beneficial.
Each ORM unfortunately/inevitably come with great learning curve. It seems to unfortunately/inevitably bring lots of new concepts and conventions to the table required for the user simply must understand. Sometimes it requires you to re-arrange the way you may write your code to be more session-oriented.
I dont like the idea of AI managing how/when to apply changes to storage media. AI can be smart and efficient yes but you lose that important transparency/predictability. On the contrary I prefer very dumb/mechanical/predictable ORM, something not elegant but the behavior of what happens when is well-understood and easily scaled out to a large team. In my experience hibernate, EF, sqlalchemy have that sort of dumb/predictable behavior (however the SQL they sometimes generate can be performant but unreadable).
On the other hand refactoring your data logic is just business as usual, and you will probably do it in both cases anyway.
What AI do you need? You map your tables to objects and relationships between objects via FK relationships.
Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.
Entity Framework and any other Linq to data provider translates the Linq expression to the native language of the source data and does it server side.
LINQ only allows for a fraction of what is possible with SQL, and good luck having the best queries generated out of it, if the RDMS doesn't happen to be SQL Server.
I've seen that pattern in lots of homegrown applications, but I've never seen such a thing in a mainstream ORM. Care to provide examples ?
Nowadays I only bother with Dapper, MyBatis, using a mix of SQL and stored procedures.
EF only if the RDMS happens to be SQL Server.
That is only true in a one-to-many entity relationship (and even so, it is debatable). A one-to-one relationship can be modeled in the two objects, in one of them, or delegated to a third entity. A many-to-many entity relationship can also be handled, in OO, in various different ways. Idem for a ternary relationship or, basically any higher order relation between objects.
This is known, borrowing a term from electrical engineering, as an impedance mismatch between the two models, and it's not an easy problem by any measure.
Here is why.
Bitcoin cryptocurrency AI biomedical supply chain networking quantum systems-thinker.
I recommend anyone that has to deal with ORMs create their own at some point, in a non critical project. Not because they will necessarily create the next big thing (but who knows?), but because nothing quite gives you the perspective and appreciation for what these systems can do and why they have their pain points like making one yourself. You'll most likely not use your own module long after you've created it and then again surveyed what's already available, but it's invaluable in making a good assessment of those options as well.
Similarly, making a web framework yields similar benefits.
In both cases, a good understanding of the underlying technologies they build on (SQL and HTTP), is required, and if you don't have it doing in you'll have it coming out the other side (which is really the reason for this in the end).
Similar things exist all along the spectrum. From embedded OS's and compilers to javascript utility libraries.
What it comes down to is that a tool is best utilized when the person knows when and how to apply it appropriately, and that's as often as not an understanding of the tool as it is of the context.
A person intimately familiar with hammers and their uses can build some interesting wood furniture, but they'll likely never achieve the same level of product as a master woodworker that's just well acquainted with a hammer. Investing time and effort into tools provides only so much benefit. At some point, more knowledge of the craft itself is far more beneficial.
Joking aside, SwlAlchemy is very impressive software, and zzzeek deserves all this praise and more. Every time that there's one of these anti-ORM articles I feel like SqlAlchemy pre-emtively addressed all the substantive criticisms in its flexible, well layered, design.
(And in the interests of fairness, Django's ORM is also very good at making simple things simple).
I am a huge fan of the custom column types -- they have allowed me to have code that works against SQLite as well as a production databases with ease!
I wish more people aspired to be like him, because you know there's no more authoritative answer when you run into a Stack Exchange post and he's offering up his assistance.
1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so)
2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!)
3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM)
1 has been solved, 2 was never okay (point of the article), and 3 isn't really okay either because it's too leaky (performance specifics).
5. Type checking all your queries.
6. In code query composability.
But I totally agree that ORMs are too leaky of an abstraction for 2 to be really useful, and that 3 is much harder than it appears to be.
But I'm really surprised every time people tell me they look at the schema as defined into the ORM instead of at the table in the database.
I'm really jaw dropped the few times I know somebody doesn't even know SQL, only the ORM. Maybe they look at it as if it were the reaction of somebody that thinks you must know assembly if you want to program Ruby, Python or Node (I don't.) Still, if you work with a database you must know it's internal language, SQL or NoSQL. Your going to need it or build a mess.
And involving a DBA early in the project can make your database at least twice as fast, with the right schema and the right queries. Then you translate that into the ORM you want to use.
In my experience, nearly no one knows SQL, and the attitude seems to be that learning it at all is a waste of mental bandwidth.
On the other hand, I wonder if knowing none at all is better than knowing a little.
Very happy to not have and ORM on our stack for many years.
I believe that the interaction with the database is just about the most important part of code that you need to rely on. We have had messy data written to the database due to misused or misconfigured ORM (perfomance issues, bad orphan handling, session state problems, overflown sequences, to list a few) and decided that we should not rely on all devs touching that code to be experts in the ORM api to avoid the pitfalls, we rather our devs be expert in SQL.
Never had any of them complain about mapping using something like jOOQ. Type checking and composability are also built in.
Just because we are using SQL doesn't mean we have to do the mapping fully manual.
Plus it plays very nicely with kotlin :)
For short-lived processes, like typical web applications and command line utilities – that load data from the database, do something with it, and then purge it from memory again – I'm becoming less and less convinced that ORMs are actually a benefit.
On the other hand, if your application plans to map the database data to memory for long periods of time, with the need to keep them in sync, then you're probably going to end up writing something that resembles an ORM anyway, and poorly at that. In this case a good ORM is beneficial.
I used to work in an environment where we had relations as full first class data structures. They are very pleasant to work with.
We actually had 'relation-object-mappers', ie when we had to interact with other systems that didn't use relations, we often mapped them to relations internally to make them play nicer.
It wasn't about speed of execution, but expressiveness when coding. Later on they even added proper type system support.
Common operations were things like map/project, extend, filter, join, collect-by-key / expand, etc.
Just as Codd pointed out in his original papers, relations allow you to not have to make a choice about a hierarchy for your data.
Using key-value store like a hash-table in your program, or the much vaunted has-a relationship between objects would force you to make these choices. Thus making interacting with the data awkward for all but one access pattern.
Relations work best when your program is written in a style that deals largely in immutable data. (What we call 'purely functional', but people in dysfunctional languages have also picked up on the advantages recently.)
Or, alternatively, make it really painful when you do actually need to query hierarchies, along the lines of "give me all the tuples above this one in the hierarchy".
- replaced the DB driver mid way through a project to a slower, but more complete implementation. this went flawlessly, because it’s all so generic and in the end the same DB under the hood, so a simple change (unless you don’t use an ORM where i could see it being a nightmare)
- started a new project where i had to use MSSQL, which I’d never used before, but i’m a big fan of postgres/sqlalchemy. other than ODBC oddities, it was really simple to write the new app with all the same patterns i was used to with things like update on write, lazy joins, constraint deferral, and i think most importantly would be MIGRATIONS! huge help to have the same migration framework that i was used to
I worked on a project with a home-grown ORM (in C; it was horrible) that abstracted over both MySQL and Postgres ... except the overarching application required Postgres-specific column types and functions.
EDIT: Sorry, my mistake. The partent is talking about the parent comment, not the FTA
No need to rewrite all your SQL, just the ORM description of the model!
That is quite theoretical. My PRs for fixing non-spec compliant behavior in pgjdbc get rejected because they might break some ORMs (mostly Play). My PRs for adding MariaDB sequence support to Hibernate get rejected because there are additional MariaDB features that Hibernate doesn't support as well.
as an example: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...
heaps of this kind of thing are nicely dotted around the code so you don’t have to deal with weird driver quirks, maps “Text” column type to whatever it needs to be in your given database to have an unbounded text blob
id say that’s far more than theoretical
EDIT: typo
EDIT 2: also, on DB specific features, SQLA supports a bunch (not all). eg: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...
The presence of an ORM relying on these bugs should not prevent a bug in the driver from being fixed.
I did the opposite and faced lots of difficulties. Then I concentrated on plain SQL and suddenly I felt like I am understanding ORM much better.
I've found that the people who know SQL pretty well can actually do good work with or without an ORM. But the maintenance is a lot easier when the abstractions are consistent with the abstractions that are used by a popular ORM.
This gives you a lot of flexibility and power, but as zzzeek mentioned, you need to know SQL to understand how what the ORM is inefficient and to be able to replace it as necessary while still letting the ORM do what it is really good at.
Analogously, I'd say that being able to write a compiler, down to having it generate machine code or assembly, gives you a leg up when using a compiled language. I've met and interviewed a number of coders who had cringeworthy gaps in their knowledge, because to them, a C compiler was just some kind of "magic."
Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. The ORM is one piece to the puzzle, not a shield to prevent you from having to touch SQL.
One pattern I typically use is a take on CQRS: Hibernate for writing/updating/fetching/deleting a single instance of deeply-relational object model, SQL (I like jOOQ) for larger-scale fetches and any bulk actions.
Shameless plug for a write-up I put together last year: https://www.3riverdev.com/hibernate-orm-jooq-hikaricp-transa...
In once case the project was canceled, in part because it was so late due to the developers not knowing how to use a database. In the other case I put my foot down and removed NHibernate. The schema was so simple that it was just easier to put a few extra minutes into boilerplate code than to put time into learning a new thing.
I'd really like to see a good writeup about the use cases that tools like Hibernate excel at. The problem is that, in both cases, I had to work with a high-level manager who had very bad assumptions about what Hibernate can and can't do.
Disagree. You need to understand the relational model, but you don't need to understand SQL-the-language. I've written plenty of successful systems using hibernate without needing to touch SQL, and am much happier for it.
I think ORM is a great way to translate tables into real objects that have their own methods and properties that may or may not interact with the DB. I think that's where the real power is.
But for performance-necessary actions, yeah, SQL all the way (or rather a query builder).
99% of the time, an ORM is fantastic and will make you more productive while providing performance, security, and maintainability. They come in many sizes from thin wrappers around a db connection to full-featured frameworks.
For the other 1%, use raw SQL, or perhaps a query building tool to help with parameterization, composability, etc. In fact, modern ORMs will even let you input raw SQL and handle the conversion back to objects if you need it.
Saying ORMs are always wrong is just as dumb of a statement as saying all database access needs to be in raw SQL. They are just a tool and abstraction, like everything else you use in software development. You know the right time to use it.
That being said, not knowing SQL at all means a lack of general understanding in how relational databases work and will almost always cause problems.
I've personally never seen an ORM lead to success in the long run. But I also work in a space where queries frequently end up involving something that ORMs typically don't handle well: merge statements and pivot statements, window functions, management of the lock escalation policy to fine-tune performance, temp tables... The list is endless.
What I have not ever worked on is a relatively basic CRUD datastore. Which I realize is what most people are using databases for. So at this point, I'm putting my money on ORMs being a hole in one for that application. Because, otherwise, I just can't reconcile a statement like, "99% of the time, an ORM is fantastic" with the reality I'm living in. In my career, 100% of the time, when an ORM was present, it was invariably the single biggest piece of technical debt.
SQL is the database interface so of course using it directly without abstraction helps you get all the power and control. I have seem some cases though where a query-builder with a solid DSL can be a good middle-ground.
I have found, in my experience, that people who tend to want to write SQL over ORM usually want to do so because they simply know SQL better. That’s okay, there is nothing wrong with that. But that doesn’t immediately mean ORM systems are bad. No need to be tribal about it.
The problem I see is that many new software developers these days sometimes can’t see the forest for the trees because they dwell too much on what they think is better instead of simply seeing the software and abstractions as nothing more than tools in the tool belt. It happens everywhere — PC vs Mac, iOS vs Android, Scala vs Java, SQL vs ORM. It’s fine to have opinions, I have many, but as I’ve aged I’ve become acutely aware that my biases are almost solely rooted in the limitations of my understanding.
The objective of ORMs is not to replace 100% of your queries. That 10% might still require SQL or Stored Procs and that's fine.
ORMs give you:
1. Type safe queries
2. Ability to refactor easily, click to rename prop
3. Not having to handcode joins if objects are related
customers.select(c => { cust: c, orders: c.orders })
//If getOrders() returned a query expression
getOrders().where(o => o.city === "London")
//You extend it further
getLondonOrders().where(o => o.total > 200)
//^ These queries aren't executed yet.Yes, I've hit major problems with the EF (including one on Friday which was causing huge CPU and Mem spikes at the busiest time of our year). Yes, it's a PITA to debug the queries. Yes, it can do stupid things. Yes, some programmers can massively over-complicate it.
But you can prise the EF from my cold, dead hands before I give it up. ORMs rock. It's such a huge time saver as long as you KISS and accept you have to drop down to SQL sometimes.
And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right.
I've also turned into a huge fan of Code-First and Migrations, having been against them at first.
Are you referring to EF Core? I was considering learning it. What's this lazy loading issue? It's not one I've heard of before.
2016 - https://weblogs.asp.net/ricardoperes/missing-features-in-ent...
2018 - https://github.com/aspnet/EntityFrameworkCore/wiki/Roadmap
Basic functionality like group by, lazy loading, etc. is missing. By the look of it you can't even load custom types from hand-crafted queries, which is pretty ridiculous.
Haven't really been keeping that up-to-date with it. I personally feel the whole Core thing has been a massive cluster-fuck for their existing customers.
That's funny - the first thing I do when starting a new project is turn off lazy loading globally. I find it hides poorly-performing code until it's causing problems; the equivalent code without lazy loading usually just throws an exception.
Granted I've only been in the industry for 3 years, so /shrug
Also, question: you mention code-first and migrations... do you think you'd rather use SQL for your schema definition/migrations if the tooling was better? I find SSDT doesn't quite cut it :(
It's generally very cheap to do a single item query with no joins (as in a nanoseconds db query, yes, nano), and it only has to do it once, if you re-use that object again anywhere else, it's already in the context so it doesn't have to go to the db. Even doing them hundreds of times can be super cheap[1]. Add to that it only has to load each item once you can make intelligent decisions about what to .Include and what to lazy load.
[1] Caveat, Azure db connection latency often sucks so this isn't completely true, we had 5-6ms instead of the <1ms you'd expect. Presently at about 2-3ms. This is the fault of Azure and not lazy loading though. Causes a problem when you make hundreds. 100 lazy loading calls each taking 6ms would add 600ms, or 1/2 a second, to a request.
Wait - are you "Include"ing things you don't need? If not...assuming a sane query plan, shouldn't the single query (e.g. "Include" version) outperform the deconstructed series-of-queries that brings back the same data?
E.g., Included:
context.Orders.Where(x=>x.OrderId = 5).Include(x=>x.OrderItems)
select * from Orders o join OrderItems i on o.OrderId = i.OrderId
where o.OrderId = 5
var order = context.Orders.Where(x=>x.OrderId = 5);
var items = order.OrderItems;
select * from Orders o where o.OrderId = 5;
select * from OrderItems where OrderId = 5;
Not only lazy loading, ODP.NET and designer support are also a must have.
The following gets executed on iteration (of expensiveOrders), the lambda is compiled into a Function and run on each item in orders:
IEnumerable<T> orders = ...;
const expensiveOrders = orders.Where(o => o.total > 100)
IQueryable<T> orders = ...;
const expensiveOrders = orders.Where(o => o.total > 100)
Add: Where does the 'O' part fit in? You build a entities (in plain C#) with relationships to each other, and you could do stuff like:
//Pseudo-code
db.Customers.Where(c => ...)
db.Save(customer);
(Logic programming might also be workable?)
var r = customers.Select(c => new { cust = c, orders = c.Orders })
Anyway, the typical C# ORM is Entity Framework, which includes the LINQ-to-Entities query provider.
Object-oriented programming 101 assumes that all your objects are in memory, in a graph, so you can do things like person.getFriends()get(0).getName() [assuming the person in question has >0 friends]. Each step in the graph is essentially a pointer dereference, costing a constant effort.
(If your data is small enough to fit in memory, that's what you should generally be doing. People who use hadoop for half a GB of data are usually doing it wrong.)
A relational database assumes that all your data fits on disk, but only a subset of it will be in RAM at any one time (and you generally have a network round trip every time you change that subset). This means you need a completely different way of thinking; this difference is sometimes called the "object-relational impedance mismatch". This is not to do with SQL and OOP just being different APIs for the same thing, they are designed for very different use cases.
ORM tries to pretend that this difference doesn't matter, and works quite well in simple cases when it really doesn't matter.
My standard example why it sometimes does matter: PersonDAO.fetchAll().size() is silly because it forces the database to fetch all Person objects, send them over the network, your application creates the necessary objects for them - and then you throw it all away again because all you needed was the number of people. PersonDAO.count() is much better, even if you have to implement it yourself.
If you don't like the syntax of SQL, sure - use a query builder. In C# or Java you can even get some kind of type safety that way. But you need to understand the difference between an object graph and a relational database to use either of them efficiently, long before you get to advanced ideas such as window functions.
I mean, PersonDAO.fetchAll().size() is just bad code. You probably need to know how to write ifs properly before programming? You need to know about databases before using an ORM.
Pretty much the TL;DR of my whole post.
$354.38 is a lot of money to stake your opinion on. Are you sure $389.20 is the amount you're willing to invest in this?
Yes. We insist on using this OOP style where your objects are Person, Order, Invoice, etc. Ignoring that the data is stored in tables.
But, it may not be the only way. What if your objects really are things like Tables, Records and Relationships. (e.g PersonTable, OrderTable, OrderDetailTable, etc) and your operations are whatever you can do with these tables. We are trying to abstract away something that is very real. What if we embrace it instead ?
I do think a lot of people use ORMs as a crutch, which sucks. Also, ORMs often provide too much abstraction, forcing people who actually know SQL to relearn how to do everything the way the ORM happens to like it. I should not have to learn twice as much to be productive due to an abstraction.
What I prefer are really lightweight ORMs that give me models which I can then enhance with custom code. I don't need an ORM that supports plugins or inheritance or a dozen different kinds of joins. All of that can be done more efficiently with custom code.
Also, I think SQL builders are really useful. I think a lot of people conflate SQL builders with ORMs but they're actually very different problems.
This is a really good point. Many people start with the "no ORM" philosophy, realize their application needs some way to map the SQL to the code, time passes..., they have implemented their own half-baked ORM.
In a language like Java that has a generic DBMS API you can get along just fine with a few classes that handle CRUD operations and transaction management. Somebody familiar with JDBC and SQL can write the bridge classes in about a day, while keeping the overall application vastly simpler.
Either way somebody needs to make an informed choice about ORM vs. direct SQL. It seems as some people get in trouble because they skip that part of the design process.
No need for ORM, and no inline sql logic in your application code.
If you use stored procedures, all you've done is move part of the model into the database, so you have to update the stored procedures as part of a deployment. You still need to have the SQL code written out somewhere, and you still need to have something in the application code that knows which procedures exist and how to use the data they return in business logic.
you can deploy schema changes independently
you can change everything and the app should not notice
I'm not really sure what you mean by "you can deploy schema changes independently" and "you can change everything and the app should not notice". The stored procedures are basically just an extension of the apps logic right? So you can deploy them at any time, sure, but that isn't different from an app that doesn't use stored procedures, because you could also deploy changes to any part of that app at any time.
I do think stored procedures can be more efficient, because you have a lot more control. But it's not like they are clearly superior from an organizational standpoint. If you write an ORM using stored procedures, it's still an ORM.
Why pretend your SQL database is about objects? It is not... (it is about data)
A stored procedure can act like a view or a query, or use procedural logic. Point is: your app can call it and get a concistent result, no matter what refactoring has been going on.
A direct query needs to know too much about the database (orm generated or otherwise) which prevent refactoring and couples app to database harder...
You can rename or merge tables, views functions in the database but the interface the app use (stored procedures/DAL) will stay the same and work the same way.
As for app logic... I prefer bussiness logic in the database, not the app when the data is important. Application logic stay in your application, data dependent bussiness logic stay with the data.
You dont need an ORM for testing your code...
But I think this varies from project to project. How many different applications, in different languages are using your db and do you tolerate downtime?
You can then start combining stored procedures. Great way to build a slow mess quickly.
Not really, you just mock the database calls in the code you're unit testing.
And I realize that being able to test queries without database dependencies, only really applies to a few languages that treat queries as a first class citizen in the language like C# and Linq where you can mock out your actual Linq provider - replace the EF context with in memory List<T> - and still test your Linq queries.
Depends on what you want to test. Can either write unit tests for the stored procedures or unit tests for the code that makes use of those stored procedures.
tSQLt is used by the commercial SQL Test product from Red Gate if you wanted a more polished user experience:
https://www.red-gate.com/products/sql-development/sql-test/i...
For PostgreSQL and MySQL you can bring up the DBMS in Docker. That is not a built-in fixture obviously but easy enough to do locally as well as in CI/CD systems like Travis. You'll need to load SQL into the DBMS as a prerequisite to testing. That has the benefit of testing your load/upgrade sequence.
The beauty of Linq and Expression Trees.
In the real world:
Linq (c# code) -> compiler -> expression tree -> run time Linq provider -> destination language (sql, Mongo Query etc.)
When you are unit testing you switch out the Linq provider for in memory Linq to objects provider.
https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113)....
And would you not have your IDE on one monitor and your SQL IDE in another so you could look at both sets of code.
Rolling back is a simple matter of installing the previous archive. That doesn't just apply to code anymore. You can treat "infrastructure as code" also.
You can do A/B upgrades, rollbacks, etc. There is so much better tooling around regular code than sql/stored procedures. How many times have you seen stored procedures with hundreds of lines, duplicated stored procedures with V1,V2, etc appended to it, commented out logic etc?
I've had to wade through some hairy code to but at least with a code, I can do some automated guaranteed safe refactoring, find dependencies, keep the interfaces backwards compatible, etc.
Avoiding SQL in favour of an ORM or inline sql is 95% of the time a sign of laziness.
As soon as I need to write something more complicated than select-by-id I end up reaching straight for SQL. Otherwise I have to learn both the ORM's query API or DSL and have the proper mental model for how it translates to actual SQL.
Or I could just write SQL and be done with it. No mysteries.
Maybe this is just me, but I've never written a join in an ORM that I had the slightest bit of confidence in.
ORMs are fantastic for the very common case:
* Fetch 20 rows and display them as a table,
* Fetch 1 row by primary key and display it as a form,
* Write updated fields from the form back into the 1 row in the database.
Anything more complicated, and direct SQL starts being more attractive. But for the common case that ORMs are designed for, they are a major productivity boost.
Also, basic SQL can be easily taught in as little as 10 minutes (I have been initially taught it at middle school during MS Office Query, Access and VBA class). An image of a programmer that can't use SQL (I don't mean advanced cases which can indeed be a bit tricky but these are far beyond the powers of any ORMs anyway) seems really bizarre to me.
there are plenty of real life projects out there that would beg to differ
A DSL for writing SQL in a nice, composable way is a useful thing.
Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.
I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.
But my very point was not to make it traverse object graphs at all. You can write nice SQL using it, with selects, joins, functions, etc. All these parts, SQL clauses, are composable, so you can factor out common parts.
Yes, I'm fine working with lists of tuples, not "model objects". Object graphs don't map all to well to the RDBMS model all too well. It's best done on case-by-case basis, if you care about performance at all.
[1](https://ponyorm.com/)
I don't usually work on the "model" level, though; I work on "table" level.
It's pure magic.
A DSL which manipulates queries and then produces syntactically valid SQL is tremendously valuable.
Templating can absolutely save time and boilerplate, but adding a DSL over a DSL in the form or ORM may well be the problem.
Also, views tend to pollute the namespace a bit: you want a set of descriptive names, per application, and possibly per application version. Schemata help here, though.
https://www.red-gate.com/simple-talk/sql/performance/control...
To me, the big win with SQLAlchemy is that it separates the SQL expression layer from the ORM layer so cleanly. I can write very complicated queries with the expression layer that wouldn't be possible at the ORM layer, and do it in a much more composable way than concatenating SQL strings. Example: http://btubbs.com/postgres-search-with-facets-and-location-a...
I'm very comfortable in SQL, and would prefer to write my queries. But our team has grown, and we've found that developers say they know SQL, but they really don't. In general, I've found that it's not the syntax that messes people up. There's a significant mental "jump" between the usual procedural coding paradigm and the "set based" paradigm offered by SQL. Some people just never catch on.
So we're going to start using an ORM (Entity Framework Core 2) so that the "mere mortal" developers can pitch in. I know there's a way to run raw SQL, so I know that when we find spots where the ORM fails, we can just rewrite it with some good SQL if we decide that's best.
But maybe that'll never happen? We've been trying to do simpler SQL stuff as of late so that the heavier lifting in the system is done by the application/client instead of the database (bottleneck). As the database sees simpler, less unique queries, more stay in the plan cache, indexes are more reliably hit as expected, and performance increases.
Also be specific in what ORM you are comparing to raw SQL. Are you talking about Hibernate or SQLAlchemy. Are you talking about a query builder?
Good ORMs help tremendously with maintainability and security. They also let you drop down to raw SQL when needed.
I don't think rails would have been as popular if you had to use SQL.
But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.
In the databases I've worked with extensively (PostgreSQL and, somewhat in the past, Oracle) Store Procedures are routinely versioned controlled as part of the application, just these bits of code are in a different language than the rest.
The creation of functions/procedures is not tied to state of the database in quite the same way as tables are; the functions/procedures, where they care about the data, do need to recognize the table structure and changes to that structure, but that's no different than any other of the application code which makes use of the data in the database.
I think where many people get caught up on this is that they do something like migrations to get code, including procedural code, into the database... but that's not the only game in town. And really, given what's possible with databases today, I'm not sure migrations are even the best way anymore.
Consider a tool like: http://sqitch.org/ which facilitates not treating stored procedures as migrations, but rather as individual files which change just like any other code.
There are ways to accomplish having good version control on the table/structure side as well, which again, is something you lose with the migration tools I've worked with.
Anyway, I just don't buy this argument.
.. I've seen this.
But I would recommend writing, reviewing and deploying migrations by hand, esp for critical parts of the schema (automatic tools are almost guaranteed to get something wrong, with locking etc)
ORMs seems nice for simplified problems but becomes a horrible mess for real problems imho...
I've worked on some rails apps, and the ORMs caused more problems than they solved...
For my next app, I just started using SQL only and never looked back. Simple queries are easily generated with a nice internal API (SELECT * FROM Table WHERE ID = X, etc).
However, more complex selects are all written using hand-written SQL. New developers sometimes find this a bit strange, especially the younger ones, but nobody can fault the system's speed.
ORMs don’t help with security compared to query builders or even typed text.
But surely that's better handled in the database itself?
http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
- Are maintainable by a team. "Oh, because that seemed faster at the time."
- Are unit tested: eventually we end up creating at least structs or objects anyway, and then that needs to be the same everywhere, and then the abstraction is wrong because "everything should just be functional like SQL" until we need to decide what you called "the_initializer2".
- Can make it very easy to create maintainable test fixtures which raise exceptions when the schema has changed but the test data hasn't.
- Prevent SQL injection errors by consistently parametrizing queries and appropriately quoting for the target SQL dialect. (One of the Top 25 most frequent vulnerabilities). This is especially important because most apps GRANT both UPDATE and DELETE; if not CREATE TABLE and DROP TABLE to the sole app account.
- Make it much easier to port to a new database; or run tests with SQLite. With raw SQL, you need the table schema in your head and either comprehensive test coverage or to review every single query (and the whole function preceding db.execute(str, *params))
- May be the performance bottleneck for certain queries; which you can identify with code profiling and selectively rewrite by hand if adding an index and hinting a join or lazifying a relation aren't feasible with the non-SQLAlchemy ORM that you must use.
- Should provide a way to generate the query at dev or compile-time.
- Should make it easy to DESCRIBE the query plans that code profiling indicates are worth hand-optimizing (learning SQL is sometimes not the same as learning how a particular database plans a query over tables without indexes)
- Make managing db migrations pretty easy.
- SQLAlchemy really is great. SQLAlchemy has eager loading to solve the N+1 query problem. Django is often more than adequate; and has had prefetch_related() to solve the N+1 query problem since 1.4. Both have an easy way to execute raw queries (that all need to be reviewed for migrations). Both are much better at paging without allocating a ton of RAM for objects and object attributes that are irrelevant now.
- Make denormalizing things from a transactional database with referential integrity into JSON really easy; which webapps and APIs very often need to do.
Is there a good JS ORM? Maybe in TypeScript?
It's built on top a query builder Knex (http://knexjs.org/) which is decent.
ORM require just as much investment in time and even then you still need to learn the sql to get the ORM to do what you want it to do.
SQLAlchemy on Python is a truly fine piece of software but in the end it was much simpler and felt more powerful for me to write the SQL. And not even hard BTW.
I only have a limited amount of time available for learning and if I can trim out an entire class of technology (i.e. the ORM) then that's a whole bunch of stuff I just don't need to spend time learning.
But you don't always get to pick what code you'll be working with and if you are working with others in a web framework pretty good chance you'll be learning an ORM anyway.
Also really long SQL statements are no fun. Like debugging a whole program written in one line. I think sometimes there is a temptation to get excessively clever with SQL.
This is great advice in general for everyone in a role that even slightly touches on ops.
In my day-to-day work, I frequently observe that knowing some SQL (esp. joins, and aggregate functions like SUM/MAX with GROUP BY and HAVING) turns you into some sort of mighty wizard for most people. They're trying to debug a problem in their service and not making progress for hours, and you just walk straight into psql, take a look at the schema, do a few SELECTs, and zoom in on the problem.
Yet nobody seems to consider SQL a valuable skill. I guess it's not buzzwordy enough.
On the other side you have juniors, just starting development and working with an ORM/ODM the first time. It's not uncommon for them to spend years in development while learning very little SQL at all, hardly understanding the database (apart from what they learn in school). I don't blame them. I hardly know TCP/IP at all and still I write technology for it everyday. But ORMs are a more leaky abstraction; a lot of complexity is made easier by just switching to SQL.
More principal points:
- Why do we want to abstract away further from the most important part of the business: data. Data usually outlives the applications built on top of it.
- Best practice is to keep backend API's stateless and requests short-lived. ORM's promote the use of state. In client-side applications state is a much more interesting long-lived thing, but a backend API these days? It has actually become a lot simpler there since I've started doing development; few modern backend servers render views these days.
I understand why the author (re-)embraced stored procedures but I never will. Blame the overzealous PL/SQL Oracle seniors I've met, god forbid any human bestows such complexity on it's fellow colleagues. It's also hard to automate tests for them and promote it through DTAP alongside an application, hence I keep putting all logic in the application.
I can imagine if the language and ecosystem are very strongly geared towards ORM you shouldn't try to do anything else. That's just painful. But in most languages other than Java and C# I'd make a good consideration if you really need/want an ORM (coming from Java, I never took non ORM design seriously there, but perhaps I shouldn't have).
Not sure where I'll stand in a few years time on this… but for now just happy with plain old SQL and query builders.
Many ORMs are built using the Unit of Work / Data Mapping patterns. Such ORM's map your data into a separate domain model and manage this model for you. If your orm has something like an "EntityManager" it has likely implemented this Unit of Work pattern.
A key thing the Unit of Work achieves is to commit changes to the database in a single transaction. You often need to update multiple records in an atomic way within enterprise software.
You might not be faced with such challenges in a simple app, but in monolothic enterprise software it's a core feature of what a good backend server does.
Active Record-based ORMs or query builders aid you only a little in this task; they expose the transaction handling logic so much so that it starts to read as a normal SQL database transaction (and might only be cumbersome to use at worst). Here you, the programmer manages it, similar to a normal SQL transaction.
The Unit-of-Work based ORM is more intelligent. It manages the database transaction for you and figures out any changes that were made to the managed entities. In my experience all Java-built enterprise software (I used Hibernate, EclipseLink and Toplink) are designed this way and make heavy use of it. I've used it with Doctrine in PHP quite a lot, and my guess is C#'s Entity Framework is also built around such concepts. That is a big slice of the ORM market.
Here is where the state comes in; different parts of the applications contribute to creating a single database transaction until flush-time. You as a programmer should know when "flush time" actually happens and understand which entities were marked dirty. That is a lot of hidden state that is managed for you; it is in fact the core of what such ORM's do; managing state until it's ready to be flushed. To make it really advanced, powerful ORMs (the popular enterprisey ones) do a lot of caching too, at different times and at different scopes.
When tackling with such tools the distance to normal SQL becomes very large. I think that is where quite a bit of the hate comes from. It's become very powerful magic.
I've been in places where I had to really understand how this magic works to solve serious performance issues with it. I learned a lot, solving problems that shouldn't have existed in the first place.
I don't like magic. It makes me hide in the corner and cry a little.
My comment was targeted towards the UoW / Data Mapper stuff and much less so to Active Record ORM's.
1. Roughly imagine the query you want to execute
2. Map said query into whatever annoying interface the ORM actually exposes
3. Attempt to reason about its behavior because it probably isn't exactly what you envisioned in step 1
I would much rather just write the damn query in the first place.
An ORM turns your result into a collection of objects. THAT is what an ORM is for and about.
The fact that they are built on top of query builders and lighten the load when developing is just a handy side effect.
Sounds nice, but in reality, latencies, networks, massive data sets, atomicity of operations, and a whole host of other annoyances get in the way.
... so it ends up being simpler and easier in the long run to be very explicit about your interactions with data stores. Took me a long time to get to this point.
But a query builder is extremely useful and in any complex application, if you don't use one, you end up bulding one yourself, which may not be a very good idea if you don't understand things like query injection.
So learn SQL, learn ORM, and choose in a case by case basis.
But in general I agree with you: Why just learn SQL? Learn all the layers!
You can write more efficient python if you know C(++) and the various tradeoffs between data structures, even if you don't write any C++ and don't do any data structure by hand in the current project.
...
You can use an ORM more efficiently if you know SQL. Same thing isn't it?
That assumes the ORM doesn't completely get in the way, of course.
The people who use ORMs daily are likely to favour them and those that think they are the devil's work are unlikely to have intimate experience of a range of different ORMs.
Another thing people need to really give up on is the pipe dream of switching databases -- you're not going to do it. I've never seen one single case of people actually utilizing ORM to actually change RDBMs they store data in.
Not all ORMs are like that and the best solutions are the ones like Knex/Objecion where an ORM (Objection in this case) is nice abstraction for single-object access/writing and underlying SQL builder (Knex) is fully exposed and used for everything else.
1. Using SQLite for unit testing and a real RDBMS for integration testing, acceptance testing, and production. 2. When you are writing a library that will be used by different projects, not all within a single organization (e.g., a Free Software project). 3. When you are providing a product that the end-user may want to use with different choices of database (e.g. forum software, Nextcloud, etc.)
We also managed to switch from MySQL to Postgres with about 1 minute of downtime on a 16 GB database.
This was a reasonably mature codebase, about 3 years old.
Edit: I guess I should clarify that I would favor using any library that maps result sets to lists of objects. And I would consider that to be part of "do the rest of the work in your app language of choice."
Use the right tool for the job.
1) "The database schema is the official definition."
Programmatically generate what ever ORM objects (at build time) in a 1-to-1 fashion from a schema dump. This is the approach DKOs use: https://github.com/keredson/DKO As long as the code generation step is done as part of the build process, you'll have none of the normal code generation headaches, and your build will fail if you've made a code incompatible schema change.
2) "Your ORM objects are are the official definition."
And generate the schema definition automatically. The common process of this is that most "generate schema" functions are stupidly lazy, and drop the work of calculating the diff from an existing schema on the developer (forcing them to write migrations). This is unacceptable in my eyes, just as it would be if my version control software wanted me to write my own diffs by hand in order to make a commit. I strongly prefer automatically generated diffs, like in https://github.com/keredson/peewee-db-evolve. So you can do non-destructive schema changes. It's a model I've re-implemented for any new ORM I wind up using.
I've done this a couple times (as well as the two items you listed), and this has worked the best for me.
I still have to write the actual code that maps the result set to objects, but I've found this is a very small tax to pay, especially with Kotlin's data classes.
I used an ORM in a previous job (RoR ActiveRecord). I didn't find it an altogether horrible experience, but there where many cases where we would get these 'leaky abstractions' in the wrong direction from the model -> database schema. There were also a lot of cases where we would realise that some ActiveRecord query we were doing was unintentionally loading entire tables into memory (our fault, not ActiveRecord's fault). This was usually remedied quite easily by just RTFMing the docs, but my feeling is we could have avoided it in the first place if we'd used a lower level strategy.
Personally, when developing a new feature I always like to start by thinking about the database representation first and then working my way up. I think having this sensitivity to how it should be represented in the database can allow you to avoid many of the pitfalls that may come with using a more opaque ORM.
Another valuable attribute I get from using JDBI is it's dead easy to mock (like heavyweight ORMs), so unit testing stuff that interacts with it is super simple.
We took a tack similar to how PostgREST and PostGraphQL are structured. We use views in the public schema to build our objects. Functions constrain our mutations. Triggers respond to events and maintain consistency.
It makes our web API code simple and hard to introduce errors that invalidate our customers’ data.
Don’t miss having an ORM. Always seemed like more abstraction and complication than was necessary given recent advances in servers like Postgres.
"As tedious as SQL" is not an argument to use SQL instead.
(I'm not at all affiliated with jOOQ - just a happy user)
I've been thinking about what makes jOOQ so good and a huge part of it is brilliant engineering: SQL clauses are mapped almost 1:1 into reasonable and understandable code while the author spends huge effort to cover new features as databases introduce them without turning his product into a mess or introducing API breaks in every major new version. That's hard.. but awesome! :)
But some points to be made in favor of ORMs (some of them, anyway):
* Multiple backend support to handle different SQL engines.
* Minimized risk of accidental injection.
* Migrations.
If you ORM doesn't help you manage data migration, get a better one. Any ORM worth its salt should let you generate migrations; at least Django and Hibernate (+ Liquibase) can. I find the key to making everything work nicely is to let the definition in the application/ORM be the source of truth for what the DDL looks like. If you want a particular SQL table layout, figure out how to tell the ORM to generate it. If you try to retrofit an ORM onto an existing table schema (which it seems is this author's preferred approach), you're in for a world of pain.
> These two things don't really get along because you can really only use database identifiers in the database (the ultimate destination of the data you're working with).
> What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier.
Use UUIDs. Generate them in the application, but use them directly as identifiers (pkeys) in the database.
> Something that Neward alludes to is the need for developers to handle transactions. Transactions are dynamically scoped, which is a powerful but mostly neglected concept in programming languages due to the confusion they cause if overused. This leads to a lot of boilerplate code with exception handlers and a careful consideration of where transaction boundaries should occur. It also makes you pass session objects around to any function/method that might have to communicate with the database.
> The concept of a transaction translates poorly to applications due to their reliance on context based on time. As mentioned, dynamic scoping is one way to use this in a program, but it is at odds with lexical scoping, the dominant paradigm. Thus, you must take great care to know about the "when" of a transaction when writing code that works with databases and can make modularity tricky ("Here's a useful function that will only work in certain contexts").
Use a monad to represent "this function has to happen in a transaction", then all those problems go away.
Dynamic languages such as Lisp and Python can often make-do without a specific ORM layer because it's easy to stash data into lists and dictionaries. If you can read rows from your result set into a list, make dicts or structs out of each row, and pass around that list or its entries to various functions you've just implicitly implemented a LRM: lispy relational mapping. But sometimes it just fits to create objects out of rows.
Simple wrappers will do for an ORM: the best kind always make it clear that you're just using a machinery to operate an SQL database instead of updating an object and then "saving" it back to disk in the end.
1) low-level performance. Even if, and it's big if, you manage to get your ORM to generate somewhere near the optimal query, ORMs in my experience are always significantly slower than hand-written sql. When I last benchmarked, I couldn't get hibernate to be any better than 4x as slow as JDBC, and keep in mind that's pure CPU overhead.
Think your service is I/O bound? It's probably not, and it's probably your ORM to blame. This may be less of an issue for a dynamic language like Python, but I see it as a much bigger issue for Java/C# and friends.
2) Debugging/understandability. Did you know that hibernate maintains a cache of every object you load in a session until you flush it? I didn't, until we had an outage because our service OOM'ed while loading too much data without flushing.
Do you know how exactly your ORM is loading and saving data and when? Depending on your use of the various lazy-loading and storing features of your ORM, it can be very difficult to reason about when and how your ORM is talking to your database.
Do you know how your ORM is integrating with your cache, which is likely memcached? Why is your ORM integrating into itself the concept of a cache in the first place? In my experience, hibernate gets caching wrong, and that's not entirely its fault. It's difficult to get caching right in the general case. But I would rather be forced to think about caching up front and get it right for my use case rather than try to understand how Hibernate is doing it and working around its mistakes and limitations.
The common theme is that the use of an ORM makes it incredibly more difficult to understand, reason about, and debug your application rather than using a simpler library. In my experience, this alone makes an ORM not pull its weight.
Using an ORM saves your from having to implement a lot of code. But you still have to understand how everything works. ORMs makes it look easy, but there is a lot of magic involved that you need to understand sooner or later.
The law of leaky abstractions applies to many, many things, ORMs included. I would also argue they apply in different degrees, usually related to the design of the ORM (the post mentions SQLAlchemy vs. Hibernate, for example).
But consider the following:
1) Why do people still use ORMs? Exactly.
2) Question 1 but s/ORM/framework_or_widely-used-library
3) ORMs allow you to develop faster, and deliver value
4) Beginners already have a hard time coding, designing, and understanding what they're doing. ORMs provide a nice abstraction over underlying data stores
5) Although fraught with peril, ORMs provide a common interface that'd give _some_ help if you switch data stores
6) Multi-line SQL statements are a huge pain in some languages
Providing tons of abstractions to beginners so they can build something in 10 lines of code doesn't help them get better. All it does is encourage them to learn top-down when it's often much easier to learn something from the bottom-up.
When developing API's in Golang or for microservice / serverless architectures not using a framework might actually make a lot of sense. Also microframeworks (trimmed-down versions compared to opinionated frameworks) are very popular in almost any language.
And when you've already been through the effort to make your data relational for the database, might as well re-use that effort.
That's not to say that SQL is the answer. Proper first class support for relations in your programming language / libraries is great.
As for using a DSL, Datalog is worth a look.
Objects are containers of data; that's a crazy statement to make. The relational structure maps pretty cleanly to objects, properties, and collections.
However, objects are not good for reporting. And the author mentioned doing 14 joins and hundreds of columns - that smells like a reporting query.
I dont like ORMs but did struggle some years trying to use them which imho was a detour. SQL and stored procedures in plpgsql is so much better, easier to maintain, easier to reason about etc.
you can have lots of dynamic sql but that might become a rabbithole, just as with an ORM. It sounds like a problem you shouldnt have, now throwing an ORM at such a problem... might lead to even more strange issues down the road...
And they aren't a hodgepodge collection of properties; they're entities that represent a single item in a system whether that be a person, a widget, an order, or a product.
If you are exposing base tables to the application instead of appropriate views, which as much a violation of good RDBMS design principles as what you describe is a violation of good OO design.
Maybe there are some amazing ORMs for other stacks. But my time with .net was the only time I heavily utilized sql DBs.
Nim’s Ormin is still in not fully functional but is providing the same in a static language with straight up SQL.
My experience is that the O aspect of ORM is where it doesn’t help (and often gets in the way); if that’s your experience, consider DAL and Ormin
That being said: you need to use the right tool for the job.
It's just hilarious how people expect the new "foo framework/paradigm" to solve ALL the problems... jeez! It's nice to know and understand new paradigms, but you really need to evaluate your case.
ORMs took away the complexity of 90% of web apps. All the "Model X has many model Y". If you step outside of that realm with the ORM then you're officially "fighting the framework", and bad things will happen.
This is presuming that you will never come across ORM being used almost exclusively in any future projects. After all, ORM doesn't seem to be going anywhere (even with all the hate against it). One could make an argument that learning both effectively would provide a better general foundation.
That's why hierarchical document databases have had a resurgence. At least they match the data model if the language.
EF definitely has the foreign key issue. We have around a thousand tables, we tried to generate the classes for all of them including foreign keys, problem is that when you create a context that references even only a single table, it will load everything that is foreign keyed including siblings of siblings of siblings, until you run out of memory.
Only way around it is to not set up foreign keys which massively diminishes the value of using EF, so we wound up creating two copies of each table's classes, one with and one without foreign keys. That causes its own issues.
This is a common theme I've seen with people blaming ORM's for being slow, it's the devs not using them appropriately more than the ORM's themselves. Not to say that they don't have their own issues.
LazyLoading impacts what data is retrieved from the database (or more to the point when), this is a structural issue before a query was even sent to the database. It would die while generating the query, not sending the query or populating the result.
You likely should have asked for more information before concluding it was "user error."
It impacts more than just that, it will impact the query generation as well. If you have a property that is not lazy loaded then it will attempt to join the relation or load it very another query in the same round trip. Turning it off tells the ORM that every single time you want A it needs to go and get B as well. If you have it off universally it will attempt to load the entire database, or as may be the case here, crashing while trying to generate a query to do so.
> You likely should have asked for more information before concluding it was "user error."
Perhaps, but you've got multiple conflicting accounts of what went wrong, some comments indicate that it returned data, others say it never touched the database.
What I have heard of is traversing the entire graph and causing cascading loading of navigational properties. Yes, I have done that. Something like AutoMapper will do that to you, if you are not careful. Been there and done that.
How did you determine that it OOMed while generating the query?
By looking at the call stack when the exception was thrown (and the fact that nothing hit the database).
As someone else suggested, you probably had lazy loading enabled, and some of your code tried - e.g. through reflection - to get all properties.
EF has some of it's own issues - but you can most certainly create composite primary keys, composite foreign keys and work with projections right from within the code.
None of the issues the original author had with SQLAcademy and Hibernate are really a pain in EF.
Some prefer to switch off lazy loading in EF and instead either explicit eagerly load specific child collections or explicitly load them right before use.
In EF you can do
var customers = Customers.Include(c => c.Orders).Single(c => c.CustomerNo == '1234')
LINQ isn’t ORM; ADO.NET and, on top of that, Entity Framework are the (core) ORMs that work with LINQ.
ADO.NET and EF get plenty of complaints.
The problems are: the original problem, the expressive and performance disaster that is every ORM ever, and the layered and hidden RDBMS whose peculiarities nonetheless always find a way to leak through the ORM abstraction.
Fun stuff.
Do what TFA says: just learn SQL.
The real question is, why anyone using an ORM not learning SQL, how the the thing they are mapping to objects actually works?
Know how and when to use ORMs. Probably learn SQL first. Don’t believe that any shiny bullet is silver.
Or in short: “No”
Honestly what are you doing using an orm without knowing sql? The point isn't to hide sql, it's to automate repetitive tasks. Who goes to learn Angular.js without first knowing JavaScript or html?
It's not about avoiding to write SQL, it's about to have standardized API on which all your architecture can count.
Why do you think Django was so successful ?
Because it was built in a way that allowed a rich and powerful ecosystem to flourish.
The Django ORM is not the best out there and it's doing plenty of silly things. If you don't know SQL and you use it you will be in a world of pain.
However.
Because Django features this ORM it can:
- provide auto-generated forms from db model, outputting HTML and validating user inputs, saving changes automatically to the DB.
- provide auto-generated CRUD views from the db model, that you can extend at will.
- provide auto-generated admin
- provide tookits and helpers to deal with your data: signals, various forms of getters, native object casting, advanced validation, better error messages...
- provide entry points for extending the data manipulation API, in a generic way (fields, managers, etc)
- provide tooling for migrations
- provide auth and permissions
- provide user input cleaning and escaping
- automatically deals with value normalization: encoding, timezones, text/number formats, currencies... There is one entry points for those where you can put custom code, and you don't need custom code most of the time since somebody did the work for you more often than not.
- ensure all django projects look the same, so that it's very easy to move from team to team or train people
- formalize the schema, which became a the documentation and only source of truth for your data, that is commited to your VCS. Wannan know what a Django project is all about ? Check urls.py, settings.py and models.py. Done.
The cherry for this cake is of course the fact 3rd party modules (so called django "apps") can leverage that, which lead to the amazing ecosystem Django has.
- auto-generate REST views from model (eg: django-rest-framework), again that you can tweak as much as you want.
- dozens of auth backends.
- data manipulation (workflow, filtering, dashboard, analytics) that just work.
- tags, search, comments, registration and all those stuff you alway rewrite otherwise.
And because they all use the ORM, they are all compatible with each others. And they all work on Mysql, Oracle, SQlite and Postgres, like the entire rest of the framework, out of the box, for free.
You want to do that in any other framework (except RoR) ? You'll get a lib that do half of it, and let the persistence and API integration work to you. And it will not play with others. And that will be integrated differently on another project. If you have a lib at all ! Oh, and you have to use the proper DB. If you are corporate or startup, it won't be the same one and you better hope the lib author is in your shoes.
All that stuff is easy in Django because you have a centralized, easy to inspect, standard, shareable definition of each of your model in one place.
That's what ORM are for. Not "doh, SQL is hard".
Now you could get some part of those benefits by creating central models using schemas untied to the DB, such as marshmallow. It would be an interesting take, but my guess is that you will end up with interfacing it with your DB with some kind of layer, that would look like an ORM anyway.
Could be retitled:
"I should have learned SQL before dealing with ORMs."
Every single point the author brings up comes seems to come down to simple database design, lazy development, or not understanding their tools. They really don't seem to have anything to with ORMs or query languages.
Any screwdriver can make for a bad hammer and some screws may go in with a large enough mallet.
> Perhaps the most subversive issue I've had with ORMs is "attribute creep" or "wide tables"
Normalization of data is required whether you're using a query language or an ORM to access it. The fact that ORMs make it easy to "hide" the fact that you've added 500 columns to a table isn't the ORM's fault.
> Knowing how to write SQL becomes even more important when you attempt to actually write queries using an ORM. This is especially important when efficiency is a concern.
What do they think the ORMs are doing? Magical incantations over the disks? The ORMs are just using queries too. You can write really horrifically bad queries in a query language and also abuse ORMs, but that doesn't make either one bad. Most ORMs can let you see precisely the SQL they are creating. If not, the database will surely log the queries for you and let you know what's going on.
> The problem is that you end up having a data definition in two places: the database and your application.
Welcome to the fact that we have multi-layered technology? There's always going to be discrepancies between the layers that have to be ironed out because no data designs are perfect or future-proof. The author then attempts to bring migrations into the picture as if database migrations are somehow just not a problem if you aren't using ORMs (hint: database migrations have always been tough even in very well-design systems).
> Dealing with entity identities is one of those things that you have to keep in mind at all times when working with ORMs, forcing you to write for two systems while only have the expressivity of one. What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier.
Sounds like a pretty frustrating example, but I've worked with at least 10 different ORMs I can think of off of the top of my head and not a single of them required "manually flushing a cache" or a "partial commit" to "get the actual database identifier." I wouldn't write this up as being an issue with ORMs or that this problem would be magically fixed by only writing SQL either.
> Transactions. Something that Neward alludes to is the need for developers to handle transactions. Transactions are dynamically scoped, which is a powerful but mostly neglected concept in programming languages due to the confusion they cause if overused.
Transactions are pretty straightforward and I cannot agree with: "The concept of a transaction translates poorly to applications due to their reliance on context based on time."
Transactions don't care about time at all. They care about order and making sure that things are completed in a certain series of steps. This actually translates very well to applications, especially when you have processes that take a long time, where you don't want something to happen unless another thing happens first.
While a decently-written article, this comes across as someone who learned about ORMs more deeply than databases, discovered the flaws that ORMs have, and decided that query languages must be the only way forward.
This ignores the fact that we created and adopted ORMs after struggling through years of rigid queries smattered throughout code.
Writing bare queries has a time and a place, but ORMs have saved countless hours of development time, and allowed for vastly improved longevity of code.
Don't throw the baby out with the bathwater.
the point is: use sql, not orms!
migrations are best done in pure sql, i'll contend that model-inflation is best done in pure sql also.
another thing: if the format is json, that's already a nested "joined" blob of usable data! it's what the end result of a sql join would achieve, the client often just has to drill into that data blob and everything needed for the entity in question is already there!
.NET has particularly nice support for developing typed ORM's by utilizing typed Expressions which lets you parse the syntax tree of the expression (instead of executing it) so you can generate the appropriate SQL that matches the intent of the expression. You can check out a live example of what that looks like for C# in:
http://gistlyn.com/?gist=84129042921da413661c96545a63e541&co...
Although the development experience is more productive using the rich intelli-sense inside any C# IDE. It's not just the Type Safety and producitivy that typed APIs offer, ORM's also provide built-in conventions for converting RDBMS types into the most appropriate language data type and their typed abstractions take care of generating the appropriate RDBMS specific SQL for each supported RDBMS.
A lot of the stigma of using ORMs is from "Heavy ORMs" which constantly fight the leaky abstraction of mapping a Relational Data Model into a Hierarchical Object Model which I've never seen an implementation I've liked, they're always inefficient and expose APIs that make it difficult to know what SQL is generated or have any ideas which APIs perform hidden perf-killing N+1 queries behind the scenes. Many Heavy ORMs want to maitain entire control over the source code used to interface with your RDBMS. They should be separated from "Micro ORMs" which are loosely coupled so it only needs a DB connection a Type definition that matches the RDBMS table or schema that's returned where they provide a clear 1:1 typed mapping of an RDBMS table to your programming languages Type.
The Types provide a contract your app logic can bind to and given they can map to clean disconnected POCOs/POJOs they can be reused to develop declarative, safe, typed Web Services that can be inferred from the Type's schema saving you the effort from having to implement it: http://docs.servicestack.net/autoquery-rdbms as well as automatically generating the UI to query it: https://github.com/ServiceStack/Admin
Disclaimer: I've developed the above.
If your ORM is causing you friction by all drop down to custom SQL, but don't use Stored Procedures unless you've identified situations where they provide clear benefits over their trade-offs. They're essentially free text commands without the support or capability of a proper programming language that splits your logic from your system making it harder to reason about it in isolation that doesn't benefit from the investments around maintaining source code, e.g. development environments, source control, CI, static analysis & compiler feedback, fast unit testing, REPLs, etc.
I wouldn't recommend using an ORM to save you from learning SQL, but rather to leverage ORM's to save the effort and boilerplate from interfacing your programming language with your RDBMS and provides an "in code" contract representation of your RDBMS Tables that your App logic can bind to.
> doesn't benefit from the investments around maintaining source code, e.g. development environments, source control, CI, static analysis & compiler feedback, fast unit testing, REPLs, etc.
Since then, I've written our own ORM which works in both PHP and Node.js, but we're the only ones that use it. It's been battle tested, though, with millions of users and variations. I would say that many of the issues the author brings up were things we had to face, and we solved them.
1) Schema – the ORM should have a script to regenerate base classes from the database, so that your schema only lives in once place. The nice thing is, after that, your IDE can help you out instead of writing sql by hand. It can use your language syntax to catch unbalanced parentheses, and more.
2) Adapters – the ORM should be modular so you can hook in adapters for MySQL, PostGres, SQLite, MongoDB, and various key-value stores.
3) Joins – the ORM is supposed to be smart enough to describe relationships and automatically write the most optimized JOIN queries for you. For example $article->getTags() . You could, of course, implement this stuff yourself manually but it gets tedious, when the code could easily be autogenerated with stuff like $article->hasMany('tags', ...) kind of like this: https://qbix.com/platform/guide/models#relations
4) Insight – using an ORM makes you pass actual values in a structured way, instead of interpolating them in a string. Thus you don't make the catastrophic mistake of forgetting to escape them, allowing SQL injections by Mr Bobby Tables. Also our ORM can do SHARDING in the app layer, especially useful in Node.js where it can issue simultaneous queries to several databases and combine the results. Although I recommend using CockroachDB these days :)
5) Flexibility – the ORM should support fetching partial objects, but with the Primary Key so they can be saved back. Recently we even added support for vector-valued lists, something we needed for extra flexibility.
6) Transactions – the ORM should be smart enough to handle transactions, in fact support nested transactions on various shards. Since the database engine usually does not support nested transactions, you need to emulate that in the app layer. For instance, when you start a session, you might want to begin a transaction and lock the session for update.
7) Methods – objects which are fetched can have user-friendly methods added, like $stream->exportToClient() and so on.
It's free and open source. Here are examples of usage:
* and temporary tables, etc.
seriously. grow up. learn the amount of SQL you need to and napalm any ORM that comes within arm's reach.
and if you're not sure about the SQL you've come up with then just go a few cubicles down and ask your DBA what they think. Chances are they'll write something 1000x better than you came up with an you'll have learned something along the way. win-win.