9 ms·

Objection to ORM Hatred

50 points by dsego 6y ago

rini17 6y ago
I think real improvement from SQL would be the ability to construct query in sets. Like I'd make a set of persons, check if it looks right, then another set of pets and if both are alright then compose the result set by joining them. Not by cutting and pasting a code but actually using variables. Your examples do everything in one statement which does not look so easy to compose, why?
- spacemanmatt 6y ago
  My favorite reasons for pushing something down into the database include performance, security, concurrency, and portability. Yes, portability. My client language is way more likely to change than my database.
  - rini17 6y ago
    I don't mean that. I just like to construct any nontrivial query piecewise. SQL is about sets, when doing query you in fact make subsets and combine them using set operations. But the syntax makes this composition error prone and objection.js seems not to do much to improve it.
    - spacemanmatt 6y ago
      You've sorta captured my distaste for ORMs. I'm good at sets and relational algebra. Anything ORM interface that isn't RA is a leaky abstraction, and eventually I just have to go around it.
- forgetfulness 6y ago
  SQL has views and functions for this. It's indirect when looking at the code of one application accessing the database, but as the other poster said, those can be many, in different languages, at the same time and over time.
the_af 6y ago
Obligatory mention of the (in)famous article, "ORM is the Vietnam of Computer Science", frequently linked to from HN: http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie... The Vietnam of Computer Science goes into more detail with the problems that plague ORMs and why some people have "ORM hatred". Some of these reasons are glossed over or not mentioned by TFA.
Cthulhu_ 6y ago
I don't mind SQL; my main reason for using an ORM at the moment (gorm, it has its flaws) is one, automatic database migration, and two, I don't have to manually map a resultset to my struct; code like that is tedious and error-prone. Gorm is not the best when it comes to managing relationships though, to put it mildly.
TheChaplain 6y ago
I find the loud aversion for peoples choice of tools quite "eye roll"-worthy. IMHO use whatever that helps you actually deliver something.
- coldtea 6y ago
  IYHO there's no possibility of people: (a) using the wrong tool for the job (b) succumbing to fads and hype (c) forcing said tools to other's throats (e.g. manager making decisions, or team lead, or company policy, or "opinionated" non-technical customer) ? I'd say those cases deserve "loud aversion for peoples choice of tools".
  - pdpi 6y ago
    None of those is justified, no. Where you see me using the wrong tool for the job, I see myself using a tool that may be flawed but is good enough for what I need right now. Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech. Where you see me forcing a tool down your throat, I see myself standardising on a compromise solution so we don't have ten approaches to the same problem in one single codebase. On any one of those situations, either of us might be right, wrong, or (most likely) somewhere in between. It's important to keep that in mind before loudly denouncing others' opinions, especially when the alternative is to just argue for your own opinion and letting me decide.
    - pydry 6y ago
      Where I loudly denounce something it's usually because it is supported by a wave of hype that will drown out muted objections. I can count 11 or 12 instances of this happening in tech. There's a reason Larry Ellison said the only industry more fashion driven than women's fashion is tech. Tech is extremely herdlike. If you are going against the herd you kind of have to be loud to get heard. Large corporations with marketing budgets and the people who got caught up in them will drown you out otherwise. Loud objections can cut through the bullshit-with-a-big-marketing-budget and in many cases (e.g. mongo) I'm pretty glad it did.
wzdd 6y ago
This looks a lot like LINQ to SQL. Is it the same? I'm surprised the article doesn't mention LINQ.
- cies 6y ago
  Nor does it mention jOOQ [1], arguably even more similar (as it is a lib and not a language built-in). Some page with actual queries from the docs: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-steps-step5 https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...
pjc50 6y ago
Interesting angle. I've come round to the benefits of ORMs for query building and results-to-objects. Especially since I used LINQ; linq-on-objects and linq-on-xml are incredibly useful even outside a database. The one big anti-pattern I've seen in systems that use ORMs is the "manual query" or especially "manual update": rather than doing something in the DB, the programmer retrieves some objects, iterates over them with a FOR loop or similar, makes changes, and then writes them back to the database. This is absolutely terrible for performance and prone to concurrency errors. (are there ORMs in which all returned data objects are forced to be immutable? I think that would help.)
- paulryanrogers 6y ago
  There are some patterns ORMs like Hibernate use to combat the races: versioning, pessimistic locking, etc. And while performance can be an issue for many businesses it's the read queries that are most often the bottleneck. If write performance is critical then hopefully you've got sustainable revenue to cover the cost to optimize it.
- worble 6y ago
  > (are there ORMs in which all returned data objects are forced to be immutable? I think that would help.) I'm not sure if this is exactly what you're asking, but Entity Framework has .AsNoTracking(), which will simply return the objects without doing any change tracking on them, so feel free to modify them as you wish afterwards without accidentally updating your database.
coldtea 6y ago
Came here to object to objection to ORM hatred. ORM were never worth the trouble. There's on "impedance mismatch", you just use data wrong (wrapped as objects as opposed to data records).
- jbverschoor 6y ago
  Nobody uses relational data anymore except when doing analytics or aggregation. Relational data is almost always mapped to an object.
  - thecopy 6y ago
    >Nobody uses relational data anymore Source?
    - jbverschoor 6y ago
      I meant using the relational data from a query directly. The moment you're doing a join, you're simply mapping to an object manually
  - cmrdporcupine 6y ago
    I think it's more that people don't seem to know what relational actually means, and schools are doing a terrible job in teaching it. And so we see the results.
    - ZephyrBlu 6y ago
      Care to explain?
      - BlackFly 6y ago
        I'm not porcupine, but I feel that we are definitely using relational models. The drive towards objects (and REST as well) tries to project data into a hierarchy. Orders belong to customers, so you nest orders under customers. But you are B2B, so you break into one market and realize that suddenly joint purchases are a thing. Suddenly you have orders with multiple customers. If you were using a relational model for backing the orders and customers, there is no real issue. An order is just a tuple of data, a customer is just a tuple of data, the "belongingness" is just modeled via foreign key constraints. Maybe you were smart enough to already model it as many to many and you just relax a uniqueness constraint in the join table or you had no such uniqueness constraint already and it was enforced by the application. Otherwise, you move the foreign key out of the order tuple into a joining table and move on to changing the projection queries and the downstreams. If you were storing it as nested data documents... I don't know because I have never done that, but I do know the trouble with trying to invert hierarchical addresses in such cases and it is annoying and inefficient (abstraction inversion). In a relational model, any hierarchy is a contrivance via foreign key constraints. These same attributes are easy to invert. If you have a business need for a hierarchy, that shows up in the business logic by the precise query used to project the relations along the foreign keys. In the scope of the comment made. I find it hard to believe that someone doesn't see that we are indeed using relational models of data and I find it sad that the known benefits apparently aren't so widely known. Yes, in a concrete use case you project out the data you need and type that (map it to objects). But if you modeled the data well, as your use cases increase in number or evolve your data model often doesn't need to change as much as your business logic if you did your job well. The job being relational data modelling. I myself use async programming, and there aren't really good ORMs for that since you don't want every single property of your object to be an async property (which most ORMs do with lazy loading).
