9 ms·

I hate ORMs with the fury of a thousand suns. The problem is that I know SQL but now I have to spend a bunch of time trying to figure out how to convert SQL in

by Osiris 5y ago

I hate ORMs with the fury of a thousand suns. The problem is that I know SQL but now I have to spend a bunch of time trying to figure out how to convert SQL into ORM X just so it can convert it back to inefficient SQL. SQL mostly translates between various databases but ORMs are unique and you have to learn a new API for each one. I'm on a project using TypeORM and it has been fantastic at helping developers on my team make really bad schemas due to not understanding how to use TypeORM to make the right relationships. Currently I'm looking at pg-types because you just write SQL and it just helps by making some TypeScript types for you. (I have used ORMs in C#, PHP, and JavaScript and I hate all of them).

hn_throwaway_99 5y ago
You're being downvoted, but I completely agree. If you are on Node and Postgres I highly recommend using slonik - it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... https://github.com/gajus/slonik https://github.com/gajus/slonik
- bhj 5y ago
  Along similar lines but more simple/agnostic, check out node-sql-template-strings[0] or sqlate[1]. [0]: https://github.com/felixfbecker/node-sql-template-strings https://github.com/felixfbecker/node-sql-template-strings [1]: https://github.com/moll/js-sqlate https://github.com/moll/js-sqlate
  - mirekrusin 5y ago
    for mssql https://github.com/appliedblockchain/tsql/ https://github.com/appliedblockchain/tsql/
- girvo 5y ago
  > it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections While I think this is totally the way to go, it is kind of amusing that PHP's PDO system was the "right" way to go all along in some ways
  - hn_throwaway_99 5y ago
    Meh, not really. Some of the biggest benefits from slonik are only possible because of Javascript's support for tagged template literals [1], e.g. you write a statement like sql`SELECT id FROM foo WHERE bar = ${barValue}` Tagged template literals make it feel like you're just doing string concatenation, but it's done in such a way where under the covers it's actually creating prepared statements, and it is literally impossible to have SQL injection bugs unless you deliberately go out of your way to make them. 1. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#tagged_templates https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
    - girvo 5y ago
      That is exactly what PDO’s prepared statements do. It’s not string concatenation, and it’s explicitly to prevent SQL injection? I must be misunderstanding I guess.
- golergka 5y ago
  Thanks for the link to the slonik, never heard about it before. But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped?
  - hn_throwaway_99 5y ago
    > But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped? You are correct. But note that pgtyped is really only able to generate types because it actually needs to run your queries against a running instance of your DB. Some contributors have commented they are working on something similar for Slonik, https://github.com/gajus/slonik/pull/267#issuecomment-840559530 https://github.com/gajus/slonik/pull/267#issuecomment-840559...
- zomglings 5y ago
  The "Stop using knex.js" blog post misses a very important point in favor of using knex.js: database migrations. Migrations allow a development team to synchronize database schema changes across multiple branches of development. It is the main reason I end up using tools like knex.js (node) and sqlalchemy (python). As far as knex is concerned, you can use it to manage migrations without using it in your application code.
  - hn_throwaway_99 5y ago
    I don't really think that blog post misses that, I just think that DB migrations functionality is very different from SQL builder functionality, and that blog post is referring to the SQL builder part of this. FWIW, I do use knex for migrations, but I hate the SQL builder part of it, so most of my migrations are littered with knex.raw statements. This was discussed at length in this slonik issue, https://github.com/gajus/slonik/issues/42 https://github.com/gajus/slonik/issues/42 , with some recommendations for migrations functionality.
    - ttfkam 5y ago
      Idempotent SQL updates are my preference. ``` ALTER TABLE IF EXISTS ADD COLUMN IF NOT EXISTS foo varchar; CREATE TABLE IF NOT EXISTS sample ( -- all the fields including foo ); ``` Run it on a clean DB, you get the table. Run it on an existing DB, it adds the column. Run it on a current DB, it does nothing. Run it once or a hundred times, the DB ends up in the same target state. Can only be used on some databases though. The DBs without transactional DDL are a risk. Then again, they were a risk no matter what method you use. (I'm looking at you, MySQL.)
- ttfkam 5y ago
  I prefer the postgres module. 0 dependencies. Easy to use API. Wicked fast. Also really hard to fall victim to SQL injection attacks unless you actually put some extra effort into doing something foolish. https://github.com/porsager/postgres#readme https://github.com/porsager/postgres#readme
nemo1618 5y ago
> the fury of a thousand suns off topic, but I recently learn that this phrase dates back to the Bhagavad Gita! If the radiance of a thousand suns Were to burst at once into the sky That would be like the splendour of the Mighty One
- zentropia 5y ago
  If I remember well was a phrase used by Oppenheimer about the atomic bomb, a sentence that he read from Bhagavad Gita.
- OJFord 5y ago
  How literally was that translated though? Ngram viewer shows [0] plenty of usage of 'of a thousand suns' well before that 1944 translation [1], though it certainly picked up after that, no doubt helped by Oppenheimer quoting it (that translation specifically, recently published at the time) w.r.t. the Manhattan project. Confusingly [1] links to the passage in a different (1909-14) translation [2] which features the 'of a thousand suns' part but is otherwise different. I just wonder, for example if the original was something like लक्षाणाम् सूर्याणाम् 'lakshāñām sūryāñām' (I think that's what I mean, I'm learning Hindi not Sanskrit! Just looking up लाख & सूरज roots.) but translated to 'thousand' on the basis that it just meant 'a lot', and that sounded better in English and was perhaps even already a phrase. I don't mean to doubt you, just thought it was interesting, and translated texts are a bit of a can of worms. [0] - https://books.google.com/ngrams/graph?content=of+a+thousand+suns&year_start=1800&year_end=2008&corpus=26&smoothing=3 https://books.google.com/ngrams/graph?content=of+a+thousand+... [1] - https://www.bartleby.com/73/123.html https://www.bartleby.com/73/123.html [2] - https://www.bartleby.com/45/4/11.html#44 https://www.bartleby.com/45/4/11.html#44
runarberg 5y ago
I’m usually not a fan of dissing technologies that one does not use/like but others do. I don’t use ORMs but I’m sure the reason they are so popular is that they provide something of value to the people that do use them. However, your reasons for disliking ORMs seems a little like hearsay. I am interested in seeing how the responses to this post will be, although I’m afraid that this might turn into a flame-war. I wonder how often the pro-ORM flame wars the anti-ORM camp here on HN.
trm42 5y ago
I like ORMs for simple things like basic joins and wheres but in many cases more complicated SQL feels actually easier and more predictable than trying to convert them into ORM syntax. There's a limit to the usefulness of the ORM syntaxes.
Waterluvian 5y ago
Why are you being forced to use ORMs if you don't want to and they're not the correct tool for the job?
- runawaybottle 5y ago
  There are a lot of insufferable tech leads that make the decision. I know how to spot them because I’m insufferable too. My only saving grace is that I make a deliberate attempt to always say to myself ‘would this feel like cognitive overhead to others?’, and walk away from picking that fight. Many people don’t do that. I’ve been on projects where every other week the tech lead shows up and adds more and more layers to the stack. Unless you want to constantly fight, you just let it go and deal with it like a professional. Otherwise, I would have reached across my screen by now and slapped someone. I can tell you this, there’s someone out there that’s going to build something (at an actual paying job with other humans) with bleeding edge Deno, DenoDB, Typescript, AWS infra sometime very soon for no good reason. There’s not enough slaps in the world to stop them. The fear of god does not exist in these people because no one ever says shit.
- pjmlp 5y ago
  When one is yet another cog on the machine, without any saying on the project technical directions.
  - williamdclt 5y ago
    ...or when one is an empowered individual contributor yet doesn't have full decision power in the project technical direction. Other people also have opinions
29athrowaway 5y ago
The premise of an ORM is that you will be saving time using simple abstractions. But in reality, those abstractions will be leaky, and you'll be spending significant time trying to understand what the ORM is doing, and finding workarounds for ORM problems. Also, each time something fails, there will be one more moving part to troubleshoot. Once you troubleshoot your problems and analyze what the ORM is doing, you'll realize that the price you paid for convenience is very high: you have sold your future in exchange for some "convenience" that actually gives you more work to do, makes everything slower and you didn't even need. Why? Because most ORMs try to target every database, so they offer a feature set that is the lowest common denominator for all the databases they support. Does that sound like a good idea? no.
- kyleee 5y ago
  Yep, and most projects probably don't switch underlying DBs so that part of the allure of an ORM is wasted
  - ttfkam 5y ago
    Yes! 100x this!
TranquilMarmot 5y ago
Where I'm at now, we have a big huge Java app that uses MyBatis (https://mybatis.org/mybatis-3/ https://mybatis.org/mybatis-3/) It involves writing SQL inside of XML files and doing a lot of manual mapping from columns back to the objects. At first I REALLY disliked it but over time I have grown to absolutely love being able to just write SQL queries that can return whatever. Writing raw SQL really lets you optimize and create complex queries when you need to in a way that you can't with an ORM.
- hu3 5y ago
  I love SQL and being able to squeeze performance from these magical blackboxes called database servers. But how do you deal with refactoring? If I want to change a column on a db table or a property in a Java/C# class can the compiler tell me where I forgot to update without much ceremony? This is a problem I had in the past on a large project. The result is that we devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt. Some ORMs solve this by scanning the database schema and creating a class per table and a field/property per column. This way we get compilation errors if database doesn't match code and have the opportunity to investigate it before it blows on the customer screen as a runtime error.
  - dansomething 5y ago
    > But how do you deal with refactoring? We solved this problem by having thorough automated test coverage for all SQL queries. Here's basic lifecycle of a test: 1) Create the test database 2) Migrate the database to the latest schema version 3) Insert test data 4) Execute the SQL query 5) Validate the results of the query
    - hu3 5y ago
      Tests are nice. But I see disadvantages with relying on them: 1) We now need 100% test coverage for queries. Forget one of them and the castle might fall on the client side. This can be mitigated with gatekeepers on CI/CD pipeline to ensure 100% coverage. But it's still quite the effort. 2) Our feedback loop for developing refactoring is now slower now since we need to run all tests to see what broke after a schema change. In contrast to an IDE giving compilation errors. 3) If we build SQL queries by composing them, which is common pattern for business rule validations, there will be many branches to be tested. 4) We won't have auto-completion of table and column names in the IDE. And if you mistype them your feedback is slower. I say that as someone who went all in SQL query builders and composability in a project but when it grew large we were still afraid of changing schema despite having a very high test coverage.
hermanradtke 5y ago
Check out slonik if you want something that does not go full pg-types. I combine it with io-ts for runtime validation.
lxe 5y ago
Have you tried Ruby on Rails' ActiveRecord? I like it! It's easy to use and easy to integrate with "raw sql" if needed.
KingMachiavelli 5y ago
What's the alternative? Are you saying it's better to use raw SQL or to use your own home grown convenience functions for creating tables, selecting rows, etc.? And once you have the data from the SQL database are you keeping it just in arrays/dictionaries? Or should it at least be mapped to a class structure? As someone dealing with a bespoke SQL schema and mix of in-house SQL translation layers for different DBs, wrapper functions... I would think any ORM would be better designed since it has a singular purpose and database experts work on their development.
- bjt 5y ago
  > What's the alternative? Not sure the parent comment would like it, but there's a middle ground between ORM and raw SQL that I consider a sweet spot. It's more of a "query builder" library that gives you language-appropriate constructs for building any SQL you like, but also provides more correctness guarantees than just writing raw SQL strings. SQLAlchemy's "expression" layer, for example, does this really nicely. It existed long before the higher "ORM" layer came along, and can be used without having to touch the ORM. In Go, I like the Goqu library for the same purpose. In Rails land, my understanding is that the underlying Arel layer is more like this pattern, as opposed to the higher level Active Record ORM. In Java, it's jOOQ instead of Hibernate.
  - Supermancho 5y ago
    There was a PHP library called NotORM which was great.
    - g8oz 5y ago
      ezSQL is a similar project, also good.
  - gmac 5y ago
    For TypeScript, there’s a very helpful list here: https://phiresky.github.io/blog/2020/sql-libs-for-typescript/ https://phiresky.github.io/blog/2020/sql-libs-for-typescript...
woutr_be 5y ago
I use TypeORM quite often, especially in projects with fairly simple queries. But I always make it a point to: * Write migrations first, so that you define your own schemas * Disable TypeORM synchronize, make your migrations the source of truth instead of the entities * Model your entities after your schema
smt88 5y ago
I've been using pgTyped in production for months and it has been phenomenal. There's another one called (I think) Zapatos that has some similar qualities.
- gmac 5y ago
  Yes: https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
alexose 5y ago
I'm with you. ORMs feel like _exactly_ the wrong place to abstract. In my experience, database calls always end up being the thing you want the most control over.
redact207 5y ago
I find people hate/are ok with ORMs based on how they're used. If you're using it to ad-hoc query your db then it's understandable you'll hate it - a leaky and poor abstraction over sql. Probably a bad fit. Projects where I've seen it work well is when most of the logic is in the app with per row/per aggregate changes. In these apps it's only used for the "object relational mapping" side of things - ie to marshal types to/from db rows. I've never found auto-migrations in ORMs good for anything less than a 1 day project - it's a world of hurt.
- Aeolun 5y ago
  > I've never found auto-migrations in ORMs good for anything less than a 1 day project - it's a world of hurt. Just to offer a counterpoint. Every project I worked on that did not have automatic migrations was extremely flawed in other ways as well. Manually keeping track of your DB schema, and indeed, seeing it as something separate from the code that needs to interact with it is a bad idea in my opinion. It’s same as ‘infrastructure as code’, database also needs to be ‘database as code’.
  - ttfkam 5y ago
    That's why I prefer idempotent SQL DDL updates. A disk full of up/down scripts makes me nervous. Most folks test their up scripts. They almost NEVER adequately test their down scripts, so you're left with this false sense of security moving forward even though revision 63 of 64 has a bug in the down portion…which you only find when you're trying to revert to the state of rev 60. As for ORM migrations, of course they work. They've dumbed down your use of the database to the lowest common denominator (looking at you, MySQL). IF EXISTS and IF NOT EXISTS are your good friend. Run the script, the database will be at the target state. When all databases are past a certain point, remove the appropriate ALTER TABLE IF EXISTS ADD COLUMN statements. Makes source diffing much easier, is a consistent single source of truth, and allows for pruning old parts as needed.
wolframhempel 5y ago
I couldn't agree more. ORMs might be an ok choice to get started quickly and to maintain a consistent way of working across teams, but over time they very frequently become a bottleneck. Having a handwritten, optimized SQL with specific joins and subqueries, tailored to the exact problem - or even better yet, a pre-planned query or postgres function is often orders of magnitudes more efficient and can make a substantial difference in terms of response times and running costs for your db servers.
- tester34 5y ago
  Why not both? Approach where you use pure SQL for reads and ORM for saves is not that uncommon
  - wolframhempel 5y ago
    That's fair - though I feel that ORM enthusiasts would advocate for the consistency that comes with the ORM on read, the automated resolution of relationships and the mapping to an object in the native language.
tester34 5y ago
I don't agree I used EF Core and Dapper for like 3 years and ORMs boost productivity significantly. I tend to check what SQL is generated and if something is complicated and generated SQL sucks, then I use micro ORM like Dapper and use raw sql. I agree that ORMs aren't easy because you have to learn them, but I think it's worth, unless you have to learn many different ORMs.
bobmaxup 5y ago
Don't most ORMs have ways to do raw queries when needed?
- viraptor 5y ago
  Yes. It's often a false dichotomy. You don't have to make a decision 100% either way. Just write SQL when it's complicated enough for a query to be useful. And you can still use ORM to write that `foo = Foos.recent.for_user(id)` which happens in small variations many times in the app, where you gain nothing but typing practice by writing pure SQL.
  - KptMarchewa 5y ago
    With JDBI you can just write @SqlQuery("SELECT * FROM users WHERE id = :id") User find(String id); and not use any of the ORM footguns.
    - viraptor 5y ago
      I posted an example which is not a simple lookup by id. It was specifically "Foos.recent.for_user(id)" because you can have multiple Foos, with a (reusable) scope `.recent` that you can use in other queries and a lookup for a given user which likely includes a join. So sure, you can write this out as multiple simple queries, but do you really want to code the repeated query for every case of: Foos.recent.for_user, Foos.active.for_user, Foos.recent.by_something, Foos.recent.active.by_something? And what if you don't want a whole object in that case, but only one column? ActiveRecord for example has you covered with `.pluck(:single_column)` that you can append at the end without writing yet another full query. Writing the simple stuff every time, you get footguns simply by repeating trivial code - that leads to copy-paste and forgot-to-change-one-of-them mistakes.
nine_k 5y ago
No, ORMs do much more. They turn slices of resultsets into objects. They lazy-load related objects instead of using one efficient join and doing one query; they in general have trouble representing results of projections and joins. They fetch all "attributes of the object" when you need to select a couple of columns from two dozen. They make the objects mutable, and introduce dirty state without transactional control. They allow running other code, including other DML, in hooks before or after loading or saving an object, leading to very strange errors sometimes, where a database trigger would fail to run and indicate an error. They make DML over a group of records, trivial and efficient in SQL, a litany of one-record updates, each requiring a round-trip. They sort of allow a developer ignore the fact that the data are stored in an RDBMS. It looks cool in toy examples, and becomes progressively worse as your code starts doing serious things on serious amounts of data.
- supermatt 5y ago
  Sounds like all your gripes are from a naive usage of ORMs. Rails ActiveRecord, for example, handles ALL your mentioned scenarios (includes, joins, select, Transaction.do, update_all - and you can still run SQL queries or fragments thereof). Theres a lot more in the docs than you will see in "toy examples". Of course, not all ORMs are created equally...
  - OJFord 5y ago
    Not GP, and I've never used Rails so I'm not fanboying over it, but I've long had the impression that ActiveRecord is at least one of the most sophisticated ORMs; it wouldn't surprise me if it handles more complex cases better than others. I think my preferred style (vs. full ORM or raw SQL) is that of Diesel (which is incidentally from a (former?) maintainer of ActiveRecord, Sean Griffin, though I think he may have since stepped back from it) for Rust - it's more like 'SQL bindings' than 'ORM's typically are or grow to be; so you pretty much write SQL, just in Rust syntax with type checking etc. instead of actual SQL in one big Rust string.
  - nine_k 5y ago
    Indeed, I have seen a lot of semi-naive usage of ORMs, and enough expert usage that sidesteps the leaking abstractions and drops into non-ORM access. Table models are hugely useful. "Business object" models, not as much.
williamdclt 5y ago
I mostly agree with you, but to be fair TypeORM is a particularly bad ORM.
handrous 5y ago
Agreed. My reasoning: 1) Experience. I've seen so many terrible performance issues because of ORM-centric programming. Talking pages that take tens of seconds or a couple minutes(!) to load when they should take under 5s. 2) Ditto on the "ORMs apparently encourage people to write terrible schemas" observation. 3) "Database-agnostic" (not strictly an ORM thing and not required for an ORM, but strongly associated with ORMs) is a bad idea at least 90% of the time (I suspect more like 99%) it's applied. I've seen codebases replaced atop databases several times. I've yet to see a database replaced on an actual, live product. Moreover, your "database-agnostic" code means it's absolute hell to write any code that touches the DB that doesn't use your main codebase as an intermediary, especially if it's written in another language. That's crippling for your liberty to Move Fast and leverage the data you have, if you're thinking in terms of the business and a product suite rather than a single software product. Use your database. Let it do the work. Your customers will thank you for faster feature delivery ("Oh no, we can't use that feature in this database that immediately and perfectly solves this problem, because that would lock us in to it"), better response times, and lower chances of data loss or corruption. 4) Object-per-table isn't technically the only way to operate in ORMs, but boy is it sure treated that way in-the-wild, more often than not. You want your DB schema and your object hierarchy to be nonsense? Have I got the technology for you! 5) For the tedious cases where you do actually just want to map a select to an object and then write "row.save()" or whatever instead of a SQL statement... that's so easy to write. For the single-table cases it's trivial to write something highly re-usable, even. There, now you have much of the day-to-day benefit of an ORM with 1% of the fat and tech debt. And re: TypeORM, in particular—I entirely do not get the appeal. It's like some kind of obfuscation engine for both SQL and the intent of the code itself, in a way no other ORM I've seen is. What the hell.
- ttfkam 5y ago
  "Database-agnostic" is a pervasive and pernicious lie. It almost always means "lowest common denominator amongst all supported databases". Some databases are so far behind, they really shouldn't be abstracted by the same library. They have VERY different use cases and abilities. The access model for a SQLite-based app is quite different from a Postgres-based app. It's like having an F-150, a Cybertruck, and a Prius while hiring a driver that won't take either off-road or for more than 100 miles at a time because he also has to be able to drive a Nissan Leaf the exact same way. But folks still hire him because he claims to handle anything with a steering wheel and pedals. Technically true, but misses the point of the different options. That's an ORM.

