15 ms·

"this is mostly because i relied on a SQL ORM which in short is a tool that makes writing SQL easier to pick up and faster to develop. the biggest downside is t

"this is mostly because i relied on a SQL ORM which in short is a tool that makes writing SQL easier to pick up and faster to develop. the biggest downside is that it might execute 50 queries to your database to get a list of information, when it probably only needs 1, which will cause slowdown." I appreciate this honesty. Listen to this old man's advise: learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life. Then just write SQL directly. I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls. You can then become proficient in trying to reason and predict about what your ORM calls do in the actual database, but when you're several joins deep, this becomes near impossible. At which point you become the ORM, and might as well just write SQL.

andrewstuart 4y ago
These days I build applications that actually use SQL. Typically a single statement to get the job done for any query.
akprasad 4y ago
If writing SQL directly, what process do you use to update your queries during schema changes? Do you rely on a test suite to catch errors then update queries by hand? Are you using compile-time checks through libraries like sqlx [1]? [1]: https://github.com/launchbadge/sqlx https://github.com/launchbadge/sqlx
- willio58 4y ago
  This is exactly why I find no solution fits all here. For me, I use an ORM as a catch-all and then for certain applications I manually write the queries. It's best of both worlds, and I know what components in my app have custom queries so I can test against them.
- tetha 4y ago
  Sorry to butt heads there, but what ORM does automatically handle schema changes? I have so many teams with highly abstracted ORMs telling me that no-downtime schema changes are impossible, no matter how trivial the changes are, or would be in e.g. Hibernate. And the only team capable of zero-downtime schema changes uses a minimal DSL to SQL lib.
  - ipaddr 4y ago
    This problem seems foreign to me. Are your teaming not using Laravel? If not maybe it's time to upgrade. https://laravel.com/docs/10.x/eloquent https://laravel.com/docs/10.x/eloquent
  - akprasad 4y ago
    I don't know of any ORM that handles schema changes automatically (edit: for the most strict definition of "automatically" -- I've used ORMs that generate migration files and am generally happy with them). My experience here is quite limited, so I'm inclined to defer to yours. With that caveat, I find that ORMs play more nicely with my IDE (e.g. I can rename a column name and automatically update all call sites) and often raise early warnings if their declarative classes are out of sync with the underlying table schema.
  - travisjungroth 4y ago
    There are two parts to the schema change, the table changes (migrations) and the code changes. I think the person you were replying to was asking about the code part. Every ORM I've used or looked at[0] does that automatically in the sense that you make a change to a model and all queries generated by that model will now have that change. Depending on the change they could break, but the point is the changes propagate. If you're not using an ORM and are just using string templates, this won't happen. There are query builders, too, which are sort of in between. [0] Django, SQLAlchemy, Active Record, Piccolo, Tortoise, SQLModel, peewee
    - akprasad 4y ago
      ^yes, that's indeed what I had in mind, and you've articulated the position very clearly.
- eyelidlessness 4y ago
  Finally something that bucks my instinct that integration tests are a waste of time. If I’m deploying a SQL query, everything that gets in or out gets thorough automated testing. Regardless of your attitude on the subject, directly interfacing a database is business logic. Test it like you would anything else that interacts with an external dependency. If you made a schema change and it broke something, you’ll either get test failures, or learn where your coverage is incomplete, or learn where you don’t care about a particular failure mode. Normally I’d prefer testing more isolated units, but if it’s your own data access, just write the integration tests. They’re at least implicitly part of your “unit” anyway.
- phendrenad2 4y ago
  I recommend taking a step back before making a backward-incompatible schema change. Do you really need this? Chances are, it's just faffing about out of a sense of neatness and orderliness and you don't actually need to break the schema.