mkl95 6y ago
I enjoy using the SQLAlchemy ORM. It makes it trivial to divide a query in several parts that you can mix and match. In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries.
fbn79 6y ago
Using Objection for more than a year in a medium sized ERP project. Not bad, but for sure on the next project I will use directly knex as querybuilder (with no ORM part). The ORM part that Objection add to knex don't bring much value and add a layer of complexity. The only functionality that I would miss would be the graph operations (withGraphFetched, upsertGraph, ecc.) but writing them in SQL (using KNEX) would not be a great problem.
- nicoburns 6y ago
  We're using knex at $dayjob, and we're not even using it for half our queries. Knex is pretty nice for simple CRUD operation, but for more complex queries we've found it much nicer to drop down to raw SQL (with knex.raw providing escaping). What we have found helpful is to stick common queries into a helper function so that we can get a consistently shaped object back.
- bvm 6y ago
  We use both in our code base. Sometimes knex makes sense, sometimes Objection makes sense. That's actually one of the nice things about it wrapping knex.
- dsego 6y ago
  Have you considered a tagged template literal sql builder, such as https://github.com/blakeembrey/sql-template-tag https://github.com/blakeembrey/sql-template-tag ? Keeps the SQL syntax so less to learn, but still offers parametrized queries.
  - fbn79 6y ago
    Considered it, but I think that there are advantage in programmatically construct the query (see for example knex modify). Directly write down SQL or use template with sql make it harder. The only problem with knex is that it mutate at every operation instead of returning a new instance. I'm experimenting in wrapping it in a lazy monad that return a Fluture (https://github.com/FbN/iodio https://github.com/FbN/iodio)
mgreenleaf 6y ago
I have projects that use ORMs and projects that do not use ORMs. I have specific reasons for both types, and in certain circumstances one or another is more appropriate. ORMs are a dependency that are often constantly updated, prone to corner cases, and require a lot of library specific knowledge-- but they often make code more secure against small mistakes or tossing in a string interpolation, which is important when working with others, especially junior developers. They can be quite nice to work with in having type checked queries, building up queries from pieces using an API, but they also tend to be heavy and miss some features of your DB. Raw SQL on the other hand, is very flexible, standard, easy to read, and anyone who knows SQL can jump in. You can do REPL for queries and copy in, and edits to SQL are easy. The only dependency you need is the standard database drivers, and updates don't typically break things. If you have a basic understanding and use it right, security is great as well.
- px43 6y ago
  > If you have a basic understanding and use it right, security is great as well. I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. I'm sure there are a ton of reasonable use cases for raw SQL, but when I see the same dumb mistakes over and over again, it just makes me shake my head and wish more people just used ORMs. Major breaches happen every day due to SQL injections, so building up a narrative that developers can do raw SQL right if they're smart enough is just irresponsible IMO. Over and over again when I tell developers that they should use an ORM, they get offended, and then I end up finding trivial SQL injections. It kind of feels like telling people that it's perfectly safe to drive without a seat-belt so long as you're a good driver.
  - sjaak 6y ago
    By Sturgeon's law [1] 90% of all code is crap, so it doesn't surprise me that that 90% of code contains sql injections ;-) [1] https://en.wikipedia.org/wiki/Sturgeon%27s_law https://en.wikipedia.org/wiki/Sturgeon%27s_law -> "Ninety percent of everything is crap."
  - aww_dang 6y ago
noisy_boy 6y ago
I consider myself reasonably comfortable in SQL and don't mind crafting queries when it is optimized for performance and/or ORM wouldn't be able to produce something optimum. However, for typical CRUD stuff, I almost always use ORM because not having to deal with the boilerplate makes things so much more easier. E.g. JPA's query creation[0] allows me to dynamically generate the query based on the method name - that is just way too convenient to give up, I could use SQL too but there is no payoff in writing boilerplate compared to the convenience and decent performance provided by the ORM. [0]: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation https://docs.spring.io/spring-data/jpa/docs/current/referenc...
tyingq 6y ago
It has warts and limitations, but I find the approach that RedBean PHP uses to be very intuitive. It supports other patterns, but the one where you open up the ORM and let it make the schema dynamically as your code runs, then later "freeze" it, is pretty neat. See https://www.redbeanphp.com/index.php?p=/quick_tour https://www.redbeanphp.com/index.php?p=/quick_tour for an overview. Are there popular ORMs that work this way for other languages?
cies 6y ago
Query builders (as I call these type of tools) are great, especially when they allow typesafety over the SQL barrier. jOOQ[1] (on JVM) does this with code generation: migrate your db, re-generate the project specific jOOQ lib and all incompatible queries are not showing up red-underlined in your IDE. I liked this as well in ORMs: the type safety them CAN provide. Query builders VS ORMs, both have their advantages. Doing a Users.byId(123) is easier to read'n'write than doing so with a query builder. Its the complex queries where ORMs suck, string-SQL gives no type safety and query builders shine (given they provide type safety). 1: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-steps-step5 https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...
- lukaseder 6y ago
  > Doing a Users.byId(123) jOOQ can do that as well through various ways if that's a burden: https://www.jooq.org/doc/latest/manual/sql-execution/daos/ https://www.jooq.org/doc/latest/manual/sql-execution/daos/
  - cies 6y ago
    I stand corrected. And thanks for chiming in chief :)
deleted 6y ago
[deleted]
pydry 6y ago
>I created an ORM called objection.js for exactly the reasons Thomas listed. The design goal of objection.js is to allow you to use SQL whenever possible and only provide a DSL (Domain Specific Language) or a custom concept when something cannot be easily done using SQL. That's the exact opposite way I use an ORM.
NicoJuicy 6y ago
Normally, i go by ORM by default. If i know it's a heavy query or the ORM won't optimize it ( eg. lots of relations and creating a n+1 problem), then i do the query myselve. If something is going slow, I try to optimize it. If that means writing SQL, sure. Why not A programmer's toolbox contains multiple tools. Use the right one.
krzkaczor 6y ago
Objection.js is neat but give me Objection with full type safety (Prisma style) and I am in haven.
Tade0 6y ago
As a complete amateur regarding the backend I use ORMs for two reasons: 1. Migrations - I only recently started diving deeper into the concept so an abstraction is in order for now. 2. Security - I'm at a point where I don't know what I don't know and thus would rather not rely solely on my own knowledge. I'm sure such systems introduce their own security risks, but since those are mostly open-source projects I can at least count on somebody to eventually discover any such issue.
- aeyes 6y ago
  Database migrations are completely independent of using an ORM. You can still create migrations from your model definitions but that doesn't mean that you have to use an ORM to query a database. Some tools you might find useful: Flyway, Liquibase, Evolve