by Osiris 5y ago

hn_throwaway_99 5y ago

You're being downvoted, but I completely agree. If you are on Node and Postgres I highly recommend using slonik - it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... https://github.com/gajus/slonik https://github.com/gajus/slonik

bhj 5y ago
Along similar lines but more simple/agnostic, check out node-sql-template-strings[0] or sqlate[1]. [0]: https://github.com/felixfbecker/node-sql-template-strings https://github.com/felixfbecker/node-sql-template-strings [1]: https://github.com/moll/js-sqlate https://github.com/moll/js-sqlate
- mirekrusin 5y ago
  for mssql https://github.com/appliedblockchain/tsql/ https://github.com/appliedblockchain/tsql/
girvo 5y ago
> it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections While I think this is totally the way to go, it is kind of amusing that PHP's PDO system was the "right" way to go all along in some ways
- hn_throwaway_99 5y ago
  Meh, not really. Some of the biggest benefits from slonik are only possible because of Javascript's support for tagged template literals [1], e.g. you write a statement like sql`SELECT id FROM foo WHERE bar = ${barValue}` Tagged template literals make it feel like you're just doing string concatenation, but it's done in such a way where under the covers it's actually creating prepared statements, and it is literally impossible to have SQL injection bugs unless you deliberately go out of your way to make them. 1. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#tagged_templates https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
  - girvo 5y ago
    That is exactly what PDO’s prepared statements do. It’s not string concatenation, and it’s explicitly to prevent SQL injection? I must be misunderstanding I guess.
