7 ms·

Every time we discuss an ORM project, someone is bound to complain about ORMs in general. I have a small suggestion: please try ORMs in different languages. A

Every time we discuss an ORM project, someone is bound to complain about ORMs in general. I have a small suggestion: please try ORMs in different languages. A lot of the power of an ORM depends on the language. Your ORM may not be the same as my ORMs - Django ORM, SQLAlchemy, Pony ORM. Try them and you will know why. I will wait. From my limited understanding, the language needs to allow reflecting and manipulating your model definitions (like a model Class) which define the data model as a set of tuples (field name and field type, which is a representation of SQL type) into instances that hold data in the native types of the programming language and "magically" convert them to the SQL types. It usually falls under the realm of meta programming and is not exactly a first-class feature in many languages. I am totally not a language expert and perhaps did not articulate this well enough, but if you are curious just search a bit and try. Just my 2 Rupees.

KptMarchewa 5y ago
>I have a small suggestion: please try ORMs in different languages. I mean, I've done it and had entirely different experience. Starting from the entire premise of learning N different abstraction layers over one abstraction layer.
smt88 5y ago
I've used ORMs for 20 years in Java, PHP, C#, JavaScript, TypeScript, Python, and Kotlin. I have pushed code to production with at least 10 ORMs, possibly more. ORMs are a bad idea. They are a productivity killer. Do not use them. I also used to think I'd find a "good" ORM because the early magic is so exciting and efficient. But for anything beyond a toy or proof-of-concept, they are more cost than benefit. Some of them (e.g. TypeORM, Entity Framework) are literally not debuggable because they use some much configuration magic. Just avoid them. Write SQL in SQL (or a query builder that compiles to SQL).
- SomeCallMeTim 5y ago
  Wow. See this other comment I wrote: https://news.ycombinator.com/item?id=27551288 https://news.ycombinator.com/item?id=27551288 TypeORM in particular is well designed and quite awesome. A huge benefit of TypeORM is that you get full static type support in all of your queries, including in the returned results. SQL results are effectively one-off dynamic type result piles that you need to validate by hand, and are insanely inefficient to code.
  - smt88 5y ago
    > A huge benefit of TypeORM is that you get full static type support in all of your queries, including in the returned results. When did I say I wanted to give up static typing? You can have both[1][2]. I would never give up static typing and would not have used Node if TypeScript/Flow were unavailable. > TypeORM in particular is well designed and quite awesome. I've used TypeORM extensively in production on multiple projects for years, including with a fairly large team. It has silent failures, and its API was unstable/unclear for a long time. It's been a very messy project for most of its lifetime. They couldn't even decide on an API for lazy-loading of relations, and there was some hidden API that was semi-supported and not type-checked. Partly because it's built on Node, it's very difficult to use a debugger to see the relevant frames when you hit a breakpoint or an exception -- if you can even set a breakpoint where you want it to be before just running into an error at runtime. There's also the same core issue that all ORMs suffer from, which is that the abstraction becomes leaky as soon as you need to do anything complicated with lots of joins or advanced SQL features. 1. https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped 2. https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
    - moltar 5y ago
      Zapatos ftw
    - SomeCallMeTim 5y ago
      Pgtyped looks really cool. So does Zapatos. But my current gig has an absolute non-negotiable business requirement to support multiple database backends. Specifically SQL Server and MySQL in addition to PostgreSQL. Additionally: Not seeing any way to take the schema info from either of the above and have it crank out a full GraphQL server code base. Check out my linked comment if you want more details, but I'm seriously talking 20-40k lines of code that I didn't need to write because the resolvers were auto-generated by the stack I'm using. At least a first pass of Google searching isn't coming up with anything similar with pgtyped or Zapatos. And no, you're not going to convince me that 40k lines of code that shouldn't be necessary to write by hand are somehow superior to 1/100 the code that just includes the queries that don't map as well onto the ORM or the GraphQL generated code.
