One thing I really miss from the JVM world was something like jOOQ.
I don't want an ORM (like specifically the object relational mapping stuff) for most of my use cases but I do want an abstraction above text for interacting with SQL.
gorm's sql builder is alright but something better would be really nice.
Agreed. Really, just an open/transparent SQL builder part and an uncoupled row scanner that can populate structs reflectively is the perfect middleground to work around the impedance mismatch that inevitably occurs using these magic ORMs.
I don't even want an abstraction for building SQL unless I need to build queries dynamically from some other (presumably simpler) query language.
Put all the SQL in .sql files and make all queries parametrized. Or wrap all queries in VIEWs or functions at the RDBMS. This makes maintenance much easier.
> Or wrap all queries in VIEWs or functions at the RDBMS.
This is like the oldest best practice for RDBMS use, for security, and for decoupling consuming apps from each other and the DBs low level implementation (so that app views and base tables can evolve independently to the extent possible), and for maintainability: all app access to the DB should be through views adapted to the apps needs.
I was referring to the string composition [0] of psycopg2, a library which also takes care of converting SQL results to the correct Python data type. IMO that meets the criteria for an "abstraction above text" that GP was asking for.
But yes, SQLAlchemy Core is far more full-featured and gives you more than just a few primitives to work with. It also has a lot of library-specific constructs and is much closer to a full-fledged "ORM" than a query builder; what you're writing is not exactly SQL and is yet another dialect to learn.
Sample size one 1 but ORM drives me crazy. Why can't we just use SQL? How does it save time when I have to learn the ORM language, which probably has a lot less support and users?
The OP here says it "reduces boilerplate" -- rarely have I created an application and thought its biggest problem was too much boilerplate.
But everyone at work loves them so I must be wrong somehow.
Frequently the easiest path to perform a complex query with an ORM results in N+1 performance disasters. The inevitable retort is something like "but the ORM has better ways to do that." Of course it does; ORM implementers aren't incompetent. The key word is "easiest," meaning the ORM user doesn't have to spend time learning anything beyond simply chaining method calls. In production such work goes haywire when resolving some related entity causes tens of millions of round trips.
Without an ORM the easiest path is to write a single join, giving the database optimizer a fair shot at correctly optimizing the query, including over time as the database evolves. Again, yes an ORM can encapsulate the equivalent query. Guess what; for every time these ORM capabilities are actually utilized there are probably hundreds of expediently written N+1 queries because the programmer either didn't know better or was indifferent.
To a pragmatic mind this is the inevitable outcome when a tool makes the inefficient solution the easiest thing to do.
I look at this as not playing to core competencies.
SQL's strength is that it's declarative.
By wrapping an ORM around it, suddenly you have a DB engine talking to an ORM engine through a declarative interface. There simply isn't enough information and context passed across the interface to properly optimize.
So in the end, it's like trying to run two optimizing compilers in series, where either knows the specs of the other.
It's the good old "pit of success" story. When doing the right thing is extra work, you will find countless examples of people doing the wrong thing. The only way to always do the right thing is to make that the easiest possible way.
I've only worked with a couple ORMs, but they all allowed me to write my own queries if I wanted to. The ORM generated 90%, but when there was a performance win I just wrote my own.
So, I'm the 'database guy' and I come in when the DB is on fire. I find the bad query(ies) and tell the application developer they need to do it another way. (And suggest ways)
Then they tell me, it's too hard / impossible to do something else, and that's after it took them two weeks to figure out how the query is formed.
For these applications, it's best to ensure their DB is isolated from other DB needs and let the tire fire burn as a signal to others, because I can help a lot with performance, but adding indexes can only help so much.
When I've had similar experiences with people running bad queries from strings, it's a lot easier to get traction on fixes. Even when they think my suggestions are crazy (nobody likes it when I suggest a client side join, however, sometimes it's significantly faster)
Yes, this happens with complicated schemas with complicated queries. ORMs account for this. Almost every major ORM allows you to override those "bad" queries when needed with your own manual SQL calls. It's an old problem with an old solution that works in production.
I agree with you. I've experience with a few ORM apps, and, frankly, they always suck. In the beginning everything is perfect because an ORM makes writing trivial DB apps trivial. Then your app grows up and needs more features. Now the ORM only gets in the way, and you can't excise it easily. Now you're in a world of hurt.
They make a simple crud operation so easy, you don't even have to think about them.
Every remotely competent developer will however tell you that there are always cases where the orm is a bad fit. And that's exactly why basically all orms let you write your own queries.
There is really no reason not to use an orm. Just don't ever frown on people doing anything more advanced than a middle join without it.
You don't need ORM for simple CRUD operations. All you need is a function you can pass a table name and a map of column names/values you want to insert/update.
Until you want to be able to sort and filter and do all of the other things that most CRUD applications do.
Once you're there, if you're doing SQL directly, now you're manipulating strings in your code, instead of being able to build some kind of object that then generates a query for you.
Query builders and ORMs often go hand in hand in my experience, as the ORM will expose the ability to perform custom querying capabilities around the specific object you're working with; however, you're correct in that I am more considering query builders than ORMs directly in this instance.
Not at all. The only cumbersome thing about SQL, that is common enough and unpleasant to always spell out by hand are basic INSERT and UPDATE commands.
I use a set of 3-4 functions that I reuse pretty much in all my programs for this. It's no ORM, as it doesn't map classes to tables/data in the database. It's just a shortcut to generate and execute INSERT/UPDATE commands on arbitrary tables with arbitrary columns.
Also it has zero bugs, because you can hardly create bugs in something so simple.
ORM is good when you were going to have to be converting your query results into Python/Ruby/PHP objects defined by your framework. Why write that code yourself if the framework can handle it for you? Particularly if it's a common CRUD app.
I think that our love/hate for ORMs stems from the 80/20 rule.
For 80% apps/workloads/programmers, ORMs are pretty helpful (in that they save you computation time, thinking time, etc).
For 20% of cases, the ORM tends to get in the way. In these cases, the ORM doesn't prevent you from needing to know SQL, it doesn't give you performance gains, and it actively obfuscates what's "really going on". These cases make your job harder, and are what stick up in your mind when you think about ORMs.
At my place of work, we use an ORM (SQLAlchemy), but codegen the "annoying/boring" bits of ORM code (model/class specification, table/index creation). IME, it's wonderful and good – until I start wondering about how much memory this query is going to take up, 5 minutes after the client has made it. I know that SQLAlchemy probably caches things, but I have no idea how it's cache policy is set. When will my query be garbage collected? How much memory should I request for my app?
I can probably find this out by digging into the SQLAlchemy docs, but I haven't yet so uncertainty looms. TFA's "I'd much rather spend an extra bit of time typing, but this will save me time reading ORM's documentation, optimizing my queries, and most importantly debugging" rings true to me.
With raw SQL, answering these questions might be more straightforward – but I only ask these questions 20% of the time! Anyways, there's some food for thought. :)
In my experience an ORM will cause issues if used thoughtlessly throughout an application. Objects created by an ORM can be used "naively" and cause many round trips to the database, very inefficient queries, etc., but they boilerplate they can save is really helpful also.
I worked on a simple Python CRUD app project a few years ago with some folks who were absolutely against using an ORM for those reasons. There was so much code written that basically just took tuples returned from the DB and instantiated Python objects with them (and vice versa for inserts) that making changes to anything was absolutely maddening. I find that SQLAlchemy Core can be used very simply to essentially take a DB query and return Python objects.
I'm currently working on an e-shop. I need to display many tables with different filters.
Administrator wants products starting with string? OK: if filter['category']: qs = qs.filter ...
Administrator wants products without category? Ok: if filter['category']: qs = qs.filter ...
Add multilang supprt? Ok: subclass model, mark fields and translatable, run migrate script
I can combine any filters, ORM automatically creats query with necessary joins, subqueries, group by expressions etc.
If i didn't use ORM i would have to write code that generate select with cca 200 ifs because almost every filter can generate join or add fields to group by or generate subselect etc.
Reducing boilerplate can be very valuable. Why would you want to spend time writing yet another query when you could be focusing on the parts of the application that deliver value to your customer?
Maintainability is also something to think about. The classes you write for the ORM implicitly document the content model. It can be very hard to decipher the same information from a "show tables" and reading a bunch of SQL SELECT statements, especially if they are all mixed up in the code and broken up with string concatenation and variables. Future maintainers will be able to read the documentation for the ORM which will probably be better than trying to understand a bunch of custom code to deal with complex queries.
For an example of this, notice how in the article the author's "linking table" was nowhere to be found in the ORM section. That got abstracted away! Someone having to analyze the tables directly will be running into those sorts of things all over the place, which is more mental overhead for understanding the system.
I suppose it depends on your flavor of ORM. My experience using Django has been amazing from day one, and support for more exotic operations has improved by orders of magnitude since I last used it.
I think a good ORM is one that strives to make 80-90% of operations ridiculously easy, leaving edge cases to the developer who can always drop down to a raw query as needed. Django does that fairly well, IMHO, and my limited experience with SQLAlchemy felt pretty underwhelming in comparison
EDIT: Though admittedly I did not have access at the RDBMS level to write VIEWs to match my business needs back when I was using Django. If I did, then perhaps I could have chosen that approach – though I would have to forgo all the useful django-admin stuff that comes out of the box, so there's always a tradeoff...
> my limited experience with SQLAlchemy felt pretty underwhelming in comparison
That's interesting, because I usually hear of people having the exact opposite experience. The appeal of SQLAlchemy is that it lets you work at any level arbitrarily—you can use its high-level operations and abstractions for the 80-90% of common cases, but then drop down to its lower level and write complex queries where needed (including CTEs, window functions, stored procedures, etc), without ever resorting to writing SQL by hand. Django's ORM, in comparison, is very limited in the types of queries you can write with it, and is difficult and unintuitive to optimize.
Edit: That said, the Django ORM has improved by leaps and bounds over the past few years. These days, I'd say it gives you a way to cover about 90% of cases in an efficient manner (even if it has a bit of a learning curve). But if you're consistently doing more complex queries, I think SQLAlchemy is vastly superior.
Have you ever tried it? Your coworkers are right. It also helps you avoid common mistakes like SQL injection. When the ORM doesn't work for a specific case, they usually have ways where you can override the ORM and use your own SQL calls. Virtually every major, popular ORM does this across different languages (can personally confirm with Ruby, Java, & Python ORMs - confident that JS ones support it too).
I disagree, and SQLi is not simply prevented by avoiding user input. There are many cases where you need to use user input for a SQL query and its a valid pattern, and can be done securely with Prepared Statements or Parameterization.
Using an ORM discourages you from writing SQL queries and it also automatically parameterized queries. This is a good thing! In fact, from experience, the single easiest way to mitigate a naive developer from introducing SQLi is requiring them to use an ORM. People should not be constructing SQL statements by hand today, its too easy to mess this up. SQLi shouldnt be a thing in 2019, but, it is.
My point is it is VERY MUCH not orthogonal, its very much related. Avoid naive SQLi, use an ORM. Directly related.
ORM builds on top of mechanisms for prepared statements or parametrization.
Yes, ORM API can perhaps limit developers to such an extent, that they can't construct SQL themselves, and thus can't make the mistakes leading to SQL injection.
SQL query is a string. (that's where you don't pass the user input, not even quoted/escaped, as a policy) User input is passed "out of band", meaning not as a part of the query string. How exactly that happens depends on the RDBMS's particular client/server interface.
That's all that's enough to avoid SQL injections. And it has nothing to do with ORM.
I'm not sure I understand since there are common cases where you have no choice but to deal with user input. Sure you can manually escape user input in your manual sql calls, but if you're doing that manually there's no hard guarantee that you'll always escape that input vs using an ORM.
Which ORMs make SQL Injection easy to happen? From my experience input sanitation is one of the main features of every major ORM. I am not aware of any popular ORM that makes it easy for SQL injection to occur.
I agree mostly. I do like C#/LINQ based ORMs because as the name implies - it is integrated into the language and the “ORM” is treated like a first class citizen and it separates the query from the provider that translates it the destination. But even with LINQ, when you get into complicated queries or you have to do a left join, the syntax leaves a lot to be desired.
Also, it can get obtuse when you try to take advantage of any of its complex features.
I will never choose a real ORM for a green field project. The most I will usually want is something simple that takes in a SQL query and projects the result onto an object without all of the overhead - a Micro ORM like Stack Overflow’s Dapper.
And I agree, by the time you create DTOs and add attributes and the entire ceremony around ORMs. It really doesn’t reduce the boilerplate.
The friction depends on the situation
* x number of teams x number of dBs x number of packages
And the permutations (of x 1,2,many)
When it’s 1 team 1 package you can get away with whatever when it’s many DB many teams many packages you are going to find it very hard to refactor change the scheme when you don’t have a central location for the DB abstraction
I agree with that sentiment. Concatenating strings is miserable and error-prone, especially for highly dynamic queries.
But there's still another step between a full-blown ORM and a query builder, where you still define the models and the relations between them, but fall back to a plain query builder for constructing queries. That way, you're not dealing with plain arrays, etc., but you're still essentially writing SQL. Like Objection.js[0], which is built on Knex.
Shameless plug: if you're using TypeScript, checkout https://www.npmjs.com/package/@wwwouter/typed-knex
I love Knex, but was missing the type safety, so I created a TypeScript wrapper around it.
If you look at the code, you can see I started out as a C# developer :)
It's being used in production for a few projects, but it would be great if more people started using it.
Feedback is more than welcome!
I want to write sql but have my library map between my languages built-in types and my sql types. I feel like something like Dapper is the correct abstraction for communicating with a database.
Both... I prefer a hybrid approach when it comes to ORM's. In most projects I will use an ORM to avoid boilerplate for simple entity queries (save, findByid, findByX, delete) and converting a database row to a class instance but I avoid mapping complex relations.
An ORM like Spring Data with JPA/Hibernate in the Java/Kotlin world works well for this. I write my db schema by hand, create an entity class with the same fields and an empty repository interface extending CrudRepository. This gives me simple CRUD access to the table with almost no code.
When I need complex queries I inject a JdbcRepository which allows me to query the DB using standard SQL and a RowMapper lambda.
Yep, the Spring Data CrudRepository interface based stuff is an amazing time saver. Just extend an interface, and BOOM, you've got the basic CRUD operations ready to go. And then you just use HQL in annotations for more specialized queries. Other people's mileage may vary, but I've found this to be a tremendous boon.
I believe that People who hate ORMs haven't used a good ORM, so what they really hate is the tool, not the concept. I like using my ORM of choice when it's appropriate, I like just using SQL statements when it's appropriate. Choose the tool appropriate for the task.
And FWIW, I've written some moderately complex apps over the years, and routinely use Hibernate / JPA (or GORM when using Grails) and I've never, or very rarely, encountered most of the problems cited by critics of ORMs. And Hibernate makes it easy enough to call raw SQL when you need to (and yes, I have needed that facility a few times).
By and large, for the vast majority of projects that I can imagine working on, my default starting assumption is almost always going to be "use an ORM" -- unless something about the situation strongly dictates otherwise.
I've used many ORMs. I don't hate them, but I think their net utility is negative.
While you initially might think that interfacing with the db is going to be very tedious and labor intensive, it usually turns out to not be that bad.
Battling the ORM to make it do what you want can on the other hand be very tedious.
When you have the SQL in the code it's much more obvious what the performance profile and potential concurrency problems are. That's very important to solve real problems which arise in almost all projects with some scale.
If I were to choose my tools, I would pick some sql-based schema versioning system, and potentially a simple data mapper for moving data back and forth between db records and entities.
But I would be very reluctant to use a full-blown ORM nowadays.
I tend to disagree. To me a good ORM totally (ok, not totally, but as totally as possible) abstracts the database away from your code. That's it's purpose. You just want to work with data. You want to fetch data, persist data and delete data - you don't really want to care about how that's done. That's where a good ORM can really help you out.
However, if the ORM gets in your way, either because of API complexity, performance issues or other reasons, and you start to "battle" it as you said, you immediately move out of that context and start thinking in a more data oriented context. That's where SQL comes in.
As I said before, I think it's a question of using the appropriate tool. I've written hundreds of applications using multiple different ORMs and just plain SQL—and I'll tell you I'll pick a good ORM over raw SQL for basic CRUD operations every time.
However, I'll give you that I'll pick plain SQL over a bad ORM any day.
I've used many ORM's in multiple languages and on multiple teams and have never had a good experience with any of them, once the complexity grew beyond trivial.
I've been tasked with various database optimisation tasks over the years and ORM's have always gotten in my way when it came to understanding a queries indexing and locking and improving/fixing it. I always end up just looking at the logged SQL, fixing it in SQL and then trying to convert it back to ORM code, which has always been tedious. In my experience, in multiple otherwise talented teams, ORM's seem to encourage less-than-ideal database modelling and overfetching data because its too easy to just make an ORM class do it for you rather than really thinking about your data and the data model you need. This isn't ORM's fault of course, but rather the teams using them, but its happened so frequently that I don't think "discipline" or "better developers" is a valid answer. [1] I used to hate databases and dread working with them for the above reasons.
I recently started loving databases by working with Clojure and the HugSQL library. The reason I love this approach is that Clojure isn't OO, so there's no Object mapping: I deal with plain old data instead, in whatever format I make the query return it (I can then easily transform that data further if I need) and HugSQL lets me write (composable and parameterised) raw SQL. Its fantastic and has been super productive for me. I can think in pure data and model the database how it makes sense and still have a good understanding of the indexing and locking of my queries.
[1] As jandrewrogers said: "in almost every place I've seen them used they've become a way for developers to avoid understanding how databases work, inevitably leading to inexplicable data models and poor performance. In practice, ORMs tend to end up creating crippling technical debt" and this has been my experience too. Sure, you can use an ORM well, but in reality, this seems to happen so rarely that in my opinion, ORM's are a net negative to be avoided if possible. Saying that you just gotta use them right (when few people seem to actually do so) seems like a bit of a no true scotsman argument to me.
Learning and using an ORM is not an excuse for not learning and knowing SQL + Database concepts. (I'm not saying the author argued that, I just want to sweep away an straw men that say not having to learn SQL is an argument in favor of using an ORM).
For me the biggest benefit of using an ORM is separation of concerns. I truly hate it when SQL and query building get mixed into controllers and other parts of the application. However, I also hate the layered complexity that the author talked about, which definitely means ORM's are not appropriate for all use cases! But if you don't use an ORM you need to be diligent with using some other pattern and code organization to keep your code clean and your concerns separated.
I worked at a consulting company for a number of years and we got immense value out of Symfony, which includes the Doctrine ORM. Many of our projects boiled down to custom Content Management Systems, so we got the huge benefit of being able to define the data model in code and not worry about any of the SQL while also not having very complex queries. So we got pretty much maximum benefit and minimal exposure to the down-sides.
I will say that these systems almost always wanted some detailed reporting, and that I usually got tasked with writing them. They always had some complicated queries and I almost always made use of the "raw sql" option to just write my own queries and spit out CSV. Which was always a much different use case than the rest of the application.
I wish people did not think the choice was solely between "write raw SQL with raw strings" and "try to pretend the database is object-oriented when it is not."
The third approach is to safely wrap the database and its columns with code in a way that is composable.
In Python, SQLAlchemy has an ORM, but it is optional, and you can just work with tables and columns if you want.
The real third approach is that you can safely pretend the database is object-oriented for manipulation and simple lists and still use SQL for complex queries. Most ORMs let you safely mix and match both methods easily.
This ORM or not ORM is the wrong question. Use an ORM to save you headaches where it's appropriate and use direct SQL when it's not.
I use this approach with Mongoose in Node.JS. If you want to update a user document, we always query the entire document, set the field, and call .save(). This triggers all kinds of very useful validation hooks and is easy to think about. But if you want to find a series of users, or build an API for a specific front-end form, I've found the ORM just gets in the way, and we have not had any trouble writing db queries inline.
We used to use Mongoose in our project[0], but found that at the volumes of interactions we were dealing with, it was orders of magnitude faster to just use the raw Node.JS driver.
You are absolutely correct. Using both is a very valid options.
We use ActiveRecord a lot, and then have custom SQL queries using `find_by_sql` for very complex, optimized joins. It works very well. Rails gets out of the way when we need it to.
Two caveats with find_by_sql: it’s read-only, so no insert or update commands, and it still does column-to-instance-variable monkeypatching on the object level, as opposed to the class-level monkeypatching that’s applied to normal ActiveRecord classes as soon as the DB schema is read.
For the former, there's always ActiveRecord::Base.connection.execute. For the latter, I think it's more complicated than that. There also is object-level mapping even for regular AR usage. If you do something like Foo.select("true as bar"), your Foo objects will have a bar variable available to them.
There are so many incompatible needs for databases that most DB wrappers of any kind rarely make sense except when prototyping. And database wrapper authors have a tendency to cater to lowest common denominator of features so that they can treat all databases the same, which is going even further in the wrong direction. Choosing a database requires learning what your requirements are, learning what the candidates are, and when you pick one, learning how to use it correctly. People and companies rarely seem to do all of these steps.
The "write-raw-SQL-with-raw-strings" approach has one serious issue -- sql-injections. Some people argue that it is not so hard to filter strings before concatenating them into sql-query, but I know also people who argue that it is not so hard to write C code and to not introduce bugs around NULL and wild-pointers, one just needs to be careful. I, personally, do not believe that strategy "be careful" can work reliably here.
I cannot imagine work with SQL without query builder, which introduces type checks and conversions, and escapes strings when needed. ORM is an optional thing, but it is nice to have some layer that maps rows into structs and vice-versa. With dynamically typed scripts it doesn't matter: in any case you would get a hashtable (the only difference is a syntax used), but with compiled language and static typing I feel some uneasiness when using slow hash table mapping instead of blazingly fast struct field access. And the tooling can help to declare that structs statically.
> The third approach is to safely wrap the database and its columns with code in a way that is composable.
Like Haskell's Esqueleto[1] which lets you work with SQL code as Haskell functions and values. As an example from the documentation:
select $ from $ \p -> do
where_ (p ^. PersonAge >=. just (val 18))
return p
results in roughly:
SELECT * FROM Person
WHERE Person.age >= 18
This lets you define new functions to use in your queries that use SQL functions. For example, you can define `isPrefixOf` for SQL using the SQL functions `char_length()` and `left()`. The use of the function will be expanded into the corresponding SQL code when the query runs. Like,
select $ from $ \p -> do
where_ $ val "John" `isPrefixOfE` p ^. PersonName
return p
could result in:
SELECT * FROM Person
WHERE LEFT(Person.name, CHAR_LENGTH('John')) = 'John'
For a long time these were the main choices and the third way had very little library support and generally required you to role your own, which were generally crappy and inefficient. Even when "lightweight ORM's" were first gaining traction their composability was quite poor (and some ways it still is) and would often leave the denormalisation part to the application.
Even though I think this third way should be the "default" way to write an application now there's an awful lot of code left over from when an ORM was better.
I've wanted to like ORMs but they always get in the away as requirements start to get more complex. On the other hand, I like writing in plain SQL, but that's often hard to comprehend by other people - you cannot just glance through to know what's happening under hood.
I find Ecto to be the perfect balance of expressiveness, flexibility and clarity. In fact it's the single reason to have picked up Elixir.
Have to agree. been really enjoying Elixir / Ecto / Phoenix.
I find Ecto lets me be as expressive as I need to with Queries, and handles the mapping into models very well for the 90% of easy cases that I need it to.
rails g model Post published_at:datetime title content:text
rails g model Comment post:references author published_at:datetime content:text
rails g model Tag name
rails g model PostTag post:references tag:references
class Post < ApplicationRecord
has_many :comments
has_many :post_tags
has_many :tags, through: :post_tags
end
class Tag < ApplicationRecord
has_many :post_tags
has_many :posts, through: :post_tags
end
Fair enough. I’ll concede that wasn’t a fair comparison to the OP. My point remains that examples focusing on the initial setup are uninteresting (to me) because that’s not where the bulk of the work is.
You're gonna have to pry the Rails from my cold dead fingers if you want me to maintain a web app's database abstraction layer long-term. No other tool works half as well as ActiveRecord. You could maybe convince me to try out https://rom-rb.org/ but only on a brand new project and only if Rails isn't appropriate.
If it's a project that wasn't built with Rails, I'm going to want https://github.com/jeremyevans/sequel if ActiveRecord is too much trouble to introduce.
I have been lucky to be able to pick what I want to use, so I pick non-rails stack (Sinatra/Padrino and now Roda + Sequel). No other tool works as half as Sequel. You're gonna have to pry Sequel from my cold dead fingers.
Adding an index on tags.name is likely not very useful on its own but adding an index with an uniqueness constraint to prevent duplicates is probably a good idea. Assuming there are just a few different tags records (<1000) then a sequential scan is generally either insignificantly slower or even faster than an index scan.
>Not the subsequent years of maintenance.
I'd much rather maintain this example than a code base replicating the same behavior without an ORM.
I like the Django ORM. This is likely for two reasons:
1. I only use Django for small use cases where I rarely see any sort of scope creep. There was no real conscious decision about this, just kind of the way it happens.
2. The Django ORM is fairly mature and makes it quite easy to get a small project out the door.
I regularly use SQL directly at work and wouldn't want to try to replace any of it with an ORM even on new projects because we are all just used to working with it and we wouldn't want to add any complexity to our workflow without real, tangible benefits.
I would not, however, be opposed to working on a larger Django project (ORM and all) if the opportunity presented itself.
(tl;dr: there is an impedance mismatch / leaky abstraction between relational data and OOP for all but the most trivial use cases, guaranteeing that whatever problems an ORM might solve will be traded for new ORM problems instead)
I wonder how people of Vietnamese heritage would feel that you are characterizing their country as synonymous with war and disaster? IIUC Vietnam had been a relatively peaceful country for almost 50 years.
Not coincidentally ORMs have been doing great since that silly article was written so many years ago.
Personally I think an active record style ORM for Go like gorm is a poor fit for a language that doesn't come across as inherently OOP. Going through some of the documentation for gorm, it seems to rely heavily on method chaining which for Go seems wrong considering how errors are handled in that language. In my opinion, an ORM should be as idiomatic to the language as possible.
I've used sqlx[1] before, and it feels pretty idiomatic to Go. You tag your structs with their respective database columns, write up a query, and hand it to sqlx to perform the deserialisation of the data. I've also come across squirrel[2] too, though I haven't used it, it does look rather interesting.
For my 2¢, We use squirrel at work (I haven't touched it myself though). People here seem to like it.
It's not an ORM per se, but it seems to occupy the sweet spot you're describing. It takes away some of the more tedious parts of using SQL, but allows you to still reason about what's happening under the hood without committing tons of documentation to memory.
Or HugSQL, which is similar, but actively maintained. Coupled with Clojure's data-centric focus, there's no need for a traditional ORM and you get to work directly in SQL. Yesql and Hugsql made me enjoy using databases for the first time ever.
But I never got to use it at work, and thus lost interest. The biggest thing missing with it was that you lost type safety if you had to dynamically build a query. These days its main problem is lack of compatibility with .NET Core, which somebody else was working on for a while.
I think the flat row model for querying data from related tables is just not very good. I'd MUCH rather work with an object model like LINQ, where I get a list of Foos and each one has foo.Bars nested within it. As opposed to the output of a join where I get one row for each Bar, and parent Foo's columns are duplicated across each row.
Entity Framework does an excellent job of mapping the LINQ model to the SQL one, but since they really are different under the covers, it's easy to produce hefty queries. For example a LINQ "group by" frequently cannot be translated to a SQL "group by".
I tried to keep it simple and not deviate too far from straight SQL. But I still didn't want to force processing those damn row-based outputs onto the programmer, so I at least had to add notation for breaking out the columns of a top-level result set into an object hierarchy:
For my money your type provider was the single coolest one I've seen before. The ahead of time query execution and planning is a killer feature, and for us the only gaps were either process related, meaning we did not have a good out-of-band migration pipeline, or some constructs that were not understood by your type provider.
I really like Rezoom.SQL even though I haven't done a deep dive yet. But that Navigation Properties syntax is enlightening... I just realized that my idealized query language (WIP) needs something similar.
Great work. I used rezoom in couple of minor projects and like most that I could really trust that query runs if it compiles. Unlike in Entity Framework where you have unlimited possiblities write code which compiles just fine but crash runtime. In principle I don't like an idea that your primary language get compiled to SQL due it's very leaky abstraction. Instead I like expressing queries as data (ideally compile time checked).
In rezoom query composition part wasn't the strongest part. When for example some model needed additional join you had to modify N queries. This kind of composition could be done of course with dynamics but like you say, you lose biggest selling point: compile-time safety.
However these compositions are usually all known at compile time so you should be able to just build them before rezoom checks them. But out of the box compile time programming in F# is not there yet...
Thanks. I agree composition was a pain point. I had various ideas to make things better, like adding support for "erased" views/functions/TVFs that would be inlined at compile time, but it always felt like it'd be hacky and still not solve enough problems.
Type providers are such a cool language feature, but the way developing one works is too damn confusing. Especially when you try to publish one as an easy-to-use package and simple stuff like loading dependencies feels like uncharted territory. When it comes to my precious free time I hate, hate, hate figuring out packaging/deployment type stuff, I just want to focus on my code. So that's a big part of why I haven't done a great job maintaining it.
I feel your pain although I haven't ever implemented type provider to be precise. You have no reason to feel guilty! It's out and it's runs without any major bugs(!). Hopefully community will carry it and at very least it's one nice show case for compile time computing of F# (and maybe add motivation to develop that side of language further).
While I learn towards not using an ORM, the productivity gains (at the very least early on in development) are undeniable. What I've always looked for are frameworks that give you an ORM but also make lower level queries very easy, normally via a query builder, allowing you to go back and forth between levels of abstraction. If I had to choose, I prefer libraries that give you the lower level of abstractions first and then build upon those to offer an OOP-based ORM approach (which is what I believe most people think of when they say "ORM").
One of the best libraries I've ever seen get this right was TypeORM[0]. It was easy to get started with, includes consideration for migrations, allows you to use both the query building and annotated-class approaches where appropriate, also allowing for use of the repository pattern if you're comfortable with that, and has pretty great support for lots of different backends (I've used postgres the most though). All of this from a F/OSS project (~2 years ago I was also involved in a C# project during the switch from .NET 4.x to .NET core/standard and was very very annoyed that things I could easily do with TypeORM weren't available/worked out yet in EF core at the time).
The DataMapper pattern is very much included in the normal discussion of what an ORM is expected to provide -- so much so that almost most larger ORMs (that I know of at least) is listed on the Wiki[0], including TypeORM.
That said, you're right, the DataMapper pattern isn't quite identical, and it is a piece of fully OOP-based ORMs that I think are on the right side of the spectrum. I'd say that the DataMapper pattern fits in the middle -- I call it Serialization/Deserialization, and assuming the syncing of data is not constant (as in triggering action on another thread or something), then it can be anything from a function (ex. db::insert_user(user), user.write_to_db(db) or Adapter.save(obj)) to a dedicated object (Adapter.update(db, obj)).
If I had to try and point to a difference between the two, I'd say that DataMapper-ish or DataMapper-and-below levels of abstraction expose more internals by default (which is obvious) -- I find that ORMs on the right side of the spectrum I sketched up top try to never expose their internals, which quickly falls apart as soon as you step off the paved/well-traveled path.
[EDIT] I want to note that dealing with this syncing behavior in EF (Entity Framework) was really annoying. The constant management of a pool of objects that "reflected" the objects in the DB and had to be saved all together/flushed before they were manipulated or whatever was really annoying. That said, I am now aware that C# is one of my greatest weaknesses, so take this opinion with a grain of salt, it's entirely possible that I just didn't have the skill to do it right and keep it in my mind the right way. Just to make sure I'm not unfairly bashing EF I went and found a relevant SO post to show the kind of stuff I didn't like having to look up/deal with [1]. I'm picking on EF but this is likely an issue with any ORMs that handle it this way (w/ the object pooling & syncing).
This is where I land also. ORM's are excellent for the repetitive stuff, especially when integrated with frameworks but I don't want the presence of an ORM to prevent me from accessing the more advanced aspects of my database. I'm a heavy Postgres user and there's just...so much that it's capable of that hiding it behind an ORM to pretend it's "just a database" is a bit like a tragedy.
One of the better balances I ever came across from multiple languages was actually ActiveRecord because of the Scopes functionality. The scopes let you abstract certain parts of queries and name them, reuse them, include parameters, combine multiples of them back together, switch parts out, etc. You can combine raw snippets with fully abstracted parts.
It's extremely flexible and probably the feature of Rails that I miss most when I don't have it.
You can do this with most any ORM by mapping tables on top of views defined in SQL. I regularly use this pattern with Django's ORM to make complex aggregations only a foreign key away. I can write detailed performant SQL that it is impossible to make an ORM output this way.
There's another big aspect of ORMs a lot of people tend to skip in discussion: Security.
Raw SQL can be dangerous, and given enough people and code somebody will eventually make a mistake (as is human) and introduce a vector for a SQL injection attack or some other DB specific vulnerability.
A good ORM can be a fairly effective layer of safety.
IMO it is a humongous red flag if someone on your team talks about "raw SQL" and does not mean parametrized queries. I guess I could say I was fairly fortunate in my education/early career but this is a lesson you have to learn early when working with user input and databases.
often they don't mean that - they really do just mean "run any sql you want". just worked on a project that explicitly wrote their own bolted on 'db layer' and avoided the built-in ORM which had parameterized query support. i now know what 1200 sql injection-capable queries look like...
It can be, but it is hardly the only solution to that problem. I've also seen it solved, for example, with a git commit hook that just bounced any non-parameterized queries.
I loved TypeORM initially, but came to the conclusion that it was written by people who really got TypeScript, but didn't really get SQL.
For example, we were converting from Sequelize, where we were catching unique constraint violations and responding appropriately, and we got quite confused that in TypeORM those errors never got thrown ... until we discovered that TypeORM decides unilaterally (and almost unbelievably) to replace your original record in that situation.
We have since ripped out all third-party ORMs and migrated to a system of typed helper functions and tagged templates (where all the typings are generated automatically at compile-time by inpecting the database). This is really rather wonderful — we now have full access to all sorts of goodies like native UPSERT, we know exactly what SQL we're going to get every time we touch the database, and yet the ergonomics are excellent, because everything going in and out of the DB is fully typed, including even column and table names in basically-raw SQL. A blog post (and perhaps eventually a library) is in the works ...
I couldn't agree with this more. I've just started using TypeORM and the TypeScript aspect is great. However, here I am trying to do a simple distinct left join, but it's extremely difficult to write because the query APIs consistently get in the way.
We're regularly receiving invalid SQL errors, which from a typed language/API seems particularly odd.
We're doing this exact same query on the exact same DB in ActiveRecord/Ruby and it's trivial to both write and understand.
I recently put a lot of refactoring work in a java app. The enterprise architects made a list of decisions that amounted to forbidding an ORM, so a lot of JDBC/ResultSet boilerplate was included. The program had a tendency to UPDATE a few of the columns, then SELECT the same data right back a few lines later. Plenty of inconsistencies in the logic. This means refactoring went like this:
First step: Create for each table generic methods to insert, update,select all fields. Now at least memory and DB are consistent.
Next step: All these methods are mostly identical and there is a lot of commons-beanutils in there. So add an annotation to the relevant getters and generate most SQL statements on the fly.
Next step: Replace the incoherent commit/rollback mess with clear boundaries. Either it succeeds or it fails.
Next step: To stop the never ending reloads of data already in memory, SELECT data only when it's not already there. Flush all caches on commit.
All of this gives great results. Batch run time went down from 20+ minutes to a few seconds. Most weird crashes disappear. The need for manual data fixup after crashes evaporates. And then it hits me: I just wrote a custom ORM.
I don't have any issue with ORMs in principle, and even wrote an ORM once. However, in almost every place I've seen them used they've become a way for developers to avoid understanding how databases work, inevitably leading to inexplicable data models and poor performance. In practice, ORMs tend to end up creating crippling technical debt that is difficult to fix.
If ORMs were typically used by developers that fully understood the implications for the underlying database, there would be few issues with them and they would be a valuable tool. Unfortunately, they seem to be most popular with developers that are the opposite of that description.
Frankly that says more about the people you've worked with than the underlying technology.
I work with devs that have over a decade of Django experience. We use the ORM because it's just ridiculously easier to write queries on it and because SQL is impossible to compose without substantial problems.
90% of the code we write is CRUD and API endpoints. There's no reason to write SQL by hand except for the complex aggregations that comprise 5% of our queries, with luck.
Ah, I see what you mean. I was thinking in terms of union'ing and the like where you can use set theory to compose sets of information from various queries.
Well, I'm not the OP. AFAIK, he may be thinking the same as you.
SQL lacks some power on negative and consolidated joins, forcing one to write more complex queries than necessary. It is this way for good reasons, because those are exactly the kinds of joins that indexes help you least and that most hinder parallelism, so they should be avoided if possible. On my experience, it's not a large drawback, but YMMV.
in almost every place I've seen them used they've become a way for developers to avoid understanding how databases work
This is what ORMs and NoSQL have in common -- they're created by very smart people for very smart reasons, but the bulk of people I encounter advocating for them on the projects I work on are just intimidated by SQL and don't know or care what kind of burden they're taking on to avoid it. My opinion based on my experiences with ORMs is that to work successfully with an ORM you need to know SQL just as well, and probably better, than you would without it. Plus you need to know the ORM. So the typical adoption story I've seen is that people spend an inordinate amount of time struggling with their ORM and never lose faith that dealing more directly with SQL would be worse. The more they struggle, the more they're happy that they went with an ORM. "Gosh, who knew this would take six months. Imagine if we had to do it without Hibernate!" Uh, yeah, I can imagine writing ten tables and a dozen different joins on them without Hibernate. I can even imagine it taking less than six months and not needing an "ORM architect" who spends his afternoons trying to figure out why Hibernate isn't generating the efficient query he thought it would, and who spends standup telling scary campfire stories to the junior devs about the SQL horrors his Hibernate heroism is protecting them from.
It's true that my ORM experience mostly comes from a fairly corporate environment that I've steered clear of for years, and I've never seen a small team of strong contributors make use of an ORM. I think this difference in experience might explain why ORMs are so polarizing. I've also never found how fast I can bang out boilerplate SQL to be a limiting factor for a task at any scale, so there's probably a set of problems and situations I've never had to deal with where an ORM comes in handy.
299 comments
[ 2.5 ms ] story [ 236 ms ] threadI don't want an ORM (like specifically the object relational mapping stuff) for most of my use cases but I do want an abstraction above text for interacting with SQL.
gorm's sql builder is alright but something better would be really nice.
Put all the SQL in .sql files and make all queries parametrized. Or wrap all queries in VIEWs or functions at the RDBMS. This makes maintenance much easier.
This is like the oldest best practice for RDBMS use, for security, and for decoupling consuming apps from each other and the DBs low level implementation (so that app views and base tables can evolve independently to the extent possible), and for maintainability: all app access to the DB should be through views adapted to the apps needs.
I recommend PostgREST to export a RESTful interface to a single PG schema's VIEWs and functions, and RLS and INSTEAD OF triggers as needed.
But yes, SQLAlchemy Core is far more full-featured and gives you more than just a few primitives to work with. It also has a lot of library-specific constructs and is much closer to a full-fledged "ORM" than a query builder; what you're writing is not exactly SQL and is yet another dialect to learn.
[0] http://initd.org/psycopg/docs/sql.html
The OP here says it "reduces boilerplate" -- rarely have I created an application and thought its biggest problem was too much boilerplate.
But everyone at work loves them so I must be wrong somehow.
Without an ORM the easiest path is to write a single join, giving the database optimizer a fair shot at correctly optimizing the query, including over time as the database evolves. Again, yes an ORM can encapsulate the equivalent query. Guess what; for every time these ORM capabilities are actually utilized there are probably hundreds of expediently written N+1 queries because the programmer either didn't know better or was indifferent.
To a pragmatic mind this is the inevitable outcome when a tool makes the inefficient solution the easiest thing to do.
SQL's strength is that it's declarative.
By wrapping an ORM around it, suddenly you have a DB engine talking to an ORM engine through a declarative interface. There simply isn't enough information and context passed across the interface to properly optimize.
So in the end, it's like trying to run two optimizing compilers in series, where either knows the specs of the other.
Then they tell me, it's too hard / impossible to do something else, and that's after it took them two weeks to figure out how the query is formed.
For these applications, it's best to ensure their DB is isolated from other DB needs and let the tire fire burn as a signal to others, because I can help a lot with performance, but adding indexes can only help so much.
When I've had similar experiences with people running bad queries from strings, it's a lot easier to get traction on fixes. Even when they think my suggestions are crazy (nobody likes it when I suggest a client side join, however, sometimes it's significantly faster)
I agree with you. I've experience with a few ORM apps, and, frankly, they always suck. In the beginning everything is perfect because an ORM makes writing trivial DB apps trivial. Then your app grows up and needs more features. Now the ORM only gets in the way, and you can't excise it easily. Now you're in a world of hurt.
I like the code generation approach of SQLBoiler: https://github.com/volatiletech/sqlboiler
They make a simple crud operation so easy, you don't even have to think about them.
Every remotely competent developer will however tell you that there are always cases where the orm is a bad fit. And that's exactly why basically all orms let you write your own queries.
There is really no reason not to use an orm. Just don't ever frown on people doing anything more advanced than a middle join without it.
Once you're there, if you're doing SQL directly, now you're manipulating strings in your code, instead of being able to build some kind of object that then generates a query for you.
I use a set of 3-4 functions that I reuse pretty much in all my programs for this. It's no ORM, as it doesn't map classes to tables/data in the database. It's just a shortcut to generate and execute INSERT/UPDATE commands on arbitrary tables with arbitrary columns.
Also it has zero bugs, because you can hardly create bugs in something so simple.
You may have underestimated how bad things are in some of the unicorns..
1. Creating your own query builder before understanding the purpose of bind param: https://medium.com/tokopedia-engineering/dynamic-sql-query-b...
2. ???
3. Profit from SQL injection: https://www.youtube.com/watch?v=PKkNjOweWTE
For 80% apps/workloads/programmers, ORMs are pretty helpful (in that they save you computation time, thinking time, etc).
For 20% of cases, the ORM tends to get in the way. In these cases, the ORM doesn't prevent you from needing to know SQL, it doesn't give you performance gains, and it actively obfuscates what's "really going on". These cases make your job harder, and are what stick up in your mind when you think about ORMs.
At my place of work, we use an ORM (SQLAlchemy), but codegen the "annoying/boring" bits of ORM code (model/class specification, table/index creation). IME, it's wonderful and good – until I start wondering about how much memory this query is going to take up, 5 minutes after the client has made it. I know that SQLAlchemy probably caches things, but I have no idea how it's cache policy is set. When will my query be garbage collected? How much memory should I request for my app?
I can probably find this out by digging into the SQLAlchemy docs, but I haven't yet so uncertainty looms. TFA's "I'd much rather spend an extra bit of time typing, but this will save me time reading ORM's documentation, optimizing my queries, and most importantly debugging" rings true to me.
With raw SQL, answering these questions might be more straightforward – but I only ask these questions 20% of the time! Anyways, there's some food for thought. :)
I worked on a simple Python CRUD app project a few years ago with some folks who were absolutely against using an ORM for those reasons. There was so much code written that basically just took tuples returned from the DB and instantiated Python objects with them (and vice versa for inserts) that making changes to anything was absolutely maddening. I find that SQLAlchemy Core can be used very simply to essentially take a DB query and return Python objects.
I'm currently working on an e-shop. I need to display many tables with different filters.
Administrator wants products starting with string? OK: if filter['category']: qs = qs.filter ... Administrator wants products without category? Ok: if filter['category']: qs = qs.filter ... Add multilang supprt? Ok: subclass model, mark fields and translatable, run migrate script
I can combine any filters, ORM automatically creats query with necessary joins, subqueries, group by expressions etc.
If i didn't use ORM i would have to write code that generate select with cca 200 ifs because almost every filter can generate join or add fields to group by or generate subselect etc.
Maintainability is also something to think about. The classes you write for the ORM implicitly document the content model. It can be very hard to decipher the same information from a "show tables" and reading a bunch of SQL SELECT statements, especially if they are all mixed up in the code and broken up with string concatenation and variables. Future maintainers will be able to read the documentation for the ORM which will probably be better than trying to understand a bunch of custom code to deal with complex queries.
For an example of this, notice how in the article the author's "linking table" was nowhere to be found in the ORM section. That got abstracted away! Someone having to analyze the tables directly will be running into those sorts of things all over the place, which is more mental overhead for understanding the system.
I think a good ORM is one that strives to make 80-90% of operations ridiculously easy, leaving edge cases to the developer who can always drop down to a raw query as needed. Django does that fairly well, IMHO, and my limited experience with SQLAlchemy felt pretty underwhelming in comparison
EDIT: Though admittedly I did not have access at the RDBMS level to write VIEWs to match my business needs back when I was using Django. If I did, then perhaps I could have chosen that approach – though I would have to forgo all the useful django-admin stuff that comes out of the box, so there's always a tradeoff...
That's interesting, because I usually hear of people having the exact opposite experience. The appeal of SQLAlchemy is that it lets you work at any level arbitrarily—you can use its high-level operations and abstractions for the 80-90% of common cases, but then drop down to its lower level and write complex queries where needed (including CTEs, window functions, stored procedures, etc), without ever resorting to writing SQL by hand. Django's ORM, in comparison, is very limited in the types of queries you can write with it, and is difficult and unintuitive to optimize.
Edit: That said, the Django ORM has improved by leaps and bounds over the past few years. These days, I'd say it gives you a way to cover about 90% of cases in an efficient manner (even if it has a bit of a learning curve). But if you're consistently doing more complex queries, I think SQLAlchemy is vastly superior.
Using an ORM discourages you from writing SQL queries and it also automatically parameterized queries. This is a good thing! In fact, from experience, the single easiest way to mitigate a naive developer from introducing SQLi is requiring them to use an ORM. People should not be constructing SQL statements by hand today, its too easy to mess this up. SQLi shouldnt be a thing in 2019, but, it is.
My point is it is VERY MUCH not orthogonal, its very much related. Avoid naive SQLi, use an ORM. Directly related.
Yes, ORM API can perhaps limit developers to such an extent, that they can't construct SQL themselves, and thus can't make the mistakes leading to SQL injection.
Django's ORM will sanitize input when you pass it in as raw SQL.
That's all that's enough to avoid SQL injections. And it has nothing to do with ORM.
This is a matter of interface design.
Also, it can get obtuse when you try to take advantage of any of its complex features.
I will never choose a real ORM for a green field project. The most I will usually want is something simple that takes in a SQL query and projects the result onto an object without all of the overhead - a Micro ORM like Stack Overflow’s Dapper.
And I agree, by the time you create DTOs and add attributes and the entire ceremony around ORMs. It really doesn’t reduce the boilerplate.
The ORM is just an API to your database in your programming language. If you an handle any other API, you can handle an ORM.
It's has obvious benefits like automatically handling mapping database columns to objects, doing simple querying without lots of strings, etc.
When it’s 1 team 1 package you can get away with whatever when it’s many DB many teams many packages you are going to find it very hard to refactor change the scheme when you don’t have a central location for the DB abstraction
In the Node world knexjs.org fits the bill.
But there's still another step between a full-blown ORM and a query builder, where you still define the models and the relations between them, but fall back to a plain query builder for constructing queries. That way, you're not dealing with plain arrays, etc., but you're still essentially writing SQL. Like Objection.js[0], which is built on Knex.
[0] https://vincit.github.io/objection.js/
An ORM like Spring Data with JPA/Hibernate in the Java/Kotlin world works well for this. I write my db schema by hand, create an entity class with the same fields and an empty repository interface extending CrudRepository. This gives me simple CRUD access to the table with almost no code.
When I need complex queries I inject a JdbcRepository which allows me to query the DB using standard SQL and a RowMapper lambda.
Best of both worlds.
* you don't have to use HQL, if you pass `nativeQuery=true` in the @Query annotations you can write standard SQL instead of HQL.
* You can add an AccountRepositoryImpl Bean so you have a class in which you can inject dependencies like JdbdTemplate for full JDBC database access.
And FWIW, I've written some moderately complex apps over the years, and routinely use Hibernate / JPA (or GORM when using Grails) and I've never, or very rarely, encountered most of the problems cited by critics of ORMs. And Hibernate makes it easy enough to call raw SQL when you need to (and yes, I have needed that facility a few times).
By and large, for the vast majority of projects that I can imagine working on, my default starting assumption is almost always going to be "use an ORM" -- unless something about the situation strongly dictates otherwise.
While you initially might think that interfacing with the db is going to be very tedious and labor intensive, it usually turns out to not be that bad.
Battling the ORM to make it do what you want can on the other hand be very tedious.
When you have the SQL in the code it's much more obvious what the performance profile and potential concurrency problems are. That's very important to solve real problems which arise in almost all projects with some scale.
If I were to choose my tools, I would pick some sql-based schema versioning system, and potentially a simple data mapper for moving data back and forth between db records and entities.
But I would be very reluctant to use a full-blown ORM nowadays.
However, if the ORM gets in your way, either because of API complexity, performance issues or other reasons, and you start to "battle" it as you said, you immediately move out of that context and start thinking in a more data oriented context. That's where SQL comes in.
As I said before, I think it's a question of using the appropriate tool. I've written hundreds of applications using multiple different ORMs and just plain SQL—and I'll tell you I'll pick a good ORM over raw SQL for basic CRUD operations every time.
However, I'll give you that I'll pick plain SQL over a bad ORM any day.
I've been tasked with various database optimisation tasks over the years and ORM's have always gotten in my way when it came to understanding a queries indexing and locking and improving/fixing it. I always end up just looking at the logged SQL, fixing it in SQL and then trying to convert it back to ORM code, which has always been tedious. In my experience, in multiple otherwise talented teams, ORM's seem to encourage less-than-ideal database modelling and overfetching data because its too easy to just make an ORM class do it for you rather than really thinking about your data and the data model you need. This isn't ORM's fault of course, but rather the teams using them, but its happened so frequently that I don't think "discipline" or "better developers" is a valid answer. [1] I used to hate databases and dread working with them for the above reasons.
I recently started loving databases by working with Clojure and the HugSQL library. The reason I love this approach is that Clojure isn't OO, so there's no Object mapping: I deal with plain old data instead, in whatever format I make the query return it (I can then easily transform that data further if I need) and HugSQL lets me write (composable and parameterised) raw SQL. Its fantastic and has been super productive for me. I can think in pure data and model the database how it makes sense and still have a good understanding of the indexing and locking of my queries.
[1] As jandrewrogers said: "in almost every place I've seen them used they've become a way for developers to avoid understanding how databases work, inevitably leading to inexplicable data models and poor performance. In practice, ORMs tend to end up creating crippling technical debt" and this has been my experience too. Sure, you can use an ORM well, but in reality, this seems to happen so rarely that in my opinion, ORM's are a net negative to be avoided if possible. Saying that you just gotta use them right (when few people seem to actually do so) seems like a bit of a no true scotsman argument to me.
For me the biggest benefit of using an ORM is separation of concerns. I truly hate it when SQL and query building get mixed into controllers and other parts of the application. However, I also hate the layered complexity that the author talked about, which definitely means ORM's are not appropriate for all use cases! But if you don't use an ORM you need to be diligent with using some other pattern and code organization to keep your code clean and your concerns separated.
I worked at a consulting company for a number of years and we got immense value out of Symfony, which includes the Doctrine ORM. Many of our projects boiled down to custom Content Management Systems, so we got the huge benefit of being able to define the data model in code and not worry about any of the SQL while also not having very complex queries. So we got pretty much maximum benefit and minimal exposure to the down-sides.
I will say that these systems almost always wanted some detailed reporting, and that I usually got tasked with writing them. They always had some complicated queries and I almost always made use of the "raw sql" option to just write my own queries and spit out CSV. Which was always a much different use case than the rest of the application.
If the query itself is an object, I can chain it with other query objects and have a cleaner syntax.
Those are my usual reasons for liking ORMs.
The third approach is to safely wrap the database and its columns with code in a way that is composable.
In Python, SQLAlchemy has an ORM, but it is optional, and you can just work with tables and columns if you want.
This ORM or not ORM is the wrong question. Use an ORM to save you headaches where it's appropriate and use direct SQL when it's not.
[0]: https://github.com/coralproject/talk/tree/next
We use ActiveRecord a lot, and then have custom SQL queries using `find_by_sql` for very complex, optimized joins. It works very well. Rails gets out of the way when we need it to.
I cannot imagine work with SQL without query builder, which introduces type checks and conversions, and escapes strings when needed. ORM is an optional thing, but it is nice to have some layer that maps rows into structs and vice-versa. With dynamically typed scripts it doesn't matter: in any case you would get a hashtable (the only difference is a syntax used), but with compiled language and static typing I feel some uneasiness when using slow hash table mapping instead of blazingly fast struct field access. And the tooling can help to declare that structs statically.
Nobody has advocated writing "raw SQL with raw strings" in years.
The valid way of using Raw SQL is using prepared statements and parametrized queries.
This method will protect you from SQL injection, will handle most issues with type/conversions and the queries are cacheable, so it's fast too.
Parametrization is handled by the database itself (not the specific driver), so it is battle tested.
https://stackoverflow.com/questions/8263371/how-can-prepared...
It is an overstatement. Every time I look into some random PHP code I see there raw SQL with raw strings. Maybe it is just me being "lucky"?
By the way, the thread starter comment was mentioned it, I got phrase from it.
https://github.com/kennethreitz/records
https://github.com/StackExchange/Dapper
Like Haskell's Esqueleto[1] which lets you work with SQL code as Haskell functions and values. As an example from the documentation:
results in roughly: This lets you define new functions to use in your queries that use SQL functions. For example, you can define `isPrefixOf` for SQL using the SQL functions `char_length()` and `left()`. The use of the function will be expanded into the corresponding SQL code when the query runs. Like, could result in: [1] https://www.stackage.org/haddock/nightly-2019-05-07/esquelet...Even though I think this third way should be the "default" way to write an application now there's an awful lot of code left over from when an ORM was better.
I find Ecto to be the perfect balance of expressiveness, flexibility and clarity. In fact it's the single reason to have picked up Elixir.
I find Ecto lets me be as expressive as I need to with Queries, and handles the mapping into models very well for the 90% of easy cases that I need it to.
> from there it's just regular rails
That's the problem. Examples like this focus on the first few minutes of development. Not the subsequent years of maintenance.
adding an index:
adding a null constraint: https://github.com/localhostdotdev/bug/commit/3765237008c36c...Fair enough. I’ll concede that wasn’t a fair comparison to the OP. My point remains that examples focusing on the initial setup are uninteresting (to me) because that’s not where the bulk of the work is.
You're gonna have to pry the Rails from my cold dead fingers if you want me to maintain a web app's database abstraction layer long-term. No other tool works half as well as ActiveRecord. You could maybe convince me to try out https://rom-rb.org/ but only on a brand new project and only if Rails isn't appropriate.
If it's a project that wasn't built with Rails, I'm going to want https://github.com/jeremyevans/sequel if ActiveRecord is too much trouble to introduce.
>Not the subsequent years of maintenance.
I'd much rather maintain this example than a code base replicating the same behavior without an ORM.
1. I only use Django for small use cases where I rarely see any sort of scope creep. There was no real conscious decision about this, just kind of the way it happens.
2. The Django ORM is fairly mature and makes it quite easy to get a small project out the door.
I regularly use SQL directly at work and wouldn't want to try to replace any of it with an ORM even on new projects because we are all just used to working with it and we wouldn't want to add any complexity to our workflow without real, tangible benefits.
I would not, however, be opposed to working on a larger Django project (ORM and all) if the opportunity presented itself.
In fact, missing the database integration wouldn't even be that large loss. Sometimes I wonder if it isn't even holding the framework back.
(tl;dr: there is an impedance mismatch / leaky abstraction between relational data and OOP for all but the most trivial use cases, guaranteeing that whatever problems an ORM might solve will be traded for new ORM problems instead)
Not coincidentally ORMs have been doing great since that silly article was written so many years ago.
I've used sqlx[1] before, and it feels pretty idiomatic to Go. You tag your structs with their respective database columns, write up a query, and hand it to sqlx to perform the deserialisation of the data. I've also come across squirrel[2] too, though I haven't used it, it does look rather interesting.
[1] - https://github.com/jmoiron/sqlx
[2] - https://github.com/masterminds/squirrel
It's not an ORM per se, but it seems to occupy the sweet spot you're describing. It takes away some of the more tedious parts of using SQL, but allows you to still reason about what's happening under the hood without committing tons of documentation to memory.
Suits the language quite well, I think!
https://github.com/rspeele/Rezoom.SQL/
But I never got to use it at work, and thus lost interest. The biggest thing missing with it was that you lost type safety if you had to dynamically build a query. These days its main problem is lack of compatibility with .NET Core, which somebody else was working on for a while.
I think the flat row model for querying data from related tables is just not very good. I'd MUCH rather work with an object model like LINQ, where I get a list of Foos and each one has foo.Bars nested within it. As opposed to the output of a join where I get one row for each Bar, and parent Foo's columns are duplicated across each row.
Entity Framework does an excellent job of mapping the LINQ model to the SQL one, but since they really are different under the covers, it's easy to produce hefty queries. For example a LINQ "group by" frequently cannot be translated to a SQL "group by".
I tried to keep it simple and not deviate too far from straight SQL. But I still didn't want to force processing those damn row-based outputs onto the programmer, so I at least had to add notation for breaking out the columns of a top-level result set into an object hierarchy:
https://rspeele.gitbooks.io/rezoom-sql/doc/Language/Navigati...
In rezoom query composition part wasn't the strongest part. When for example some model needed additional join you had to modify N queries. This kind of composition could be done of course with dynamics but like you say, you lose biggest selling point: compile-time safety.
However these compositions are usually all known at compile time so you should be able to just build them before rezoom checks them. But out of the box compile time programming in F# is not there yet...
Type providers are such a cool language feature, but the way developing one works is too damn confusing. Especially when you try to publish one as an easy-to-use package and simple stuff like loading dependencies feels like uncharted territory. When it comes to my precious free time I hate, hate, hate figuring out packaging/deployment type stuff, I just want to focus on my code. So that's a big part of why I haven't done a great job maintaining it.
It's a spectrum to me:
raw (parametrized) queries ------ query builder + serialiation/deseralization ---------------- OOP-based ORM
One of the best libraries I've ever seen get this right was TypeORM[0]. It was easy to get started with, includes consideration for migrations, allows you to use both the query building and annotated-class approaches where appropriate, also allowing for use of the repository pattern if you're comfortable with that, and has pretty great support for lots of different backends (I've used postgres the most though). All of this from a F/OSS project (~2 years ago I was also involved in a C# project during the switch from .NET 4.x to .NET core/standard and was very very annoyed that things I could easily do with TypeORM weren't available/worked out yet in EF core at the time).
[0]: https://github.com/typeorm/typeorm
That said, you're right, the DataMapper pattern isn't quite identical, and it is a piece of fully OOP-based ORMs that I think are on the right side of the spectrum. I'd say that the DataMapper pattern fits in the middle -- I call it Serialization/Deserialization, and assuming the syncing of data is not constant (as in triggering action on another thread or something), then it can be anything from a function (ex. db::insert_user(user), user.write_to_db(db) or Adapter.save(obj)) to a dedicated object (Adapter.update(db, obj)).
If I had to try and point to a difference between the two, I'd say that DataMapper-ish or DataMapper-and-below levels of abstraction expose more internals by default (which is obvious) -- I find that ORMs on the right side of the spectrum I sketched up top try to never expose their internals, which quickly falls apart as soon as you step off the paved/well-traveled path.
[EDIT] I want to note that dealing with this syncing behavior in EF (Entity Framework) was really annoying. The constant management of a pool of objects that "reflected" the objects in the DB and had to be saved all together/flushed before they were manipulated or whatever was really annoying. That said, I am now aware that C# is one of my greatest weaknesses, so take this opinion with a grain of salt, it's entirely possible that I just didn't have the skill to do it right and keep it in my mind the right way. Just to make sure I'm not unfairly bashing EF I went and found a relevant SO post to show the kind of stuff I didn't like having to look up/deal with [1]. I'm picking on EF but this is likely an issue with any ORMs that handle it this way (w/ the object pooling & syncing).
[0]: https://en.wikipedia.org/wiki/Data_mapper_pattern
[1]: https://stackoverflow.com/questions/5462620/entity-framework...
One of the better balances I ever came across from multiple languages was actually ActiveRecord because of the Scopes functionality. The scopes let you abstract certain parts of queries and name them, reuse them, include parameters, combine multiples of them back together, switch parts out, etc. You can combine raw snippets with fully abstracted parts.
It's extremely flexible and probably the feature of Rails that I miss most when I don't have it.
https://guides.rubyonrails.org/active_record_querying.html#s...
Raw SQL can be dangerous, and given enough people and code somebody will eventually make a mistake (as is human) and introduce a vector for a SQL injection attack or some other DB specific vulnerability.
A good ORM can be a fairly effective layer of safety.
For example, we were converting from Sequelize, where we were catching unique constraint violations and responding appropriately, and we got quite confused that in TypeORM those errors never got thrown ... until we discovered that TypeORM decides unilaterally (and almost unbelievably) to replace your original record in that situation.
We have since ripped out all third-party ORMs and migrated to a system of typed helper functions and tagged templates (where all the typings are generated automatically at compile-time by inpecting the database). This is really rather wonderful — we now have full access to all sorts of goodies like native UPSERT, we know exactly what SQL we're going to get every time we touch the database, and yet the ergonomics are excellent, because everything going in and out of the DB is fully typed, including even column and table names in basically-raw SQL. A blog post (and perhaps eventually a library) is in the works ...
We're regularly receiving invalid SQL errors, which from a typed language/API seems particularly odd.
We're doing this exact same query on the exact same DB in ActiveRecord/Ruby and it's trivial to both write and understand.
Like others have said, I'd also love to see if you've written anything about this!
How do you deal with queries that are slightly formulaic & different or need to be assembled dynamically? do you use any query builder at all?
First step: Create for each table generic methods to insert, update,select all fields. Now at least memory and DB are consistent.
Next step: All these methods are mostly identical and there is a lot of commons-beanutils in there. So add an annotation to the relevant getters and generate most SQL statements on the fly.
Next step: Replace the incoherent commit/rollback mess with clear boundaries. Either it succeeds or it fails.
Next step: To stop the never ending reloads of data already in memory, SELECT data only when it's not already there. Flush all caches on commit.
All of this gives great results. Batch run time went down from 20+ minutes to a few seconds. Most weird crashes disappear. The need for manual data fixup after crashes evaporates. And then it hits me: I just wrote a custom ORM.
If ORMs were typically used by developers that fully understood the implications for the underlying database, there would be few issues with them and they would be a valuable tool. Unfortunately, they seem to be most popular with developers that are the opposite of that description.
I work with devs that have over a decade of Django experience. We use the ORM because it's just ridiculously easier to write queries on it and because SQL is impossible to compose without substantial problems.
90% of the code we write is CRUD and API endpoints. There's no reason to write SQL by hand except for the complex aggregations that comprise 5% of our queries, with luck.
Would you mind providing an example of what you mean?
- A good module system
- View-like variables
- Scoped singletons (related to the module system)
- First class functions
- First class symbols (that can be used as object names)
- Composable queries (related to the view-like variables and first class symbols)
If I wasn't on vacation I would have a few more fresh on memory. Different ones each day.
SQL lacks some power on negative and consolidated joins, forcing one to write more complex queries than necessary. It is this way for good reasons, because those are exactly the kinds of joins that indexes help you least and that most hinder parallelism, so they should be avoided if possible. On my experience, it's not a large drawback, but YMMV.
This is what ORMs and NoSQL have in common -- they're created by very smart people for very smart reasons, but the bulk of people I encounter advocating for them on the projects I work on are just intimidated by SQL and don't know or care what kind of burden they're taking on to avoid it. My opinion based on my experiences with ORMs is that to work successfully with an ORM you need to know SQL just as well, and probably better, than you would without it. Plus you need to know the ORM. So the typical adoption story I've seen is that people spend an inordinate amount of time struggling with their ORM and never lose faith that dealing more directly with SQL would be worse. The more they struggle, the more they're happy that they went with an ORM. "Gosh, who knew this would take six months. Imagine if we had to do it without Hibernate!" Uh, yeah, I can imagine writing ten tables and a dozen different joins on them without Hibernate. I can even imagine it taking less than six months and not needing an "ORM architect" who spends his afternoons trying to figure out why Hibernate isn't generating the efficient query he thought it would, and who spends standup telling scary campfire stories to the junior devs about the SQL horrors his Hibernate heroism is protecting them from.
It's true that my ORM experience mostly comes from a fairly corporate environment that I've steered clear of for years, and I've never seen a small team of strong contributors make use of an ORM. I think this difference in experience might explain why ORMs are so polarizing. I've also never found how fast I can bang out boilerplate SQL to be a limiting factor for a task at any scale, so there's probably a set of problems and situations I've never had to deal with where an ORM comes in handy.