golergka 5y ago
Thanks for the link to the slonik, never heard about it before. But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped?
- hn_throwaway_99 5y ago
  > But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped? You are correct. But note that pgtyped is really only able to generate types because it actually needs to run your queries against a running instance of your DB. Some contributors have commented they are working on something similar for Slonik, https://github.com/gajus/slonik/pull/267#issuecomment-840559530 https://github.com/gajus/slonik/pull/267#issuecomment-840559...
zomglings 5y ago
The "Stop using knex.js" blog post misses a very important point in favor of using knex.js: database migrations. Migrations allow a development team to synchronize database schema changes across multiple branches of development. It is the main reason I end up using tools like knex.js (node) and sqlalchemy (python). As far as knex is concerned, you can use it to manage migrations without using it in your application code.
- hn_throwaway_99 5y ago
  I don't really think that blog post misses that, I just think that DB migrations functionality is very different from SQL builder functionality, and that blog post is referring to the SQL builder part of this. FWIW, I do use knex for migrations, but I hate the SQL builder part of it, so most of my migrations are littered with knex.raw statements. This was discussed at length in this slonik issue, https://github.com/gajus/slonik/issues/42 https://github.com/gajus/slonik/issues/42 , with some recommendations for migrations functionality.
  - ttfkam 5y ago
    Idempotent SQL updates are my preference. ``` ALTER TABLE IF EXISTS ADD COLUMN IF NOT EXISTS foo varchar; CREATE TABLE IF NOT EXISTS sample ( -- all the fields including foo ); ``` Run it on a clean DB, you get the table. Run it on an existing DB, it adds the column. Run it on a current DB, it does nothing. Run it once or a hundred times, the DB ends up in the same target state. Can only be used on some databases though. The DBs without transactional DDL are a risk. Then again, they were a risk no matter what method you use. (I'm looking at you, MySQL.)
ttfkam 5y ago
I prefer the postgres module. 0 dependencies. Easy to use API. Wicked fast. Also really hard to fall victim to SQL injection attacks unless you actually put some extra effort into doing something foolish. https://github.com/porsager/postgres#readme https://github.com/porsager/postgres#readme

nemo1618 5y ago

> the fury of a thousand suns off topic, but I recently learn that this phrase dates back to the Bhagavad Gita! If the radiance of a thousand suns Were to burst at once into the sky That would be like the splendour of the Mighty One

zentropia 5y ago
If I remember well was a phrase used by Oppenheimer about the atomic bomb, a sentence that he read from Bhagavad Gita.
OJFord 5y ago
How literally was that translated though? Ngram viewer shows [0] plenty of usage of 'of a thousand suns' well before that 1944 translation [1], though it certainly picked up after that, no doubt helped by Oppenheimer quoting it (that translation specifically, recently published at the time) w.r.t. the Manhattan project. Confusingly [1] links to the passage in a different (1909-14) translation [2] which features the 'of a thousand suns' part but is otherwise different. I just wonder, for example if the original was something like लक्षाणाम् सूर्याणाम् 'lakshāñām sūryāñām' (I think that's what I mean, I'm learning Hindi not Sanskrit! Just looking up लाख & सूरज roots.) but translated to 'thousand' on the basis that it just meant 'a lot', and that sounded better in English and was perhaps even already a phrase. I don't mean to doubt you, just thought it was interesting, and translated texts are a bit of a can of worms. [0] - https://books.google.com/ngrams/graph?content=of+a+thousand+suns&year_start=1800&year_end=2008&corpus=26&smoothing=3 https://books.google.com/ngrams/graph?content=of+a+thousand+... [1] - https://www.bartleby.com/73/123.html https://www.bartleby.com/73/123.html [2] - https://www.bartleby.com/45/4/11.html#44 https://www.bartleby.com/45/4/11.html#44

runarberg 5y ago

I’m usually not a fan of dissing technologies that one does not use/like but others do. I don’t use ORMs but I’m sure the reason they are so popular is that they provide something of value to the people that do use them. However, your reasons for disliking ORMs seems a little like hearsay. I am interested in seeing how the responses to this post will be, although I’m afraid that this might turn into a flame-war. I wonder how often the pro-ORM flame wars the anti-ORM camp here on HN.

trm42 5y ago

I like ORMs for simple things like basic joins and wheres but in many cases more complicated SQL feels actually easier and more predictable than trying to convert them into ORM syntax. There's a limit to the usefulness of the ORM syntaxes.

Waterluvian 5y ago

Why are you being forced to use ORMs if you don't want to and they're not the correct tool for the job?

runawaybottle 5y ago
There are a lot of insufferable tech leads that make the decision. I know how to spot them because I’m insufferable too. My only saving grace is that I make a deliberate attempt to always say to myself ‘would this feel like cognitive overhead to others?’, and walk away from picking that fight. Many people don’t do that. I’ve been on projects where every other week the tech lead shows up and adds more and more layers to the stack. Unless you want to constantly fight, you just let it go and deal with it like a professional. Otherwise, I would have reached across my screen by now and slapped someone. I can tell you this, there’s someone out there that’s going to build something (at an actual paying job with other humans) with bleeding edge Deno, DenoDB, Typescript, AWS infra sometime very soon for no good reason. There’s not enough slaps in the world to stop them. The fear of god does not exist in these people because no one ever says shit.
pjmlp 5y ago
When one is yet another cog on the machine, without any saying on the project technical directions.
- williamdclt 5y ago
  ...or when one is an empowered individual contributor yet doesn't have full decision power in the project technical direction. Other people also have opinions

29athrowaway 5y ago

The premise of an ORM is that you will be saving time using simple abstractions. But in reality, those abstractions will be leaky, and you'll be spending significant time trying to understand what the ORM is doing, and finding workarounds for ORM problems. Also, each time something fails, there will be one more moving part to troubleshoot. Once you troubleshoot your problems and analyze what the ORM is doing, you'll realize that the price you paid for convenience is very high: you have sold your future in exchange for some "convenience" that actually gives you more work to do, makes everything slower and you didn't even need. Why? Because most ORMs try to target every database, so they offer a feature set that is the lowest common denominator for all the databases they support. Does that sound like a good idea? no.

kyleee 5y ago
Yep, and most projects probably don't switch underlying DBs so that part of the allure of an ORM is wasted
- ttfkam 5y ago
  Yes! 100x this!

TranquilMarmot 5y ago

Where I'm at now, we have a big huge Java app that uses MyBatis (https://mybatis.org/mybatis-3/ https://mybatis.org/mybatis-3/) It involves writing SQL inside of XML files and doing a lot of manual mapping from columns back to the objects. At first I REALLY disliked it but over time I have grown to absolutely love being able to just write SQL queries that can return whatever. Writing raw SQL really lets you optimize and create complex queries when you need to in a way that you can't with an ORM.

hu3 5y ago
I love SQL and being able to squeeze performance from these magical blackboxes called database servers. But how do you deal with refactoring? If I want to change a column on a db table or a property in a Java/C# class can the compiler tell me where I forgot to update without much ceremony? This is a problem I had in the past on a large project. The result is that we devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt. Some ORMs solve this by scanning the database schema and creating a class per table and a field/property per column. This way we get compilation errors if database doesn't match code and have the opportunity to investigate it before it blows on the customer screen as a runtime error.
- dansomething 5y ago
  > But how do you deal with refactoring? We solved this problem by having thorough automated test coverage for all SQL queries. Here's basic lifecycle of a test: 1) Create the test database 2) Migrate the database to the latest schema version 3) Insert test data 4) Execute the SQL query 5) Validate the results of the query
  - hu3 5y ago
    Tests are nice. But I see disadvantages with relying on them: 1) We now need 100% test coverage for queries. Forget one of them and the castle might fall on the client side. This can be mitigated with gatekeepers on CI/CD pipeline to ensure 100% coverage. But it's still quite the effort. 2) Our feedback loop for developing refactoring is now slower now since we need to run all tests to see what broke after a schema change. In contrast to an IDE giving compilation errors. 3) If we build SQL queries by composing them, which is common pattern for business rule validations, there will be many branches to be tested. 4) We won't have auto-completion of table and column names in the IDE. And if you mistype them your feedback is slower. I say that as someone who went all in SQL query builders and composability in a project but when it grew large we were still afraid of changing schema despite having a very high test coverage.