jw1224 4y ago
> I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls That doesn’t sound like ORM… More like an N+1 problem. Eager-loading makes N+1 more likely with ORMs, but it’s easy to avoid when you know what to look for. ORMs are designed to reduce querying, not increase it a thousand-fold :)
- eyelidlessness 4y ago
  N+1 is a common problem with many ORMs, and a classic example of the object-relation impedance mismatch. Even the notion of eager vs implied-lazy loading suggests N+1, just spread out over time. Granted that might be optimal for a whole lot of use cases! But it’s definitely not optimal for a use case where you need a join upfront and your ORM does it in memory. Also granted many ORMs can handle this in a lot of general cases if you know how to use them, and know their limitations. But they’re inherently highly dynamic, and inevitably deoptimize for some cases where just querying the database directly will be much more effective. That’s not even an admonishment to “learn SQL” as is the common retort, it’s just the generic “abstractions are leaky and sometimes it’s better to bail out a layer or more”.
  - noduerme 4y ago
    Upfront: I really dislike ORMs. They're an unnecessary abstraction and a performance headache as an extra layer of middleware to worry about keeping up to date. I'd never use one in production. But we do always need a clean way to handle CRUD between apps and DBs, that ideally doesn't require custom calls for each and every data view. Here's the thing: Probably 90% of CRUD can be handled with generic updates and inserts. Raw JSON reads can be passed back to the client... let the client know how to cast or structure those to complex data types. The rest, that the server has to manipulate, can be cast / structured using the same classes the client uses, if in node. The actual meat of really complex reads or really optimized writes should always be exceptional and done by hand.
- BiteCode_dev 4y ago
  Right. In fact any ORM worth its salt will have methods specifically designed to let you address the N+1 problem easily. E.G: django "select_related" Not to mention they will will have ways to use raw SQL through the lib infrastructure, which is still better than querying SQL manually, and give you full control of the query. Also wordpress shows that you can write manual SQL and still have terrible performances because your application is badly structured, which an ORM helps with. In the end, I don't think there is less trap with raw SQL, the traps are just different. It's not a matter of "better".
chillfox 4y ago
The core problem with ORMs is that you end up having to learn the ORM on top of learning SQL and the particular database. It ends up being like working with the database through an opaque indirect layer.
droobles 4y ago
What do you think of "micro ORMs" like knex.js, or Dapper for C#? I personally agree with you and my personal projects just use stored procedures, but these micro orms have always piqued my interest since they're just a SQL abstraction for building single queries.
- jiayo 4y ago
  I would consider those "query builders" rather than fully fledged ORMs.
  - notfed 4y ago
    Yeah no. A micro ORM still expects you to write the full, raw SQL. The raison d'etre for a micro ORM is to capture the results of that SQL query into a simple, flat collection of objects in a way that has a better UX than your framework's default behavior. Micro ORMs are the perfect middle ground for debates like these.
- skeeter2020 4y ago
  This is typically as far as I go with an ORM, CRUD operations on single tables that give you some type safety at compile time. If you need more complex data shapes I usually create a view or occasional stored procedure but that's about it. I use Entity Framework in my day job and do like the migrations though...
- dgb23 4y ago
  I personally like those. They are qualitatively different from ORMs. They give you three things: - sensible ways of composing queries beyond direct string concatenation - safe parametrization - return results into usable data structures (not objects) None of these things carry the problems of ORMs. They don’t impose a paradigm that isn’t SQL. They simply let you interface with SQL in an ergonomic manner.
lmm 4y ago
> learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life. Exactly the same is true of ORMs. I find most of the people who advocate "just use SQL" have a bizarre aversion to applying the same learning effort to their ORM. > I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls. That kind of problem happens with regular SQL all the time, and the tools to test/investigate it are less available.
- smallnamespace 4y ago
  > have a bizarre aversion to applying the same learning effort to their ORM. SQL is a lingua franca while an ORM is specific to a stack. If you know that you will always, say, be using SQLAlchemy on Python, then learning it well might be a good investment, but I don't find learning multiple ORMs to be a good time investment over just learning SQL. Most every stack has good, lightweight query builders.
- deleted 4y ago
  [deleted]
- jjoonathan 4y ago
  > bizarre aversion to applying the same learning effort to their ORM Oh, sure, instead of just learning SQL I'll just learn SQL, the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated, and then I'll commit a patch to the open source project to fix the undocumented internals and shepherd a custom dependency for 6 months while it gets into a numbered release. Then I'll do it all over again when I switch stacks and have to learn a completely new ORM. Or I could just use SQL.
  - ttymck 4y ago
    How do I map my complex join into a list of structs with embedded associations? I'd probably need to write a good bit of code just to unmarshal the data, right? How are you doing this every time you write a new query?
