I think real improvement from SQL would be the ability to construct query in sets. Like I'd make a set of persons, check if it looks right, then another set of pets and if both are alright then compose the result set by joining them. Not by cutting and pasting a code but actually using variables. Your examples do everything in one statement which does not look so easy to compose, why?
My favorite reasons for pushing something down into the database include performance, security, concurrency, and portability. Yes, portability. My client language is way more likely to change than my database.
I don't mean that. I just like to construct any nontrivial query piecewise. SQL is about sets, when doing query you in fact make subsets and combine them using set operations. But the syntax makes this composition error prone and objection.js seems not to do much to improve it.
You've sorta captured my distaste for ORMs. I'm good at sets and relational algebra. Anything ORM interface that isn't RA is a leaky abstraction, and eventually I just have to go around it.
SQL has views and functions for this. It's indirect when looking at the code of one application accessing the database, but as the other poster said, those can be many, in different languages, at the same time and over time.
The Vietnam of Computer Science goes into more detail with the problems that plague ORMs and why some people have "ORM hatred". Some of these reasons are glossed over or not mentioned by TFA.
Where you see me using the wrong tool for the job, I see myself using a tool that may be flawed but is good enough for what I need right now.
Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech.
Where you see me forcing a tool down your throat, I see myself standardising on a compromise solution so we don't have ten approaches to the same problem in one single codebase.
On any one of those situations, either of us might be right, wrong, or (most likely) somewhere in between. It's important to keep that in mind before loudly denouncing others' opinions, especially when the alternative is to just argue for your own opinion and letting me decide.
Where I loudly denounce something it's usually because it is supported by a wave of hype that will drown out muted objections.
I can count 11 or 12 instances of this happening in tech. There's a reason Larry Ellison said the only industry more fashion driven than women's fashion is tech. Tech is extremely herdlike.
If you are going against the herd you kind of have to be loud to get heard. Large corporations with marketing budgets and the people who got caught up in them will drown you out otherwise.
Loud objections can cut through the bullshit-with-a-big-marketing-budget and in many cases (e.g. mongo) I'm pretty glad it did.
>Where you see me using the wrong tool for the job, I see myself using a tool that may be flawed but is good enough for what I need right now.
Sure, but one might also be delluded, and the tool they use be not just "flawed but good enough", but not even "good enough" for what they actually need, if not actively detrimental to their use case.
>Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech.
Sure, but that might more often than no be delluded, and be indeed "succumbing to fads and hype", and "trialling a new piece of tech" for no good business or technical reason other than that.
So, "none of those is justified" is premised on some misunderstanding from the part of those doing the critique, which is not always (or even often) the case.
Like I said — in any one given situation you might be right and I might be wrong, or vice versa. Disagreement is healthy. My point is just that it's the "loud aversion for peoples choice of tools" bit that is not healthy.
These cases are the small minority of cases where other tech folks are yelling at them for doing it wrong. If the tech community cannot distinguish between helpful and harmful advice, it is better to keep quiet.
You can find "X sucks" blog posts for virtually any X in software development. Languages, language paradigms, testing procedures, development lifecycles, hiring practices, management and org structures, support styles, oss licenses. Everything. Most of these hot takes are not helpful.
>You can find "X sucks" blog posts for virtually any X in software development. Languages, language paradigms, testing procedures, development lifecycles, hiring practices, management and org structures, support styles, oss licenses.
I don't mind SQL; my main reason for using an ORM at the moment (gorm, it has its flaws) is one, automatic database migration, and two, I don't have to manually map a resultset to my struct; code like that is tedious and error-prone.
Gorm is not the best when it comes to managing relationships though, to put it mildly.
Interesting angle. I've come round to the benefits of ORMs for query building and results-to-objects. Especially since I used LINQ; linq-on-objects and linq-on-xml are incredibly useful even outside a database.
The one big anti-pattern I've seen in systems that use ORMs is the "manual query" or especially "manual update": rather than doing something in the DB, the programmer retrieves some objects, iterates over them with a FOR loop or similar, makes changes, and then writes them back to the database. This is absolutely terrible for performance and prone to concurrency errors.
(are there ORMs in which all returned data objects are forced to be immutable? I think that would help.)
There are some patterns ORMs like Hibernate use to combat the races: versioning, pessimistic locking, etc.
And while performance can be an issue for many businesses it's the read queries that are most often the bottleneck. If write performance is critical then hopefully you've got sustainable revenue to cover the cost to optimize it.
> (are there ORMs in which all returned data objects are forced to be immutable? I think that would help.)
I'm not sure if this is exactly what you're asking, but Entity Framework has .AsNoTracking(), which will simply return the objects without doing any change tracking on them, so feel free to modify them as you wish afterwards without accidentally updating your database.
I think it's more that people don't seem to know what relational actually means, and schools are doing a terrible job in teaching it. And so we see the results.
I'm not porcupine, but I feel that we are definitely using relational models. The drive towards objects (and REST as well) tries to project data into a hierarchy. Orders belong to customers, so you nest orders under customers. But you are B2B, so you break into one market and realize that suddenly joint purchases are a thing. Suddenly you have orders with multiple customers.
If you were using a relational model for backing the orders and customers, there is no real issue. An order is just a tuple of data, a customer is just a tuple of data, the "belongingness" is just modeled via foreign key constraints. Maybe you were smart enough to already model it as many to many and you just relax a uniqueness constraint in the join table or you had no such uniqueness constraint already and it was enforced by the application. Otherwise, you move the foreign key out of the order tuple into a joining table and move on to changing the projection queries and the downstreams. If you were storing it as nested data documents... I don't know because I have never done that, but I do know the trouble with trying to invert hierarchical addresses in such cases and it is annoying and inefficient (abstraction inversion).
In a relational model, any hierarchy is a contrivance via foreign key constraints. These same attributes are easy to invert. If you have a business need for a hierarchy, that shows up in the business logic by the precise query used to project the relations along the foreign keys.
In the scope of the comment made. I find it hard to believe that someone doesn't see that we are indeed using relational models of data and I find it sad that the known benefits apparently aren't so widely known. Yes, in a concrete use case you project out the data you need and type that (map it to objects). But if you modeled the data well, as your use cases increase in number or evolve your data model often doesn't need to change as much as your business logic if you did your job well. The job being relational data modelling.
I myself use async programming, and there aren't really good ORMs for that since you don't want every single property of your object to be an async property (which most ORMs do with lazy loading).
Relations are sets of sets, and the relationships are products of the query method, not a static property of the storage of the data. This insight by Date and Codd was a huge discovery in data management.
Unfortunately SQL tended to paper over some of this beauty with its talk about "tables" with "columns", which are just one way of representing relation and tuples. I can't count the number of times I've heard someone say "but my data doesn't fit into a table", completely not getting it.
That combined with the rapid rise in object-oriented programming through the 90s and early 2000s, as well as a bunch of other things I won't get into, led to many people mistreating and/or rejecting the relational database.
We were taught, or taught ourselves to model our applications in an object-oriented way, and then became frustrated because we treated the database as a "datastore" rather than an information modeling and retrieval tool like it was meant.
Without an understanding of relational fundamentals people repeated the same mistakes that Date and Codd were originally trying to rectify with their pioneering work on the relational database. The relational model was a response to the problems with hierarchical and network databases; to try and formalize a more flexible model, with a mathematical and logical basis.
"Graph databases" and "document databases" and "object databases" _look_ more flexible from the front, but fundamentally are the opposite. They confine the kinds of relationships data can take, in advance.
The object oriented model is good for representing some aspects of our world; especially, perhaps, graphical interfaces, or simulation systems. In my opinion it is inferior for the modeling of data/information itself.
Ahh.. the beautiful times of the .war and .ear files. So much information got lost from J2EE, and now we're rebuilding things with so many different tools.
Tell that to the company I used to work for that build custom business systems where all business logic and all the crud was written as stored procedures in SQL Server.
In particular CRUD maps very nicely to SQL, and there’s no reason to map it to objects.
I enjoy using the SQLAlchemy ORM. It makes it trivial to divide a query in several parts that you can mix and match. In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries.
Using Objection for more than a year in a medium sized ERP project. Not bad, but for sure on the next project I will use directly knex as querybuilder (with no ORM part). The ORM part that Objection add to knex don't bring much value and add a layer of complexity. The only functionality that I would miss would be the graph operations (withGraphFetched, upsertGraph, ecc.) but writing them in SQL (using KNEX) would not be a great problem.
We're using knex at $dayjob, and we're not even using it for half our queries. Knex is pretty nice for simple CRUD operation, but for more complex queries we've found it much nicer to drop down to raw SQL (with knex.raw providing escaping).
What we have found helpful is to stick common queries into a helper function so that we can get a consistently shaped object back.
We use both in our code base. Sometimes knex makes sense, sometimes Objection makes sense. That's actually one of the nice things about it wrapping knex.
Have you considered a tagged template literal sql builder, such as https://github.com/blakeembrey/sql-template-tag ?
Keeps the SQL syntax so less to learn, but still offers parametrized queries.
Considered it, but I think that there are advantage in programmatically construct the query (see for example knex modify). Directly write down SQL or use template with sql make it harder. The only problem with knex is that it mutate at every operation instead of returning a new instance. I'm experimenting in wrapping it in a lazy monad that return a Fluture (https://github.com/FbN/iodio)
I have projects that use ORMs and projects that do not use ORMs. I have specific reasons for both types, and in certain circumstances one or another is more appropriate.
ORMs are a dependency that are often constantly updated, prone to corner cases, and require a lot of library specific knowledge-- but they often make code more secure against small mistakes or tossing in a string interpolation, which is important when working with others, especially junior developers. They can be quite nice to work with in having type checked queries, building up queries from pieces using an API, but they also tend to be heavy and miss some features of your DB.
Raw SQL on the other hand, is very flexible, standard, easy to read, and anyone who knows SQL can jump in. You can do REPL for queries and copy in, and edits to SQL are easy. The only dependency you need is the standard database drivers, and updates don't typically break things. If you have a basic understanding and use it right, security is great as well.
> If you have a basic understanding and use it right, security is great as well.
I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. I'm sure there are a ton of reasonable use cases for raw SQL, but when I see the same dumb mistakes over and over again, it just makes me shake my head and wish more people just used ORMs.
Major breaches happen every day due to SQL injections, so building up a narrative that developers can do raw SQL right if they're smart enough is just irresponsible IMO. Over and over again when I tell developers that they should use an ORM, they get offended, and then I end up finding trivial SQL injections.
It kind of feels like telling people that it's perfectly safe to drive without a seat-belt so long as you're a good driver.
I disagree. As long as you use prepared statments and bounded parameters, your application is safe from SQL injections. NEVER use string concatiation to generate any SQL queries - not in your app and not in your database! Its unsafe and slow.
https://security.stackexchange.com/questions/15214/are-prepa...
That's easy enough to say, but time and time again I see codebases, even ones making extensive use of prepared statements, falling back to doing string concatenation from time to time. Prepared statements etc are an example of "opt-in security", which is a good band-aid to have for quickly fixing up old code, but it still allows for some pretty egregious errors.
Again, with the seat-belt analogy. As long as you're safe and careful all the time, seat-belts are worthless. Therefore seat-belts are only for dumb, reckless people.
Then again, prepared statements (and SQL injection) are a solved problem. Imagine what people who can't bother to use prepared statements would do with an ORM in non-trivial cases.
Anyone writing raw SQL should use parametrized queries for all cases where this is possible. That has been the recommendation for a long time now, and there is really no excuse to not doing that.
There are some cases where you can't use them, e.g. dynamic queries where you modify which columns are queried or larger parts of the entire query. But that more of an exception, and you do need to be careful about only using whitelisted terms when you modify the raw SQL part that can't be replaced by parameters.
> I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it.
Just parameterize the query and you're done. If someone isn't doing so in 2020 they're either working on a very old system or they're not doing it right.
SQL injection is just not a reason to avoid raw SQL.
One of the reasons I love Dapper.net is because it allows me to use raw SQL and helps solve the only pain point I have with raw SQL, dynamically building queries.
I consider myself reasonably comfortable in SQL and don't mind crafting queries when it is optimized for performance and/or ORM wouldn't be able to produce something optimum. However, for typical CRUD stuff, I almost always use ORM because not having to deal with the boilerplate makes things so much more easier. E.g. JPA's query creation[0] allows me to dynamically generate the query based on the method name - that is just way too convenient to give up, I could use SQL too but there is no payoff in writing boilerplate compared to the convenience and decent performance provided by the ORM.
It has warts and limitations, but I find the approach that RedBean PHP uses to be very intuitive.
It supports other patterns, but the one where you open up the ORM and let it make the schema dynamically as your code runs, then later "freeze" it, is pretty neat.
Query builders (as I call these type of tools) are great, especially when they allow typesafety over the SQL barrier. jOOQ[1] (on JVM) does this with code generation: migrate your db, re-generate the project specific jOOQ lib and all incompatible queries are not showing up red-underlined in your IDE.
I liked this as well in ORMs: the type safety them CAN provide.
Query builders VS ORMs, both have their advantages. Doing a Users.byId(123) is easier to read'n'write than doing so with a query builder. Its the complex queries where ORMs suck, string-SQL gives no type safety and query builders shine (given they provide type safety).
>I created an ORM called objection.js for exactly the reasons Thomas listed. The design goal of objection.js is to allow you to use SQL whenever possible and only provide a DSL (Domain Specific Language) or a custom concept when something cannot be easily done using SQL.
As a complete amateur regarding the backend I use ORMs for two reasons:
1. Migrations - I only recently started diving deeper into the concept so an abstraction is in order for now.
2. Security - I'm at a point where I don't know what I don't know and thus would rather not rely solely on my own knowledge. I'm sure such systems introduce their own security risks, but since those are mostly open-source projects I can at least count on somebody to eventually discover any such issue.
Database migrations are completely independent of using an ORM. You can still create migrations from your model definitions but that doesn't mean that you have to use an ORM to query a database.
Some tools you might find useful: Flyway, Liquibase, Evolve
You don't need an ORM for migrations. Tools like flyway and others can rely purely on `.sql` files with some naming conventions and can help keep your app and db schemas in sync.
You also don't need an ORM for security, and an ORM doesn't always prevent you from footgunning. Use query builders, `?` or `:named` placeholders (as your DB driver recommends), and almost never use string operations to build queries. Similarly, grow suspicious of your db or application model if you find yourself having to write increasing amounts of SQL to appease your ORM.
ORMs are a tool. Neither good or bad on their own. Many applications outgrow them, or they don't properly refine their object models as the db or application changes. Lots of applications hold on to the security blanket of their ORMs for far too long, or they let the DB objects proliferate very deeply into their stack such that changing the db or the app model become nearly impossible. This is when apps increasingly try to outsmart the ORM, creating massive amounts of tension and friction in doing even simple things. (As anyone who has tried to do large-scale migrations or backfills that weren't possible in raw sql will tell you.)
Learning to recognize all this is above the level of "amateur", but it seems like you've already learned to identify some of the good that the tooling can provide.
For me, ORM are the good illustration of the quote:
"The road to hell is paved with good intentions"
For expert-beginner and "architecture manager", it looks like a good idea, giving an abstraction and co.
But, I think that most haters are the people that already had used for a few of them for long enough to be burned and get the experience.
ORM job is supposed to simplify the job of dealing with the database, but most of the time, the final things is a lot more complicated and a mess because of the ORM.
The main issue being that ORM operations looks like auto magic to the user, but this auto-magic still have a lot of rules of specific functions and settings that have to be used for the things working correctly, because it is just executing sql in background.
And in the end, it is so much more complicated to do understand and do simple things with the orm that it is to do the original sql operation.
The worst of all is when the ORM has support for some callback or automatic hooks, like "post-update", etc...
I don’t find the “magic” SQL generation that problematic in practice. The main problem with ORMs is that as a class they are, broadly speaking, all fat model ActiveRecord patterns which encourage you to make the models fatter and to spread invisible dependencies on these models (and their related models and the methods) throughout the codebase. The result is invariably a big ball of mud.
Immutable entities, produced by a set of ORM-like and mostly automatic GetX/PersistX services, is more generally the sound way forward. Unfortunately it’s also far less convenient than most ORMs in practice, as frameworks like Rails prioritize realizing the messier vision.
Looks like to be logical blocks that you can easily chain together, but is the order important and can each "subfunction" be used anywere? or after a specific previous one? Probably join couldn't be used after orderby.
What is the state before the join, the available tables?
Where inside the sql will be applied the where? What object will be available?
Some ORM even do kind of "autojoin" on table that it detects based on arguments used in later where clauses.
The problem with the object-based model is that, by definition, it constrains the developer into patterns that have an impedance mismatch with the underlying database: as a general rule, the objects are either fat models that always pull the entire record from the database, or they're proxy objects that only fetch minimal data first, and then do a round-trip to the database for every subsequent property access.
Neither is a good fit for databases, which excel in slicing and dicing your data to retrieve only the exact properties you require, no more, no less. LINQ alleviates this somewhat because it allows anonymous objects, but the fundamental problem is still the same: the SQL (relational) model relies heavily on column filtering and result set composition, neither of which can be abstracted properly with object-oriented programming.
And let's not even get started about using the object model to enforce storage invariants, like NOT NULL or FOREIGN KEY validation. These invariants should have little bearing on how the models are treated during runtime, because there are valid reasons for a record to be incomplete (the partial resultset mentioned above, an object still under construction, etc).
So, in my view, here's what a good "ORM" should be able to do:
- construct and fetch an object using any subset of the model properties, leaving the others empty (preferably explicit)
- allow composition of multiple models in both data retrieval and search parameters
- have an explicit distinction between storage invariants and business invariants (e.g. a function reporting on aggregate sales should not need to fetch every individual sales line, even though the model says that every sales order requires at least one item line)
At which point, you're merely replicating the relational model in your "ORM", and you're better off avoiding the abstraction entirely. That doesn't mean you shouldn't use model definitions that match your database records. But your ORM shouldn't be an abstraction between your models and your database -- it should expose the full relational capabilities to your developers, because they're going to need those capabilities anyway.
[ but note, I haven't actually worked with any recent ORM except for esqueleto -- and I'm pretty sure it doesn't qualify as an ORM ]
I use dapper with a plugin called dapper.simplecrud (https://github.com/ericdc1/Dapper.SimpleCRUD). This already helps to reduce the need to write simple SQL code repeatedly quite a lot. When doing SQL operations more complicated than a simple crud, I resort to raw SQL or stored procedure.
> People always seem to ignore the third option: using an ORM that embraces SQL!
I had a similar revelation several years ago, based on the distinctions between a "domain model" and "persistence model" described by Mehdi Khalili, resulting in Atlas for PHP: http://atlasphp.io
Yes, the upper/outer layers are a Data Mapper implementation; cf. the Atlas.Mapper package.
FWIW, the Data Mappers are built on top of Table Data Gateways, which in turn are built using Query Builders and a connection locator. This lets you start at lower levels and work your way up, while at the same time allowing you to "reach down" from the upper levels to the lower ones when you need to.
One case I find really annoying to write in SQL is inserting or updating entities with several many-to-many relationships. That is just plain annoying to do by hand, so I'd either have to use an ORM or write a small wrapper myself for this.
Reading entities with many-to-many relationships is also often easier with an ORM, though you really have to be aware how the ORM will write this kind of query to avoid issues. ORMs are a very leaky abstraction when performance is concerned.
Another part that is very useful is the query building itself, though you can get that without an ORM. Having composable query fragments is very useful, doing that by hand is extremely tedious, error-prone and a potential security risk if you're not careful.
I'm comfortable writing SQL by hand, but I do prefer to have an ORM as long as I can also bypass it for queries that are difficult or impossible to do with the ORM. But an ORM can't replace knowing how databases work, and they do require you to understand more under-the-hood details than most other types of libraries.
Personally, I think ORM has a reasonable use case here, but I also find doing this using SQL to be straightforward and easier to reason about during future refactors.
Take a look at JDBI's Repository pattern for a simple compromise between SQL and ORM for this use case in particular.
I've certainly fallen in to the anti-ORM camp, because I know SQL well and at least like to think I can put together a schema better than any ORM can. (I usually can.) However, then my code becomes tightly coupled to the schema, despite my abstractions, which makes it harder to change and basically impossible to unit test.
I make my own abstractions. I might have a file called 'kvs' which implements a key-value-store abstraction, or one called 'friendnet' which implements a graph of social relations.
* Those files have all the SQL. I never have raw SQL in my main application.
* Those files never e.g. call into my web framework, have HTML, or otherwise. They're a stand-along abstraction.
An ORM mutates a good abstraction (SQL) into a not-as-good one. Making a generic abstraction which works everywhere is hard, and SQL is the best one we've got. By making it domain-specific, we can mutate it into a good one.
One of the key principles I use is to try to have more than one back-end. For some (like a kvs), that might be Elasticache AND in-memory AND postgres. For something relational, it might be postgres and sqlite.
Not a fan of ORMs at all, especially ActiveRecord style. They just bring a costly new layer of complexity and not enough value to justify it.
I do very much like the object hydration that ORMs bring though. I think libraries like Dapper hit the perfect balance of manual everything vs automagic complexity.
I'm more averse to people not using their DB to their full extent than whether they're doing that with an ORM or by just writing overly simplistic SQL. Granted, it's a bit more common with ORMs, especially if it's all ActiveRecord. But theoretically you could express yourself sufficiently well with DBIx::Class, SQLAlchemy, Hibernate and whatever's not just stubbed NPM-ware for JS.
Just as long as people avoid too many uneccesary requests and/or do too much in code what could easily be solved by a view, window function, lateral join, stored procedure etc.
And yeah, a lot of that gets thrown out if you need to boil it down to a lowest common denominator to ease testing or different deployments, but a lot of programmers don't even utilize SQlite to a degree beyond beginner's SQL-92.
88 comments
[ 10.1 ms ] story [ 146 ms ] threadThe Vietnam of Computer Science goes into more detail with the problems that plague ORMs and why some people have "ORM hatred". Some of these reasons are glossed over or not mentioned by TFA.
IMHO use whatever that helps you actually deliver something.
(a) using the wrong tool for the job
(b) succumbing to fads and hype
(c) forcing said tools to other's throats (e.g. manager making decisions, or team lead, or company policy, or "opinionated" non-technical customer)
?
I'd say those cases deserve "loud aversion for peoples choice of tools".
Where you see me using the wrong tool for the job, I see myself using a tool that may be flawed but is good enough for what I need right now.
Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech.
Where you see me forcing a tool down your throat, I see myself standardising on a compromise solution so we don't have ten approaches to the same problem in one single codebase.
On any one of those situations, either of us might be right, wrong, or (most likely) somewhere in between. It's important to keep that in mind before loudly denouncing others' opinions, especially when the alternative is to just argue for your own opinion and letting me decide.
I can count 11 or 12 instances of this happening in tech. There's a reason Larry Ellison said the only industry more fashion driven than women's fashion is tech. Tech is extremely herdlike.
If you are going against the herd you kind of have to be loud to get heard. Large corporations with marketing budgets and the people who got caught up in them will drown you out otherwise.
Loud objections can cut through the bullshit-with-a-big-marketing-budget and in many cases (e.g. mongo) I'm pretty glad it did.
Sure, but one might also be delluded, and the tool they use be not just "flawed but good enough", but not even "good enough" for what they actually need, if not actively detrimental to their use case.
>Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech.
Sure, but that might more often than no be delluded, and be indeed "succumbing to fads and hype", and "trialling a new piece of tech" for no good business or technical reason other than that.
So, "none of those is justified" is premised on some misunderstanding from the part of those doing the critique, which is not always (or even often) the case.
These cases are the small minority of cases where other tech folks are yelling at them for doing it wrong. If the tech community cannot distinguish between helpful and harmful advice, it is better to keep quiet.
You can find "X sucks" blog posts for virtually any X in software development. Languages, language paradigms, testing procedures, development lifecycles, hiring practices, management and org structures, support styles, oss licenses. Everything. Most of these hot takes are not helpful.
Yes, and they're all pretty accurate.
Gorm is not the best when it comes to managing relationships though, to put it mildly.
Some page with actual queries from the docs: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...
The one big anti-pattern I've seen in systems that use ORMs is the "manual query" or especially "manual update": rather than doing something in the DB, the programmer retrieves some objects, iterates over them with a FOR loop or similar, makes changes, and then writes them back to the database. This is absolutely terrible for performance and prone to concurrency errors.
(are there ORMs in which all returned data objects are forced to be immutable? I think that would help.)
And while performance can be an issue for many businesses it's the read queries that are most often the bottleneck. If write performance is critical then hopefully you've got sustainable revenue to cover the cost to optimize it.
I'm not sure if this is exactly what you're asking, but Entity Framework has .AsNoTracking(), which will simply return the objects without doing any change tracking on them, so feel free to modify them as you wish afterwards without accidentally updating your database.
ORM were never worth the trouble. There's on "impedance mismatch", you just use data wrong (wrapped as objects as opposed to data records).
Relational data is almost always mapped to an object.
Source?
If you were using a relational model for backing the orders and customers, there is no real issue. An order is just a tuple of data, a customer is just a tuple of data, the "belongingness" is just modeled via foreign key constraints. Maybe you were smart enough to already model it as many to many and you just relax a uniqueness constraint in the join table or you had no such uniqueness constraint already and it was enforced by the application. Otherwise, you move the foreign key out of the order tuple into a joining table and move on to changing the projection queries and the downstreams. If you were storing it as nested data documents... I don't know because I have never done that, but I do know the trouble with trying to invert hierarchical addresses in such cases and it is annoying and inefficient (abstraction inversion).
In a relational model, any hierarchy is a contrivance via foreign key constraints. These same attributes are easy to invert. If you have a business need for a hierarchy, that shows up in the business logic by the precise query used to project the relations along the foreign keys.
In the scope of the comment made. I find it hard to believe that someone doesn't see that we are indeed using relational models of data and I find it sad that the known benefits apparently aren't so widely known. Yes, in a concrete use case you project out the data you need and type that (map it to objects). But if you modeled the data well, as your use cases increase in number or evolve your data model often doesn't need to change as much as your business logic if you did your job well. The job being relational data modelling.
I myself use async programming, and there aren't really good ORMs for that since you don't want every single property of your object to be an async property (which most ORMs do with lazy loading).
Relations are sets of sets, and the relationships are products of the query method, not a static property of the storage of the data. This insight by Date and Codd was a huge discovery in data management.
Unfortunately SQL tended to paper over some of this beauty with its talk about "tables" with "columns", which are just one way of representing relation and tuples. I can't count the number of times I've heard someone say "but my data doesn't fit into a table", completely not getting it.
That combined with the rapid rise in object-oriented programming through the 90s and early 2000s, as well as a bunch of other things I won't get into, led to many people mistreating and/or rejecting the relational database.
We were taught, or taught ourselves to model our applications in an object-oriented way, and then became frustrated because we treated the database as a "datastore" rather than an information modeling and retrieval tool like it was meant.
Without an understanding of relational fundamentals people repeated the same mistakes that Date and Codd were originally trying to rectify with their pioneering work on the relational database. The relational model was a response to the problems with hierarchical and network databases; to try and formalize a more flexible model, with a mathematical and logical basis.
"Graph databases" and "document databases" and "object databases" _look_ more flexible from the front, but fundamentally are the opposite. They confine the kinds of relationships data can take, in advance.
The object oriented model is good for representing some aspects of our world; especially, perhaps, graphical interfaces, or simulation systems. In my opinion it is inferior for the modeling of data/information itself.
We need a relational renaissance.
In particular CRUD maps very nicely to SQL, and there’s no reason to map it to objects.
What we have found helpful is to stick common queries into a helper function so that we can get a consistently shaped object back.
ORMs are a dependency that are often constantly updated, prone to corner cases, and require a lot of library specific knowledge-- but they often make code more secure against small mistakes or tossing in a string interpolation, which is important when working with others, especially junior developers. They can be quite nice to work with in having type checked queries, building up queries from pieces using an API, but they also tend to be heavy and miss some features of your DB.
Raw SQL on the other hand, is very flexible, standard, easy to read, and anyone who knows SQL can jump in. You can do REPL for queries and copy in, and edits to SQL are easy. The only dependency you need is the standard database drivers, and updates don't typically break things. If you have a basic understanding and use it right, security is great as well.
I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. I'm sure there are a ton of reasonable use cases for raw SQL, but when I see the same dumb mistakes over and over again, it just makes me shake my head and wish more people just used ORMs.
Major breaches happen every day due to SQL injections, so building up a narrative that developers can do raw SQL right if they're smart enough is just irresponsible IMO. Over and over again when I tell developers that they should use an ORM, they get offended, and then I end up finding trivial SQL injections.
It kind of feels like telling people that it's perfectly safe to drive without a seat-belt so long as you're a good driver.
[1] https://en.wikipedia.org/wiki/Sturgeon%27s_law -> "Ninety percent of everything is crap."
ORM isn't required to avoid SQL injections. The two concepts maybe conflated with some frameworks, but they aren't necessarily related.
Again, with the seat-belt analogy. As long as you're safe and careful all the time, seat-belts are worthless. Therefore seat-belts are only for dumb, reckless people.
There are some cases where you can't use them, e.g. dynamic queries where you modify which columns are queried or larger parts of the entire query. But that more of an exception, and you do need to be careful about only using whitelisted terms when you modify the raw SQL part that can't be replaced by parameters.
Just parameterize the query and you're done. If someone isn't doing so in 2020 they're either working on a very old system or they're not doing it right.
SQL injection is just not a reason to avoid raw SQL.
One of the reasons I love Dapper.net is because it allows me to use raw SQL and helps solve the only pain point I have with raw SQL, dynamically building queries.
[0]: https://docs.spring.io/spring-data/jpa/docs/current/referenc...
It supports other patterns, but the one where you open up the ORM and let it make the schema dynamically as your code runs, then later "freeze" it, is pretty neat.
See https://www.redbeanphp.com/index.php?p=/quick_tour for an overview. Are there popular ORMs that work this way for other languages?
I liked this as well in ORMs: the type safety them CAN provide.
Query builders VS ORMs, both have their advantages. Doing a Users.byId(123) is easier to read'n'write than doing so with a query builder. Its the complex queries where ORMs suck, string-SQL gives no type safety and query builders shine (given they provide type safety).
1: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...
jOOQ can do that as well through various ways if that's a burden: https://www.jooq.org/doc/latest/manual/sql-execution/daos/
That's the exact opposite way I use an ORM.
If i know it's a heavy query or the ORM won't optimize it ( eg. lots of relations and creating a n+1 problem), then i do the query myselve.
If something is going slow, I try to optimize it. If that means writing SQL, sure. Why not
A programmer's toolbox contains multiple tools. Use the right one.
1. Migrations - I only recently started diving deeper into the concept so an abstraction is in order for now.
2. Security - I'm at a point where I don't know what I don't know and thus would rather not rely solely on my own knowledge. I'm sure such systems introduce their own security risks, but since those are mostly open-source projects I can at least count on somebody to eventually discover any such issue.
Some tools you might find useful: Flyway, Liquibase, Evolve
You also don't need an ORM for security, and an ORM doesn't always prevent you from footgunning. Use query builders, `?` or `:named` placeholders (as your DB driver recommends), and almost never use string operations to build queries. Similarly, grow suspicious of your db or application model if you find yourself having to write increasing amounts of SQL to appease your ORM.
ORMs are a tool. Neither good or bad on their own. Many applications outgrow them, or they don't properly refine their object models as the db or application changes. Lots of applications hold on to the security blanket of their ORMs for far too long, or they let the DB objects proliferate very deeply into their stack such that changing the db or the app model become nearly impossible. This is when apps increasingly try to outsmart the ORM, creating massive amounts of tension and friction in doing even simple things. (As anyone who has tried to do large-scale migrations or backfills that weren't possible in raw sql will tell you.)
Learning to recognize all this is above the level of "amateur", but it seems like you've already learned to identify some of the good that the tooling can provide.
For expert-beginner and "architecture manager", it looks like a good idea, giving an abstraction and co. But, I think that most haters are the people that already had used for a few of them for long enough to be burned and get the experience.
ORM job is supposed to simplify the job of dealing with the database, but most of the time, the final things is a lot more complicated and a mess because of the ORM.
The main issue being that ORM operations looks like auto magic to the user, but this auto-magic still have a lot of rules of specific functions and settings that have to be used for the things working correctly, because it is just executing sql in background.
And in the end, it is so much more complicated to do understand and do simple things with the orm that it is to do the original sql operation.
The worst of all is when the ORM has support for some callback or automatic hooks, like "post-update", etc...
Immutable entities, produced by a set of ORM-like and mostly automatic GetX/PersistX services, is more generally the sound way forward. Unfortunately it’s also far less convenient than most ORMs in practice, as frameworks like Rails prioritize realizing the messier vision.
query(object1).where(object1.prop=10).orderby(prop).join(object2).where(object1.prop=object2.bar)
Looks like to be logical blocks that you can easily chain together, but is the order important and can each "subfunction" be used anywere? or after a specific previous one? Probably join couldn't be used after orderby. What is the state before the join, the available tables? Where inside the sql will be applied the where? What object will be available?
Some ORM even do kind of "autojoin" on table that it detects based on arguments used in later where clauses.
Neither is a good fit for databases, which excel in slicing and dicing your data to retrieve only the exact properties you require, no more, no less. LINQ alleviates this somewhat because it allows anonymous objects, but the fundamental problem is still the same: the SQL (relational) model relies heavily on column filtering and result set composition, neither of which can be abstracted properly with object-oriented programming.
And let's not even get started about using the object model to enforce storage invariants, like NOT NULL or FOREIGN KEY validation. These invariants should have little bearing on how the models are treated during runtime, because there are valid reasons for a record to be incomplete (the partial resultset mentioned above, an object still under construction, etc).
So, in my view, here's what a good "ORM" should be able to do:
- construct and fetch an object using any subset of the model properties, leaving the others empty (preferably explicit)
- allow composition of multiple models in both data retrieval and search parameters
- have an explicit distinction between storage invariants and business invariants (e.g. a function reporting on aggregate sales should not need to fetch every individual sales line, even though the model says that every sales order requires at least one item line)
At which point, you're merely replicating the relational model in your "ORM", and you're better off avoiding the abstraction entirely. That doesn't mean you shouldn't use model definitions that match your database records. But your ORM shouldn't be an abstraction between your models and your database -- it should expose the full relational capabilities to your developers, because they're going to need those capabilities anyway.
[ but note, I haven't actually worked with any recent ORM except for esqueleto -- and I'm pretty sure it doesn't qualify as an ORM ]
I had a similar revelation several years ago, based on the distinctions between a "domain model" and "persistence model" described by Mehdi Khalili, resulting in Atlas for PHP: http://atlasphp.io
https://typeorm.io/#/active-record-data-mapper/what-is-the-d...
FWIW, the Data Mappers are built on top of Table Data Gateways, which in turn are built using Query Builders and a connection locator. This lets you start at lower levels and work your way up, while at the same time allowing you to "reach down" from the upper levels to the lower ones when you need to.
Reading entities with many-to-many relationships is also often easier with an ORM, though you really have to be aware how the ORM will write this kind of query to avoid issues. ORMs are a very leaky abstraction when performance is concerned.
Another part that is very useful is the query building itself, though you can get that without an ORM. Having composable query fragments is very useful, doing that by hand is extremely tedious, error-prone and a potential security risk if you're not careful.
I'm comfortable writing SQL by hand, but I do prefer to have an ORM as long as I can also bypass it for queries that are difficult or impossible to do with the ORM. But an ORM can't replace knowing how databases work, and they do require you to understand more under-the-hood details than most other types of libraries.
Take a look at JDBI's Repository pattern for a simple compromise between SQL and ORM for this use case in particular.
* Those files have all the SQL. I never have raw SQL in my main application.
* Those files never e.g. call into my web framework, have HTML, or otherwise. They're a stand-along abstraction.
An ORM mutates a good abstraction (SQL) into a not-as-good one. Making a generic abstraction which works everywhere is hard, and SQL is the best one we've got. By making it domain-specific, we can mutate it into a good one.
One of the key principles I use is to try to have more than one back-end. For some (like a kvs), that might be Elasticache AND in-memory AND postgres. For something relational, it might be postgres and sqlite.
I do very much like the object hydration that ORMs bring though. I think libraries like Dapper hit the perfect balance of manual everything vs automagic complexity.
Just as long as people avoid too many uneccesary requests and/or do too much in code what could easily be solved by a view, window function, lateral join, stored procedure etc.
And yeah, a lot of that gets thrown out if you need to boil it down to a lowest common denominator to ease testing or different deployments, but a lot of programmers don't even utilize SQlite to a degree beyond beginner's SQL-92.