hermanradtke 5y ago

Check out slonik if you want something that does not go full pg-types. I combine it with io-ts for runtime validation.

lxe 5y ago

Have you tried Ruby on Rails' ActiveRecord? I like it! It's easy to use and easy to integrate with "raw sql" if needed.

KingMachiavelli 5y ago

What's the alternative? Are you saying it's better to use raw SQL or to use your own home grown convenience functions for creating tables, selecting rows, etc.? And once you have the data from the SQL database are you keeping it just in arrays/dictionaries? Or should it at least be mapped to a class structure? As someone dealing with a bespoke SQL schema and mix of in-house SQL translation layers for different DBs, wrapper functions... I would think any ORM would be better designed since it has a singular purpose and database experts work on their development.

bjt 5y ago
> What's the alternative? Not sure the parent comment would like it, but there's a middle ground between ORM and raw SQL that I consider a sweet spot. It's more of a "query builder" library that gives you language-appropriate constructs for building any SQL you like, but also provides more correctness guarantees than just writing raw SQL strings. SQLAlchemy's "expression" layer, for example, does this really nicely. It existed long before the higher "ORM" layer came along, and can be used without having to touch the ORM. In Go, I like the Goqu library for the same purpose. In Rails land, my understanding is that the underlying Arel layer is more like this pattern, as opposed to the higher level Active Record ORM. In Java, it's jOOQ instead of Hibernate.
- Supermancho 5y ago
  There was a PHP library called NotORM which was great.
  - g8oz 5y ago
    ezSQL is a similar project, also good.