superb-owl 4y ago
ORMs are great for avoiding boilerplate and adding some typesafety. But once you start doing complicated things like JOINs, it's better to drop into raw SQL. > Listen to this old man's advise: learn SQL properly. It's not that hard. I couldn't agree more with the first half, or disagree more with the second :) Once you're operating at scale, it takes a lot of fine-tuning, know-how, experimentation, and reading docs to get things moving efficiently.
- phendrenad2 4y ago
  What are some examples of fine-tuning or trial-and-error you might need at scale?
  - superb-owl 4y ago
    Postgres has a ton of parameters that can greatly affect performance. I've used https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/ to help w/ that. In terms of schemas, where you put your indexes matters a TON. Indexes make reads faster and writes slower (to oversimplify). Figuring out where to break out a separate table vs keep everything in one table is also a common issue.
    - phendrenad2 4y ago
      True, all of these would still be an issue with ORMs.
partly_cloudy 4y ago
who you callin old man i'm still in my 20s!
- waste_monk 4y ago
  One foot in the grave. Practically a fossil.
xarope 4y ago
Better yet, learn stored procedures, then call them just like any API with a well established set of inputs and expected return typed outputs. You'll thank me later once you decide to split your DB from your app server.
- noduerme 4y ago
  It's been at least 12 years since I had a DB on the same server as the app or service that called it... I can't fathom why anyone would do that these days. But either way, the stored procedures would work just as well. The issue you're alluding to (I think) is the time to set up connections for a dozen calls between the backend and the DB if they're on separate servers. But that's actually less of a bottleneck now than it used to be, with persistent pooled connections and most things hosted in the same VPN zones. I'm not arguing for ORMs... but appside processing [edit: "joins"] can still be appropriate if you're dealing with a monolithic DB and don't want certain giant queries or procedures to block rapid reads. The point is more to understand your infrastructure, and ORMs obfuscate it to a point that makes you irresponsible as a developer in some cases. Also, with lateral joins and windows in mysql 8 you can really have control over whether you prefer your execution plan to loop or scan. Which takes away most of the rest of the argument for appside processing.
manv1 4y ago
It's hard to explain to developers why an ORM isn't really that great of a solution....but at a high level: * your database is the core of your app. Abstraction implies that you don't care about the details of your data store, which eventually will lead to two major problems: 1. When things are slow you won't be able to debug it, because you're abstracted away your database and don't understand what queries your ORM is using to do things, and 2. when your ORM migration fails you won't know what to do, because you don't understand your database. In general this happens with every ORM product, ever, because you didn't put in a constraint initially because you didn't know what that meant and then you did later, which caused the migration to fail because your constraint is being violated. Or the migration fails because the tool uses some attribute and not others for drift detection, so it tries to delete your table/database because it's detected drift. * the way you deal with data in code and in a database are different. Code iterates over objects. If you just use your ORM naively you'll end up doing some ridiculous number of selects because in code it looks like you're just iterating over objects...when in reality the ORM is doing a select/join for each one of those objects. And if you start getting more complicated there are structures that are just harder to do with ORMs...because you have to map what you're trying to do in SQL to the way your ORM works. At that point why not ditch your ORM? SQL isn't really that hard...but thinking about how do queries is harder than you would expect. It's almost an order of magnitude faster to do joins in the database than to join stuff in-code...but that also assumes that your schema isn't screwed up, it has indexes, etc. Lastly, a lot of ORMs don't put indexes on columns for some reason, which kills performance. You'd think that would be a detail that would be abstracted away for you at the ORM level, but it isn't. I mean it knows what you're using to select data, so it should auto-create indexes for you, right?
- selcuka 4y ago
  > I mean it knows what you're using to select data, so it should auto-create indexes for you, right? Curious, how does it know what you're using to select data?
  - manv1 4y ago
    Because you're using the ORM to do selects. I'll give you an example: prisma has a "find/findMany." As part of the build process it should examine your usage and put indexes in the column that you're using in find/findMany Indexes are an implementation detail - but to use RDBMSs successfully you need indexes. ORMs try to hide those implementation details, but without those kinds of details you get bad performance. Prisma just fixed another problem it had with transaction isolation levels. To even understand the problem you need to understand SQL, how databases work, and how each database implements that. It's another important implementation detail that's abstracted away...until it bites you in the ass. Prisma also finally allowed you to use database-specific types instead of just using varchar(191), which was its default for strings. I'm picking on prisma because I happen to be using it right now. There are good things about it, like the way it does migrations and handles schema changes. But there are times its abstractions get in the way, like when you're trying to do aggregated group by (which is easier in SQL than it is in prisma). And its migrations often fail, and to understand why you really need to understand SQL. Hibernate was worse, because it provides a transparent object-level backing store.