andrewmcwatters 5y ago
For me, ORMs are a sign one isn’t comfortable reading and writing SQL. I think this happens because in so many projects, commercial and hobbyist, you write queries a few times and then forget about it. So, it’s totally understandable. I was there once. I’m very comfortable reading and writing basic SQL now, and so I’d prefer to think in SQL when working with a database, and think about objects when I’m in a programming language. I think you do yourself a disservice when you hide from it that you eventually have to face.
- brainless 5y ago
  I am also very happy reading, writing and understanding SQL. My own side-product depends on that ability: https://dwata.com https://dwata.com. It uses reflection of the underlying SQL, using an ORM based reflect library, generates a vector of the relations and state of schema and creates SQL on the fly. But, would I recommend a team to start writing SQL by hand: No, absolutely not.
  - pionar 5y ago
    Why not? If you're going to use a tool (be it SQL, NoSQL, docker, kubernetes), your team needs to have/build some expertise in it. IMHO, using something like an ORM abstracts that layer. It also introduces a "black box" into your ecosystem. Going by my opinion in the first paragraph above, you'd have to build some expertise in this ORM. Why spend that time when you can just spend time getting to know your data storage technology better?
    - SomeCallMeTim 5y ago
      Because "getting to know your data storage technology better" isn't the business goal. On the surface, that's a great goal to have, but taking an approach that requires you to do 100x as much work so you can learn SQL better isn't software engineering. It's over-billing. I throw together 200 lines of schema in Prisma.io and it creates 20,000 lines of GraphQL handling code plus more ORM-specific code that allows me to create type-safe access to the database. And "the database" right now is PostgreSQL, but one of the business requirements is that "the database" could also be SQL Server or MySQL. Having learned the salient details of the ORM in ... hmm ... about three days? ... I feel that the advantages of getting a full GraphQL API that can be wired up to an arbitrary backend database for 99% of the queries vastly outweigh the fact that most of my database queries are entirely inside a "black box." In six months I've encountered exactly one problem where the black box made my life more difficult. I think I wasted about an hour, maybe 90 minutes, on tracking down why it didn't work as expected. Given that "writing custom SQL" for all of the queries that I've created would have required 10s of thousands of lines of additional code plus system tests to verify that the queries were all working as expected? That's a profoundly huge win. I'd still be writing SQL queries months from now if I had taken the approach you're suggesting. Maybe it's advice that's good for consulting firms that charge by the hour and love to find ways to make their developers work more hours, but in my book it's not even ethical to recommend.
vishnugupta 5y ago
I worked on two projects that used ORMs; Java and Node and it was a total shitshow. The reasons are well known and too many so I won't get into them. My sense is ORMs are terrific to get a quickly launch a product/project i.e., 0 -> 1 when worked upon by a small (3-5 member) team. But as a project succeeds and more people begin contributing it derails very fast and very badly. By the time an experienced data person comes in and sees the mess it'll be too late. Foreign keys everywhere, incomprahensible auto-generated queries, all sorts of joins etc., So they won't touch a data/model layer with a 100ft pole and continue to get entangled. When a data layer becomes unsalvagable the project is doomed. No amount of re-design/refactor/re-architecture can revive a project if the data layer is messed up. Data model is the heart and soul of a software please don't outsource its design to an ORM. There is a good reason Linus said something to the effect of "Bad programmers worry about the code. Good programmers worry about data structures and their relationships".
dimitrios1 5y ago
Used ORMs in many different languages, from ActiveRecord, Hibernate, EntityFramework, SQLAlchemy, Bookshelf.js, GORM, and Ecto (Ecto was the best, ActiveRecord was the most "feature rich", Hibernate makes me cry, and EntityFramework is OK so long as you give Microsoft a lot of money). For dynamic languages the downsides are increased runtime cost over simply writing and executing sql and doing the mapping yourself, and tons of magic. Magic everywhere. Magic makes things harder to debug. For static languagges the downsides are heavy impedence mismatch, obtuse to use APIs at times (to overcome type systems), and again, a performance hit. What I have found is for the majority of the time, people who say they want an ORM, what they really want is a type-safe (or type-hinted) way to write queries and operate on returned data. That's probably 90% of the value add. That can be done without a heavy, complex ORM framework.
- moltar 5y ago
  Totally agree! I come from the world of Perl, at the start of my career. Perl has DBIx::Class, still one of the best ORMs IMO. I’ve transitioned via PHP and Laravel, which also has a decent ORM. Nowadays I’m heavy into TypeScript and have tried every ORM there is in the JS/TypeScript land. And I’ve settled on Zapatos, which is not an ORM, but exactly what you describe. It’s a utility that helps writing type safe and hinted queries. This approach contrasted with, let’s say, TypeORM which is full of broken magic under the hood, is a breath of fresh air and is a delight to use. Together with that I write simple repository wrappers manually and surprisingly there aren’t that many of them.