- gmac 5y ago
  For TypeScript, there’s a very helpful list here: https://phiresky.github.io/blog/2020/sql-libs-for-typescript/ https://phiresky.github.io/blog/2020/sql-libs-for-typescript...

woutr_be 5y ago

I use TypeORM quite often, especially in projects with fairly simple queries. But I always make it a point to: * Write migrations first, so that you define your own schemas * Disable TypeORM synchronize, make your migrations the source of truth instead of the entities * Model your entities after your schema

smt88 5y ago

I've been using pgTyped in production for months and it has been phenomenal. There's another one called (I think) Zapatos that has some similar qualities.

gmac 5y ago
Yes: https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/

alexose 5y ago

I'm with you. ORMs feel like _exactly_ the wrong place to abstract. In my experience, database calls always end up being the thing you want the most control over.

redact207 5y ago

I find people hate/are ok with ORMs based on how they're used. If you're using it to ad-hoc query your db then it's understandable you'll hate it - a leaky and poor abstraction over sql. Probably a bad fit. Projects where I've seen it work well is when most of the logic is in the app with per row/per aggregate changes. In these apps it's only used for the "object relational mapping" side of things - ie to marshal types to/from db rows. I've never found auto-migrations in ORMs good for anything less than a 1 day project - it's a world of hurt.