ookblah 4y ago
Honestly, I started on the ORM hate train when I first started and wrote my own queries in an effort to be "performant", but my frankenstein of writing direct queries and abstracting code to be reusable became basically a super shitty ORM that was a nightmare to maintain for a large codebase lol. I don' think there's anything wrong with it and for 90% of CRUD actions it's what you should choose. By the time you hit performance issues you've probably thought of something else anyway.
- layoric 4y ago
  I am the same, the advice of "learn SQL" is still very important. Most ORMs have the escape hatch of just mapping results of a straight SQL query to an expected structure (thinking of typed languages like C#/Java here). 95% of the time I can just use the ORM to make simple queries in a easy to maintain way for your application. For the 5% of queries that are either complex or can't be easily represented via your ORM of choice, declare the expected type response and use that escape hatch to write your performant SQL. I've been on projects where the team has re-invented an ORM organically, and I've never seen it go well. Likewise with projects that are ORM 'purest', bending themselves over backwards to use an ORM for a query that can't easily be represented by whatever query syntax it has. This is why I still think 'lite' weight ORMs are the best of both worlds since they usually have a pretty good experience for common/easy queries but then when things get tricky, it is best to just use SQL and map to an app language data structure for the results. I mostly use OrmLite [0] in dotnet and have found it has a balance that done well by me for years (note I now work at ServiceStack who built OrmLite). [0] https://docs.servicestack.net/ormlite/ormlite-apis#query-examples https://docs.servicestack.net/ormlite/ormlite-apis#query-exa...
  - partly_cloudy 4y ago
    "95% of the time I can just use the ORM to make simple queries in a easy to maintain way for your application. For the 5% of queries that are either complex or can't be easily represented via your ORM of choice, declare the expected type response and use that escape hatch to write your performant SQL." site creator here - yeah this is the approach i'm taking now. the ORM is useful for sure and there's still a benefit to using it, but anything that needs to read from a few different tables, i'm definitely going with raw SQL
uoaei 4y ago
Honestly, ORM is an unnecessary layer most of the time. You can have database schemas, and you can have software schemas, and they don't necessarily need to coincide as long as your database schema allows efficient queries. The rest of your relational-manager-logic can be embedded in the post-processing methods you apply to the query results.
fulafel 4y ago
An alternative to learning SQL is to use a non relational database, that has more of a impedance match to your host language to start with. (Eg XTDB or another Datalog DB in case of Clojure)
closeparen 4y ago
I'm happy to write my own SELECT queries, but it sucks to be manually schlepping row[0], row[1], etc. into the fields of my domain entities, or making sure I provide the parameters to the parameterized INSERT statement in the right order. ORMs usually let you drop down to SQL as desired, and I do that when I have a nontrivial query to express, but CRUD plumbing is nice.
- ilamparithi 4y ago
  Not sure which programming language you are using. For Java, I find Spring JDBC templates with Row Mappers more productive than ORMs. DataClassRowMapper will automatically populate the domain entity.
  - closeparen 4y ago
    Go, Java language-level 8, Python. All have an approach, usually with some third party library, but the bone stock database drivers deal in field indexes. And people consider those libraries "ORM" whether or not they really are technically.
danmaz74 4y ago
I'm pretty proficient with SQL, and I completely agree that you need to know it well if you use a relational DB for something that isn't trivial, but I still love using ActiveRecord. In most web applications I worked on I needed to manually optimise some queries, either massaging AR a bit or directly writing SQL when necessary, but I would say that's much less than 1% of all interactions with the DB. In all the other situations, using AR saved me a lot of time.
freetonik 4y ago
In my limited experience, the best thing about ORM isn't the querying, but the mapping to objects. This is why I really like ActiveRecord's `find_by_sql`[1], which allows to write a raw SQL query and get an array of objects. [1]: https://guides.rubyonrails.org/v3.2/active_record_querying.html#finding-by-sql https://guides.rubyonrails.org/v3.2/active_record_querying.h...
petervandijck 4y ago
Learn SQL and use the ORM is usually the correct answer. Let it print out the SQL queries and how long they take (ORMs do that out of the box), then check for indexes and other standard issues. If there’s some unusual complex query to be written, sure, write that by hand.

andrewstuart 4y ago

These days I build applications that actually use SQL. Typically a single statement to get the job done for any query.

akprasad 4y ago

If writing SQL directly, what process do you use to update your queries during schema changes? Do you rely on a test suite to catch errors then update queries by hand? Are you using compile-time checks through libraries like sqlx [1]? [1]: https://github.com/launchbadge/sqlx https://github.com/launchbadge/sqlx

willio58 4y ago
This is exactly why I find no solution fits all here. For me, I use an ORM as a catch-all and then for certain applications I manually write the queries. It's best of both worlds, and I know what components in my app have custom queries so I can test against them.
tetha 4y ago
Sorry to butt heads there, but what ORM does automatically handle schema changes? I have so many teams with highly abstracted ORMs telling me that no-downtime schema changes are impossible, no matter how trivial the changes are, or would be in e.g. Hibernate. And the only team capable of zero-downtime schema changes uses a minimal DSL to SQL lib.
- ipaddr 4y ago
  This problem seems foreign to me. Are your teaming not using Laravel? If not maybe it's time to upgrade. https://laravel.com/docs/10.x/eloquent https://laravel.com/docs/10.x/eloquent
- akprasad 4y ago
  I don't know of any ORM that handles schema changes automatically (edit: for the most strict definition of "automatically" -- I've used ORMs that generate migration files and am generally happy with them). My experience here is quite limited, so I'm inclined to defer to yours. With that caveat, I find that ORMs play more nicely with my IDE (e.g. I can rename a column name and automatically update all call sites) and often raise early warnings if their declarative classes are out of sync with the underlying table schema.