- spion 5y ago
  I don't think EntityFramework Core (on NET Core) costs all that much?
  - dimitrios1 5y ago
    To get the full power of EntityFramework, with all the cool code and graph generation, I think you need a paid version of Visual Studio and SSMS, although I could be wrong. Either way costing anything at all already puts it at a disadvantage compared to the others I mentioned.
akudha 5y ago
I have tried ORMs in only 3 languages, they all sucked. It is not all fault of the language though - writing SQL in SQL feels natural, but writing SQL in PHP/Go etc feels just plain weird, no matter how well the ORM libraries are defined. I also don't enjoy writing HTML in non-HTML languages - it just doesn't feel nice or natural. This is before getting into the other problems (having to learn the libraries, the libraries generating inefficient SQL etc). Maybe I am old and not hip, I dunno. Using ORMs is just annoying and unpleasant
throwaway894345 5y ago
FWIW, some people rave about SQLAlchemy and for the life in me I can't understand why. It does too much behind the scenes, the high level interface overloads a lot of operators (IIRC `==` and/or `and`/`or`) are overloaded and return objects rather than booleans, and by default all objects are truthy--this has lead to bugs at least a few times. There is the "core" which is basically just a query builder and the only part worth using as far as I can tell, but searching for things in the core always turns up results from the ORM layer. The documentation is hard to read because it's all rendered on a single page and many method names and class names are overloaded, so if you ctrl+f for a method, you have to scroll up several pages to find the class heading. Further, because it's all dynamically typed, it's very difficult to tell precisely what types a method accepts or returns. I'm not going to use that alone to write off all ORMs--I've heard the Ruby folks like their Active Record--but it seems like if such a thing as a helpful ORM exists, they are few and far between.
halostatue 5y ago
My biggest issue with _many_ ORMs don’t deal with production deployment as a thing but as an afterthought (I’m looking at _most_ of the JS ORMs for this nonsense; `.sync()` should _NOT_ be how you introduce database changes). Early Rails got many things wrong from a DB perspective (what do you expect when MySQL is the baseline for capabilities?), but its insistence on using migrations from the first word was one of the best decisions ever made.

KptMarchewa 5y ago

>I have a small suggestion: please try ORMs in different languages. I mean, I've done it and had entirely different experience. Starting from the entire premise of learning N different abstraction layers over one abstraction layer.

smt88 5y ago

I've used ORMs for 20 years in Java, PHP, C#, JavaScript, TypeScript, Python, and Kotlin. I have pushed code to production with at least 10 ORMs, possibly more. ORMs are a bad idea. They are a productivity killer. Do not use them. I also used to think I'd find a "good" ORM because the early magic is so exciting and efficient. But for anything beyond a toy or proof-of-concept, they are more cost than benefit. Some of them (e.g. TypeORM, Entity Framework) are literally not debuggable because they use some much configuration magic. Just avoid them. Write SQL in SQL (or a query builder that compiles to SQL).