Aeolun 5y ago
> I've never found auto-migrations in ORMs good for anything less than a 1 day project - it's a world of hurt. Just to offer a counterpoint. Every project I worked on that did not have automatic migrations was extremely flawed in other ways as well. Manually keeping track of your DB schema, and indeed, seeing it as something separate from the code that needs to interact with it is a bad idea in my opinion. It’s same as ‘infrastructure as code’, database also needs to be ‘database as code’.
- ttfkam 5y ago
  That's why I prefer idempotent SQL DDL updates. A disk full of up/down scripts makes me nervous. Most folks test their up scripts. They almost NEVER adequately test their down scripts, so you're left with this false sense of security moving forward even though revision 63 of 64 has a bug in the down portion…which you only find when you're trying to revert to the state of rev 60. As for ORM migrations, of course they work. They've dumbed down your use of the database to the lowest common denominator (looking at you, MySQL). IF EXISTS and IF NOT EXISTS are your good friend. Run the script, the database will be at the target state. When all databases are past a certain point, remove the appropriate ALTER TABLE IF EXISTS ADD COLUMN statements. Makes source diffing much easier, is a consistent single source of truth, and allows for pruning old parts as needed.

wolframhempel 5y ago

I couldn't agree more. ORMs might be an ok choice to get started quickly and to maintain a consistent way of working across teams, but over time they very frequently become a bottleneck. Having a handwritten, optimized SQL with specific joins and subqueries, tailored to the exact problem - or even better yet, a pre-planned query or postgres function is often orders of magnitudes more efficient and can make a substantial difference in terms of response times and running costs for your db servers.