- ryanianian 6y ago
  You don't need an ORM for migrations. Tools like flyway and others can rely purely on `.sql` files with some naming conventions and can help keep your app and db schemas in sync. You also don't need an ORM for security, and an ORM doesn't always prevent you from footgunning. Use query builders, `?` or `:named` placeholders (as your DB driver recommends), and almost never use string operations to build queries. Similarly, grow suspicious of your db or application model if you find yourself having to write increasing amounts of SQL to appease your ORM. ORMs are a tool. Neither good or bad on their own. Many applications outgrow them, or they don't properly refine their object models as the db or application changes. Lots of applications hold on to the security blanket of their ORMs for far too long, or they let the DB objects proliferate very deeply into their stack such that changing the db or the app model become nearly impossible. This is when apps increasingly try to outsmart the ORM, creating massive amounts of tension and friction in doing even simple things. (As anyone who has tried to do large-scale migrations or backfills that weren't possible in raw sql will tell you.) Learning to recognize all this is above the level of "amateur", but it seems like you've already learned to identify some of the good that the tooling can provide.
greatgib 6y ago
For me, ORM are the good illustration of the quote: "The road to hell is paved with good intentions" For expert-beginner and "architecture manager", it looks like a good idea, giving an abstraction and co. But, I think that most haters are the people that already had used for a few of them for long enough to be burned and get the experience. ORM job is supposed to simplify the job of dealing with the database, but most of the time, the final things is a lot more complicated and a mess because of the ORM. The main issue being that ORM operations looks like auto magic to the user, but this auto-magic still have a lot of rules of specific functions and settings that have to be used for the things working correctly, because it is just executing sql in background. And in the end, it is so much more complicated to do understand and do simple things with the orm that it is to do the original sql operation. The worst of all is when the ORM has support for some callback or automatic hooks, like "post-update", etc...
- fennecfoxen 6y ago
  I don’t find the “magic” SQL generation that problematic in practice. The main problem with ORMs is that as a class they are, broadly speaking, all fat model ActiveRecord patterns which encourage you to make the models fatter and to spread invisible dependencies on these models (and their related models and the methods) throughout the codebase. The result is invariably a big ball of mud. Immutable entities, produced by a set of ORM-like and mostly automatic GetX/PersistX services, is more generally the sound way forward. Unfortunately it’s also far less convenient than most ORMs in practice, as frameworks like Rails prioritize realizing the messier vision.
  - greatgib 6y ago
    A few examples of the magic and the implicit rules that leads to confusion: query(object1).where(object1.prop=10).orderby(prop).join(object2).where(object1.prop=object2.bar) Looks like to be logical blocks that you can easily chain together, but is the order important and can each "subfunction" be used anywere? or after a specific previous one? Probably join couldn't be used after orderby. What is the state before the join, the available tables? Where inside the sql will be applied the where? What object will be available? Some ORM even do kind of "autojoin" on table that it detects based on arguments used in later where clauses.
terenceng2010 6y ago
I use dapper with a plugin called dapper.simplecrud (https://github.com/ericdc1/Dapper.SimpleCRUD https://github.com/ericdc1/Dapper.SimpleCRUD). This already helps to reduce the need to write simple SQL code repeatedly quite a lot. When doing SQL operations more complicated than a simple crud, I resort to raw SQL or stored procedure.
pmjones 6y ago
> People always seem to ignore the third option: using an ORM that embraces SQL! I had a similar revelation several years ago, based on the distinctions between a "domain model" and "persistence model" described by Mehdi Khalili, resulting in Atlas for PHP: http://atlasphp.io http://atlasphp.io
- dsego 6y ago
  Does this correspond to the data mapper pattern? Seems like people mostly object to active record orm variants. https://typeorm.io/#/active-record-data-mapper/what-is-the-data-mapper-pattern https://typeorm.io/#/active-record-data-mapper/what-is-the-d...
  - pmjones 6y ago
    Yes, the upper/outer layers are a Data Mapper implementation; cf. the Atlas.Mapper package. FWIW, the Data Mappers are built on top of Table Data Gateways, which in turn are built using Query Builders and a connection locator. This lets you start at lower levels and work your way up, while at the same time allowing you to "reach down" from the upper levels to the lower ones when you need to.
fabian2k 6y ago
One case I find really annoying to write in SQL is inserting or updating entities with several many-to-many relationships. That is just plain annoying to do by hand, so I'd either have to use an ORM or write a small wrapper myself for this. Reading entities with many-to-many relationships is also often easier with an ORM, though you really have to be aware how the ORM will write this kind of query to avoid issues. ORMs are a very leaky abstraction when performance is concerned. Another part that is very useful is the query building itself, though you can get that without an ORM. Having composable query fragments is very useful, doing that by hand is extremely tedious, error-prone and a potential security risk if you're not careful. I'm comfortable writing SQL by hand, but I do prefer to have an ORM as long as I can also bypass it for queries that are difficult or impossible to do with the ORM. But an ORM can't replace knowing how databases work, and they do require you to understand more under-the-hood details than most other types of libraries.
- cbetti 6y ago
  Personally, I think ORM has a reasonable use case here, but I also find doing this using SQL to be straightforward and easier to reason about during future refactors. Take a look at JDBI's Repository pattern for a simple compromise between SQL and ORM for this use case in particular.
Xophmeister 6y ago
I've certainly fallen in to the anti-ORM camp, because I know SQL well and at least like to think I can put together a schema better than any ORM can. (I usually can.) However, then my code becomes tightly coupled to the schema, despite my abstractions, which makes it harder to change and basically impossible to unit test.
- arcturus17 6y ago
  What do you end up doing, then?
  - Xophmeister 6y ago
    Hope I get it right (or right enough) first time an then worry about how I'm going to set up integration testing for this "thing" :P
- wegs 6y ago
  I make my own abstractions. I might have a file called 'kvs' which implements a key-value-store abstraction, or one called 'friendnet' which implements a graph of social relations. * Those files have all the SQL. I never have raw SQL in my main application. * Those files never e.g. call into my web framework, have HTML, or otherwise. They're a stand-along abstraction. An ORM mutates a good abstraction (SQL) into a not-as-good one. Making a generic abstraction which works everywhere is hard, and SQL is the best one we've got. By making it domain-specific, we can mutate it into a good one. One of the key principles I use is to try to have more than one back-end. For some (like a kvs), that might be Elasticache AND in-memory AND postgres. For something relational, it might be postgres and sqlite.
matt7340 6y ago
Not a fan of ORMs at all, especially ActiveRecord style. They just bring a costly new layer of complexity and not enough value to justify it. I do very much like the object hydration that ORMs bring though. I think libraries like Dapper hit the perfect balance of manual everything vs automagic complexity.
mhd 6y ago
I'm more averse to people not using their DB to their full extent than whether they're doing that with an ORM or by just writing overly simplistic SQL. Granted, it's a bit more common with ORMs, especially if it's all ActiveRecord. But theoretically you could express yourself sufficiently well with DBIx::Class, SQLAlchemy, Hibernate and whatever's not just stubbed NPM-ware for JS. Just as long as people avoid too many uneccesary requests and/or do too much in code what could easily be solved by a view, window function, lateral join, stored procedure etc. And yeah, a lot of that gets thrown out if you need to boil it down to a lowest common denominator to ease testing or different deployments, but a lot of programmers don't even utilize SQlite to a degree beyond beginner's SQL-92.
enord 6y ago
I'm beginning to suspect a big problem here is one of level-violation. At least in the often promised push-button domainObjectClass to domainEntityTable ORM (batteries included). Three common (IME) manifestations: 1. the domainObjectClass is disallowed to do something across the ORM due to constraints enforced on the domainEntityTable (varchar truncation, anyone? No? Just me?) 2. some domainObjects fetched through the ORM are deserialized into an implicitly (e.g. null/empty collection members) or explicitly (violates some invariant enforced by domainObjectClass) invalid state 3. domainObjectClass has to grow hairy with bookkeeping metadata (read: implementation details) to facilitate work with partial object graphs while still enforcing domain invariants on the data in scope (null vs DbNull, "object with PK already exists" oh God please just kill me now) The common cause for the preceding examples is that the ORM (and it's tutorials) encourage you to do the same domain modelling twice, for the price of one. To wit: You do your domain modelling in your classes and the ORM translates this into a database schema with the same entities at the same level of abstraction. This causes a coupling of the semantics of database constraints to your domain objects and vice-versa: your database records may not violate domainObjectClass invariants otherwise inexpressible in the database engine. Whether or not this is a problem depends on context, but it happens either way. If and when it becomes a problem your options are usually: quit, rant on you blog or both.
matthewmacleod 6y ago
Recently I was thinking that the biggest reason this discussion keeps coming up (and is so unedifying) is that many of us have entirely different experiences with ORMs that colour our views. I "grew up" using Rails and ActiveRecord. I maintain that (from version 3 onwards) it's absolutely flipping amazing. Can't get enough of it. Never had an issue constructing queries, defining migrations, or dropping down to SQL when I needed to – and generally being able to define and use the entire "database layer" of an application in a robust and sensible manner. Love everything about it. In contrast, I picked up GORM to use with Go – thinking that I was generally pro-ORM and so it would be a straightforward thing to use. It has been an active impediment to getting work done, and I hate interacting with it. Part of this is no doubt due to my mindset and expectations, so I don't think it's fair to criticise the project itself. But I think it revealed to me that other people might have an entirely different experience with the benefits and costs of using an ORM, and that this is likely derived from both the tools they have used and the projects they have worked on. I guess the point is… being open-minded about the benefits and costs is always a good thing.
- midrus 6y ago
  Can't agree more with you. I have the same experience with the django ORM. At work all the Go fans are advocating against ORMs because the exact reasons you give. Then I look at the code they write, and just seeing hand written sql migrations ran by very flaky bash scripts makes me cringe. Let's not talk about all those sql queries embedded in the code....
dyeje 6y ago
JavaScript ORM hatred is well deserved. None of them are great. TypeORM is probably the best but its documentation leaves alot to be desired and there are many rough edges you have to watch out for.
- jbob2000 6y ago
  I like Mongoose for MongoDB. For my dinky API and mobile app, it does exactly what I need it to.
- 2color 6y ago
  Have you looked at Prisma? The documentation is very extensive. Would love to hear your thoughts. Disclaimer: I work there.
  - dyeje 6y ago
    I inherited TypeORM and I've used Sequelize in the past. I looked at Prisma out of curiosity. My main concerns were: 1. Focus on integrating with tech I don't care about (e.g Next.js, GraphQL). 2. Don't understand the business model. Seems like you want to lock folks into your ecosystem and then start charging for the surrounding products (e.g. Studio, Migrate).
    - nikolasburk 6y ago
      Nikolas here, I'm Daniel's (2color) colleague :) quickly want to follow up! > 1. Focus on integrating with tech I don't care about (e.g Next.js, GraphQL). Just to clarify, we don't focus on any particular stack but Prisma can be used with any framework/library you like! Would love to learn more about what exactly gave you the impression that we'd focus on Next.js and GraphQL so that we can make sure folks don't misinterpret this in the future :) > 2. Don't understand the business model. Seems like you want to lock folks into your ecosystem and then start charging for the surrounding products (e.g. Studio, Migrate). We are commercial open source company, similar to companies like Hashi, Vercel or Gatsby. It's not a secret that we are a VC backed company and will need to make money eventually. In fact, we're currently working on a first version of a commercial cloud product that, among other things, will enable developers to collaborate better when using Prisma's open source tools in their projects. This commercial offering will be primarily targeted at teams and larger development organizations. Nonetheless, the open source tools remain the core of our business and we're investing a lot of time and energy into them. Nobody will be forced to use the commercial services if all you need for your app is an ORM :)_
