321–330 of 354 posts
Earlier quoted context omitted.
The reason given to use raw SQL is for the performance not the perceived code clarity.
If you never used a CTE, maybe… The reason to use SQL is to get what you need out of a database. Performance is orthogonal to that.
Earlier quoted context omitted.
The reason given to use raw SQL is for the performance not the perceived code clarity.
I’m not sure why you thought I meant code clarity and not performance? It’s clear in all cases the correct SQL query will be more performant. Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?
2. No, my point was that talking about code clarity was a distraction because to talk about lines of code as a determinant of performance is clearly wrong.
3. Tangentially, yes, if some behavior takes 100 lines of general purpose code to express, I would rather read it in the general purpose language than in SQL even if the SQL was fewer lines. It's hard to imagine why this would ever be the case though.
Earlier quoted context omitted.
It's 2026. CPU goes brrr. It's absolutely trivial compared to the query execution time.
Profile your code sometime; I assure you, with a properly indexed query, the actual query time is insignificant compared to everything else, unless your app is Rust, C, Nim, etc. The overwhelming majority of OLTP queries I see running on massive prod systems execute in < 1 msec. More time is spent in network RTT than execution, let alone the ORM parsing the result.
2. Query performance is dependent on the query and table size. They won't all be 3. Generally speaking, network RTT and query performance is going to dwarf time for ORM to parse the result
4. A raw SQL driver ALSO needs to parse the result if you want to do anything with the data in the general purpose language
Earlier quoted context omitted.
With a few extra lines and mapping objects to classes this can be done. To have all this ease of use you give up so much in performance. Most apps and companies never get to the point where performance matters that’s why we have ORMs.
> you give up so much in performance. Not really. ORMs (memory) and databases (disk) are distant by multiple orders of magnitude performance wise. Skipping the ORM to shave off some cycles is akin to haggling over a few pennies on your thousand dollars bill.
Earlier quoted context omitted.
> ORMs are good for management and simple CRUD cases I for one think that "simple CRUD cases" is bullshit, those applications don't exist. In practice, System-of-Records systems are rare. (and should be, their value are inversely proportional of how many of those you have in your overall system). Because if it was "just simple CRUD", one would use the database directly? Databases are already capable of handling CRUD…
No. If you have a simple line-of-business app, writing Django/Rails models is FAR easier than the equivalent SQL. Even if you think that maintaining your domain model is easier in SQL (it’s not, for most full-stack engineers), the extra capabilities you get from an ActiveRecord framework such as full-stack admin pages, free migrations, etc. win overall. I can believe that the gap is closing with the “api for your Pos…
The naive "here's a row-level view of the database records" that things like ActiveRecord/ActiveAdmin give you by default are entirely inappropriate for any line of business administrative interaction. Line of business admin sites should be workflow based and focused on surfacing specific information needed for processes outside of the admin site itself. Non-developer staff should not be expected to interpret the state of rows and relationships among the tables.
Earlier quoted context omitted.
A query builder is not an ORM. ORMs build queries for you, but a query builder does not need to be an ORM.
ORMs do not inherently build queries. They only provide data transformations between relations (i.e. rows and columns) and objects. Hence the literal name: Object relation mapping. You can absolutely have ORM without query building just as much as you can have query building without ORM. Sometimes ORMs and query builders are combined into a higher order system, such as what is described by the active record pattern.…
User {
name
friends: List
posts: List
}
I now do:
user = get_mapped_user()
for post in user.friends[0].friends[0].posts {
...
}
What happens when I access `.friends`?
Does it give me an empty list, because I didn't ask for it?
I am not aware of anything that calls itself an ORM which merely does:
user: User = map_from_relational_to_user(query_user())
ORMs, at their core, try to abstract away something like `user.friends[0].friends[0].posts` more or less into some underlying queries against a relational database. The main distinction between them being in the availability and first-class nature of the escape hatches when this operation inevitably becomes slow.
Earlier quoted context omitted.
It's not that your domain is different, it sounds more like you don't know how to use ORMs. ORMs don't have to manage migrations, they don't have to even write into the database. When dealing with a bad database design, it can be a legitimate tactic to use ORMs in read-only mode and have writes still as hand-rolled SQL. You can do database-first ORMs, as well as code-first, where the database design is king, not the…
That doesn't sound at all like any ORM I've ever used. I've struggled in the past because The ones I've used are actively hostile to laying out data in the database in a way not proscribed by the ORMs philosophy. Heck of the ORMs I've used, one didn't support parameterized joins and the other didn't support joins at all. --- It's not usually a DB guy gatekeeping, it's that multiple apps use the same database so layou…
Earlier quoted context omitted.
> Document behaviors. Model the system. Let the system decide what data storage it requires. Counterpoint: force the system to use an RDBMS to store data in properly normalized schema, because it’s the only thing guaranteeing that the data will continue to exist as you expect.
This implies nosql data stores are not ACID capable. Mongo is fully capable and DynamoDB is mostly capable. I would challenge you to look at event driven architectures, CQRS, event sourcing, and how to implement and leverage read models. It will expand your architecture toolkit.
I also note that in their FAQs [2], they erroneously state:
"MongoDB’s data modeling best practice suggests storing related data together in a single document using a variety of data types, including arrays and embedded documents. So, a lot of the time, ACID is not required as it is a single-document transaction."
Whether or not you're operating on a single document has nothing to do with its ability to meet durability guarantees (or consistency, for that matter).
NoSQL databases make tradeoffs for performance, and making the lives of devs easier in the short term. That's fine, if and only if you accept what you're losing, and document it for others who may not be aware. If at any point you can have your application get a write ack'd and subsequently lose the write, you do not have a durable data store, and you do not have ACID compliance. Whether that's the fault of the DBMS, the operating system (Postgres' fsyncgate), or hardware (drives lying to the OS about the write's durability without the benefit of PLP) is irrelevant – you have to understand the entire chain to make those guarantees, or at the very least, trust your upstream provider to have understood it for you and made the correct decisions.
0: https://www.mongodb.com/docs/manual/core/journaling/
1: https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.ht...
2: https://www.mongodb.com/resources/products/capabilities/acid...
Earlier quoted context omitted.
I have list of issues with SQL. Not composable. Unable to detect query errors at compile time because the schema is only loosely coupled to the code base. And as you yourself point out, SQL is not standardized, which is also terrible and leads to things like Oracle vendor lock in. And frankly this list hasn't changed in 30 or maybe 40 years now. And DBA's were so notoriously egregious that Martin Fowler made his "NoD…
> as a stepping stone, and maybe as a useful tool to help people understand SQL itself. But that is not what ORMs are. They teach bad habits that make SQL harder to understand, not easier, because the power of SQL depends on good data modelling. Perhaps the worst habit is treating the database as subservient to the application code. This assumption comes naturally to many programmers. In most programming contexts, fi…
I am no SQL God by any means, but I am quite proficient. Despite my SQL skills, I cannot give up EF Core. Even when using other languages, I just pine for LINQ/EF Core. It's truly the best ORM in my opinion. Also, even if one does not want to use the LINQ or the Query syntax (I forgot what it was called), the ability to execute SQL is also still a game changer.