tester34 5y ago
Why not both? Approach where you use pure SQL for reads and ORM for saves is not that uncommon
- wolframhempel 5y ago
  That's fair - though I feel that ORM enthusiasts would advocate for the consistency that comes with the ORM on read, the automated resolution of relationships and the mapping to an object in the native language.

tester34 5y ago

I don't agree I used EF Core and Dapper for like 3 years and ORMs boost productivity significantly. I tend to check what SQL is generated and if something is complicated and generated SQL sucks, then I use micro ORM like Dapper and use raw sql. I agree that ORMs aren't easy because you have to learn them, but I think it's worth, unless you have to learn many different ORMs.

bobmaxup 5y ago

Don't most ORMs have ways to do raw queries when needed?

viraptor 5y ago
Yes. It's often a false dichotomy. You don't have to make a decision 100% either way. Just write SQL when it's complicated enough for a query to be useful. And you can still use ORM to write that `foo = Foos.recent.for_user(id)` which happens in small variations many times in the app, where you gain nothing but typing practice by writing pure SQL.
- KptMarchewa 5y ago
  With JDBI you can just write @SqlQuery("SELECT * FROM users WHERE id = :id") User find(String id); and not use any of the ORM footguns.
  - viraptor 5y ago
    I posted an example which is not a simple lookup by id. It was specifically "Foos.recent.for_user(id)" because you can have multiple Foos, with a (reusable) scope `.recent` that you can use in other queries and a lookup for a given user which likely includes a join. So sure, you can write this out as multiple simple queries, but do you really want to code the repeated query for every case of: Foos.recent.for_user, Foos.active.for_user, Foos.recent.by_something, Foos.recent.active.by_something? And what if you don't want a whole object in that case, but only one column? ActiveRecord for example has you covered with `.pluck(:single_column)` that you can append at the end without writing yet another full query. Writing the simple stuff every time, you get footguns simply by repeating trivial code - that leads to copy-paste and forgot-to-change-one-of-them mistakes.