deergomoo 6y ago
I was about to comment how similar the examples look to Laravel's Eloquent ORM/query builder. Objection.js says it's built on top of something called Knex.js, and sure enough: > "Special thanks to Taylor Otwell and his work on the Laravel Query Builder, from which much of the builder's code and syntax was originally derived." [1] I've always loved Eloquent. It doesn't shy away from the fact that it's never going to be able to do everything a SQL query can, instead it focuses on making the stuff that's tedious and error prone to do by hand really expressive, and lets you drop back to SQL when you really need to. [1] http://knexjs.org http://knexjs.org
jillesvangurp 6y ago
ORMs solve several problems that depending on what you do may or may not be important for you. Problem #1: translating between objects/classes and table rows is mildly tedious to program depending on what language you use. For small amounts of tables it's actually not that much and it's relatively straightforward to maintain this. I'd argue that having a large number of tables is probably a result of using an ORM and encountering the object impedance mismatch. So, it's a problem you aggravate by using an ORM and not doing a proper domain design. More tables usually means more joins. And that's only convenient because the ORM does this for you. It's also slow and a bit of an anti-pattern whether you use ORMs or not. Problem #2: writing SQL natively is hard (for some). It can be of course; and you can do some complicated things in SQL. But if your table structure is simple, it's actually fairly easy to write the handful of queries you need. Being able to test and prototype your queries against a development database actually makes it easier. Also, this is another problem that is may be made worse by using an ORM because having too many tables means your queries become a mess of nested joins. Problem #3: porting queries between different databases is hard as they each have their own SQL dialects and subtle implementation differences. Having your ORM generate the appropriate queries for you side steps this problem. But of course you then lock yourself in to your ORMs way of writing queries making switching to another ORM harder. Also, you get to deal with the limitations of your ORMs support for SQL, which typically is a subset of native features. Also, switching databases is not actually that common as it is quite invasive to do. And of course they can be different enough that even using an in memory database for tests and postgresql for production is going to cause you issues with subtle differences in transaction semantics, or how certain queries work, etc. I usually side step that by just using a dockerized db. That's valid whether you use ORMs or not. Whether you use ORMs or not, good database design is important and knowing enough SQL that you don't need an ORM is kind of a pre-requisite for that. I've seen a lot of bad ORM usage where the people using it were quite clueless about that and made a lot of rookie mistakes. You can't blame the tools for that obviously. In my case, the above 3 problems are not really problems I have or that are high on my list of solving. I keep my table structure simple and easy to query. Whenever possible, I dumb my schema down to a glorified document store with just a handful of columns. I can knock those queries out in SQL easily and generally I don't migrate between different databases. In any case, most of my projects also use Elasticsearch so most of my querying is actually done against that instead.
eitland 6y ago
The hate against ORMs is mostly irrational but sadly self-sustaining at this point: I recently saw someone here honestly believe that the n+1 problem is unsolvable in major ORMs like Hibernate. That person seemed to be delighted to get a pointer to a solution, but way too often many just dismiss all explanations. If you are still reading you might be a bit interested so here are some things some of you might find interesting (Disclaimer, this is mostly from a PHP/Java/.Net-background): - When people like me and others choose ORMs it isn't because we don't know SQL, I for example knew SQL long before I touched an ORM. - There is generally no problem to break out of the ORM if you really need to optimize a statement. - But to be honest once you get rid of the (famous) n+1 situations ORMs are crazy fast. I think often some of them can be faster for a lot of people just like compilers started outsmarting more and more programmers lately.
Bodhisattya 6y ago
The funny thing is that the article here preaches that ORMs are here to make SQL easier, which runs counter to the perceived benefit that your devs won't deal with SQL once an ORM is in place (or atleast that is how I was introduced to Hibernate). I understand the point, but once I am SQL-aware, kinda don't see the point of having the middle layer.