- travisjungroth 4y ago
  There are two parts to the schema change, the table changes (migrations) and the code changes. I think the person you were replying to was asking about the code part. Every ORM I've used or looked at[0] does that automatically in the sense that you make a change to a model and all queries generated by that model will now have that change. Depending on the change they could break, but the point is the changes propagate. If you're not using an ORM and are just using string templates, this won't happen. There are query builders, too, which are sort of in between. [0] Django, SQLAlchemy, Active Record, Piccolo, Tortoise, SQLModel, peewee
  - akprasad 4y ago
    ^yes, that's indeed what I had in mind, and you've articulated the position very clearly.
eyelidlessness 4y ago
Finally something that bucks my instinct that integration tests are a waste of time. If I’m deploying a SQL query, everything that gets in or out gets thorough automated testing. Regardless of your attitude on the subject, directly interfacing a database is business logic. Test it like you would anything else that interacts with an external dependency. If you made a schema change and it broke something, you’ll either get test failures, or learn where your coverage is incomplete, or learn where you don’t care about a particular failure mode. Normally I’d prefer testing more isolated units, but if it’s your own data access, just write the integration tests. They’re at least implicitly part of your “unit” anyway.
phendrenad2 4y ago
I recommend taking a step back before making a backward-incompatible schema change. Do you really need this? Chances are, it's just faffing about out of a sense of neatness and orderliness and you don't actually need to break the schema.

jw1224 4y ago

> I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls That doesn’t sound like ORM… More like an N+1 problem. Eager-loading makes N+1 more likely with ORMs, but it’s easy to avoid when you know what to look for. ORMs are designed to reduce querying, not increase it a thousand-fold :)

