SQL is readable (at least far more than 20m lasigna of boilerplate objects decorated with tons of annotations googled from internet where no one really know what they do) and you KNOW that if you have optimized the database structure (and filesystem, and network,... :D) and SQL statement you will get peak performance while with ORMs you are on constant hunt what else can you turn on while they are far too huge to read their code.
And they are becoming quite absurd after they "mature" and begin adding corner cases that no one has thought about when they were a starting project.
public interface UserDao {
@SqlUpdate("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)")
void createTable();
@SqlUpdate("INSERT INTO user(id, name) VALUES (?, ?)")
void insertPositional(int id, String name);
@SqlQuery("SELECT * FROM user ORDER BY name")
@RegisterBeanMapper(User.class)
List<User> listUsers();
}
context
.update(User.USER)
.set(User.USER.NAME, userName)
.where(User.USER.ID.eq(userId))
.execute()For small queries with straight forward joins, a query builder is nice and readable.
But for larger, more complex queries, I found putting the query into its own file was best for readability.
And to me, SQL is the easiest language to learn and read of all, and while I understand doing basic marshalling of SQL records into objects is tedious, its really not hard at all, and it just saves so so much heartache down the road.
The exception might be very basic CRUD apps that are meant to be used by third parties and want to support multiple backends like mysql/postgres/whatever. There might be other exceptions as well where you are just trying to prototype/find market fit, but for a typical project that you know is going to be used in a real way, the risks just don't outweigh the benefits IMHO.
Some of the team griped a bit, but I absolutely think it was the right move in hindsight. At least once a year I would hear about an outage due to an ORM gone wild, and these outages were usually prolonged by the fact that things function, and then there is finger pointing between the DB/DBAs and the app developers, etc.
I'd love to have a tool that just generates an object type for a given SQL query's result rows, and a function signature for its query parameters.
We don't need ORM, we need OQM (object-query mapping)!
For a similar purpose in Python I generally use psycopg with namedtuple resultsets; the namedtuple 'records' do what I need for the returned data and are reasonably efficient.
If you want to keep going forward with it, I'd recommend working on making it clearer what the examples do, and looking at sqlc for Go for inspiration. Of the existing versions of this people replied with, that one had the clearest API, or at least clearest explanation on its landing page.
So now my pattern is to use for example ActiveModel in Ruby for the models, but not ActiveRecord for the persistence part.
Also, the big thing is you won't know how to translate to other ORMs if you don't know SQL. Eg once you know that you want an index, it's a matter of a web search to find out what the syntax is in your ORM. But if you just started with ORM, you might not realize that kind of thing is part of how it works.
plus it means you don't have to write all the dumb "select * from books where bookName = :bookName" code that obviously can be handled trivially by the ORM. You just use SQL the places where it makes sense.
the ORM hate always strikes me as a little misplaced because of this - you can always write SQL where it's appropriate, in any decent ORM. And you can write bad queries in SQL too. Obviously very complex queries are maybe better reserved for raw SQL but it seems like a lot of the hate comes from maybe less experienced engineers getting in over their head with complex work items because ORMs "make it easy", and that is going to happen with raw SQL too if you throw those same engineers at those work items.
ORMs aren't inherently that heavyweight, I see people complaining here about SQLAlchemy and as a Java developer I don't have performance concerns about hibernate. That sounds to me like a Python problem and a "this specific ORM isn't performant" problem, not ORMs being bad as a whole. And if you really want a "just load the data for me and do nothing else that incurs a performance hit" approach then you can use stateless objects and it's just a wrapper around the DB to load and transform the data for you and/or do a raw, whole-object update back to the DB.
The reason is that sure, for your first 10 basic select queries, the ORM saved you half an hour. Then you got to that complicated join and had to resort to looking up archane syntaxes and prototyping attribute quirks for an hour, when the junior guy got the whole thing written in 15 minutes of trial/stackoverflow/error in a SQL prompt.
Then, even when the "expert" did get it working, guess who is going to be the one looking at it again when trying to figure out production support issues? The junior guy, who is now clueless and has to spend two hours to figure out what this crazy ORM mess does here. The better alternative was just to have the SQL there ready to go so it is well understood and can be ran against the production database or a test database to reproduce the issue. No questions whatsoever.
I have seen this over and over again, more than a statistically relevant number of times.
Maybe we work in very different fields, but "basic select queries" makes up 90% of what I need to fetch from the database.
If I'm working on a forum and I want to load user 123, with all their posts, all the awards each post has, and the count of friends the user has, with Eloquent (Laravel's ORM), I could do:
`User::with('posts.awards')->withCount('friends')->findOrFail(123);`
That would return me a User model, with a collection "posts" containing a list of Post models, each with a collection "awards" of Award models, and a field `friends_count` with the number of friends. It would run three queries: one to fetch the user, one to fetch the posts, and one to fetch the awards. Depending on how I have configured my models, I can have things like dates automatically hydrated to DateTime objects.
Compare that to plain SQL queries; I would have to fetch the users, including manually writing the subquery for the friend count. Once I had those users I would then have to fetch the posts, and then again for the awards. If I want them in a hierarchy like the ORM example gives me, I then need to loop through each set of records and manually stitch them together. Not difficult, but super tedious.
Sure a complicated join is better done with as little magic as possible, but Eloquent exposes functions for adding subselects, joins, etc. in a way that just reads like SQL (and maps 1:1 underneath).
But the thing is, a lot of things aren't really on the hot path and optimizing them isn't worth a ton of time. Like yeah so what if this web request that only gets used 2% of the time makes 5 extra database requests that it shouldn't, on a single data item. Not gonna tank the overall program.
I guess I'd accept that it's important to be aware of what you're pulling, regardless of whether that's automagically when a proxy object sees it needs to be lazy loaded, or explicitly in a query. Throwing junior developers on performance-critical paths is going to be a problem anywhere and on any DB access layer.