SomeCallMeTim 5y ago
Wow. See this other comment I wrote: https://news.ycombinator.com/item?id=27551288 https://news.ycombinator.com/item?id=27551288 TypeORM in particular is well designed and quite awesome. A huge benefit of TypeORM is that you get full static type support in all of your queries, including in the returned results. SQL results are effectively one-off dynamic type result piles that you need to validate by hand, and are insanely inefficient to code.
- smt88 5y ago
  > A huge benefit of TypeORM is that you get full static type support in all of your queries, including in the returned results. When did I say I wanted to give up static typing? You can have both[1][2]. I would never give up static typing and would not have used Node if TypeScript/Flow were unavailable. > TypeORM in particular is well designed and quite awesome. I've used TypeORM extensively in production on multiple projects for years, including with a fairly large team. It has silent failures, and its API was unstable/unclear for a long time. It's been a very messy project for most of its lifetime. They couldn't even decide on an API for lazy-loading of relations, and there was some hidden API that was semi-supported and not type-checked. Partly because it's built on Node, it's very difficult to use a debugger to see the relevant frames when you hit a breakpoint or an exception -- if you can even set a breakpoint where you want it to be before just running into an error at runtime. There's also the same core issue that all ORMs suffer from, which is that the abstraction becomes leaky as soon as you need to do anything complicated with lots of joins or advanced SQL features. 1. https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped 2. https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
  - moltar 5y ago
    Zapatos ftw
  - SomeCallMeTim 5y ago
    Pgtyped looks really cool. So does Zapatos. But my current gig has an absolute non-negotiable business requirement to support multiple database backends. Specifically SQL Server and MySQL in addition to PostgreSQL. Additionally: Not seeing any way to take the schema info from either of the above and have it crank out a full GraphQL server code base. Check out my linked comment if you want more details, but I'm seriously talking 20-40k lines of code that I didn't need to write because the resolvers were auto-generated by the stack I'm using. At least a first pass of Google searching isn't coming up with anything similar with pgtyped or Zapatos. And no, you're not going to convince me that 40k lines of code that shouldn't be necessary to write by hand are somehow superior to 1/100 the code that just includes the queries that don't map as well onto the ORM or the GraphQL generated code.

andrewmcwatters 5y ago

For me, ORMs are a sign one isn’t comfortable reading and writing SQL. I think this happens because in so many projects, commercial and hobbyist, you write queries a few times and then forget about it. So, it’s totally understandable. I was there once. I’m very comfortable reading and writing basic SQL now, and so I’d prefer to think in SQL when working with a database, and think about objects when I’m in a programming language. I think you do yourself a disservice when you hide from it that you eventually have to face.

brainless 5y ago
I am also very happy reading, writing and understanding SQL. My own side-product depends on that ability: https://dwata.com https://dwata.com. It uses reflection of the underlying SQL, using an ORM based reflect library, generates a vector of the relations and state of schema and creates SQL on the fly. But, would I recommend a team to start writing SQL by hand: No, absolutely not.
- pionar 5y ago
  Why not? If you're going to use a tool (be it SQL, NoSQL, docker, kubernetes), your team needs to have/build some expertise in it. IMHO, using something like an ORM abstracts that layer. It also introduces a "black box" into your ecosystem. Going by my opinion in the first paragraph above, you'd have to build some expertise in this ORM. Why spend that time when you can just spend time getting to know your data storage technology better?
  - SomeCallMeTim 5y ago
    Because "getting to know your data storage technology better" isn't the business goal. On the surface, that's a great goal to have, but taking an approach that requires you to do 100x as much work so you can learn SQL better isn't software engineering. It's over-billing. I throw together 200 lines of schema in Prisma.io and it creates 20,000 lines of GraphQL handling code plus more ORM-specific code that allows me to create type-safe access to the database. And "the database" right now is PostgreSQL, but one of the business requirements is that "the database" could also be SQL Server or MySQL. Having learned the salient details of the ORM in ... hmm ... about three days? ... I feel that the advantages of getting a full GraphQL API that can be wired up to an arbitrary backend database for 99% of the queries vastly outweigh the fact that most of my database queries are entirely inside a "black box." In six months I've encountered exactly one problem where the black box made my life more difficult. I think I wasted about an hour, maybe 90 minutes, on tracking down why it didn't work as expected. Given that "writing custom SQL" for all of the queries that I've created would have required 10s of thousands of lines of additional code plus system tests to verify that the queries were all working as expected? That's a profoundly huge win. I'd still be writing SQL queries months from now if I had taken the approach you're suggesting. Maybe it's advice that's good for consulting firms that charge by the hour and love to find ways to make their developers work more hours, but in my book it's not even ethical to recommend.

vishnugupta 5y ago