50 points by dsego 6y ago

I think real improvement from SQL would be the ability to construct query in sets. Like I'd make a set of persons, check if it looks right, then another set of pets and if both are alright then compose the result set by joining them. Not by cutting and pasting a code but actually using variables. Your examples do everything in one statement which does not look so easy to compose, why?

spacemanmatt 6y ago
My favorite reasons for pushing something down into the database include performance, security, concurrency, and portability. Yes, portability. My client language is way more likely to change than my database.
- rini17 6y ago
  I don't mean that. I just like to construct any nontrivial query piecewise. SQL is about sets, when doing query you in fact make subsets and combine them using set operations. But the syntax makes this composition error prone and objection.js seems not to do much to improve it.
  - spacemanmatt 6y ago
    You've sorta captured my distaste for ORMs. I'm good at sets and relational algebra. Anything ORM interface that isn't RA is a leaky abstraction, and eventually I just have to go around it.
forgetfulness 6y ago
SQL has views and functions for this. It's indirect when looking at the code of one application accessing the database, but as the other poster said, those can be many, in different languages, at the same time and over time.

the_af 6y ago

Obligatory mention of the (in)famous article, "ORM is the Vietnam of Computer Science", frequently linked to from HN: http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie... The Vietnam of Computer Science goes into more detail with the problems that plague ORMs and why some people have "ORM hatred". Some of these reasons are glossed over or not mentioned by TFA.

Cthulhu_ 6y ago

I don't mind SQL; my main reason for using an ORM at the moment (gorm, it has its flaws) is one, automatic database migration, and two, I don't have to manually map a resultset to my struct; code like that is tedious and error-prone. Gorm is not the best when it comes to managing relationships though, to put it mildly.

TheChaplain 6y ago

I find the loud aversion for peoples choice of tools quite "eye roll"-worthy. IMHO use whatever that helps you actually deliver something.

coldtea 6y ago
IYHO there's no possibility of people: (a) using the wrong tool for the job (b) succumbing to fads and hype (c) forcing said tools to other's throats (e.g. manager making decisions, or team lead, or company policy, or "opinionated" non-technical customer) ? I'd say those cases deserve "loud aversion for peoples choice of tools".
- pdpi 6y ago
  None of those is justified, no. Where you see me using the wrong tool for the job, I see myself using a tool that may be flawed but is good enough for what I need right now. Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech. Where you see me forcing a tool down your throat, I see myself standardising on a compromise solution so we don't have ten approaches to the same problem in one single codebase. On any one of those situations, either of us might be right, wrong, or (most likely) somewhere in between. It's important to keep that in mind before loudly denouncing others' opinions, especially when the alternative is to just argue for your own opinion and letting me decide.
  - pydry 6y ago
    Where I loudly denounce something it's usually because it is supported by a wave of hype that will drown out muted objections. I can count 11 or 12 instances of this happening in tech. There's a reason Larry Ellison said the only industry more fashion driven than women's fashion is tech. Tech is extremely herdlike. If you are going against the herd you kind of have to be loud to get heard. Large corporations with marketing budgets and the people who got caught up in them will drown you out otherwise. Loud objections can cut through the bullshit-with-a-big-marketing-budget and in many cases (e.g. mongo) I'm pretty glad it did.

wzdd 6y ago

This looks a lot like LINQ to SQL. Is it the same? I'm surprised the article doesn't mention LINQ.

cies 6y ago
Nor does it mention jOOQ [1], arguably even more similar (as it is a lib and not a language built-in). Some page with actual queries from the docs: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-steps-step5 https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...

pjc50 6y ago

Interesting angle. I've come round to the benefits of ORMs for query building and results-to-objects. Especially since I used LINQ; linq-on-objects and linq-on-xml are incredibly useful even outside a database. The one big anti-pattern I've seen in systems that use ORMs is the "manual query" or especially "manual update": rather than doing something in the DB, the programmer retrieves some objects, iterates over them with a FOR loop or similar, makes changes, and then writes them back to the database. This is absolutely terrible for performance and prone to concurrency errors. (are there ORMs in which all returned data objects are forced to be immutable? I think that would help.)

paulryanrogers 6y ago
There are some patterns ORMs like Hibernate use to combat the races: versioning, pessimistic locking, etc. And while performance can be an issue for many businesses it's the read queries that are most often the bottleneck. If write performance is critical then hopefully you've got sustainable revenue to cover the cost to optimize it.
worble 6y ago
> (are there ORMs in which all returned data objects are forced to be immutable? I think that would help.) I'm not sure if this is exactly what you're asking, but Entity Framework has .AsNoTracking(), which will simply return the objects without doing any change tracking on them, so feel free to modify them as you wish afterwards without accidentally updating your database.

coldtea 6y ago

Came here to object to objection to ORM hatred. ORM were never worth the trouble. There's on "impedance mismatch", you just use data wrong (wrapped as objects as opposed to data records).

jbverschoor 6y ago
Nobody uses relational data anymore except when doing analytics or aggregation. Relational data is almost always mapped to an object.
- thecopy 6y ago
  >Nobody uses relational data anymore Source?
  - jbverschoor 6y ago
    I meant using the relational data from a query directly. The moment you're doing a join, you're simply mapping to an object manually
- cmrdporcupine 6y ago
  I think it's more that people don't seem to know what relational actually means, and schools are doing a terrible job in teaching it. And so we see the results.
  - ZephyrBlu 6y ago
    Care to explain?
    - BlackFly 6y ago
      I'm not porcupine, but I feel that we are definitely using relational models. The drive towards objects (and REST as well) tries to project data into a hierarchy. Orders belong to customers, so you nest orders under customers. But you are B2B, so you break into one market and realize that suddenly joint purchases are a thing. Suddenly you have orders with multiple customers. If you were using a relational model for backing the orders and customers, there is no real issue. An order is just a tuple of data, a customer is just a tuple of data, the "belongingness" is just modeled via foreign key constraints. Maybe you were smart enough to already model it as many to many and you just relax a uniqueness constraint in the join table or you had no such uniqueness constraint already and it was enforced by the application. Otherwise, you move the foreign key out of the order tuple into a joining table and move on to changing the projection queries and the downstreams. If you were storing it as nested data documents... I don't know because I have never done that, but I do know the trouble with trying to invert hierarchical addresses in such cases and it is annoying and inefficient (abstraction inversion). In a relational model, any hierarchy is a contrivance via foreign key constraints. These same attributes are easy to invert. If you have a business need for a hierarchy, that shows up in the business logic by the precise query used to project the relations along the foreign keys. In the scope of the comment made. I find it hard to believe that someone doesn't see that we are indeed using relational models of data and I find it sad that the known benefits apparently aren't so widely known. Yes, in a concrete use case you project out the data you need and type that (map it to objects). But if you modeled the data well, as your use cases increase in number or evolve your data model often doesn't need to change as much as your business logic if you did your job well. The job being relational data modelling. I myself use async programming, and there aren't really good ORMs for that since you don't want every single property of your object to be an async property (which most ORMs do with lazy loading).

mkl95 6y ago

I enjoy using the SQLAlchemy ORM. It makes it trivial to divide a query in several parts that you can mix and match. In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries.

fbn79 6y ago