eyelidlessness 4y ago
N+1 is a common problem with many ORMs, and a classic example of the object-relation impedance mismatch. Even the notion of eager vs implied-lazy loading suggests N+1, just spread out over time. Granted that might be optimal for a whole lot of use cases! But it’s definitely not optimal for a use case where you need a join upfront and your ORM does it in memory. Also granted many ORMs can handle this in a lot of general cases if you know how to use them, and know their limitations. But they’re inherently highly dynamic, and inevitably deoptimize for some cases where just querying the database directly will be much more effective. That’s not even an admonishment to “learn SQL” as is the common retort, it’s just the generic “abstractions are leaky and sometimes it’s better to bail out a layer or more”.
- noduerme 4y ago
  Upfront: I really dislike ORMs. They're an unnecessary abstraction and a performance headache as an extra layer of middleware to worry about keeping up to date. I'd never use one in production. But we do always need a clean way to handle CRUD between apps and DBs, that ideally doesn't require custom calls for each and every data view. Here's the thing: Probably 90% of CRUD can be handled with generic updates and inserts. Raw JSON reads can be passed back to the client... let the client know how to cast or structure those to complex data types. The rest, that the server has to manipulate, can be cast / structured using the same classes the client uses, if in node. The actual meat of really complex reads or really optimized writes should always be exceptional and done by hand.
BiteCode_dev 4y ago
Right. In fact any ORM worth its salt will have methods specifically designed to let you address the N+1 problem easily. E.G: django "select_related" Not to mention they will will have ways to use raw SQL through the lib infrastructure, which is still better than querying SQL manually, and give you full control of the query. Also wordpress shows that you can write manual SQL and still have terrible performances because your application is badly structured, which an ORM helps with. In the end, I don't think there is less trap with raw SQL, the traps are just different. It's not a matter of "better".

chillfox 4y ago

The core problem with ORMs is that you end up having to learn the ORM on top of learning SQL and the particular database. It ends up being like working with the database through an opaque indirect layer.

droobles 4y ago

What do you think of "micro ORMs" like knex.js, or Dapper for C#? I personally agree with you and my personal projects just use stored procedures, but these micro orms have always piqued my interest since they're just a SQL abstraction for building single queries.

jiayo 4y ago
I would consider those "query builders" rather than fully fledged ORMs.
- notfed 4y ago
  Yeah no. A micro ORM still expects you to write the full, raw SQL. The raison d'etre for a micro ORM is to capture the results of that SQL query into a simple, flat collection of objects in a way that has a better UX than your framework's default behavior. Micro ORMs are the perfect middle ground for debates like these.
skeeter2020 4y ago
This is typically as far as I go with an ORM, CRUD operations on single tables that give you some type safety at compile time. If you need more complex data shapes I usually create a view or occasional stored procedure but that's about it. I use Entity Framework in my day job and do like the migrations though...
dgb23 4y ago
I personally like those. They are qualitatively different from ORMs. They give you three things: - sensible ways of composing queries beyond direct string concatenation - safe parametrization - return results into usable data structures (not objects) None of these things carry the problems of ORMs. They don’t impose a paradigm that isn’t SQL. They simply let you interface with SQL in an ergonomic manner.

lmm 4y ago

> learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life. Exactly the same is true of ORMs. I find most of the people who advocate "just use SQL" have a bizarre aversion to applying the same learning effort to their ORM. > I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls. That kind of problem happens with regular SQL all the time, and the tools to test/investigate it are less available.

smallnamespace 4y ago
> have a bizarre aversion to applying the same learning effort to their ORM. SQL is a lingua franca while an ORM is specific to a stack. If you know that you will always, say, be using SQLAlchemy on Python, then learning it well might be a good investment, but I don't find learning multiple ORMs to be a good time investment over just learning SQL. Most every stack has good, lightweight query builders.
deleted 4y ago
[deleted]
jjoonathan 4y ago
> bizarre aversion to applying the same learning effort to their ORM Oh, sure, instead of just learning SQL I'll just learn SQL, the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated, and then I'll commit a patch to the open source project to fix the undocumented internals and shepherd a custom dependency for 6 months while it gets into a numbered release. Then I'll do it all over again when I switch stacks and have to learn a completely new ORM. Or I could just use SQL.
- ttymck 4y ago
  How do I map my complex join into a list of structs with embedded associations? I'd probably need to write a good bit of code just to unmarshal the data, right? How are you doing this every time you write a new query?

superb-owl 4y ago

ORMs are great for avoiding boilerplate and adding some typesafety. But once you start doing complicated things like JOINs, it's better to drop into raw SQL. > Listen to this old man's advise: learn SQL properly. It's not that hard. I couldn't agree more with the first half, or disagree more with the second :) Once you're operating at scale, it takes a lot of fine-tuning, know-how, experimentation, and reading docs to get things moving efficiently.

