16 ms·
What ORMs Have Taught Me: Just Learn SQL (2014)
- 02020202 6y agoyeah, the thing is that at the beginning you just don't know what you'll need later. so sticking to anything strict will become a limitation sooner rather than later. it is pain to do the manual work in every project over and over again but "this is the way", unfortunately. i have done two event-sourced project in a row and i am trying to make a reusable library that i can use from now on, which will allow me to write projections so i am not tied to any schema and can build any data structures i'll need.
- tehlike 6y agoIf using .net check marten.
- tehlike 6y agoSomewhat flawed reasonings. Re: identities. Use of db generated identity has the downside, but n/hibernate has a bunch of other Id generators to mitigate the problems. You can use sequential guid, hilo, guid, or whatever. I use sequential guid because it helps with a bunch of other things. So it's not really a leaky abstraction. It's not really an orm problem, you need to do that regardless. Re queries: I think linq showed the true power of orm in some sense. Query your database as if you are querying your objects. It has problems like n+1 or exploding cartesian but postgres and the likes fixes it nicely with json_agg. I have to agree though that I like graphql way too. For nodejs compile time linq is not an option, so graphql it is. Transactions: I don't know how this is related to orms? Disclaimer: former nhibernate developer that was around when ayende was building linq for nhibernate and my view is probably dated and biased.
- jayd16 6y agoI think you misunderstand the identity issue. I think its more a complaint about mutating objects not cleanly mapping to running UPDATE commands. Live objects and partial updates are messy and dirty writes become an issue. I often see a a pattern of a Save() method on an ORM type and its ambiguous as to what exactly that will write to the DB at any time. Maybe there are better ORM patterns but the problem is its a common anti-pattern none the less. If you use a more RPC approach to your data layer, partial updates based on an entity id are easier to groc.
- tehlike 6y agoI think I understood correct "When you have foreign keys, you refer to related identities with an identifier." "What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier." This(and the sentences before it) basically saying if you have if you have a foreign key you have to first save the main object to get it's id. Id doesn't have to be responsibility of the database. In fact, I'd argue an identifier is an application layer concern. Orm could still solve this problem by simply cascading, or you can use one of the identity strategies that don't rely on database assigning it. You can generate them in either application layer or orm layer through identity generators.
- jayd16 6y agoLet me be more clear. Partial objects, such as a book with an author created from a user request may or may not have DB ids that may or may not exist. If you want to add a Book object with an Author you must first upsert the Author to possibly retrieve the DB id which already exists and cannot simply be generated locally. This can often be a bit confusing or cumbersome compared to the SQL. Why are you caring about DB ids if the ORM is supposed to handle it, asks OP.
- baq 6y agoalso read the manual for your DB. it's probably a thousand pages. likely more. i understand it isn't the most thrilling reading of your life. it might just be once you have an issue in prod, though. if you really don't have time for that, i also understand. i was you. but really please read the table of contents in that case.
- rbirkby 6y agoPerfect example of the trough of disillusionment. The ORM slope of enlightenment comes when you realise the power of the unit of work, not just a single query. But that was then, and microservices mean we no longer use large units of work. So ORMs without complex UoW make less sense.
- swyx 6y agoI've never come across this unit of work concept. care to elaborate or recommend a link to learn more?
- hipnoizz 6y agoThis is I think primarily related to transaction boundaries and tracking changes. You can read https://www.martinfowler.com/eaaCatalog/unitOfWork.html https://www.martinfowler.com/eaaCatalog/unitOfWork.html as a starting reference. I haven't heard this phrase much lately to be honest. ;) In 'typical' Java application (since many comments here as well as the original article mentions Hibernate...) you will likely use annotations like @Transactional to mark your transaction boundaries (likely with default propagation and isolation levels...) and then Hibernate will track any changes ('dirty checking') to objects you asked him to fetch and then at the end the transaction Hibernate will issue whatever DML commands (INSERT, UPDATE, DELETE) needs to be issued in an appropriate order. In a galaxy far, far away i.e. before Java 1.5 instead using @Transactional you would maybe use (write) some object like TransactionManager which provides an execute() method that receives a block of code in a form of a interface implementation (no closures for you!). This part is relatively straightforward. Tracking changes in any semi-automatic way was always messy...
- sopromo 6y agoI do not agree and this is coming from someone that loves SQL. I only experienced the problems that this article describes with people that do not know how the ORM works. You can use an ORM and it works flawless for most cases. There might be some cases where I need to write SQL to generate a custom function or some weird edge case but that is why the ORM gives you the possibility to write your own SQL if you want. I forgot to mention that having to write raw SQL queries and mantain them, doing migrations and keeping up with the changes is kind of a pain when most of the time the ORM takes care of everything.
- emmanuelay 6y agoORMs are good for quickly building something. But if you're building something that is expected to last and that will be passed on to other developers... ORMs will likely derail or put severe limitations on your application.
- lcrz 6y agoCounterpoint: a lot of ruby devs are very familiar and capable with ActiveRecord. Al lot of python devs are familiar and capable with using the Django ORM. Why are these ORM more likely to limit your application? You provide no proof or explanation of your statement.
- ClikeX 6y agoThe only limiting thing I've experienced with ActiveRecord is when the database becomes larger and more complex. The performance takes a bit of a hit. Which isn't ActiveRecords fault, it's the developer's lack of understanding how the database works. Most of the issues I see is a lack of understanding of things like N+1 queries. I see this mistakes from junior to senior level. ActiveRecord has built in solutions to fix them too. Some developers don't know about them.
- yxhuvud 6y agoThere are built-in solutions but also quite severe limitations when you get to advanced usage of activerecord. Especially with regards to composability. Of course, raw SQL is also really bad in that scenario.
- lmm 6y agoIf you half-ass using an ORM then you get the worst of both worlds. To get value out of it you have to embrace it completely: write all the queries in the ORM (so that the ORM's caching functionality etc. can actually work for you), define the schema in the ORM and generate the tables and migrations from that. Working with an ORM and avoiding SQL is not just doable, it's easy, but you have to actually try.
- viach 6y agoHow do you deal with complex reporting queries when embracing ORM completely?
- mcv 6y agoI think the most important part is to create your database schema through your ORM. I used to do that ages ago with Ruby on Rails, and that worked very well. We still had our complex reporting queries in SQL, and that was no problem. The point is that if you use an ORM, your database needs to be optimised for that ORM. Bolting an ORM onto an existing database is a recipe for disaster.
- collyw 6y agoI learned to use the Django ORM in depth, but there were some things it couldn't do at the time (it may have improved). Extra conditions on joins. Subqueries, conditional aggregates (it does those now). Once you get to a certain level of complexity you can shoehorn the query into the ORM,but I am not sure it makes sense. The next developer is more likely to know SQL well than the Djnago ORM.
- Ensorceled 6y agoYeah, all of that stuff is much easier in Django now. I find myself dipping into raw SQL or stored procs far less often.
- lmm 6y agoEither figure out how to express the queries you need within your ORM, or create a place where you can run ad-hoc SQL reporting queries that's completely segregated from your live data - e.g. a read-only replica, or regular database dumps.
- jeswin 6y agoThere are many reasons why ORMs work better than it did earlier. For one, people prefer simpler database schema these days - and what used to be larger monolithic apps are often broken into micro services. Earlier, you'd have one big database - today you'll have many. And ORMs are great with simple queries and joins. The language plays a big role in whether an ORM is actually useful or not. In the .Net world, ORMs work quite well because querying capabilities are integrated into the language. Queries and schema are verified at compile-time, and I wouldn't trade it for slightly better performance or control. On the other hand, with Python (or say Node), or with Java and Hibernate the wins are smaller. Of course, there will be some queries which are just better written in plain SQL. If you're willing to accept that, ORMs are a good tool in your toolbox.
- Philip-J-Fry 6y agoI'm a Go developer and I notice a lot of other Go developers instantly suggest things like GORM to noobs writing applications. Whereas I always suggest against it. I'm a big advocate of understand your data model at the database level. Need to join on too many tables is too easy to do with an ORM. My go-to strategy for SQL is simple. Abstract your SQL as far away from your application as possible. Not just by using interfaces in your application, but by using stored procedures in your database. In my mind SQL is just another service your application depends on. And you want that interface to be as simple as possible for your application. If you need to tweak SQL performance it should not need any input from your application. I could completely migrate to a new table relationship structure without the application realising if that was what was needed. You could even go as far as to unit test just your SQL code if you wanted, something you can't do too easily when it's in your application. Yes, if you need to return new data then you need to update your stored proc and code. But that's so worth it in my opinion for that extra layer of abstraction. My opinion is slightly skewed from a decently sized business perspective, but I do still follow this pattern in personal projects. When migrating applications to different tech stacks (like Java to Go, or C# to Go) this abstraction has meant the world to us.
- C1sc0cat 6y agoI agree and for any serious database used in production SPROC's have so many advantages improved security as well.
- lmilcin 6y agoI have been working with Java for the past 20 years. I have noticed that over the years the knowledge of SQL eroded a lot in population of Java developers. Not only that, but the design of application seems to be lacking. Where problems could easily be solved with a little bit of efficient SQL people mindlessly accept huge performance losses due to ORM as if they just did not see other possibility.
- hackerfromthefu 6y agoIt makes sense .. if all you have is a hammer .. The problem is exacerbated though by the amount of churn on styles of hammers, and newer developers are often too hammered to pickup other tools ..
- dimeatree 6y agoMy project has no ORM, and now that other developers are coming on it is hard to work with - the readability of an ORM far surpasses writing your own queries and I can see that the benefits far outweigh the cons. And what's not to say you can't write your own lightweight ORM to abstract the database if you can't find a tool that suits your goals - and as always it all depends on your use case.
- C1sc0cat 6y agoWhy is it not properly documented or are you not hiring developers with the right skills or providing training? Instead of fizz buzz maybe testing basic SQL abilities in an interview would be better.
- dimeatree 6y agoEven with documentation, it is a lot to consume - someone on the project for a longer time can get more done definitely, but bringing on more people will lead to wildly different quality control. I find it do be a scaling problem if anything.
- C1sc0cat 6y agoWould not splitting responsibility for the Database to more senior developers work better. Not sure what you mean by quality control you don't enforce any coding standards / style guides for writing SQL. It does sound like your employer is cheeping out and not hiring enough experienced developers
- dimeatree 6y agoManagement is an issue unfortunately, we need a larger developer team, he wants more sales. Yeesh.
- yen223 6y agoApplication code have one way of describing data and their relationships. RDBMSes have a very different way of describing data and their relationships. At some point, you are going to have to reconcile the differences between the two worlds - this is the so-called "object-relational impedence mismatch". Unfortunately, even if you choose to reject ORMs and go SQL, you're still going to have solve this problem at some point, and it will not be pleasant.
- eska 6y agoYou don't, if your application code is not OOP.
- banq 6y agoORM +DDD = business!
- tehlike 6y agoGreg young would agree!
- gmac 6y agoI agree with most of this, but once you’ve learned SQL, how do you integrate it with your code? I find value in libraries that occupy a middle ground between nothing but raw SQL and a full-blown ORM. In TypeScript (and with apologies for hawking my own project): https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
- seer 6y agoThe biggest “aha” moment for me was when I started working with graphql. The tooling would statically analyze your queries, and produce types for exactly what you were requesting. Then you would just have a bunch of raw queries laying around, but you’d be confident the data retuned is the right data at compile time. That made just writing raw queries a lot more simple and feasible than building abstractions on top of them. Now TS and sql doesn’t really have a robust lib for that I think, last time I checked it was only https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped But haven’t looked at how far it has progressed.
- chmod600 6y agoMuch is said about what to learn, but when you learn it is just as important and I'd like to see more written about that. Is an ORM good to help onramp beginners, or is it some syntactic sugar for experts who already know SQL very well? Or both? Or neither?
- izietto 6y agoThe point of ORMs is not avoiding SQL, it is avoiding concatenating strings in order to build complex queries. In our app we have very complex query logics depending on the request params, and I can hardly imagine how dirty the code would be without the relational algebra abstraction.
- throw_m239339 6y ago> The point of ORMs is not avoiding SQL, it is avoiding concatenating strings in order to build complex queries. In our app we have very complex query logics depending on the request params, and I can hardly imagine how dirty the code would be without the relational algebra abstraction. You're talking about query builders. ORM often come with a query builder but the goal is really about mapping object relations(1:n,1:1,n:n) to SQL queries in an automatic fashion, nothing more. today, most of the time it can easily be done with with JSON queries on most RDMS. But advanced ORM also come with a bunch of useful stuff like unity of work, caching and co...
- revskill 6y agoThat's why in my last Ruby On Rails projects, all my query and mutation are just sql query. No ORM, no callback. The reason is that, not all our Rails devs understand well ActiveRecord, but they know SQL enough to make things work.
- throwaway4good 6y agoJust say no to ORMs.
- fabian2k 6y agoI like ORMs for routine queries, there's a lot of stuff that is just much nicer and less tedious to write that way. It's also generally much less annoying to have some dynamic aspects in your queries with an ORM, e.g. adding different WHERE clauses depending on some parameters. But even with relatively small and simple things you can run into issues very quickly if you don't know what kind of queries the ORM will create. ORMs are very leaky abstractions, they're useful but unless you understand SQL and understand their quirks you're likely to create some weird and monstrous queries at times. You should know how your ORM handles related entities if you query them, there are some big footguns there with some strategies. And of course you should know how to use the ORM so that it doesn't do a "SELECT *" everywhere (which can be trickier than I'd like in some cases). I would also not hesitate to drop to plain SQL for some cases, if your query doesn't fit neatly into the capabilities of your ORM.
- twic 6y ago> ORMs are very leaky abstractions I think this is where a lot of the anti-ORM crowd trip up. They start with the idea that an ORM completely abstracts away a database, show that isn't true, and then condemn ORMs. But ORMs don't do that, aren't advertised as doing that, and aren't useless because they don't do that.
- ragnese 6y agoYes and no. I find that using leaky abstractions is often harmful as a general principle. So it doesn't matter that ORMs admit that they are a leaky abstraction. I still pretty much hate them for it. It just feels pointless when I know I'm going to have to understand and use the underlying tech anyway. For ORMs specifically, I much rather use a query builder that's basically just a type-checked SQL DSL. Some of those aren't complete either, though, which also enrages me.
- noisy_boy 6y agoLike everything else, use the tool but understand the inner workings too. E.g. Hibernate allows logging of the SQLs being executed so when I make changes, I review that the SQL being executed is as expected + performance is acceptable. From that point of view, understanding SQL performance is important. However, I don't really want to write boilerplate SQLs when the ORMs can basically form the query for me using query method name - that is super convenient and I don't want to give that up. Selecting * from a table with 200 columns isn't performant but if the table had 200 columns, I won't be blindly relying on the ORM generated query anyway since most provide the escape hatch of executing direct SQL.
- xupybd 6y agoYes ORMs are terrible but there is a middle ground required. Something to nicely pool connections and handle all the boilerplate involved in binding parameters to prepared statements. I normally roll my own but would love to find a good library that does it all. One problem I've yet to find a good solution to is automatic reconnect on database errors with proper transaction support.
- shoesdontfit 6y agoI can never really decide the answer to this question and I have been programming a long time. I end up just not thinking about it too much and doing what I feel like because getting things done is more important than standing around thinking about the millions of different options. There are various arguments that seem to have flaws of their own. For example, "use ORMs for all the simple things, and raw SQL for the complex things". The problem with this is that even simple things like "insert this into the database" often require checking that the foreign keys you insert are within the domain of the user trying to insert them, and various other constraints like that. So with SQL, you can do this all in one query, but with an ORM, you often create a query for each check. Related to that is the idea of "premature optimisation". The problem with this concept is that all queries add up together to determine how much hardware you need, which determines your costs. You can argue the opposite of premature optimisation, depending on your case. Why not spend 5 minutes extra writing a manual query that will be run hundreds of thousands of times for years? Are your goals about reducing infrastructure costs as much as possible? Is "developer time" really a thing, or are you doing this in your own time for a startup or something? Then there is the fact that doing simple things faster isn't much of a selling point because they are already simple. It is very obvious though that ORMs are much, much more readable than raw SQL. Then another question starts to come up, if you are using this abstraction like an ORM or GraphQL as an ORM, why are you even using a SQL database when none of the features are really available to you? SQL has has interesting new features in that you can do the object mapping inside the queries now. For example: select post.id, post.content, json_agg(comments), json_build_object('id', a.id, 'name', a.name) as "author" from posts, comments, authors group by etc... Still, it is nowhere near as easy as using an ORM. The other thing is that when you start using an ORM, you really do end up having a different approach to querying your data in every situation. You don't use all of the various features of SQL. It ends up being a lot slower, but maybe in some cases that is worth it.
- nablaone 6y agoYou may create view with complex query. Views are ORM friendly to use, and are human friendly to edit.
- csnweb 6y agoWhat I found to work really well (esp. with GraphQL / Dataloaders) is using something like postloader by gajus [1]. It generates a slim interface from the database schema. For the simple run off the mill things you get an easy interface to get certain rows of a table or load related data, which is a huge part of what you will need when writing GraphQL resolvers. We extended the idea to generate simple wrappers for creating and updating tables as well, if anyone is interested in that I may dump the code for that in some gist. [1] https://github.com/gajus/postloader https://github.com/gajus/postloader
- nodamage 6y agoIMO people discussing this topic really need to clarify what type of applications they are working on because the cost/benefit analysis changes quite significantly based on use case. If you're building a web-based reporting tool where you simply query records out of a database to dump to HTML and your objects are short lived, you might not get as much value out of an ORM compared to a client-side app where objects stick around for the entire lifetime of the application and you have to worry about things like object identity and staleness.
- 3np 6y agoI'd also add that if it's a community-based open source project, there can be great benefit in more safely supporting a bigger set of database engines (e.g. sqlite for the simple entry-level, postgres et al for the more serious) at the cost of some performance overhead. If it's a performance-critical in-house service where you have full control of the deployment and can tailor it to your use-case, it's a completely different story.
- midasz 6y agoWe have to support both MySQL and MSSQL so Hibernate has been mostly great for that. For really simple queries it's also nice to be able to write "findAllBySomethings_IdAndPropertyIsNull" etc instead of writing a SQL query. Been there, done that. Though I have to admit I do check what queries Hibernate produces to make sure it's not doing some funky stuff that's not really needed (as a result of a mistake of my own, mostly) Edit: It has come to my attention that it is ofcourse Spring Data that offers those named queries. Still see nothing wrong with hibernate since I still only have to write it once and I'll be relatively safe supporting both db systems.
- roenxi 6y agoI'll put it in the mix of ideas that the problem isn't ORMs, it is trying to build ORMs on top of SQL. Languages could be placed on a spectrum of how easy it is to build a DSL over the top of the language. Lisp is on one extreme, where it is easy to write a new language over the top of it. SQL is pretty close to the other end of the spectrum. I've never seen a DSL that can compile to the full breadth of SQL dialects out there. DSLs generally handle the trivial well then fall apart when they hit complicated SQL statements. If an ORM just had to fit over the top of a relational model it would probably work fine. The problem is, in the middle of all this, something has to be constructed in performant & parseable SQL.
- qwerty456127 6y agoI couldn't agree more. We all should just learn and use SQL. In case it really really really (because avoid multiplying standards) doesn't fit a significant portion of the real life tasks well we should just design a new SQL.
- joshsyn 6y agoMicro-ORMS for boilerplate sql - RepoDb. Rest just use Query Builder or raw sql.
- jtolmar 6y agoI'd really like some sort of macro that takes my random SQL query (with arguments), looks at the names and types of the returned columns, constructs some sort of struct/pojo/whatever to match, and gives me a function(my, args) -> array<that>.
- eNTi 6y agoI'm currently in the process of porting api code from .net 3.2 soap where the code is a mixture of string based SQL queries and stored procedures to a .net core 3.1 webapi mvc + ef. Let me tell you... readability and type saftey are a boons. Not a curse. Things still get convoluted but boy the errors that can sneak into a complex sql statement are painful to debug. As always the pendulum in this "article/rant" is swinging in the other direction ("everything was better in the past"). Also it's fricking (almost) 7 years old. That's a lifetime in software development.
- ashtonian 6y agoThe two are not mutually exclusive. Dapper provides types safety cleanly and imo is more readable than ef. Ef is heavy, requires trading to understand and is a giant pain if you need to implement performant sql.
- majewsky 6y ago> [7 years] is a lifetime in software development. If we were talking about something like Kubernetes best practices, I'd definitely agree with this point. In this case, though, I don't see what substantially changed about ORMs or SQL in the last 7 years.
- jillesvangurp 6y agoORMs have their place but they are IMHO overused and frequently fail for reasons that have to do with the good old object impedance mismatch; which is a pitfall that lots of junior developers fall into where they over engineer their database schema to make it resemble some platonic ideal of some class hieararchy. When that hierarchy inevitably starts changing, the schema erodes along with it. Some symptoms that I've seen in multiple projects: - requests are slow because every request triggers dozens to hundreds of joins. - overuse of the @Transactional annotation (spring/hibernate) because of developers slapping it on anything that looks like it might be doing anything with a database while neither understanding transactional semantics or aspect oriented programming (which causes some funny behavior) - Attempts to implement class inheritance via database tables and corresponding hacks and complexity to query because of that. - Lack of a coherent database design. IMHO, a good use of ORM should start with a good old database design. A 1 to 1 mapping of your domain to tables is typically not it. - Over and under use of database constraints, indices, etc. because of a lack of knowledge of how databases actually work resulting in unenforced referential integrity constraints, poor performance, and weak transactional semantics. I've used lots of different styles of databases over the years. I generally break things down into: - simple key value stores like redis, memcached, etc. - document databases like couchdb, elasticsearch. These tend to have schemas and fields - SQL databases (mysql, postgres, mssql, oracle, ....) - Nosql object databases (mongodb, firestore, etc. I tend to mix these styles and would happily use postgres as a document store. IMHO if I'm not going to query on it, putting it in separate column adds little or no value. If I am going to query on it; it should have an index. Years of using document stores have taught me the value of de-normalizing stuff like user names and other things that rarely change but are a part of pretty much everything. I've ripped out hibernate in favor of JDBCTemplate + TransactionTemplate on several projects where hibernate was causing more problems than it solved. Hand crafted joins are pretty easy to do.
- wodenokoto 6y agoWhen working directly with SQL instead of an ORM, how do you elegantly handle things like parameterizing table names or having flags that turns on or off filters? In non-ORM codebasese I am seeing patterns where sql queries are "copy-pasted" together and I am not sure I like it. Just imagine the following example had hundreds of lines of SQL and several optional filters, some of which could themselves be several lines long: def get_data(table1, table1, use_subset=True): if use_subset: filter_query = "AND column2="subset_value" else: filter_query = "" query = f""" SELECT * FROM {table1} JOIN {table2} WHERE column1="value" {filter_query} """
- ashtonian 6y agoI have come to the same conclusion as the author. In go I use rql: https://github.com/a8m/rql https://github.com/a8m/rql Which I ported to c# for use in .NET: https://github.com/Ashtonian/RQL.NET https://github.com/Ashtonian/RQL.NET
- strken 6y agoYou could use a query builder for this, rather than a full ORM. Programmatically generating SQL is a subset of what an ORM does, and it's one of the less contentious parts.
- ipiz0618 6y agoThis is basically how I use ORMs. Only as a query builder. Writing plain SQLs in code can be very messy and hard to debug, and this works similar to an API the author mentioned in his article. IMO ORMs can spare some duplication, but relying too much on that is quite painful.
- zwack 6y agoIn quma this would look like this (when using the default mako templating). example.sql SELECT * FROM %(table1)s JOIN %(table2)s WHERE column1 = "value" % if use_subset: AND column2=%(filter_value)s % endif def get_data(table1, table1, use_subset=True): if use_subset: filter_value = "subset_value" else: filter_query = "" data = cur.example(table1=table1, table2=table2, use_subset=use_subset, filter_value=filter_value).all() https://github.com/ebenefuenf/quma/ https://github.com/ebenefuenf/quma/ https://quma.readthedocs.io/en/latest/templates.html https://quma.readthedocs.io/en/latest/templates.html
- preommr 6y agoYou start using an orm when you're basically rewriting a crappier version of one. You stop using an orm when you're wasting time going through it's documentation trying to figure out how to do something tricky for some obscure ad-hoc execution.
- wonnage 6y agoWhether or not you use an ORM, you ideally have your own abstraction on top of it, defining a set of known queries. This avoids the classic Rails problem where the User model has a million methods, and you don't know which of them might make a SQL query. Also, ORM caching always finds a way to become a huge pain. It's easy to wind up with multiple objects representing the same underlying DB row, but updating one in memory won't update the others, so you update them all from DB just to be safe... You might say "find a better ORM" but I'm pretty sure they all run into this problem at some point. IMO you're better off doing it yourself and making it explicit.
- gls2ro 6y agoMy main reason to use ORM and why I recommend to others to use it (specially if they are juniors) is because it usually (depending on the language/framework) comes with great support for protecting against SQL Injection. Of course not all ORMs will protect against SQL Injection completely and some of them will only implement partial protection. But it is a good start. What I recommend strongly is not to mix ORM with plain SQL in the same line of code. The code should either be full ORM or plain SQL so that it is clear where the responsibility of protecting against SQL injection should be. Edit: Also it is easier to add extra layers of protection after a while if using an ORM then when using plain SQL. Because with an ORM I can redefine lets say the Repo.all or Query.all method to add extra layers of protection. But with plain SQL I need to edit every single line where the SQL statement is present.
- imhoguy 6y agoInstead of polarizing between massive native queries in ORM strings vs DB-specific imperative stored procedures I would suggest a middle ground which are table VIEWs [0]. They are trivial to design (just SELECTs), stay declarative like tables, can be materialized for efficiency [1]. Finally they can be easily handled in ORMs as both static and dynamic entities, with all read-only benefits, with no need of any non-standard mapping or native queries. [0] https://www.postgresql.org/docs/current/sql-createview.html https://www.postgresql.org/docs/current/sql-createview.html [1] https://www.postgresql.org/docs/current/rules-materializedviews.html https://www.postgresql.org/docs/current/rules-materializedvi...
- chunkyfunky 6y agoThis is an excellent point. I don't get the polarised nature of the debate to be honest - there is room for all approaches in a "best tool for the job" way. I use EF Core a lot in my work, and it's the first tool I reach for, and if I need a view, then I use a view (EF Core has fantastic first-class support for these nowadays). For everything else there is Dapper. I think a large part of the problem here is that developers who learn to leverage a database only through an ORM are missing out, and really they should also learn SQL (literally the only part of the article that is still objectively correct is the author's advice to learn SQL) to gain a better understanding of when (and when not) to use an ORM. Every other complaint in the article is either classic misuse of the ORM, or else a shortcoming of the ORM in question.
- klausjensen 6y agoMy preference is currently ORM + drop back to SQL when you need something that is complicated or inefficient using the ORM. I have been writing SQL for 20 years. I currently use dotNet Core and Entity Framework + Dapper. It has these benefits: - Strong types! - My schema+migrations are part of the source code and I never have to worry about if my schema is in sync - with migrations it just is - Simple, straightforward tasks as super easy (insert, update, delete, simple select) - I can fall back to Dapper (straight SQL) when I need to do something that doesnt fit well with Entity Framework. I recently worked on a project where EF was not allowed - and we spent so much time doing all the simple stuff, especially when the data model was still not completely locked down.
- mrlala 6y agoI'm a huge Dapper fan. To be fair, I have never really done a full blown project with entity framework.. but I seem to always need a bit more control than it's offering. So I just created a simple repository framework (inspired by https://www.youtube.com/watch?v=rtXpYpZdOzM https://www.youtube.com/watch?v=rtXpYpZdOzM ) overlay in which the functions end up calling dapper. The beauty of this is it's pretty simple to setup the overlay, and allows complete control of what is going on. I have a need for "dynamic" table names. I don't think there is even a way to do this in EF (at least not a simple way), but when I have my own repo overlay it's really a piece of cake. I just tack on whatever specific function input I need to help define what the resulting table name is that its great. This is probably not the most common use, but a simple repo overlay to a micro orm is not much work and gives full control.
- zby 6y agoThe title suggests that you would be better with no ORM, just SQL. I have not been coding for years - but still remember why ORMs came into being - before them there was lots and lots of repetitive code with the query preparation and execution with long ugly constant strings and string manipulation. With ORMs (and query builders) it all became much more compact (and looking better without the uppercase SQL). As every programming abstraction it is not perfect and you still probably need to learn SQL if you use ORM - so you need to learn both.
- BMSmnqXAE4yfe1 6y agoWhy article writers always assume everyone knows all their abbreviations?
- petepete 6y agoI've seen this post and similar ones many times. ORMs don't fit all scenarios, but most of the time it's a question of when you fall back to something more flexible/powerful. In Rails, writing the crud part of your app with SQL is pointless. Trying to get AR to do dashboard gymnastics is equally pointless. Pick the best tool for the job.
- deleted 6y ago[deleted]
- syspec 6y agoRails with Arel is really special, the queries it creates are really impressive coming out of an ORM. I would be curious to hear an updated version of this critique which focuses on Arel. Now of course learning SQL is still a good thing to do, but for some a good ORM can be a way of doing that
- keithnz 6y agoI'm an advocate of lightweight ORMs, in C# world, I've used Dapper a lot, and now using RepoDB. Lightweight means I can easily craft SQL and quickly map it to types, and things like CRUD on simple entities is very straightforward. But I generally agree with learning SQL, and avoid mapping it to types if you are just going to pipe into json for the front end. SQL is good for query and projecting data into new forms, complete waste going through a type if not needed
- gigatexal 6y agoI understand the usefulness of an ORM and how much its use can clean up a codebase but... truly performant queries (that take advantage of every aspect of your DB not just some vanilla lowest common denominator provided by an ORM abstraction) are done in SQL. In the end one has to make the tradeoffs between development speed (something an ORM has going for it) and performance and the eventual issue of having to handle the object impedence mismatch. For me it's just so much more clear to read a handful of queries and see how things work. You can even create user defined functions and call them like an API from your codebase (a sort of ORM) and get the best of both worlds. It's not a easy choice to ORM or not but SQL should be a skill everyone learns.
- paulmd 6y agowell, most ORMs give you the ability to drill down into native queries if you want, and often even will help you with the conversion into native objects if what you are pulling back is still a native object. So it's not a binary thing, you can use ORMs to reduce the amount of boilerplate for simple queries and then where it makes sense you can drill down into native SQL. (a lot of what ORMs give you is just a reduction in boilerplate code, manually populating 30 different fields on an object and so on.)
- gigatexal 6y agoin my anecdotal experience this almost never happens because devs have an aversion to putting SQL in the code.
- paulmd 6y agoMost simple queries won't benefit much from hand-coded SQL and it's always there for the minority of the ones that do. And again, there is the middle ground of writing something like custom HQL that returns an object(s) of interest. Again, there is a huge amount of error-prone boilerplate that is avoided simply by doing that, the next time you add a column you don't have to chase down 27 different hand-coded functions manually populating one field at a time.
- Traubenfuchs 6y agoJust use a mature ORM where you can easily combine ORM capabilities and raw SQL, like Hibernate / Spring Data. There you can do raw-raw calls to the SQL driver, map your optimized by hand query to an object or let Hibernate fetch a whole graph of stuff by itself. Use the right tool for the job.
- nickik 6y agoHaving worked with Hibernate, droping it and using JOOQ has made my life 10x better.
- kumarvvr 6y agoThe development speed with ORM's is ridiculous fast and easy on the mind. I do agree that for large, complex performant queries, ORMs might not work out, but I would say that those queries are better written as SQL procedures / functions because they are bound to be modified or changed anyway. Having said that, most LOB apps, small apps, apps that have in-frequent access of the DB, etc, can benefit from using ORMs.
- nickik 6y agoGoing from Hibernate to JOOQ for the DB managment has been the best change ever. JOOQ is a wunderful SQL abstraction that is very nice to work with. What I would really like is a Datomic style Database and have a JOOQ like Datalog, but I can't have everything at the moment.
- zimpenfish 6y agoWhilst I have long railed against ORMs, I do appreciate `pg-orm` (and for my own stuff, `crud`) in Go because it means I don't have to write Nx100 (177 distinct structs at $WORK) versions of "scan the results into this struct type-safely". But that's less of an ORM and more of a data mapper?
- aww_dang 6y ago1)Clearly state the goals of the project. 2)Use the minimum amount of technology necessary to solve the problem.
- mmatczuk 6y agoNot all ORMs are like hibernate and gorm. I'm author of a lightweight ORM for Go and Scylla / Cassandra [1] so I may be biased. In my view use of a good ORM makes your code more resilient to change, refactorings are simple and stupid mistakes largely eliminated. A benchmark of usefulness of an ORM can be how many lines need to be changed to add a new field to a struct / class. In a good ORM this should be about 1, it should still work be ~1 if you use a hand crafted SQL / CQL. The worst thing that ORMs try to do is handling relations (eager / lazy loading) and handling sessions / object lifetime. This is mainly because it's impossible to have one-fit-all solution for all usecases even in one application. [1] https://github.com/scylladb/gocqlx https://github.com/scylladb/gocqlx
- scaryclam 6y agoI use a lot of raw sql and I also use ORMs. I don't see ORMs as being the issue the author thinks it is, bit rather that developers suck at designing the data layer. A half decent design will do as you say; it will allow the use of an ORM or hand written sql. ORMs make magic easier, and pulling that magic apart is a real pain, but so is working with triggers and stored procs. The problem exists on both sides. The solution isn't to get rid of ORMs, but to train developers in better system design.
- PeterCorless 6y agoAll reminds me of this talk at Aerospike back in 2015: https://twitter.com/i/events/1032400612653129729 https://twitter.com/i/events/1032400612653129729
- PeterCorless 6y agoMy favorite quote of the talk, "There's a lot of stuff that's deployed everywhere that sucks everywhere."
- kumarvvr 6y agoThe "Vietnam" article referenced in the post makes some very valid points about ORMs and their marriage to Relational data. When using Object Oriented Languages, like C# / Java / C++ etc., the programmer is forced to represent data into objects. True, representing table and relations between tables as objects is difficult, but do OO programmers have any choice at all? Afterall, the issue is not a single table (a row can very well be represented in OO constructs), it's the relation between tables that causes headaches. Even when using languages like Python, I have to inevitable gather related pieces of data together, in a dict say, and it still feels like mapping related tables to objects. Another contention with the "vietnam" article is that the author assumes that OO programmers will want to use inheritance to model relations between tables. This seems outdated in my view. Composition is more suitable and powerful and is almost always used in ORMs, for ex Entity Framework. The original post also claims that ORMs tend to gravitate towards "select *" queries. This is true, but has many solutions. Many ORMs optimize queries based on required columns. But there is also another issue with this. Say you have a table with 100 columns, but are only querying 5 at a time, isn't it prudent to separate out those 5 columns into another table? Or perhaps create a view? If the use case demands just 5 columns, but the table has 100, isn't there a problem with the data modeling rather than the ORM?
- stuaxo 6y agoORMs are good if you already know SQL. Not all ORMs are created equally. I enjoy the Django ORM and it's composability, OTOH I was really not keen on earlier ORMs.
- deleted 6y ago[deleted]
- dep_b 6y agoThis. Everything else that does more than mapping SQL results 1-1 to a query result and maybeeeee helps you with atomic actions like INSERT, UPDATE and DELETE ends up being a chore. They all have their own quirks like around threading and usually end up forcing you to design your application around it unless you take some measures to isolate it from your main application, which means you're now writing code while you wanted to type less code. Add to insult a ton of the them seem really resistant to map the results of hand-written queries to an object. Also SQL dependency trees and object dependency trees never really seem to map that well. And don't try to do more complex queries with the built in query language in Django or .Net. I have sometimes spent hours trying to get an SQL query working in an optimal way that would not cost me more than 5 minutes two write and map with simpler ORM's. The other day I talked to a dev that was otherwise a very strong iOS developer in a discussion why I always used SQLite directly with a simple wrapper that said "I really should learn SQL sometimes". There probably hasn't been a skill more universally useful in my career as a developer than SQL apart from HTTP? But it also made me think about why I never really learned to write C (I can read and futz with it). For anybody new in the trade reading this, the following things have been or could have been useful at any step in my career: * HTTP * HTML * SQL * JavaScript (blegh but true) * C * Unix The rest were just passing by for a while, like Windows, Flash, PHP or any god damn JS framework out there.
- mozey 6y agoThe sqlc lib "generates fully type-safe idiomatic Go code from SQL". It makes sense to generate code from SQL and not the other way around. https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc
- unklefolk 6y agoI personally have found that how to approach data access has been a real bone of contention on projects and quite damaging. It seems half the team want to use an ORM and have a long list of reasons not to use SQL/Stored Procs (slower to develop, out dated, not testable, business logic in the wrong place). The other half of the team wants to avoid ORMs and would rather use SQL/Stored Procs (performance, ORMs start off okay but soon aren't up to the job, more control and power with direct SQL). In fact, you can see these two standpoints in this very discussion. I find both have valid points and there isn't really a compromise. Whichever approach is taken you end up with half the team feeling not listened to and disenfranchised. I have found few things to be more divisive than the ORM vs No ORM debate and I am not sure what the answer is.
- davidgl 6y agoAgreed. As ever, it depends a lot on the type of work and how big the system is. We use a lot of SQL (not sprocs), but are moving back to LINQ for reasons of composition and maintainability. I love writing SQL, but in our system which is north of 300 tables, on a project over 10 years old now, the lack of compatibility and general lack of static dependencies in SQL is hurting us more and more
- sjwright 6y agoThe problem comes when anyone thinks the ORM vs No ORM debate has a single answer. For me it's a simple test: • Your data makes sense as objects; • Your data is small enough to fit in memory; • Most of your operations are CRUD. If you can answer all three with yes, then use an ORM or some other kind of SQL abstraction layer. Otherwise don't.
- TobiasA 6y agoPer your last point, why don't you think ORMs work well with complex domains?
- murgindrag 6y agoORMs completely fail for many complex domains. If your data represents an arbitrary tree hierarchy, a graph, or otherwise, relational databases have nice data architectures, but those completely misalign with how ORMs handle data. You don't even need to go that complex. Even moderately complex JOINs start to look better in SQL than in ORMs. If you have an employees database, an inventory database, and a clients database, an ORM is perfect. If you have a fixed hierarchy, ORM works great too. It maps objects to the database. That's 90% of web apps. If you're building e.g. an online CAD system with a complex data model for storing hierarchical layers of objects with complex relations, and where you need to perform complex operations on that data for e.g. simulations and optimizations, SQL will actually handle that just fine. You'll just be doing SQL beyond what fits into an ORM. At that point, if you use an ORM, you'll be doing complex contortions. Think back to your data structures class. Then to the grad-level data structures class. Most of those structures, SQL will handle fine, but ORMs won't. SQL has also turned out to be surprisingly resilient to different programming paradigms. SQL came out just around a half-century ago, before OO was common, and did fine with OO, functional, structured, and a whole range of other paradigms which have moved into and out of vogue over that time. ORMs, as the name implies, are specific to OO. A few posts up, the poster is correct that there are design patterns around ORMs which work well, and design patterns around SQL which work well. You want to pick one and stick to it. The mess comes in when you mix layers of abstractions. One or two SQL procedures might not kill you, but when you have big chunks of code using ORM and big chunks NOT using ORM, you'll crash-and-burn. Footnote: "Complex domains" is also about the database layer, and the type of complexity. Right now, I'm working on a very algorithmically complex system, but that complexity isn't in the data layer. Most of the data engineering is about moving GB of data around in realtime. All the database needs to handle are simple things like auth/auth. That's an ideal use-case for an ORM. I've also built systems with ORMs where the database layer was complex, but complex in ways which aligned well with ORMs. So this shouldn't be read as "dumb programmers who can't handle functional use ORMS." Footnote to posterity: Should someone stumble upon this in a web search: If you grew up in OO and Java, and never ventured beyond, none of the above will make sense to you. You only learned the programming paradigm ORMs were designed for.
- UK-Al05 6y agoI don't mind very simple stored procs for queries, saving data, and the application just has lightweight abstraction around them. Putting core business logic in stored procs though is a big no no.
- ozim 6y agoThat is such no discussion.. and articles from 2014 are not having any valid arguments anymore. I put it along the discussions about which OS is the best. Which does not matter at all. Please stop wasting everyone time with those arguments. Do what is best for your project and shut the f up.
- commandlinefan 6y agoThe reason this is a good discussion to have is because, at a superficial level, it looks like ORM's should be faster than writing SQL, but when you get down into the details you find that the complexities are a lot deeper than a superficial view suggests. "Do what is best for your project" is good advice, and the author states a good case for ORM's not being "what's best for your project".
- dkarp 6y agoORMs should not be sold as "Now you don't need to learn SQL". Like any abstraction, it pays to know about the layer below even if you're being saved from directly using it. In the case of SQL and ORMs, this is particularly true.
- kuon 6y agoI have been using ecto for some time now, and its approach is very good. It is just some syntax sugar to help to write safe SQL from elixir. There is no magic like cache or anything from complex ORM and it has been the best experience in many years.
- ThePhysicist 6y agoSQLAlchemy is pretty sweet in the sense that you don't have to use the ORM, it has a non-ORM part that lets you write almost arbitrary SQL statements programmatically. This often results in cleaner code than when writing those queries by hand (which you can also do in SQLAlchemy), so I prefer it to raw SQL. For things like database migrations I prefer raw SQL files though, as it's a bit painful to get edge cases right with tools like Alembic (haven't used if for two years though so maybe things got better).
- INTPenis 6y agoI'm glad I learned to program in the late 90s because ORMs were a new thing I had to learn in the mid 2000s.
- ganonm 6y agoI'm surprised more people aren't recommending e.g. JDBI https://jdbi.org/ https://jdbi.org/ I've used it on several projects and it seems to be the perfect balance between 'close to the DB' and 'close to the domain/application'.
- mattmanser 6y agoJust to be explicitly clear to newer programmers. This is an extremely niche view, ORMs save you a huge amount of time, and the days before ORMs were a real pain in the arse. As a noob you are much more likely to introduce a massive security hole by rolling your own solution. Don't do it! So this article is sorta true, and you should learn SQL, but for trivial queries ORMs are actually a massive time saver and really useful in maintaining a system, especially in strongly typed languages (Java/C#/etc.).
- commandlinefan 6y agoThe article outlines four of the (many) places where ORMs actually cost you time rather than save it.
- mattmanser 6y agoNone of which are completely true. There's a good reason ORMs are so ubiquitous. Look at his description of the things he was making. You do not use ORMs for anything but the most basic reports.
- brunojppb 6y ago> I work on the principle that the database’s data definitions aren’t things you should manipulate in the application. Instead, manipulate the results of queries. Following this principle will definitely make your life easier. I have been using Slick[1] and the Play Framework[2] for my backends for a good while and having the freedom to define my migration scripts in pure SQL is a big plus. My application models are very lean and don't get mixed up with DDL. I can also fully understand what goes in the db migrations, zero magic there. - [1] http://scala-slick.org/ http://scala-slick.org/ - [2] https://www.playframework.com/ https://www.playframework.com/
- Tomis02 6y agoMy experience is that most developers are really bad with SQL. Because of that, they would rather use an ORM because they think they can avoid internalising the relational paradigm. But, guess what - if you don't speak relational then your table design will suck, which in turn will impact DB performance. Given a long enough time, you'll either switch jobs before this becomes a problem or you'll be forced to learn SQL properly. Most developers just switch jobs.
- projektfu 6y agoI’ve found a lot of programmer friends are apprehensive about SQL. I’m not sure why, but they see it as a wizard language instead of a friendly DSL like I do.
- marcan_42 6y agoEveryone here is arguing for their favorite side. As often is the case, the best solution is often somewhere in the middle, using the best tools for the job. I authored a complete rewrite of an ancient and rotting PHP+MySQL web ticket reservation app in Python+SQLAlchemy+PostgreSQL. I use an ORM - except where it doesn't make sense because a SQL query expresses what I need to do more concisely and effectively. I don't use stored procedures - except where I do because I need one specific atomic DB operation to be performant and not bottlenecked on the app. I use relational storage - except almost every table in the database has a big JSON column for everything I don't need to ever join, filter on, or index in production codepaths (though I can still do that with PG's native json support, which is great for the rare case I have to move something to a real column). And I use triggers, stored procedures, and notifies to implement live change notifications for a table, that eventually get fed via WebSockets server to users. This hybrid approach has served me extremely well, resulting in very readable and maintainable code, minimal DB schema migration pain (most upgrades only touch JSON fields and thus require no migration), and much better performance than the old app, especially in that hot path using the SP, while keeping table column bloat down, and avoiding the join spam that results from keeping everything religiously normalized even in cases where that doesn't buy you anything. Of course, that does mean you need to know all the relevant technologies involved, SQL, ORMs, etc. YMMV, but consider that if you think a single solution is the right solution in all cases, you're most likely wrong.
- jbob2000 6y agoThe thing I struggle with is knowing whether or not the solution I designed was biased towards SQL (because that's what I learned years ago) and therefore would be better suited for SQL. OR maybe if I learned more about ORMs I could craft a solution that isn't SQL biased. Or maybe SQL "just works" for most of what we need to do with applications right now, and that's where the SQL bias comes in?
- msluyter 6y agoYMMV, but consider that if you think a single solution is the right solution in all cases, you're most likely wrong. This is sort of the meta-principle that I apply to all of software development: no principle or methodology (DRY, YAGNI, SOLID, TDD, etc...) applies 100% of the time (except for this principle. ;))
- bullen 6y agoORMs thought me to build my own ORM, I even ported the thing to MySQL, Oracle and Postgres! It's available to try if you use my own "wordpress": https://github.com/tinspin/sprout https://github.com/tinspin/sprout But the source is not all there: http://rupy.se/util.zip http://rupy.se/util.zip (bunch of helpers) http://rupy.se/memory.zip http://rupy.se/memory.zip (I called the ORM memory!?) and http://rupy.se/test.zip http://rupy.se/test.zip (my first test of any software, more of a tutorial) is where you find all the details. Also the thing has a graphical editor: http://move.rupy.se/file/logic.html http://move.rupy.se/file/logic.html All of this is very complex for something I later solved with JSON files over HTTP instead: http://root.rupy.se http://root.rupy.se but parts of it can be reused for other useful things; like logic was used to build game dialogue trees.
- bovermyer 6y agoI'll use an ORM until I start running into performance bottlenecks. Then I'll write raw SQL for those specific instances, and continue to use the ORM elsewhere. This approach has worked so far.
- thrownaway954 6y agoyeah, no... ORMs are A LOT more than just mashing strings together to perform a query. they are there to take care of all of the crap that you don't want or need to worry about. for instance, parametrizing string against sql injections, handling transactions properly, concatenating joins, proper pagination and sql syntax discrepancies to name a few. and let's not to forget to mention things that go BEYOND the database interactions that are built on top of the ORM like data validations, custom properties, callbacks and so on. there are way more benefits to using an ORM than not using one. a good ORM (like ActiveRecord) will let you break out of it and write raw sql when you need the perform boost while preventing yourself from shooting yourself in the foot. * i've written and contributed to a couple of ORMs in my lifetime
- nojokes 6y agoCompletely agree. Knowing and understanding SQL and query optimization is necessary because ORM is not magic.
- tekkk 6y agoI would like developers to learn SQL first before picking ORM to actually understand what the ORM is doing for them. Sure they'll be doing their share of sub-optimal subqueries and head bumping trying to write group by queries. But in the end I think this would make them better developers who knew the underlying software and application logic, not just the API of the ORM. Yet we don't really live in that kind of ideal world and there are many factors that people have to consider. If everybody else in the company is using ORM, why shouldn't you. Or, if you just need to ship products and don't necessarily enjoy learning SQL. Or, if you don't know how to properly abstract the structured SQL queries without making it a huge mess. Saying that ORM is better/worse is like saying everybody likes music. If I'm building a Nodejs app I would choose not to use ORM. For other languages and frameworks I'd probably reconsider and evaluate my options. To me however, the biggest benefit of using no ORM is the learning of SQL. Time and time again that proves itself so invaluable, and I'm immensely happy I've learnt the quirks of SQL instead of some language specific API. If I can code faster with ORM that's great, but in ideal world I would much rather learn SQL and become a master of it rather than of some ORM.
- mumblemumble 6y agoSo, I'm typically not a lover of ORMs as they're typically implemented, and tend to also favor doing something close to the sproc-based method that Philip-J-Fry proposes in another comment. It's always struck me as odd that, as a profession, we generally agree with, and can be quite fanatical about, the idea that different modules and services should try to hide their implementation details as much as possible, and instead speak over well-defined, constrained protocols such as APIs or interfaces; but as soon as an SQL database comes into the mix, we happily throw all that hard-earned discipline out the window and go back to directly swizzling the internal state of external collaborators. That said, there are some use cases where I'm not sure how you get around something like an ORM. One is when you need to allow users to execute arbitrary searches against the data. If that's your situation, then, any way you cut it, you're going to end up with some system that takes an abstract representation of a query and compiles it to SQL. The only question is if you want to use something off-the-shelf to do it, or if you'd rather hack it together yourself. In my experience, there are few things on this earth that present a greater maintenance burden than a homegrown ORM-type library does once the original author has moved on to other things. And there are others where avoiding an ORM is over-engineering. If your database is just an honest entity store, and you're just doing fairly straightforward CRUD operations against it, and it belongs to a single application, go ahead and punch the easy button and have a happy life.
- gwbas1c 6y ago> different modules and services should try to hide their implementation details as much as possible > but as soon as an SQL database comes into the mix, we happily throw all that hard-earned discipline out the window The data belongs to the database, not to whatever program you happen to work on at the moment. IMO, the impedance mismatch situation is a result of poor programming language design. Saying that the database abstraction "leaks" into your code is backwards; the shortcomings of your programming language "leak" into how you're handling your data.
- mumblemumble 6y agoI tend to blame the impedance mismatch more on the popularity of "entities" as a way of doing domain modeling in object-oriented programming. It's far from the only way to do domain modeling in an object-oriented language. It's not necessarily even a particularly good way, and it creates problems even if your app comes nowhere near a database.
- jbjohns 6y agoWhat I don't see a lot of mention here is database selection. If you really need an Object->? mapping, why not just use a document store or the like? I don't know how many remember but in the earlier days of Java everyone was talking about OO-databases. I think they didn't work out because they tended to be language specific but now you could serialise your object tree to JSON and store it in a document store if you wanted to.
- drbawb 6y agoThe last bit of the article reminds me of an experience I had recently working w/ another group of developers. They write in C# w/ Entity Framework, but they just use it to map stored procedures to C# data-types. All queries to their database go through a stored procedure w/o exception. They push as much of the business logic as possible to the database. Their reasoning being that if the client insists on a separation between the DB & application servers: you should do as much computation as close to the data as possible. Then just send the end result over the wire. Due to my own ORM-induced brain damage I found it hard to wrap my head around this at first: a data type in the application no longer represented a table, but the result of a query. Once you realize the database is just another API though it clicks really nicely into your architecture. I think I still prefer things like Linq, jOOq, Arel, Ecto, etc. where you can write the query in your programming language and have it translated to SQL. It's just nice to see your query right next to the code when debugging. The author is absolutely right though you still have to know SQL to use tools like that effectively, so you might as well just learn it early instead of wasting effort learning the quirks of a specific ORM.
- deepstack 6y agoMost programmer has problem with SQL because it is declarative langue, and doesn't fit into the C, Java context very well. However, if you just spend some time getting use to declarative langue, you will find SQL is quite nice. You just ask for stuff you are looking for and the computer will give to you. And SQL servers like Postgres has gotten so optimised in returning search results, it really out performs mapReduce.
- drbawb 6y agoYup that has been my experience as well - if you read someone's stored procedure it's pretty easy to tell if they're a "relational thinker" or an "imperative thinker." People who embrace an RDBMS will tend to use constructs like CTEs, windows/partitions, set operations, they'll do their conditionals in the projection (i.e w/ CASE statements), they might use a CROSS APPLY to do a transform over a "collection", etc. Whereas people who are "imperative thinkers," who just treat the database like a giant excel spreadsheet, will use flow control for branches, cursors for iteration, tons of temporary variables, etc. Without fail the iterative thinkers wonder why they need monster CPUs for their DB server and it's always pegging one core. Well of course the query planner can't optimize your code: you told it how to get the data, rather than asking for what data you wanted.
- XVincentX 6y agoI'm not an expert in databases, but I am starting to think that building an interface primarily designed for humans (that's what SQL is) as the main medium to interact with the database maybe in retrospective was not a winning idea. I'm not criticising anybody here (I am literally not in a position of critiquing anything); SQL was created probably 40 years ago and it probably made sense back in the times. My point is that you can always build a human interface from a machine one; the reverse is not that easy. ORMs are a (failed?) attempt to do so. I would very much push for computer-language integrated query support — (such as Datalog or Linq), and that is what the database server should be accepting as input, instead of a raw string. Although a lot of people hate MongoDB, I've personally felt way more productive expressing queries with their query document system rather than using SQL. Fixing design flaws with other software ain't gonna bring us that far maybe.
- slotrans 6y agoORMs ultimately serve no purpose. If your data model is small, they save you very little work. If your data model is large, they collapse and you have to abandon them. There is no middle ground.
- mmcnl 6y agoThis article hits the nail on the head. ORMs rarely solve a problem but they almost always add complexity. I always advise against ORM.