Using Objection for more than a year in a medium sized ERP project. Not bad, but for sure on the next project I will use directly knex as querybuilder (with no ORM part). The ORM part that Objection add to knex don't bring much value and add a layer of complexity. The only functionality that I would miss would be the graph operations (withGraphFetched, upsertGraph, ecc.) but writing them in SQL (using KNEX) would not be a great problem.

nicoburns 6y ago
We're using knex at $dayjob, and we're not even using it for half our queries. Knex is pretty nice for simple CRUD operation, but for more complex queries we've found it much nicer to drop down to raw SQL (with knex.raw providing escaping). What we have found helpful is to stick common queries into a helper function so that we can get a consistently shaped object back.
bvm 6y ago
We use both in our code base. Sometimes knex makes sense, sometimes Objection makes sense. That's actually one of the nice things about it wrapping knex.
dsego 6y ago
Have you considered a tagged template literal sql builder, such as https://github.com/blakeembrey/sql-template-tag https://github.com/blakeembrey/sql-template-tag ? Keeps the SQL syntax so less to learn, but still offers parametrized queries.
- fbn79 6y ago
  Considered it, but I think that there are advantage in programmatically construct the query (see for example knex modify). Directly write down SQL or use template with sql make it harder. The only problem with knex is that it mutate at every operation instead of returning a new instance. I'm experimenting in wrapping it in a lazy monad that return a Fluture (https://github.com/FbN/iodio https://github.com/FbN/iodio)

mgreenleaf 6y ago

I have projects that use ORMs and projects that do not use ORMs. I have specific reasons for both types, and in certain circumstances one or another is more appropriate. ORMs are a dependency that are often constantly updated, prone to corner cases, and require a lot of library specific knowledge-- but they often make code more secure against small mistakes or tossing in a string interpolation, which is important when working with others, especially junior developers. They can be quite nice to work with in having type checked queries, building up queries from pieces using an API, but they also tend to be heavy and miss some features of your DB. Raw SQL on the other hand, is very flexible, standard, easy to read, and anyone who knows SQL can jump in. You can do REPL for queries and copy in, and edits to SQL are easy. The only dependency you need is the standard database drivers, and updates don't typically break things. If you have a basic understanding and use it right, security is great as well.

px43 6y ago
> If you have a basic understanding and use it right, security is great as well. I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. I'm sure there are a ton of reasonable use cases for raw SQL, but when I see the same dumb mistakes over and over again, it just makes me shake my head and wish more people just used ORMs. Major breaches happen every day due to SQL injections, so building up a narrative that developers can do raw SQL right if they're smart enough is just irresponsible IMO. Over and over again when I tell developers that they should use an ORM, they get offended, and then I end up finding trivial SQL injections. It kind of feels like telling people that it's perfectly safe to drive without a seat-belt so long as you're a good driver.
- sjaak 6y ago
  By Sturgeon's law [1] 90% of all code is crap, so it doesn't surprise me that that 90% of code contains sql injections ;-) [1] https://en.wikipedia.org/wiki/Sturgeon%27s_law https://en.wikipedia.org/wiki/Sturgeon%27s_law -> "Ninety percent of everything is crap."
- aww_dang 6y ago

noisy_boy 6y ago

I consider myself reasonably comfortable in SQL and don't mind crafting queries when it is optimized for performance and/or ORM wouldn't be able to produce something optimum. However, for typical CRUD stuff, I almost always use ORM because not having to deal with the boilerplate makes things so much more easier. E.g. JPA's query creation[0] allows me to dynamically generate the query based on the method name - that is just way too convenient to give up, I could use SQL too but there is no payoff in writing boilerplate compared to the convenience and decent performance provided by the ORM. [0]: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation https://docs.spring.io/spring-data/jpa/docs/current/referenc...

tyingq 6y ago

It has warts and limitations, but I find the approach that RedBean PHP uses to be very intuitive. It supports other patterns, but the one where you open up the ORM and let it make the schema dynamically as your code runs, then later "freeze" it, is pretty neat. See https://www.redbeanphp.com/index.php?p=/quick_tour https://www.redbeanphp.com/index.php?p=/quick_tour for an overview. Are there popular ORMs that work this way for other languages?

cies 6y ago

Query builders (as I call these type of tools) are great, especially when they allow typesafety over the SQL barrier. jOOQ[1] (on JVM) does this with code generation: migrate your db, re-generate the project specific jOOQ lib and all incompatible queries are not showing up red-underlined in your IDE. I liked this as well in ORMs: the type safety them CAN provide. Query builders VS ORMs, both have their advantages. Doing a Users.byId(123) is easier to read'n'write than doing so with a query builder. Its the complex queries where ORMs suck, string-SQL gives no type safety and query builders shine (given they provide type safety). 1: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-steps-step5 https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...

lukaseder 6y ago
> Doing a Users.byId(123) jOOQ can do that as well through various ways if that's a burden: https://www.jooq.org/doc/latest/manual/sql-execution/daos/ https://www.jooq.org/doc/latest/manual/sql-execution/daos/
- cies 6y ago
  I stand corrected. And thanks for chiming in chief :)

deleted 6y ago

[deleted]

pydry 6y ago

>I created an ORM called objection.js for exactly the reasons Thomas listed. The design goal of objection.js is to allow you to use SQL whenever possible and only provide a DSL (Domain Specific Language) or a custom concept when something cannot be easily done using SQL. That's the exact opposite way I use an ORM.

NicoJuicy 6y ago

Normally, i go by ORM by default. If i know it's a heavy query or the ORM won't optimize it ( eg. lots of relations and creating a n+1 problem), then i do the query myselve. If something is going slow, I try to optimize it. If that means writing SQL, sure. Why not A programmer's toolbox contains multiple tools. Use the right one.

krzkaczor 6y ago

Objection.js is neat but give me Objection with full type safety (Prisma style) and I am in haven.

Tade0 6y ago

As a complete amateur regarding the backend I use ORMs for two reasons: 1. Migrations - I only recently started diving deeper into the concept so an abstraction is in order for now. 2. Security - I'm at a point where I don't know what I don't know and thus would rather not rely solely on my own knowledge. I'm sure such systems introduce their own security risks, but since those are mostly open-source projects I can at least count on somebody to eventually discover any such issue.

