8 ms·
Don't use your ORM entities for everything – embrace the SQL
- apwell23 2y agoThis has to be one of the long lasting topics on HN. I remember commenting on "vietnam of cs" blogpost like two decades ago.
- gigatexal 2y agoCouldn’t agree more. There’s huge benefits to ORMs but when the fast path is needed hand written and optimized SQL is my go to.
- deleted 2y ago[deleted]
- wuliwong 2y agoI think the correct approach is to understand the SQL that your ORM is using. If you can't have it create the correct query than roll your own. I think most of the trouble people get in with ORMs is not taking the time to understand the SQL that it creates and using it incorrectly as a result.
- gigatexal 2y agoImo software engineers look to the ORM to abstract the declarative set-theory, blah blah of SQL back into the object model they’re familiar with. IMO that’s a dereliction of duty as a developer but it happens and ORMs have gotten pretty good of late. In the Java space the JOOQ team is doing really good work. In my space of Python SQLalchemy has been the dominate ORM of choice and I hated it but without putting out a better product I have just decided to work around it or adapt. But if I can I use raw SQL where possible.
- cqqxo4zV46cp 2y agoDjango’s ORM is also widely popular in the Python world. It’s just not standalone.
- Zambyte 2y agoThe article mentions database portability as an advantage of ORMs, and I agree - to me that is basically the only real advantage to ORMs. The article also mentions that database portability is not a very compelling advantage, since lots of applications don't actually need that, which I also agree with. Personally I just pick either PostgreSQL or SQLite depending on my use case (and they're different enough that there is always one obvious choice) and just interface with them directly. Hasn't served me wrong yet.
- add-sub-mul-div 2y agoPostgres is the one time I embrace lock-in, if the topic of switching to mysql or something else comes up it's a good thing if there'd be a prohibitive amount of work involved.
- moritonal 2y agoWhilst true from a performance level, do appreciate there is a complexity cost with raw SQL given how it allows for inconsistent ways of working. Coming from a Rails legacy app, these raw SQL "clever ideas" are where I found the most issues.
- mannyv 2y agoI still don't understand why people believe that abstracting away something as important as your data store is a good idea. They quickly run into performance problems that are quite difficult to fix. It's funny that the same developers that try to avoid vendor lock-in don't realize they've locked themselves into an ORM forever. Plus, SQL isn't that hard. And as a backend person, you should be able to visualize your data model anyway. A SQL datatbase is just a bunch of planes, for the most part, and the queries are how you intersect them...more or less.
- internetter 2y agoWe use Kysely[0], which is a nice balance. The code you write in Kysely translates 1:1 into SQL, which means there's very little perf left on the table, but we get an interface that's automatically typed, type safe, and works well with our js language tools. [0]: https://kysely.dev/ https://kysely.dev/
- clarkdave 2y agoKysely is excellent! Another library that complements it is pgTyped[0]. It compiles .sql files into functions with strongly typed parameters and return values [0] https://pgtyped.dev/ https://pgtyped.dev/
- ckmar 2y agoHave you seen Pure ORM? It's an ORM that purely does object relational mapping. You write SQL but instead of getting back flat and potentially-collided data, you get back pure objects which are properly structured. https://github.com/craigmichaelmartin/pure-orm https://github.com/craigmichaelmartin/pure-orm
- jillesvangurp 2y agoMy simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'll be better off. The classic example here is persons that have addresses and phone numbers. Most applications have no requirements to query on most of that: street name, postal code, phone number, etc. Less tables and columns mean simpler joins (or better, no joins at all). Any structured data that you don't query, just store it in json form in a blob and simplify your schema. Anything that actually requires complex querying, consider using a proper search engine. Or extract it to a dedicated field with some index on it that actually helps you querying effectively. The golden rule here is that if that query is connected to user input you probably need some notion of ranking, fuzzy searches, etc. Either way, everything gets easier with a simple schema. Faster query and insert performance, easier to reason about when doing transactions, easier to maintain, etc.
- mixmastamyk 2y agoMaybe, but not sure you’re getting much from that trade. If you’re not querying often, then performance shouldn’t be an issue. Modern hardware/dbms can definitely handle a couple of joins without blinking. So you’ve mostly lost flexibility. Also having to redo later will wipe out the time savings of many, many simplifications. Not to mention doc and teaching reqs you’ve added to new devs.
- eddd-ddde 2y agoI think they mean that adding a bunch of uncommon columns to your user table is just gonna decrease performance of common queries. In those cases I like offloading uncommon columns to separate tables that are joined only when needed.
- jillesvangurp 2y agoI've fixed a few systems by simplifying their schemas. Over-engineering at the database level can lead to a lot of issues beyond just performance. The extra complexity causes a lot of overhead with technical debt, lots of ceremony around changes, etc. Performance issues are usually a good indicator of a team that is a bit out of their depth. Usually goes hand in hand with over engineered data models being mapped 1 to 1 to a table structure.
- Spivak 2y ago> Will you actually need to change between fundamentally different database technologies? Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing. I'm of the opinion that SQLite is the musl of the SQL world. By deciding that you'll support it first-class you'll avoid the sharp edges of database specific behavior and extension hell and write better more maintainable code. "Sorry we can't actually put that logic the database, SQLite doesn't support spooky action at a distance."
- fooododododo 2y agoWhy not run Pg in a container if that's what's in prod?
- Wulfheart 2y agoBut then your tests and prod setup would differ.
- Spivak 2y agoYes and no, we still run the tests against PG but it's done in CI instead of locally.
- deepsun 2y agoJOOQ is awesome, doesn't try to do too much. Also tried to use Exposed from Kotlin, but it's way too young, support only simple data types.
- go_prodev 2y agoAs someone who always prefers native SQL over ORM queries, I'll add a couple of counterpoints. Most IDEs provide intellisense/validation of ORM entities, vs treating SQL like a raw string. ORM entities also make refactoring and impact analysis slightly easier. Despite those benefits, I generally find ORMs a pain for anything besides the most basic queries.
- gryn 2y agoFor typescript I've found pgtyped to be my perfect middle ground. I write SQL queries, it auto generate typing for the query and the response.
- lcnPylGDnU4H9OF 2y agoComments here make me wonder if I've just been spoiled by ActiveRecord. Not that I use it for all queries but 1) it's rare that I have to resort to raw SQL and 2) it kindly gets out of the way when I do.
- dkersten 2y ago> resort to raw SQL I'm the opposite, I would rather write SQL than "resorting to" ORM queries, which is why my favourite libraries are aiosql[1] in Python, Hugsql[2] in Clojure and similar: write the queries as SQL in .sql files, which then get exposed as functions to your code. [1] https://nackjicholson.github.io/aiosql/ https://nackjicholson.github.io/aiosql/ [2] https://www.hugsql.org/ https://www.hugsql.org/
- mixedCase 2y agoThose tend to be great until two words come into play: Dynamic SQL.
- wswope 2y agoWhat’s your issue with dynamic SQL in this case? I’ve written aiosql functions that use dynamic SQL (via plpgsql) just fine.
- dkersten 2y agoI’ve been using aiosql and Hugsql for years and have never had any problems. In fact, Hugsql has a “snippets” feature that allows you to compose bits of SQL dynamically. https://www.hugsql.org/using-hugsql/composability/snippets https://www.hugsql.org/using-hugsql/composability/snippets But the vast majority of SQL I’ve worked with simply doesn’t need it. But even when you do, the situation has never been any worse than it is with ORM’s for me.
- mixedCase 2y agoI guess I failed to set the context correctly given that you presented solutions for Clojure and Python, where it isn't as much of a problem since from the start the language fails to provide compiler guarantees you usually come to expect out of a SQL driver wrapper in typed languages (even though Clojure macros are probably powerful enough to allow this). As a comparison, DX-wise this is no safer and is indeed very similar to the usual idiom in Go for example, where you just concatenate (pre-interpolated) SQL strings. But when you actually want the compiler to prove the correctness of your queries even in a rudimentary way, these .sql file solutions usually (if not, everytime) fail to provide the necessary external checker that processes templates and uses an accurate model of your database and SQL to verify that all used combinations make sense. The closest thing to a proper take on this I've seen is https://github.com/andywer/squid https://github.com/andywer/squid with https://github.com/andywer/postguard https://github.com/andywer/postguard which, although the SQL is inlined in the code, it uses the right approach for verifying correctness as far as I could tell in the little time I experimented with it.
- ta2234234242 2y agoIn the 1980's we had C/C++/Ada/Pascal/Fortran/Assembly/Lisp. Since the 1980's the languages have exploded. But we still only have one language for querying a database. And a kinda not very good one at that. Why is that? https://www.holistics.io/blog/quel-vs-sql/ https://www.holistics.io/blog/quel-vs-sql/ One could argue that ORM's are the alternative language people are looking for.
- eddd-ddde 2y agoExcept ORMs are just worse? Unless you mean they are the symptom of longing for other languages where there are none. I would also love to see a new language for writing declarative queries. I also feel people don't like sql not for the language itself, but the lack of tooling.
- ta2234234242 2y agoAre they worse? The popularity of them has exploded like programming languages from the 80's to now. One might say that if SQL was so great there would be no ORMs -- because there would be no need. The ORMs, if anything, are a symptom of the larger problem that is SQL.
- lcnPylGDnU4H9OF 2y ago> One might say that if SQL was so great there would be no ORMs It's not like the ORMs exist without SQL. Their primary purpose is to provide a consistent interface between different flavors of SQL so the application developer doesn't need to care what database they're using. For example, delimiting object names in Mysql uses ` and in Postgres uses ". The ORM will ostensibly have different adapters which take care of these differences without changing the application code.
- eddd-ddde 2y agoNot even that works painlessly. You commonly run into incompatibilities so an ORM won't necessarily make your code database agnostic.
- nimish 2y agoThe times I've run into people who've written a library to handle something built into Postgres... everyone likes to reinvent timestamp and range handling for some reason.
- moribvndvs 2y agoI always admired micro ORMs like Dapper. The hard (or at least time wasting or error prone) tasks are: - mapping results to app entities or projections - input sanitization - dynamic query building - connection pooling and transaction management - supporting multiple SQL dialects with one code base After many years of experience with Hibernate, NHibernate, Entity Framework, and a plethora of other full ORMs, I absolutely do not want lazy/eager loading, opaque query DSLs and criteria APIs, and dealing with leaky abstractions for queryables (looking at you, LINQ and JPA), and I’m pretty wary anymore over cascading persistence. These things all look great in tutorials and are nearly magical in your PoC or simple projects, but in my experience, performance problems, unexpected and sometimes hard to diagnose bugs, and lots of ugly yak shaving are inevitable for moderate complexity apps over time. Of all of them, I enjoyed JOOQ’s SQL builder with or without codegen the most if I need to support multiple dialects or complex dynamic queries. Speaking of JOOQ, anyone have recommendations for something similar on nodejs? I haven’t been particularly excited by prisma, typeorm, or sequelize.
- microflash 2y agoDrizzle [1] comes pretty close the last time I checked. [1]: https://orm.drizzle.team https://orm.drizzle.team
- moribvndvs 2y agoLooks promising, thanks!
- karmakaze 2y agoYou can learn a lot about a system merely by looking at the database schema and data transformations to new consistent data. How exactly these processes do what they do is an implementation detail. Think in invariants, not mental simulation of incidentally complex procedures. In this light knowing SQL is a superpower that applies to all systems built with any RDBMS.