phendrenad2 4y ago
What are some examples of fine-tuning or trial-and-error you might need at scale?
- superb-owl 4y ago
  Postgres has a ton of parameters that can greatly affect performance. I've used https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/ to help w/ that. In terms of schemas, where you put your indexes matters a TON. Indexes make reads faster and writes slower (to oversimplify). Figuring out where to break out a separate table vs keep everything in one table is also a common issue.
  - phendrenad2 4y ago
    True, all of these would still be an issue with ORMs.

partly_cloudy 4y ago

who you callin old man i'm still in my 20s!

waste_monk 4y ago
One foot in the grave. Practically a fossil.

xarope 4y ago

Better yet, learn stored procedures, then call them just like any API with a well established set of inputs and expected return typed outputs. You'll thank me later once you decide to split your DB from your app server.

noduerme 4y ago
It's been at least 12 years since I had a DB on the same server as the app or service that called it... I can't fathom why anyone would do that these days. But either way, the stored procedures would work just as well. The issue you're alluding to (I think) is the time to set up connections for a dozen calls between the backend and the DB if they're on separate servers. But that's actually less of a bottleneck now than it used to be, with persistent pooled connections and most things hosted in the same VPN zones. I'm not arguing for ORMs... but appside processing [edit: "joins"] can still be appropriate if you're dealing with a monolithic DB and don't want certain giant queries or procedures to block rapid reads. The point is more to understand your infrastructure, and ORMs obfuscate it to a point that makes you irresponsible as a developer in some cases. Also, with lateral joins and windows in mysql 8 you can really have control over whether you prefer your execution plan to loop or scan. Which takes away most of the rest of the argument for appside processing.

manv1 4y ago

It's hard to explain to developers why an ORM isn't really that great of a solution....but at a high level: * your database is the core of your app. Abstraction implies that you don't care about the details of your data store, which eventually will lead to two major problems: 1. When things are slow you won't be able to debug it, because you're abstracted away your database and don't understand what queries your ORM is using to do things, and 2. when your ORM migration fails you won't know what to do, because you don't understand your database. In general this happens with every ORM product, ever, because you didn't put in a constraint initially because you didn't know what that meant and then you did later, which caused the migration to fail because your constraint is being violated. Or the migration fails because the tool uses some attribute and not others for drift detection, so it tries to delete your table/database because it's detected drift. * the way you deal with data in code and in a database are different. Code iterates over objects. If you just use your ORM naively you'll end up doing some ridiculous number of selects because in code it looks like you're just iterating over objects...when in reality the ORM is doing a select/join for each one of those objects. And if you start getting more complicated there are structures that are just harder to do with ORMs...because you have to map what you're trying to do in SQL to the way your ORM works. At that point why not ditch your ORM? SQL isn't really that hard...but thinking about how do queries is harder than you would expect. It's almost an order of magnitude faster to do joins in the database than to join stuff in-code...but that also assumes that your schema isn't screwed up, it has indexes, etc. Lastly, a lot of ORMs don't put indexes on columns for some reason, which kills performance. You'd think that would be a detail that would be abstracted away for you at the ORM level, but it isn't. I mean it knows what you're using to select data, so it should auto-create indexes for you, right?

selcuka 4y ago
> I mean it knows what you're using to select data, so it should auto-create indexes for you, right? Curious, how does it know what you're using to select data?
- manv1 4y ago
  Because you're using the ORM to do selects. I'll give you an example: prisma has a "find/findMany." As part of the build process it should examine your usage and put indexes in the column that you're using in find/findMany Indexes are an implementation detail - but to use RDBMSs successfully you need indexes. ORMs try to hide those implementation details, but without those kinds of details you get bad performance. Prisma just fixed another problem it had with transaction isolation levels. To even understand the problem you need to understand SQL, how databases work, and how each database implements that. It's another important implementation detail that's abstracted away...until it bites you in the ass. Prisma also finally allowed you to use database-specific types instead of just using varchar(191), which was its default for strings. I'm picking on prisma because I happen to be using it right now. There are good things about it, like the way it does migrations and handles schema changes. But there are times its abstractions get in the way, like when you're trying to do aggregated group by (which is easier in SQL than it is in prisma). And its migrations often fail, and to understand why you really need to understand SQL. Hibernate was worse, because it provides a transparent object-level backing store.