aeyes 6y ago
Database migrations are completely independent of using an ORM. You can still create migrations from your model definitions but that doesn't mean that you have to use an ORM to query a database. Some tools you might find useful: Flyway, Liquibase, Evolve
ryanianian 6y ago
You don't need an ORM for migrations. Tools like flyway and others can rely purely on `.sql` files with some naming conventions and can help keep your app and db schemas in sync. You also don't need an ORM for security, and an ORM doesn't always prevent you from footgunning. Use query builders, `?` or `:named` placeholders (as your DB driver recommends), and almost never use string operations to build queries. Similarly, grow suspicious of your db or application model if you find yourself having to write increasing amounts of SQL to appease your ORM. ORMs are a tool. Neither good or bad on their own. Many applications outgrow them, or they don't properly refine their object models as the db or application changes. Lots of applications hold on to the security blanket of their ORMs for far too long, or they let the DB objects proliferate very deeply into their stack such that changing the db or the app model become nearly impossible. This is when apps increasingly try to outsmart the ORM, creating massive amounts of tension and friction in doing even simple things. (As anyone who has tried to do large-scale migrations or backfills that weren't possible in raw sql will tell you.) Learning to recognize all this is above the level of "amateur", but it seems like you've already learned to identify some of the good that the tooling can provide.

greatgib 6y ago

For me, ORM are the good illustration of the quote: "The road to hell is paved with good intentions" For expert-beginner and "architecture manager", it looks like a good idea, giving an abstraction and co. But, I think that most haters are the people that already had used for a few of them for long enough to be burned and get the experience. ORM job is supposed to simplify the job of dealing with the database, but most of the time, the final things is a lot more complicated and a mess because of the ORM. The main issue being that ORM operations looks like auto magic to the user, but this auto-magic still have a lot of rules of specific functions and settings that have to be used for the things working correctly, because it is just executing sql in background. And in the end, it is so much more complicated to do understand and do simple things with the orm that it is to do the original sql operation. The worst of all is when the ORM has support for some callback or automatic hooks, like "post-update", etc...

fennecfoxen 6y ago
I don’t find the “magic” SQL generation that problematic in practice. The main problem with ORMs is that as a class they are, broadly speaking, all fat model ActiveRecord patterns which encourage you to make the models fatter and to spread invisible dependencies on these models (and their related models and the methods) throughout the codebase. The result is invariably a big ball of mud. Immutable entities, produced by a set of ORM-like and mostly automatic GetX/PersistX services, is more generally the sound way forward. Unfortunately it’s also far less convenient than most ORMs in practice, as frameworks like Rails prioritize realizing the messier vision.
- greatgib 6y ago
  A few examples of the magic and the implicit rules that leads to confusion: query(object1).where(object1.prop=10).orderby(prop).join(object2).where(object1.prop=object2.bar) Looks like to be logical blocks that you can easily chain together, but is the order important and can each "subfunction" be used anywere? or after a specific previous one? Probably join couldn't be used after orderby. What is the state before the join, the available tables? Where inside the sql will be applied the where? What object will be available? Some ORM even do kind of "autojoin" on table that it detects based on arguments used in later where clauses.

terenceng2010 6y ago

I use dapper with a plugin called dapper.simplecrud (https://github.com/ericdc1/Dapper.SimpleCRUD https://github.com/ericdc1/Dapper.SimpleCRUD). This already helps to reduce the need to write simple SQL code repeatedly quite a lot. When doing SQL operations more complicated than a simple crud, I resort to raw SQL or stored procedure.

pmjones 6y ago

> People always seem to ignore the third option: using an ORM that embraces SQL! I had a similar revelation several years ago, based on the distinctions between a "domain model" and "persistence model" described by Mehdi Khalili, resulting in Atlas for PHP: http://atlasphp.io http://atlasphp.io

dsego 6y ago
Does this correspond to the data mapper pattern? Seems like people mostly object to active record orm variants. https://typeorm.io/#/active-record-data-mapper/what-is-the-data-mapper-pattern https://typeorm.io/#/active-record-data-mapper/what-is-the-d...
- pmjones 6y ago
  Yes, the upper/outer layers are a Data Mapper implementation; cf. the Atlas.Mapper package. FWIW, the Data Mappers are built on top of Table Data Gateways, which in turn are built using Query Builders and a connection locator. This lets you start at lower levels and work your way up, while at the same time allowing you to "reach down" from the upper levels to the lower ones when you need to.

fabian2k 6y ago

One case I find really annoying to write in SQL is inserting or updating entities with several many-to-many relationships. That is just plain annoying to do by hand, so I'd either have to use an ORM or write a small wrapper myself for this. Reading entities with many-to-many relationships is also often easier with an ORM, though you really have to be aware how the ORM will write this kind of query to avoid issues. ORMs are a very leaky abstraction when performance is concerned. Another part that is very useful is the query building itself, though you can get that without an ORM. Having composable query fragments is very useful, doing that by hand is extremely tedious, error-prone and a potential security risk if you're not careful. I'm comfortable writing SQL by hand, but I do prefer to have an ORM as long as I can also bypass it for queries that are difficult or impossible to do with the ORM. But an ORM can't replace knowing how databases work, and they do require you to understand more under-the-hood details than most other types of libraries.

cbetti 6y ago
Personally, I think ORM has a reasonable use case here, but I also find doing this using SQL to be straightforward and easier to reason about during future refactors. Take a look at JDBI's Repository pattern for a simple compromise between SQL and ORM for this use case in particular.

Xophmeister 6y ago

I've certainly fallen in to the anti-ORM camp, because I know SQL well and at least like to think I can put together a schema better than any ORM can. (I usually can.) However, then my code becomes tightly coupled to the schema, despite my abstractions, which makes it harder to change and basically impossible to unit test.

arcturus17 6y ago
What do you end up doing, then?
- Xophmeister 6y ago
  Hope I get it right (or right enough) first time an then worry about how I'm going to set up integration testing for this "thing" :P
wegs 6y ago
I make my own abstractions. I might have a file called 'kvs' which implements a key-value-store abstraction, or one called 'friendnet' which implements a graph of social relations. * Those files have all the SQL. I never have raw SQL in my main application. * Those files never e.g. call into my web framework, have HTML, or otherwise. They're a stand-along abstraction. An ORM mutates a good abstraction (SQL) into a not-as-good one. Making a generic abstraction which works everywhere is hard, and SQL is the best one we've got. By making it domain-specific, we can mutate it into a good one. One of the key principles I use is to try to have more than one back-end. For some (like a kvs), that might be Elasticache AND in-memory AND postgres. For something relational, it might be postgres and sqlite.

matt7340 6y ago

Not a fan of ORMs at all, especially ActiveRecord style. They just bring a costly new layer of complexity and not enough value to justify it. I do very much like the object hydration that ORMs bring though. I think libraries like Dapper hit the perfect balance of manual everything vs automagic complexity.

mhd 6y ago

I'm more averse to people not using their DB to their full extent than whether they're doing that with an ORM or by just writing overly simplistic SQL. Granted, it's a bit more common with ORMs, especially if it's all ActiveRecord. But theoretically you could express yourself sufficiently well with DBIx::Class, SQLAlchemy, Hibernate and whatever's not just stubbed NPM-ware for JS. Just as long as people avoid too many uneccesary requests and/or do too much in code what could easily be solved by a view, window function, lateral join, stored procedure etc. And yeah, a lot of that gets thrown out if you need to boil it down to a lowest common denominator to ease testing or different deployments, but a lot of programmers don't even utilize SQlite to a degree beyond beginner's SQL-92.

enord 6y ago

I'm beginning to suspect a big problem here is one of level-violation. At least in the often promised push-button domainObjectClass to domainEntityTable ORM (batteries included). Three common (IME) manifestations: 1. the domainObjectClass is disallowed to do something across the ORM due to constraints enforced on the domainEntityTable (varchar truncation, anyone? No? Just me?) 2. some domainObjects fetched through the ORM are deserialized into an implicitly (e.g. null/empty collection members) or explicitly (violates some invariant enforced by domainObjectClass) invalid state 3. domainObjectClass has to grow hairy with bookkeeping metadata (read: implementation details) to facilitate work with partial object graphs while still enforcing domain invariants on the data in scope (null vs DbNull, "object with PK already exists" oh God please just kill me now) The common cause for the preceding examples is that the ORM (and it's tutorials) encourage you to do the same domain modelling twice, for the price of one. To wit: You do your domain modelling in your classes and the ORM translates this into a database schema with the same entities at the same level of abstraction. This causes a coupling of the semantics of database constraints to your domain objects and vice-versa: your database records may not violate domainObjectClass invariants otherwise inexpressible in the database engine. Whether or not this is a problem depends on context, but it happens either way. If and when it becomes a problem your options are usually: quit, rant on you blog or both.

matthewmacleod 6y ago

Recently I was thinking that the biggest reason this discussion keeps coming up (and is so unedifying) is that many of us have entirely different experiences with ORMs that colour our views. I "grew up" using Rails and ActiveRecord. I maintain that (from version 3 onwards) it's absolutely flipping amazing. Can't get enough of it. Never had an issue constructing queries, defining migrations, or dropping down to SQL when I needed to – and generally being able to define and use the entire "database layer" of an application in a robust and sensible manner. Love everything about it. In contrast, I picked up GORM to use with Go – thinking that I was generally pro-ORM and so it would be a straightforward thing to use. It has been an active impediment to getting work done, and I hate interacting with it. Part of this is no doubt due to my mindset and expectations, so I don't think it's fair to criticise the project itself. But I think it revealed to me that other people might have an entirely different experience with the benefits and costs of using an ORM, and that this is likely derived from both the tools they have used and the projects they have worked on. I guess the point is… being open-minded about the benefits and costs is always a good thing.

midrus 6y ago
Can't agree more with you. I have the same experience with the django ORM. At work all the Go fans are advocating against ORMs because the exact reasons you give. Then I look at the code they write, and just seeing hand written sql migrations ran by very flaky bash scripts makes me cringe. Let's not talk about all those sql queries embedded in the code....

dyeje 6y ago

JavaScript ORM hatred is well deserved. None of them are great. TypeORM is probably the best but its documentation leaves alot to be desired and there are many rough edges you have to watch out for.

jbob2000 6y ago
I like Mongoose for MongoDB. For my dinky API and mobile app, it does exactly what I need it to.
2color 6y ago
Have you looked at Prisma? The documentation is very extensive. Would love to hear your thoughts. Disclaimer: I work there.
- dyeje 6y ago
  I inherited TypeORM and I've used Sequelize in the past. I looked at Prisma out of curiosity. My main concerns were: 1. Focus on integrating with tech I don't care about (e.g Next.js, GraphQL). 2. Don't understand the business model. Seems like you want to lock folks into your ecosystem and then start charging for the surrounding products (e.g. Studio, Migrate).
  - nikolasburk 6y ago
    Nikolas here, I'm Daniel's (2color) colleague :) quickly want to follow up! > 1. Focus on integrating with tech I don't care about (e.g Next.js, GraphQL). Just to clarify, we don't focus on any particular stack but Prisma can be used with any framework/library you like! Would love to learn more about what exactly gave you the impression that we'd focus on Next.js and GraphQL so that we can make sure folks don't misinterpret this in the future :) > 2. Don't understand the business model. Seems like you want to lock folks into your ecosystem and then start charging for the surrounding products (e.g. Studio, Migrate). We are commercial open source company, similar to companies like Hashi, Vercel or Gatsby. It's not a secret that we are a VC backed company and will need to make money eventually. In fact, we're currently working on a first version of a commercial cloud product that, among other things, will enable developers to collaborate better when using Prisma's open source tools in their projects. This commercial offering will be primarily targeted at teams and larger development organizations. Nonetheless, the open source tools remain the core of our business and we're investing a lot of time and energy into them. Nobody will be forced to use the commercial services if all you need for your app is an ORM :)_