nine_k 5y ago

No, ORMs do much more. They turn slices of resultsets into objects. They lazy-load related objects instead of using one efficient join and doing one query; they in general have trouble representing results of projections and joins. They fetch all "attributes of the object" when you need to select a couple of columns from two dozen. They make the objects mutable, and introduce dirty state without transactional control. They allow running other code, including other DML, in hooks before or after loading or saving an object, leading to very strange errors sometimes, where a database trigger would fail to run and indicate an error. They make DML over a group of records, trivial and efficient in SQL, a litany of one-record updates, each requiring a round-trip. They sort of allow a developer ignore the fact that the data are stored in an RDBMS. It looks cool in toy examples, and becomes progressively worse as your code starts doing serious things on serious amounts of data.

supermatt 5y ago
Sounds like all your gripes are from a naive usage of ORMs. Rails ActiveRecord, for example, handles ALL your mentioned scenarios (includes, joins, select, Transaction.do, update_all - and you can still run SQL queries or fragments thereof). Theres a lot more in the docs than you will see in "toy examples". Of course, not all ORMs are created equally...
- OJFord 5y ago
  Not GP, and I've never used Rails so I'm not fanboying over it, but I've long had the impression that ActiveRecord is at least one of the most sophisticated ORMs; it wouldn't surprise me if it handles more complex cases better than others. I think my preferred style (vs. full ORM or raw SQL) is that of Diesel (which is incidentally from a (former?) maintainer of ActiveRecord, Sean Griffin, though I think he may have since stepped back from it) for Rust - it's more like 'SQL bindings' than 'ORM's typically are or grow to be; so you pretty much write SQL, just in Rust syntax with type checking etc. instead of actual SQL in one big Rust string.
- nine_k 5y ago
  Indeed, I have seen a lot of semi-naive usage of ORMs, and enough expert usage that sidesteps the leaking abstractions and drops into non-ORM access. Table models are hugely useful. "Business object" models, not as much.

williamdclt 5y ago

I mostly agree with you, but to be fair TypeORM is a particularly bad ORM.

handrous 5y ago

Agreed. My reasoning: 1) Experience. I've seen so many terrible performance issues because of ORM-centric programming. Talking pages that take tens of seconds or a couple minutes(!) to load when they should take under 5s. 2) Ditto on the "ORMs apparently encourage people to write terrible schemas" observation. 3) "Database-agnostic" (not strictly an ORM thing and not required for an ORM, but strongly associated with ORMs) is a bad idea at least 90% of the time (I suspect more like 99%) it's applied. I've seen codebases replaced atop databases several times. I've yet to see a database replaced on an actual, live product. Moreover, your "database-agnostic" code means it's absolute hell to write any code that touches the DB that doesn't use your main codebase as an intermediary, especially if it's written in another language. That's crippling for your liberty to Move Fast and leverage the data you have, if you're thinking in terms of the business and a product suite rather than a single software product. Use your database. Let it do the work. Your customers will thank you for faster feature delivery ("Oh no, we can't use that feature in this database that immediately and perfectly solves this problem, because that would lock us in to it"), better response times, and lower chances of data loss or corruption. 4) Object-per-table isn't technically the only way to operate in ORMs, but boy is it sure treated that way in-the-wild, more often than not. You want your DB schema and your object hierarchy to be nonsense? Have I got the technology for you! 5) For the tedious cases where you do actually just want to map a select to an object and then write "row.save()" or whatever instead of a SQL statement... that's so easy to write. For the single-table cases it's trivial to write something highly re-usable, even. There, now you have much of the day-to-day benefit of an ORM with 1% of the fat and tech debt. And re: TypeORM, in particular—I entirely do not get the appeal. It's like some kind of obfuscation engine for both SQL and the intent of the code itself, in a way no other ORM I've seen is. What the hell.

ttfkam 5y ago
"Database-agnostic" is a pervasive and pernicious lie. It almost always means "lowest common denominator amongst all supported databases". Some databases are so far behind, they really shouldn't be abstracted by the same library. They have VERY different use cases and abilities. The access model for a SQLite-based app is quite different from a Postgres-based app. It's like having an F-150, a Cybertruck, and a Prius while hiring a driver that won't take either off-road or for more than 100 miles at a time because he also has to be able to drive a Nissan Leaf the exact same way. But folks still hire him because he claims to handle anything with a steering wheel and pedals. Technically true, but misses the point of the different options. That's an ORM.