This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly.
I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One thing led to another and suddenly I looked at what I had done and said, "Wait a minute..."
There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make.
As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you connect to the database and construct queries.
Out of curiosity what platform and tech where you using? I am making the assumption of a predominately OO one based on the virtues of ORM. I have always found that when I try to solution back end or middleware based platforms with OO dominate languages (read Java, C#, et. al.) that there quickly becomes an impedance mismatch and any communication with the database becomes a monster of mapping OO philosophy to relational theory, whether that be via home rolled or an ORM.
That being said, I personally have found that I do not like OO languages for back end dev and I find that functional languages such as any variety of LISP marry extremely well to the transnational and process oriented nature of back-end systems as well as lend themselves to not having to jump thru hoops to contort the data into relational sets (Clojure's destructuring is an absolute life saver here). I find that there is little to no duplication of code in regards to transferring data to the db. You may want to give Clojure or F# (depending on your stack) a try for your back end and see if it does not alleviate a host of issues with trying to develop a process and transaction oriented system, which most back ends fit that definition.
I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly.
If you are using OO languages to communicate and transfer data to the DB you may very well be trying to solution for the impedance mismatch that is easily solved by using a functional language.
A query builder carefully preserves the underlying relational and RPC semantics and exposes all of that to the user in an easier-to-use form. That’s just good cautious modest abstraction.
An ORM believes it knows way better than those dumb RDBs how a database ought to behave by ingeniously pretending that everything you’re dealing with is just nice simple familiar arrays of local native class instances. Which, like all lies, ends up spawning more lies as each one starts to crack under inspection, until the whole rotten pile catastrophically collapses under the weight of its own total bullshit.
And of course it goes without saying which of these approaches our arrogant grandstanding consequence-shirking industry most likes to adopt.
Maybe the problem is not that you don't need a mapping layer, but because ORMs are obscure. And maybe they are obscure not because SQL is such a cursed spot, but because object-oriented programming ITSELF drift toward obscurity and magic. Don't you get the same feeling of obscurity about other libraries, e.g. web servers or clients? I often find the bare specs much clearer than (supposedly simplified) OO libraries that implement them.
The problem in many cases is actually in the OO part, in my experience - in the vast majority of cases where databases and persistence is concerned, staying in the procedural/structured + relational world keeps things simple, whereas objects often obscure what is actually happen, and invoke opaque magic such as ORMs.
I wonder what your experiences had been if after dropping the ORM you had gone one step more and dropped the objects.
Seriously Jesse, isn't this the very same reason __some__ people end up implementing yet another programming language without realizing it?
First they start out of exasperation with X language they use, because they hit some obstacles or limitations, and before they know it they end up implementing a newly created language.
You know what's the fun part? In their attempt to fix the aforementioned language's issues, they end up introducing __the very same problems__ in their own language, only under different "cloak" so to speak.
SQL is great if you will have multiple applications looking at the same dataset. E.g. An employee management program and a payroll program. In this case you should design a sane schema and mold the app around it.
ORMs are terrible in this sort of world since they tightly couple the application to the data. But if you will only ever have one application anyway the abstraction of a separate schema is pointless.
I like Clojure’s HugSQL[1] for this reason: you can simply write raw SQL, but when you start duplicating code, you can start factoring those bits out into composable “snippets”. The best of both worlds: composability and reuse, while still writing raw SQL.
I have the opposite view. I find ORMs annoying and obscure, and I think they introduce duplicated code.
If you need to run a certain query in multiple places, you need to repeat the same ORM expression or refractor it into a function. I find much better to have a module with all my SQL queries as strings. That way whenever I need to run a query I reference it from there. Of course it helps to use meaningful names.
This approach has a lot of advantages over ORMs:
* you know exactly what gets executed
* automatic DRY code
* the names of the SQL queries in the code are self-explanatory and the reader doesn't have to parse the ORM expression every time
Schema definitions are in standalone SQL files, as well as my migrations.
The only disadvantage is that it may be difficult to switch to a different database system, but that is not a problem for us.
ORMs seem to be a typical example of over-engineering. Often you don't need all that complexity they come with and when you do, you are probably better of understanding exactly what you are doing.
So maybe building a minimal API, wrapping your SQL queries isn't such a bad idea after all.
The things I've found positive about ORMs are exactly that mapping of results to business objects. The things I've found "not worth it" are the query-building APIs baked into the objects. These principles can be seen in a lightweight ORM I made, PureORM [1].
There is a lot of ancillary complexity in database connection libraries that we could attack before replacing the standard structured query language by some poorly considered mapping of objects to and from relation(s), inspired by poorly understood bad old OOP, which is generally what all ORMs boil down to.
It is striking the balance between your own queries and ORM.
My rule of thumb is that I always go with ORMs for MVPs and small apps. Optimizing for speed usually means going deeper and building a system or queries for yourself. Until that point I usually stick to less verbose code and more to business rules.
Each time I see someone complain about ORMs I remember Greenspun's tenth rule[1], which adapted to ORM would be:
"Any sufficiently complicated program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't use plain SQL where the situation calls for it. You can mix and match perfectly fine.
To me it is more of as Ted Neward describes "ORM is Vietnam of Computer Science"[1]
"Although it may seem trite to say it, Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy."
I think that this is true if you're writing your application in a way that requires object-relational mapping in the first place.
And that's only necessary when you're trying to manage your data in the application in an object-oriented way. And managing your data in an object-oriented way implies more than just the simple fact of defining classes to serve as data records. Those classes can be entirely equivalent to a struct in a procedural language or a record in a functional one. And I can't remember ever suffering from object/relational impedance mismatch when working in a procedural or functional language. Implying that the spot where you really start getting into trouble is when you trot out some distinctive feature of an object-oriented data model.
I submit that the original sin is treating instances of those data classes as if they are discrete entities that can serve as an application-side proxy for some other discrete entity that exists in the database, almost as if ODBC were just a more REST-flavored alternative to CORBA. Which is a thing that I've often been tempted to do in an object-oriented language, but never in a procedural or functional one.
Which isn't to say that I don't use anything to help with talking to databases in those other styles of language. It's just that I retain SQL as my query language (there are plenty of reasons to do it, none of which I'll bother to repeat here) and rely on a more Dapper-style library to handle unpacking the results into data structures. And I don't really consider those to be ORMs; they're just a special class of data mapping utility library.
So, in conclusion, I think that a more accurate stab would be "Any sufficiently hastily built, object-oriented, database-driven non-ORM program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
This seems like one of those topics where people often feel the need to pick a side for some reason. I've often heard criticism to the effect of, "people only use ORMs because they don't know SQL. Learn SQL!" It's seemingly impossible to convince these people that ORMs are fantastic for reducing boilerplate code and they can coexist right next to raw SQL for problems gnarlier than "select all foo where bar equals baz."
The best ORM (outside of ActiveRecord) I've seen was a proprietary hand-rolled one that solved the impedance mismatch.
It had a canonical XML format that entities were defined in and code generation for data access layers, domain models, view models etc.
It actually worked better than I've seen the abuse I've seen developers put EF through. I found it nicer and simpler than times I've worked with Hibernate.
I'm not going to say it was perfect, but every time I think back on it it makes me want to revisit the idea of leveraging a bit of code generation or metaprogramming to be able to have a canonical definition of an entity transformed into concerns that deal with the given entity at different points in the application.
That part of it just really hit the sweet spot for me.
What you are saying exactly highlights what the author is missing: That if your application has some logic, you will eventually have to map your database rows to your in memory typed structures.
It sucks. Sometimes it sucks less if you map your queries as well, sometimes it sucks less if you stick to SQL and only map your results, sometimes it sucks so much you're better off with a no-sql solution.
But when using a relational database, ORM isn't optional.
Db4j is an async transactional database engine that uses java as the query language. It doesn't eliminate the O/R impedance mismatch entirely (it's not a graph or object database), but it does simplify it greatly since everything is java
I love the idea and it's been fun for me to make demos with, but I've gotten almost zero feedback on the API, and what I have gotten is "can you add a SQL frontend". And yet neither ORM nor SQL are loved
So the question (and I'm not suggesting that Db4j is the answer) is: "what's the API that would be most natural ?"
Unless you don't have objects... then no need for an Object-relational mapping, not a well maintained one, and not an ad-hoc one. All you need is a DB connection pooling library, some helpers or libs to help you build dynamic SQL queries and that's all.
ORMs lure you in with a false sense of neat abstraction. They have nice intuitive examples on their home pages. But then you use them in the real world, doing gnarly queries, and you realize that doing anything powerful and fast in the ORM requires its own completely separate abstractions, which are often difficult for the uninitiated to follow. It's also often a big pain to debug the raw SQL that gets compiled after the ORM does its magic.
The argument I've made before when going down the path of ORMs has been: do we forsee needing to use this model code on a different database engine? Outside of simple toy applications, or needing to support different engines with the same code, I agree that ORMs are more trouble than they're worth.
For example, Ecto isn't technically an ORM but it lets you do ORM-like things. It's Elixr's data mapping and query language tool. It happens to be one of the nicest "I need to work with data" abstractions I've ever used.
It's a bit more typing than ActiveRecord and even SQLAlchemy, but you feel like you're at a good level of abstraction. It's high enough that you're quite productive but it's low enough that it doesn't feel like a black box.
You get nice benefits of higher level ORMs too such as being able to compose queries, so you can design some pretty compact and readable looking functions, such as:
def eligible_discounts(package_id, code) do
__MODULE__
|> for_package(package_id)
|> with_discount()
|> active()
|> usage_count_less_than_usage_limit()
|> after_starts_at()
|> before_ends_at()
|> maybe_code(code)
end
Each of those function calls is just a tiny bite sized query and in the end it all gets composed into 1 DB query that gets executed.
I think Ecto's biggest win was having the idea of changesets, schemas and repos as separate things. It really gives you the best of everything. A way to ensure your data is validated but also flexible enough where you can separate your UI / forms from your underlying database schema. You can even choose not to use a database backend but still leverage other pieces of Ecto like its changesets and schemas, allowing you to do validate and make UIs from any structured data and then plug in / out your data backend (in memory structs or a real DB, etc.).
Initially ORMs can save time when developing as you get an easy mapping between objects and the database.
However in practise ORM tends to give you quite horrible JOINs that quite frankly are hard to understand for humans.
Further more I think that ORM can lead to a bad practice in the sense that you do not need to think about your data layout first.
But for database performance data layout is of utter most importance. One need to have data in lay out in a form that makes the application run
fast. ORMs does not necessarily provide that. One need to normalize the database.
ORM save you time during initial development but you pay later in the maintenance phase when what is complex queries that humans may not understand are hard to optimize for performance.
This topic pops up frequently here on HN and every time I’m shocked at how many people have issues with ORMs! I’ve been using Hibernate/Spring Data for several years now and never ran into any issues. If I need to write a complex query, I can easily write a @Query annotation in HQL and it neatly fits right in to the repository class. I also develop with query logging enabled so I have better understanding of the queries actually ran on the DB. I think it really boils down to using the right tools, the right way.
Even if you use an ORM you should probably know SQL so you can use them efficiently. Also, if you use an ORM, make sure it "starts with database schema" not with objects. Otherwise the mismatch will probably be horrible for you. A dozen years ago I published this article on generating your domain objects from the database, still a reasonable thing to do if you want the added query expressiveness the ORMs have over most SQL generators.
This article was well balanced. I find title just a bit misleading, since "What ORMs have taught me: just learn SQL", to me, carries a slightly different message than "What ORMs have taught me: learn SQL".
The second sentence is a little more compatible with the first line, "they can be used to nicely augment working with SQL in a program, but they should not replace it."
That's certainly how I use them. ORMs can save you a lot of irritating typing where it comes to insert and update statements. Aside from that, I write a lot of raw SQL.
I have seen what I would describe as ORM-induced, yaml-induced database damage. This isn't meant as a criticism of these tools per se, they're perfectly compatible with a well designed app. But I have noticed that people sometimes create databases that are useful only in the context of their configuration-file/ORM heavy app. Essentially, the programmers conceive of their data as a set of objects, and they use the config file to store global constants and the ORM to persist files, almost as if they're pickling and retrieving objects back into the system.
The result is a database that can't really be queried with SQL, more or less useless outside the context of the application. I firmly agree with developers who maintain that information will outlive an application, a database will outlive the software that was originally designed to use it (perhaps in parallel with it). I think a SQL database should be useful all on its own as a data source. If you got rid of the app, you'd lose a lot of operations on that data, a lot of UI, a lot of valuable things, but you'd be able to get at and use your data. If that's not the case, I'd seriously reconsider the design.
Kind of hard to do that without understanding SQL, so yeah, definitely learn it.
I too have gone back to SQL after working with ORMs for 10+ years. Having worked with them on a wide range of projects and teams, I can say, without any reservation, they are not worth it.
Not worth it for the small projects nor the large projects. They significantly complicate the development workflow and add another layer of (often times, cumbersome) abstraction between the user and the data.
If you encounter any issues (and better pray you don't), expect to spend hours stepping through reams of highly abstracted byzantine code, all the time feeling guilty when you just want to open a database connection and send the SQL string you developed and tested in minutes.
There's a sensible middle ground here, although I agree with the thrust of the message because using an ORM doesn't obviate the need to learn SQL - something I think a lot of developers forget.
The other extreme from using an ORM "for everything" is using SQL "for everything", either via loads of handwritten ad hoc SQL, or stored procedures, UDFs, views, or a mix of all of these. This is just a different nightmare. And don't be fooled: it really is still a nightmare.
A sensible approach blends use of an ORM with handwritten SQL where needed. In fact most ORMs will allow you to do things like build collections of objects from custom SQL anyway, so there's really no need to shy away from it.
One other thing I'd say: I wouldn't necessarily trust my ORM to adequately design my database for me via a code first approach. It's more work but thinking about the data model and explicitly designing the database often yields better results, and you have more control. Code first is OK for simple stuff, but often even simple stuff becomes complex over time so I tend to shy away from it.
In my opinion this whole problem is one of the strongest condemnations of OOP. Even if OOP really were a great way to model your data, to express relationships and property types and such (setting aside all the questionability of that claim), it's all just going to end up in a database anyway. Unless you're using a denormalized database like Mongo, or the bulk of your application state is non-persistent, your perfectly thought-out OOP model is at best going to be a terrible approximation of the real data model and you're much better off using a language/paradigm that lets you just treat data as data.
Swapping from EF6 to Dapper was one of the best choices we ever made with our project stack. It is so relieving to be able to hand-tune queries and transactions now. Initially, we were sold on the apparent simplicity of EF6, but as with many things there is a cost for an abstraction like this. In our case, the performance penalties and opaqueness were dealbreakers after a while. We saw an average speedup of 10x on all SQL operations after rewriting using direct SQL with Dapper handling mapping of result sets into models.
Writing your own SQL migrations is actually incredibly straightforward if you just think for a few minutes about how you would do it if ORMs didn't exist. Many database systems have ways to store metadata like schema versions outside the scope of any table structure, so you can leverage these really easily in your scripts/migration logic. EF6 uses an explicit migration table which I was never really a huge fan of.
One thing we did do that dramatically eased the pain of writing SQL was to use JSON serialization for containing most of our complex, rapidly-shifting business models, and storing those alongside a metadata row for each instance. Our project would be absolutely infeasible for us today if it weren't for this one little trick. Deserializing a JSON blob to/from a column into/from a model containing 1000+ properties in complex nested hierarchies is infinitely faster than trying to build up a query that would accomplish the same if explicit database columns existed for each property across the many tables.
I use Entity Framework for C# and I have grown to appreciate it. I get libraries for in-memory databases which makes it easy to write thorough unit tests, the `Include` function uses join to include foreign key objects in an optimal manner, and scaffolding tools make it easy to map from SQL to C# classes. The resulting SQL from the Linq expressions is logged which makes it easy to see what is going on, if you already know SQL. The only problem is that I think the writeback to the database of manipulated or new objects is not clear but that might just be because I haven't read the documentation for that thoroughly.
Advocating for the use of SQL over an ORM in every case is like advocating for the use of Assembly over C in every case.
In both cases, one is a higher level abstraction over the lower level capabilities, which can provide a quite large gain in usability and ability to easily understand what is going on at the level you are working at, for the loss of hand optimizing at a low level to get just what you want in every case.
Similarly to with Assembly and C, you can often drop to the lower level as needed for speed or other very specific needs.
In both cases a good understanding of the lower level language will help you both know when it's appropriate to drop to a lower level for performance or for a special feature, and when it doesn't matter because the ops/SQL generated is rear optimal anyways or the gains are almost definitely less than the problems caused from a maintainability perspective.
I'm perfectly happy to use an ORM for 95% of my DB needs. Just the query builders that generally ship with them are worth the price of their inclusion IMO (at least for the good ones), as it can greatly simplify queries that are variable based on different parameters you may have per run.
I use a data mapping ORM and I've literally not have any of these concerns. They are all non-issues.
The first item about querying is relevant but all ORMs allow you to drop into SQL to execute a complex reporting-style query. It's not really necessary if you are just querying in objects to manipulate (which is what ORMs are good for).
I'm okay with ORMs, but I can't stand lazy evaluation. It often leads to situations where it's entirely unclear when the program is making a database call.
For example, given the small django program:
dbcall = models.Purchases.objects.filter(amount = 100)
if dbcall:
do_something()
if len(dbcall) > 5:
do_something_else()
third_thing(dbcall[0])
Does the above app make 1, 2 or 3 database requests? There is an answer, but it's not at all clear to the developer.
If you use Go and PostgreSQL, I’ve been working on a tool to help you “just use SQL” called sqlc[0]. It generates methods and structs for your queries, automatically. It’s made a dramatic difference in my day-to-day workflow.
I think there's often an underlying confusion between different tools and what they are supposed to do.
An ORM - as the acronym says - is helpful to map database records to objects in the system.
The meaning of the acronym already says that an ORM is not really designed for scenarios like aggregations and reporting. Within those contexts, you don't normally reason in terms of list of "objects" and "relationships" between them.
A "SQL builder" gives you a nice programming interface to build and manipulate SQL statements. Manually building complicated SQL strings is tedious, error prone and it makes it hard to reuse the same queries. With a SQL builder instead you can easily add dynamic conditions, joins etc, based on the logic of your application. Think of building a filterable Rest API that needs to support custom fields and operators passed through the URL querystring: concatenating strings would be hard to scale in terms of complexity.
Some people prefers to use templates instead of SQL builder to add conditions, dynamic values, select fields etc. I personally find that this approach is like a crippled version of a proper SQL builder interface. I prefer to use the expressiveness of a real programming language instead of some (awkward?) template engine syntax.
I think the confusion between these two different tools is caused by the fact that in some popular frameworks as Django or Rails you just get to use the ORM, even if behind the scenes the ORM uses some internal query builder.
Other ORMs like SQLAlchemy instead gives you both tools. You can indeed use SQLAlchemy as a ORM and you can also use it directly as a SQL builder when the ORM abstraction doesn't really work.
Normally, if someone tells me that it's better to write SQL queries by concatenating strings, I'd ask them how they'd build a webpage that filters products in the catalog with a series of filters specified by the user (by price, by title, by reviews, sorting, etc.). Try and build that concatenating raw SQL bits, without making a huge mess.
Also, the "just learn SQL" may apply to ORMs, but certainly not to a SQL builder.
[+] [-] JesseAldridge|6 years ago|reply
I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One thing led to another and suddenly I looked at what I had done and said, "Wait a minute..."
[+] [-] kccqzy|6 years ago|reply
As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you connect to the database and construct queries.
[0]: https://docs.sqlalchemy.org/en/13/
[+] [-] kls|6 years ago|reply
That being said, I personally have found that I do not like OO languages for back end dev and I find that functional languages such as any variety of LISP marry extremely well to the transnational and process oriented nature of back-end systems as well as lend themselves to not having to jump thru hoops to contort the data into relational sets (Clojure's destructuring is an absolute life saver here). I find that there is little to no duplication of code in regards to transferring data to the db. You may want to give Clojure or F# (depending on your stack) a try for your back end and see if it does not alleviate a host of issues with trying to develop a process and transaction oriented system, which most back ends fit that definition.
I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly.
If you are using OO languages to communicate and transfer data to the DB you may very well be trying to solution for the impedance mismatch that is easily solved by using a functional language.
[+] [-] hhas01|6 years ago|reply
A query builder carefully preserves the underlying relational and RPC semantics and exposes all of that to the user in an easier-to-use form. That’s just good cautious modest abstraction.
An ORM believes it knows way better than those dumb RDBs how a database ought to behave by ingeniously pretending that everything you’re dealing with is just nice simple familiar arrays of local native class instances. Which, like all lies, ends up spawning more lies as each one starts to crack under inspection, until the whole rotten pile catastrophically collapses under the weight of its own total bullshit.
And of course it goes without saying which of these approaches our arrogant grandstanding consequence-shirking industry most likes to adopt.
[+] [-] Mikhail_Edoshin|6 years ago|reply
Maybe the problem is not that you don't need a mapping layer, but because ORMs are obscure. And maybe they are obscure not because SQL is such a cursed spot, but because object-oriented programming ITSELF drift toward obscurity and magic. Don't you get the same feeling of obscurity about other libraries, e.g. web servers or clients? I often find the bare specs much clearer than (supposedly simplified) OO libraries that implement them.
[+] [-] vbezhenar|6 years ago|reply
[+] [-] beagle3|6 years ago|reply
I wonder what your experiences had been if after dropping the ORM you had gone one step more and dropped the objects.
[+] [-] stephen82|6 years ago|reply
Seriously Jesse, isn't this the very same reason __some__ people end up implementing yet another programming language without realizing it?
First they start out of exasperation with X language they use, because they hit some obstacles or limitations, and before they know it they end up implementing a newly created language.
You know what's the fun part? In their attempt to fix the aforementioned language's issues, they end up introducing __the very same problems__ in their own language, only under different "cloak" so to speak.
It's a vicious cycle I'm afraid...
[+] [-] jdsully|6 years ago|reply
ORMs are terrible in this sort of world since they tightly couple the application to the data. But if you will only ever have one application anyway the abstraction of a separate schema is pointless.
[+] [-] dkersten|6 years ago|reply
[1] https://www.hugsql.org/
[+] [-] rocho|6 years ago|reply
If you need to run a certain query in multiple places, you need to repeat the same ORM expression or refractor it into a function. I find much better to have a module with all my SQL queries as strings. That way whenever I need to run a query I reference it from there. Of course it helps to use meaningful names.
This approach has a lot of advantages over ORMs: * you know exactly what gets executed * automatic DRY code * the names of the SQL queries in the code are self-explanatory and the reader doesn't have to parse the ORM expression every time
Schema definitions are in standalone SQL files, as well as my migrations.
The only disadvantage is that it may be difficult to switch to a different database system, but that is not a problem for us.
[+] [-] arendtio|6 years ago|reply
So maybe building a minimal API, wrapping your SQL queries isn't such a bad idea after all.
[+] [-] thymanl23|6 years ago|reply
[1] https://github.com/craigmichaelmartin/pure-orm
[+] [-] takeda|6 years ago|reply
Here's your problem
[+] [-] inimino|6 years ago|reply
[+] [-] CraneWorm|6 years ago|reply
http://slick.lightbend.com/doc/3.2.0/orm-to-slick.html
[+] [-] inanutshellus|6 years ago|reply
You throw in SQL, provide a simple mapper, done. IMHO it's far superior to ORMs when your database is or may become complicated.
[+] [-] _the_inflator|6 years ago|reply
My rule of thumb is that I always go with ORMs for MVPs and small apps. Optimizing for speed usually means going deeper and building a system or queries for yourself. Until that point I usually stick to less verbose code and more to business rules.
[+] [-] alexandercrohde|6 years ago|reply
[+] [-] seunosewa|6 years ago|reply
Maybe you should not do that? Can you give us an idea of the domain problem you were trying to solve that made you feel the need for that?
[+] [-] iamsb|6 years ago|reply
[+] [-] 0x445442|6 years ago|reply
[+] [-] draw_down|6 years ago|reply
[deleted]
[+] [-] 725686|6 years ago|reply
"Any sufficiently complicated program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't use plain SQL where the situation calls for it. You can mix and match perfectly fine.
[1] https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule Edit: typo.
[+] [-] geodel|6 years ago|reply
"Although it may seem trite to say it, Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy."
[1] http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
[+] [-] yawaramin|6 years ago|reply
Yes, the object-relational impedance mismatch. It's the classic case of having a hammer (OOP) and trying to make everything look like a nail.
[+] [-] mumblemumble|6 years ago|reply
And that's only necessary when you're trying to manage your data in the application in an object-oriented way. And managing your data in an object-oriented way implies more than just the simple fact of defining classes to serve as data records. Those classes can be entirely equivalent to a struct in a procedural language or a record in a functional one. And I can't remember ever suffering from object/relational impedance mismatch when working in a procedural or functional language. Implying that the spot where you really start getting into trouble is when you trot out some distinctive feature of an object-oriented data model.
I submit that the original sin is treating instances of those data classes as if they are discrete entities that can serve as an application-side proxy for some other discrete entity that exists in the database, almost as if ODBC were just a more REST-flavored alternative to CORBA. Which is a thing that I've often been tempted to do in an object-oriented language, but never in a procedural or functional one.
Which isn't to say that I don't use anything to help with talking to databases in those other styles of language. It's just that I retain SQL as my query language (there are plenty of reasons to do it, none of which I'll bother to repeat here) and rely on a more Dapper-style library to handle unpacking the results into data structures. And I don't really consider those to be ORMs; they're just a special class of data mapping utility library.
So, in conclusion, I think that a more accurate stab would be "Any sufficiently hastily built, object-oriented, database-driven non-ORM program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
[+] [-] ryangittins|6 years ago|reply
Thank you!
This seems like one of those topics where people often feel the need to pick a side for some reason. I've often heard criticism to the effect of, "people only use ORMs because they don't know SQL. Learn SQL!" It's seemingly impossible to convince these people that ORMs are fantastic for reducing boilerplate code and they can coexist right next to raw SQL for problems gnarlier than "select all foo where bar equals baz."
[+] [-] james_s_tayler|6 years ago|reply
It had a canonical XML format that entities were defined in and code generation for data access layers, domain models, view models etc.
It actually worked better than I've seen the abuse I've seen developers put EF through. I found it nicer and simpler than times I've worked with Hibernate.
I'm not going to say it was perfect, but every time I think back on it it makes me want to revisit the idea of leveraging a bit of code generation or metaprogramming to be able to have a canonical definition of an entity transformed into concerns that deal with the given entity at different points in the application.
That part of it just really hit the sweet spot for me.
[+] [-] tomtomtom777|6 years ago|reply
It sucks. Sometimes it sucks less if you map your queries as well, sometimes it sucks less if you stick to SQL and only map your results, sometimes it sucks so much you're better off with a no-sql solution.
But when using a relational database, ORM isn't optional.
[+] [-] nqzero|6 years ago|reply
https://github.com/db4j/db4j
I love the idea and it's been fun for me to make demos with, but I've gotten almost zero feedback on the API, and what I have gotten is "can you add a SQL frontend". And yet neither ORM nor SQL are loved
So the question (and I'm not suggesting that Db4j is the answer) is: "what's the API that would be most natural ?"
[+] [-] didibus|6 years ago|reply
[+] [-] daenz|6 years ago|reply
The argument I've made before when going down the path of ORMs has been: do we forsee needing to use this model code on a different database engine? Outside of simple toy applications, or needing to support different engines with the same code, I agree that ORMs are more trouble than they're worth.
[+] [-] nickjj|6 years ago|reply
For example, Ecto isn't technically an ORM but it lets you do ORM-like things. It's Elixr's data mapping and query language tool. It happens to be one of the nicest "I need to work with data" abstractions I've ever used.
It's a bit more typing than ActiveRecord and even SQLAlchemy, but you feel like you're at a good level of abstraction. It's high enough that you're quite productive but it's low enough that it doesn't feel like a black box.
You get nice benefits of higher level ORMs too such as being able to compose queries, so you can design some pretty compact and readable looking functions, such as:
Each of those function calls is just a tiny bite sized query and in the end it all gets composed into 1 DB query that gets executed.I think Ecto's biggest win was having the idea of changesets, schemas and repos as separate things. It really gives you the best of everything. A way to ensure your data is validated but also flexible enough where you can separate your UI / forms from your underlying database schema. You can even choose not to use a database backend but still leverage other pieces of Ecto like its changesets and schemas, allowing you to do validate and make UIs from any structured data and then plug in / out your data backend (in memory structs or a real DB, etc.).
[+] [-] acd|6 years ago|reply
Initially ORMs can save time when developing as you get an easy mapping between objects and the database.
However in practise ORM tends to give you quite horrible JOINs that quite frankly are hard to understand for humans.
Further more I think that ORM can lead to a bad practice in the sense that you do not need to think about your data layout first. But for database performance data layout is of utter most importance. One need to have data in lay out in a form that makes the application run fast. ORMs does not necessarily provide that. One need to normalize the database.
ORM save you time during initial development but you pay later in the maintenance phase when what is complex queries that humans may not understand are hard to optimize for performance.
Database normalization https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Third_normal_form
[+] [-] invokestatic|6 years ago|reply
[+] [-] spullara|6 years ago|reply
https://javarants.com/generate-jpa-or-gorm-classes-from-your...
[+] [-] geebee|6 years ago|reply
The second sentence is a little more compatible with the first line, "they can be used to nicely augment working with SQL in a program, but they should not replace it."
That's certainly how I use them. ORMs can save you a lot of irritating typing where it comes to insert and update statements. Aside from that, I write a lot of raw SQL.
I have seen what I would describe as ORM-induced, yaml-induced database damage. This isn't meant as a criticism of these tools per se, they're perfectly compatible with a well designed app. But I have noticed that people sometimes create databases that are useful only in the context of their configuration-file/ORM heavy app. Essentially, the programmers conceive of their data as a set of objects, and they use the config file to store global constants and the ORM to persist files, almost as if they're pickling and retrieving objects back into the system.
The result is a database that can't really be queried with SQL, more or less useless outside the context of the application. I firmly agree with developers who maintain that information will outlive an application, a database will outlive the software that was originally designed to use it (perhaps in parallel with it). I think a SQL database should be useful all on its own as a data source. If you got rid of the app, you'd lose a lot of operations on that data, a lot of UI, a lot of valuable things, but you'd be able to get at and use your data. If that's not the case, I'd seriously reconsider the design.
Kind of hard to do that without understanding SQL, so yeah, definitely learn it.
[+] [-] iamleppert|6 years ago|reply
Not worth it for the small projects nor the large projects. They significantly complicate the development workflow and add another layer of (often times, cumbersome) abstraction between the user and the data.
If you encounter any issues (and better pray you don't), expect to spend hours stepping through reams of highly abstracted byzantine code, all the time feeling guilty when you just want to open a database connection and send the SQL string you developed and tested in minutes.
[+] [-] stickfigure|6 years ago|reply
Intermediate programmer: ORMs just get in the way! SQL isn't that hard after all.
Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of the boilerplate.
[+] [-] bartread|6 years ago|reply
The other extreme from using an ORM "for everything" is using SQL "for everything", either via loads of handwritten ad hoc SQL, or stored procedures, UDFs, views, or a mix of all of these. This is just a different nightmare. And don't be fooled: it really is still a nightmare.
A sensible approach blends use of an ORM with handwritten SQL where needed. In fact most ORMs will allow you to do things like build collections of objects from custom SQL anyway, so there's really no need to shy away from it.
One other thing I'd say: I wouldn't necessarily trust my ORM to adequately design my database for me via a code first approach. It's more work but thinking about the data model and explicitly designing the database often yields better results, and you have more control. Code first is OK for simple stuff, but often even simple stuff becomes complex over time so I tend to shy away from it.
[+] [-] _bxg1|6 years ago|reply
[+] [-] dang|6 years ago|reply
2016: https://news.ycombinator.com/item?id=11981045
Discussed at the time: https://news.ycombinator.com/item?id=8133835
[+] [-] bob1029|6 years ago|reply
Writing your own SQL migrations is actually incredibly straightforward if you just think for a few minutes about how you would do it if ORMs didn't exist. Many database systems have ways to store metadata like schema versions outside the scope of any table structure, so you can leverage these really easily in your scripts/migration logic. EF6 uses an explicit migration table which I was never really a huge fan of.
One thing we did do that dramatically eased the pain of writing SQL was to use JSON serialization for containing most of our complex, rapidly-shifting business models, and storing those alongside a metadata row for each instance. Our project would be absolutely infeasible for us today if it weren't for this one little trick. Deserializing a JSON blob to/from a column into/from a model containing 1000+ properties in complex nested hierarchies is infinitely faster than trying to build up a query that would accomplish the same if explicit database columns existed for each property across the many tables.
[+] [-] sword_smith|6 years ago|reply
[+] [-] kbenson|6 years ago|reply
In both cases, one is a higher level abstraction over the lower level capabilities, which can provide a quite large gain in usability and ability to easily understand what is going on at the level you are working at, for the loss of hand optimizing at a low level to get just what you want in every case.
Similarly to with Assembly and C, you can often drop to the lower level as needed for speed or other very specific needs.
In both cases a good understanding of the lower level language will help you both know when it's appropriate to drop to a lower level for performance or for a special feature, and when it doesn't matter because the ops/SQL generated is rear optimal anyways or the gains are almost definitely less than the problems caused from a maintainability perspective.
I'm perfectly happy to use an ORM for 95% of my DB needs. Just the query builders that generally ship with them are worth the price of their inclusion IMO (at least for the good ones), as it can greatly simplify queries that are variable based on different parameters you may have per run.
[+] [-] wvenable|6 years ago|reply
The first item about querying is relevant but all ORMs allow you to drop into SQL to execute a complex reporting-style query. It's not really necessary if you are just querying in objects to manipulate (which is what ORMs are good for).
[+] [-] speedplane|6 years ago|reply
For example, given the small django program:
Does the above app make 1, 2 or 3 database requests? There is an answer, but it's not at all clear to the developer.[+] [-] conroy|6 years ago|reply
[0] https://github.com/kyleconroy/sqlc
[+] [-] fabiosussetto|6 years ago|reply
An ORM - as the acronym says - is helpful to map database records to objects in the system. The meaning of the acronym already says that an ORM is not really designed for scenarios like aggregations and reporting. Within those contexts, you don't normally reason in terms of list of "objects" and "relationships" between them.
A "SQL builder" gives you a nice programming interface to build and manipulate SQL statements. Manually building complicated SQL strings is tedious, error prone and it makes it hard to reuse the same queries. With a SQL builder instead you can easily add dynamic conditions, joins etc, based on the logic of your application. Think of building a filterable Rest API that needs to support custom fields and operators passed through the URL querystring: concatenating strings would be hard to scale in terms of complexity. Some people prefers to use templates instead of SQL builder to add conditions, dynamic values, select fields etc. I personally find that this approach is like a crippled version of a proper SQL builder interface. I prefer to use the expressiveness of a real programming language instead of some (awkward?) template engine syntax.
I think the confusion between these two different tools is caused by the fact that in some popular frameworks as Django or Rails you just get to use the ORM, even if behind the scenes the ORM uses some internal query builder.
Other ORMs like SQLAlchemy instead gives you both tools. You can indeed use SQLAlchemy as a ORM and you can also use it directly as a SQL builder when the ORM abstraction doesn't really work.
Normally, if someone tells me that it's better to write SQL queries by concatenating strings, I'd ask them how they'd build a webpage that filters products in the catalog with a series of filters specified by the user (by price, by title, by reviews, sorting, etc.). Try and build that concatenating raw SQL bits, without making a huge mess.
Also, the "just learn SQL" may apply to ORMs, but certainly not to a SQL builder.