deergomoo 6y ago

I was about to comment how similar the examples look to Laravel's Eloquent ORM/query builder. Objection.js says it's built on top of something called Knex.js, and sure enough: > "Special thanks to Taylor Otwell and his work on the Laravel Query Builder, from which much of the builder's code and syntax was originally derived." [1] I've always loved Eloquent. It doesn't shy away from the fact that it's never going to be able to do everything a SQL query can, instead it focuses on making the stuff that's tedious and error prone to do by hand really expressive, and lets you drop back to SQL when you really need to. [1] http://knexjs.org http://knexjs.org

jillesvangurp 6y ago

ORMs solve several problems that depending on what you do may or may not be important for you. Problem #1: translating between objects/classes and table rows is mildly tedious to program depending on what language you use. For small amounts of tables it's actually not that much and it's relatively straightforward to maintain this. I'd argue that having a large number of tables is probably a result of using an ORM and encountering the object impedance mismatch. So, it's a problem you aggravate by using an ORM and not doing a proper domain design. More tables usually means more joins. And that's only convenient because the ORM does this for you. It's also slow and a bit of an anti-pattern whether you use ORMs or not. Problem #2: writing SQL natively is hard (for some). It can be of course; and you can do some complicated things in SQL. But if your table structure is simple, it's actually fairly easy to write the handful of queries you need. Being able to test and prototype your queries against a development database actually makes it easier. Also, this is another problem that is may be made worse by using an ORM because having too many tables means your queries become a mess of nested joins. Problem #3: porting queries between different databases is hard as they each have their own SQL dialects and subtle implementation differences. Having your ORM generate the appropriate queries for you side steps this problem. But of course you then lock yourself in to your ORMs way of writing queries making switching to another ORM harder. Also, you get to deal with the limitations of your ORMs support for SQL, which typically is a subset of native features. Also, switching databases is not actually that common as it is quite invasive to do. And of course they can be different enough that even using an in memory database for tests and postgresql for production is going to cause you issues with subtle differences in transaction semantics, or how certain queries work, etc. I usually side step that by just using a dockerized db. That's valid whether you use ORMs or not. Whether you use ORMs or not, good database design is important and knowing enough SQL that you don't need an ORM is kind of a pre-requisite for that. I've seen a lot of bad ORM usage where the people using it were quite clueless about that and made a lot of rookie mistakes. You can't blame the tools for that obviously. In my case, the above 3 problems are not really problems I have or that are high on my list of solving. I keep my table structure simple and easy to query. Whenever possible, I dumb my schema down to a glorified document store with just a handful of columns. I can knock those queries out in SQL easily and generally I don't migrate between different databases. In any case, most of my projects also use Elasticsearch so most of my querying is actually done against that instead.

eitland 6y ago

The hate against ORMs is mostly irrational but sadly self-sustaining at this point: I recently saw someone here honestly believe that the n+1 problem is unsolvable in major ORMs like Hibernate. That person seemed to be delighted to get a pointer to a solution, but way too often many just dismiss all explanations. If you are still reading you might be a bit interested so here are some things some of you might find interesting (Disclaimer, this is mostly from a PHP/Java/.Net-background): - When people like me and others choose ORMs it isn't because we don't know SQL, I for example knew SQL long before I touched an ORM. - There is generally no problem to break out of the ORM if you really need to optimize a statement. - But to be honest once you get rid of the (famous) n+1 situations ORMs are crazy fast. I think often some of them can be faster for a lot of people just like compilers started outsmarting more and more programmers lately.

Bodhisattya 6y ago

The funny thing is that the article here preaches that ORMs are here to make SQL easier, which runs counter to the perceived benefit that your devs won't deal with SQL once an ORM is in place (or atleast that is how I was introduced to Hibernate). I understand the point, but once I am SQL-aware, kinda don't see the point of having the middle layer.