News for hackers. Stuff that matters.
Re:12 requests per second: A realistic look at Python web fr... by divbzero on Thursday February 18, 2021 @10:56PM (#26189497)
I increasingly lean towards plain SQL over ORMs. It requires greater familiarity with SQL but I prefer that over greater familiarity with ORM-specific syntax that doesn’t translate across frameworks or languages. In addition, you can prototype new queries and profile existing queries in the database and copy-paste directly into your code.
Re:12 requests per second: A realistic look at Python web fr... by acdha on Friday February 19, 2021 @12:17AM (#26190029)
I favor a hybrid approach: use the Django ORM to define models, do migrations, auto generate the admin, etc. but don’t be shy about using the extension points (extra, raw, cursors) to put in an optimized query for a hotspot. You can get pretty far using the ORM but it’s really valuable to be able to be comfortable dropping down for things like reports or bulk processing.
Re:12 requests per second: A realistic look at Python web fr... by Jaxtek on Friday February 19, 2021 @02:03AM (#26190494)
I use ORM for Django's auth system alone.I write all other REST API queries in plain SQL.
Re:12 requests per second: A realistic look at Python web fr... by stiray on Friday February 19, 2021 @04:23AM (#26191159)
I agree with you. ORMs are at the end "converting" complexity of SQL capabilities into objects. It is perfectly normal that in best situation they will get at least as complex as SQLs but with features hidden behind annotations or be less efficient.

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.

Re:12 requests per second: A realistic look at Python web fr... by fn1 on Friday February 19, 2021 @01:31AM (#26190366)
In the java-world there are libraries like JDBI, which makes it possible to write interfaces with SQL-annotations and have serialization, connection setup etc. done for you:

    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();
    }
This is great because it's explicit. No hidden queries.
Re:12 requests per second: A realistic look at Python web fr... by shock-value on Friday February 19, 2021 @01:51AM (#26190442)
Another great option in Java is jOOQ, which lets you write type-safe and potentially composable queries such as:

  context
    .update(User.USER)
    .set(User.USER.NAME, userName)
    .where(User.USER.ID.eq(userId))
    .execute()
Re:12 requests per second: A realistic look at Python web fr... by alex-lx on Friday February 19, 2021 @04:09AM (#26191085)
jOOQ and its DSL is good, however IMO it's more readable using raw SQL (by using `context.fetchInto` and its variants) than to using DSL when deal with complex query.
Re:12 requests per second: A realistic look at Python web fr... by jwdunne on Friday February 19, 2021 @04:41AM (#26191264)
I’ve noticed this in other contexts too.

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.

Re:12 requests per second: A realistic look at Python web fr... by lukaseder on Friday February 19, 2021 @03:26PM (#26197637)
Why not just create views and query those with jOOQ, then?
Re:12 requests per second: A realistic look at Python web fr... by alex-lx on Friday February 19, 2021 @08:33PM (#26200529)
hi, @lukaseder, Thank you for creating jOOQ. I don't have much experience on views, and some called `Best Practice` forbid to use that because it's hard to maintain. What do you think about that.
Re:12 requests per second: A realistic look at Python web fr... by JulianWasTaken on Friday February 19, 2021 @05:30AM (#26191538)
In Python this would look like sqlalchemy's query layer (which is great).
Re:12 requests per second: A realistic look at Python web fr... by kevstev on Friday February 19, 2021 @01:08PM (#26195976)
Yeah in my last job, I used my manager power to overrule on using an ORM. In every company I have ever been in, some major outage has occurred due to a seemingly innocuous change causing a DB operation to go from O(N) to O(N^2)+ and its really hard to track down because things seem to work fine in a dev environment, but then can't scale.

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.

Re:12 requests per second: A realistic look at Python web fr... by zem on Thursday February 18, 2021 @11:39PM (#26189825)
for me the value of an ORM is not so much query synthesis as serialisation of object fields to db columns and vice versa
Re:12 requests per second: A realistic look at Python web fr... by jtolmar on Friday February 19, 2021 @01:07AM (#26190256)
Same here.

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.

Re:12 requests per second: A realistic look at Python web fr... by reader_mode on Friday February 19, 2021 @01:29AM (#26190354)
F# has this + static SQL query string checking at compile time using type providers
Re:12 requests per second: A realistic look at Python web fr... by PeterisP on Friday February 19, 2021 @09:49AM (#26193710)
"just generates an object type for a given SQL query's result rows"

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.

Re:12 requests per second: A realistic look at Python web fr... by aabbcc1241 on Sunday February 21, 2021 @01:18AM (#26211288)
Just hack that for Typescript over this weekend (: https://news.ycombinator.com/item?id=26211286
Re:12 requests per second: A realistic look at Python web fr... by jtolmar on Monday February 22, 2021 @12:36PM (#26227393)
Wow, that was fast! I'm impressed.

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.

Re:12 requests per second: A realistic look at Python web fr... by grosskur on Friday February 19, 2021 @02:17AM (#26190553)
sqlc can do this: https://sqlc.dev/
Re:12 requests per second: A realistic look at Python web fr... by jtolmar on Monday February 22, 2021 @12:32PM (#26227321)
This is the nicest one of these from the ones posted, I think it's the model to copy.
Re:12 requests per second: A realistic look at Python web fr... by branko_d on Friday February 19, 2021 @01:14AM (#26190289)
This!

We don't need ORM, we need OQM (object-query mapping)!

Re:12 requests per second: A realistic look at Python web fr... by jtdev on Friday February 19, 2021 @08:49AM (#26193088)
That’s a huge dependency to take on just for mapping and serializing, something that most languages provide in a standard library or base functionality of the language itself.
Re:12 requests per second: A realistic look at Python web fr... by specialp on Friday February 19, 2021 @11:22AM (#26194704)
I like ORMs for doing the things for me like making objects and converting dates and other data to DB ready form and vice versa. I do not like them querying. The premise with ORMs is that you should be able to query pretty much anything easily. The reality is that almost all of the time you are doing something you don't have many distinct queries. You have a few reads and writes that you have to do. Things that are easily done in SQL with full expressiveness and efficiency.

So now my pattern is to use for example ActiveModel in Ruby for the models, but not ActiveRecord for the persistence part.

Re:12 requests per second: A realistic look at Python web fr... by lordnacho on Friday February 19, 2021 @04:03AM (#26191042)
IMO both are required. I'm lucky in that I did a lot of plain SQL early on, and found ORMs later, but I think ORMs do cut out a lot of time for quick-and-dirty queries that end up not being the bottleneck. The problem arises once you find a bottleneck, you won't know how to optimize it if you haven't done a bit of SQL mucking about earlier.

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.

Re:12 requests per second: A realistic look at Python web fr... by kaba0 on Friday February 19, 2021 @06:17AM (#26191876)
Yeah, ORMs are specifically made for insert-heavy operations or very basic mapping of rows to objects. Analytical queries and the rest should be done with SQL.
Re:12 requests per second: A realistic look at Python web fr... by jtdev on Friday February 19, 2021 @08:53AM (#26193120)
I keep seeing some variation of: “ORMs are really intended for x” or “ORMs work best when y is true”. At some point we should take an honest look to determine if ORMs provide enough benefit to justify their existence. “I can talk to the database without knowing SQL” doesn’t cut it.
Re:12 requests per second: A realistic look at Python web fr... by kaba0 on Friday February 19, 2021 @12:33PM (#26195530)
Yes they do? They are basically available in every language, in some ecosystems there are multiple alternatives as well and there would not be that big of a market unless it is useful.
Re:12 requests per second: A realistic look at Python web fr... by paulmd on Friday February 19, 2021 @02:46AM (#26190651)
if that's your bag then you can still totally do that with an ORM, hibernate for example lets you just write a whole query in raw SQL while still getting all the benefits of eliding a bunch of boilerplate field copying, having "active" objects with ORM-level update/transaction management, etc.

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.

Re:12 requests per second: A realistic look at Python web fr... by arcbyte on Friday February 19, 2021 @09:29AM (#26193491)
I have never seen anyone, even the ORM "experts" in my jobs, save time with an ORM over what a junior engineer couldn't do faster with plain SQL.

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.

Re:12 requests per second: A realistic look at Python web fr... by deergomoo on Friday February 19, 2021 @10:19AM (#26194067)
> The reason is that sure, for your first 10 basic select queries, the ORM saved you half an hour

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).

Re:12 requests per second: A realistic look at Python web fr... by paulmd on Sunday February 21, 2021 @01:07AM (#26211232)
I do see the larger point though that ORM usage could still translate into problems elsewhere when you don't pull data you should, or you pull data you shouldn't, or pull data in a way you shouldn't.

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.

Re:12 requests per second: A realistic look at Python web fr... by alexchamberlain on Friday February 19, 2021 @03:22AM (#26190822)
We're gradually removing our ORM usage to avoid active objects. They look too much like VSTs, but subtly make foreign network calls when you least expect it, which makes it very hard to write effective, isolated tests or replace the database layer by something else, like a service call or a cache lookup.
Re:12 requests per second: A realistic look at Python web fr... by kaba0 on Friday February 19, 2021 @06:20AM (#26191906)
I’m sorry if it sounds harsh, but that is the mistake of the developers than. I think you would not allow anyone to write production code without knowing the language; it should be the same way with most libraries, especially ones having a big reach, like ORMs. Nonetheless, I have seen it countless of times in the team I worked in — so it is unfortunately really frequent.
Re:12 requests per second: A realistic look at Python web fr... by girvo on Friday February 19, 2021 @08:24AM (#26192871)
Plain SQL with a “hydration” system to objects is quite powerful. DataMapper, I believe is the term of art?
Re:12 requests per second: A realistic look at Python web fr... by jsmeaton on Thursday February 18, 2021 @11:08PM (#26189573)
https://github.com/charettes/django-seal Can help with these kinds of issues though I haven’t yet used it myself (I should!)
Re:12 requests per second: A realistic look at Python web fr... by wirrbel on Tuesday February 23, 2021 @04:59PM (#26243456)
SQLAlchemy expression language is cool stuff. not an ORM but also no string-templating ...