ookblah 4y ago

Honestly, I started on the ORM hate train when I first started and wrote my own queries in an effort to be "performant", but my frankenstein of writing direct queries and abstracting code to be reusable became basically a super shitty ORM that was a nightmare to maintain for a large codebase lol. I don' think there's anything wrong with it and for 90% of CRUD actions it's what you should choose. By the time you hit performance issues you've probably thought of something else anyway.

layoric 4y ago
I am the same, the advice of "learn SQL" is still very important. Most ORMs have the escape hatch of just mapping results of a straight SQL query to an expected structure (thinking of typed languages like C#/Java here). 95% of the time I can just use the ORM to make simple queries in a easy to maintain way for your application. For the 5% of queries that are either complex or can't be easily represented via your ORM of choice, declare the expected type response and use that escape hatch to write your performant SQL. I've been on projects where the team has re-invented an ORM organically, and I've never seen it go well. Likewise with projects that are ORM 'purest', bending themselves over backwards to use an ORM for a query that can't easily be represented by whatever query syntax it has. This is why I still think 'lite' weight ORMs are the best of both worlds since they usually have a pretty good experience for common/easy queries but then when things get tricky, it is best to just use SQL and map to an app language data structure for the results. I mostly use OrmLite [0] in dotnet and have found it has a balance that done well by me for years (note I now work at ServiceStack who built OrmLite). [0] https://docs.servicestack.net/ormlite/ormlite-apis#query-examples https://docs.servicestack.net/ormlite/ormlite-apis#query-exa...
- partly_cloudy 4y ago
  "95% of the time I can just use the ORM to make simple queries in a easy to maintain way for your application. For the 5% of queries that are either complex or can't be easily represented via your ORM of choice, declare the expected type response and use that escape hatch to write your performant SQL." site creator here - yeah this is the approach i'm taking now. the ORM is useful for sure and there's still a benefit to using it, but anything that needs to read from a few different tables, i'm definitely going with raw SQL

uoaei 4y ago

Honestly, ORM is an unnecessary layer most of the time. You can have database schemas, and you can have software schemas, and they don't necessarily need to coincide as long as your database schema allows efficient queries. The rest of your relational-manager-logic can be embedded in the post-processing methods you apply to the query results.

fulafel 4y ago

An alternative to learning SQL is to use a non relational database, that has more of a impedance match to your host language to start with. (Eg XTDB or another Datalog DB in case of Clojure)

closeparen 4y ago

I'm happy to write my own SELECT queries, but it sucks to be manually schlepping row[0], row[1], etc. into the fields of my domain entities, or making sure I provide the parameters to the parameterized INSERT statement in the right order. ORMs usually let you drop down to SQL as desired, and I do that when I have a nontrivial query to express, but CRUD plumbing is nice.

ilamparithi 4y ago
Not sure which programming language you are using. For Java, I find Spring JDBC templates with Row Mappers more productive than ORMs. DataClassRowMapper will automatically populate the domain entity.
- closeparen 4y ago
  Go, Java language-level 8, Python. All have an approach, usually with some third party library, but the bone stock database drivers deal in field indexes. And people consider those libraries "ORM" whether or not they really are technically.

danmaz74 4y ago

I'm pretty proficient with SQL, and I completely agree that you need to know it well if you use a relational DB for something that isn't trivial, but I still love using ActiveRecord. In most web applications I worked on I needed to manually optimise some queries, either massaging AR a bit or directly writing SQL when necessary, but I would say that's much less than 1% of all interactions with the DB. In all the other situations, using AR saved me a lot of time.

freetonik 4y ago

In my limited experience, the best thing about ORM isn't the querying, but the mapping to objects. This is why I really like ActiveRecord's `find_by_sql`[1], which allows to write a raw SQL query and get an array of objects. [1]: https://guides.rubyonrails.org/v3.2/active_record_querying.html#finding-by-sql https://guides.rubyonrails.org/v3.2/active_record_querying.h...

petervandijck 4y ago

Learn SQL and use the ORM is usually the correct answer. Let it print out the SQL queries and how long they take (ORMs do that out of the box), then check for indexes and other standard issues. If there’s some unusual complex query to be written, sure, write that by hand.