I worked on two projects that used ORMs; Java and Node and it was a total shitshow. The reasons are well known and too many so I won't get into them. My sense is ORMs are terrific to get a quickly launch a product/project i.e., 0 -> 1 when worked upon by a small (3-5 member) team. But as a project succeeds and more people begin contributing it derails very fast and very badly. By the time an experienced data person comes in and sees the mess it'll be too late. Foreign keys everywhere, incomprahensible auto-generated queries, all sorts of joins etc., So they won't touch a data/model layer with a 100ft pole and continue to get entangled. When a data layer becomes unsalvagable the project is doomed. No amount of re-design/refactor/re-architecture can revive a project if the data layer is messed up. Data model is the heart and soul of a software please don't outsource its design to an ORM. There is a good reason Linus said something to the effect of "Bad programmers worry about the code. Good programmers worry about data structures and their relationships".

dimitrios1 5y ago

Used ORMs in many different languages, from ActiveRecord, Hibernate, EntityFramework, SQLAlchemy, Bookshelf.js, GORM, and Ecto (Ecto was the best, ActiveRecord was the most "feature rich", Hibernate makes me cry, and EntityFramework is OK so long as you give Microsoft a lot of money). For dynamic languages the downsides are increased runtime cost over simply writing and executing sql and doing the mapping yourself, and tons of magic. Magic everywhere. Magic makes things harder to debug. For static languagges the downsides are heavy impedence mismatch, obtuse to use APIs at times (to overcome type systems), and again, a performance hit. What I have found is for the majority of the time, people who say they want an ORM, what they really want is a type-safe (or type-hinted) way to write queries and operate on returned data. That's probably 90% of the value add. That can be done without a heavy, complex ORM framework.

moltar 5y ago
Totally agree! I come from the world of Perl, at the start of my career. Perl has DBIx::Class, still one of the best ORMs IMO. I’ve transitioned via PHP and Laravel, which also has a decent ORM. Nowadays I’m heavy into TypeScript and have tried every ORM there is in the JS/TypeScript land. And I’ve settled on Zapatos, which is not an ORM, but exactly what you describe. It’s a utility that helps writing type safe and hinted queries. This approach contrasted with, let’s say, TypeORM which is full of broken magic under the hood, is a breath of fresh air and is a delight to use. Together with that I write simple repository wrappers manually and surprisingly there aren’t that many of them.
spion 5y ago
I don't think EntityFramework Core (on NET Core) costs all that much?
- dimitrios1 5y ago
  To get the full power of EntityFramework, with all the cool code and graph generation, I think you need a paid version of Visual Studio and SSMS, although I could be wrong. Either way costing anything at all already puts it at a disadvantage compared to the others I mentioned.

akudha 5y ago

I have tried ORMs in only 3 languages, they all sucked. It is not all fault of the language though - writing SQL in SQL feels natural, but writing SQL in PHP/Go etc feels just plain weird, no matter how well the ORM libraries are defined. I also don't enjoy writing HTML in non-HTML languages - it just doesn't feel nice or natural. This is before getting into the other problems (having to learn the libraries, the libraries generating inefficient SQL etc). Maybe I am old and not hip, I dunno. Using ORMs is just annoying and unpleasant

throwaway894345 5y ago

FWIW, some people rave about SQLAlchemy and for the life in me I can't understand why. It does too much behind the scenes, the high level interface overloads a lot of operators (IIRC `==` and/or `and`/`or`) are overloaded and return objects rather than booleans, and by default all objects are truthy--this has lead to bugs at least a few times. There is the "core" which is basically just a query builder and the only part worth using as far as I can tell, but searching for things in the core always turns up results from the ORM layer. The documentation is hard to read because it's all rendered on a single page and many method names and class names are overloaded, so if you ctrl+f for a method, you have to scroll up several pages to find the class heading. Further, because it's all dynamically typed, it's very difficult to tell precisely what types a method accepts or returns. I'm not going to use that alone to write off all ORMs--I've heard the Ruby folks like their Active Record--but it seems like if such a thing as a helpful ORM exists, they are few and far between.

halostatue 5y ago

My biggest issue with _many_ ORMs don’t deal with production deployment as a thing but as an afterthought (I’m looking at _most_ of the JS ORMs for this nonsense; `.sync()` should _NOT_ be how you introduce database changes). Early Rails got many things wrong from a DB perspective (what do you expect when MySQL is the baseline for capabilities?), but its insistence on using migrations from the first word was one of the best decisions ever made.