7 ms·
I've loved and used Django ORM and SQLAlchemy for many years. It got me a long way in my career. But at this point I've sworn-off using query-builders and ORMs.
by petcat 10mo ago
I've loved and used Django ORM and SQLAlchemy for many years. It got me a long way in my career. But at this point I've sworn-off using query-builders and ORMs. I just write real, hand-crafted SQL now. These "any db" abstractions just make for the worst query patterns. They're easy and map nicely to your application language, but they're really terrible unless you want to put in the effort to meta-program SQL using whatever constructs the builder library offers you. CTEs? Windows? Correlated subqueries? It's a lot. And they're always lazy, so you never really know when the N+1s are going to happen.
Just write SQL. I figured this out when I realized that my application was written in Rust, but really it was a Postgres application. I use PG-specific features extensively. My data, and database, are the core of everything that my application does, or will ever do. Why am I caring about some convenient abstractions to make it easier to work with in Rust, or Python, or whatever?
Nah. Just write the good SQL for your database.
- HillRat 10mo agoThe cargo-cult shibboleth of "never put business logic in your database" certainly didn't help, since a lot of developers just turned that into "never use stored procedures or views, your database is a dumb store with indexes."
- forgetfreeman 10mo agoThere's value in not having to hunt in several places for business logic, having it all in one language, etc. I was ambivalent on the topic until I encountered an 12 page query that contained a naive implementation of the knapsack problem. As with most things dogma comes with a whole host of issues, but in this case I think it's largely benign and likely did more good than harm.
- yobbo 10mo ago> hunt in several places for business logic But that is the result of having multiple applications needing to enforce valid states in the database. "Business logic" is a loose term. The database is the effective store for state so it must enforce states, eg by views, triggers, and procedures. Other "business logic" can happen outside of the db in different languages. When individual apps need to enforce valid states, then complexity, code, etc grows exponentially.
- forgetfreeman 10mo agoOther than a few ill-advised attempts to implement microservices infrastructure by well-intentioned co-workers I've not encountered situations where multiple applications needed to access a single data store. While I'm sure there are valid use cases there I suspect they're rare and should be treated like the outliers they are.
- simonw 10mo agoDid that 12 page query have any automated tests?
- mgkimsal 10mo agoI doubt it was even under version control...
- forgetfreeman 10mo agoIt was absolutely under version control and there was a full test suite. The guy that wrote it is easily in the top 3 smartest human beings I've ever met and an incredibly talented developer. Unfortunately a lot of his stuff required being at the same level on the IQ bell curve, which meant it was functionally unmaintainable by anyone else. If you're familiar with the Story of Mel, it was kinda like that.
- iamsomewalrus 10mo agogenuinely curious, can you steel man stored procedures? views make intuitive sense to me, but stored procedures, much like meta-programming, needs to be sparingly used IMO. At my new company, the use of stored procedures unchecked has really hurt part of the companies ability to build new features so I'm surprised to see what seems like sound advice, "don't use stored procedures", called out as a cargo cult.
- IanCal 10mo agoIt’s about what you want to tie to which system. Let’s say you keep some data in memory in your backend, would you forbid engineers from putting code there too, and force it a layer out to the front end - or make up a new layer in between the front end and this backend just because some blogs tell you to? If not, why would you then avoid putting code alongside your data at the database layer? There are definitely valid reasons to not do it for some cases, but as a blanket statement it feels odd. Stored procedures can do things like smooth over transitions by having a query not actually know or care about an underlying structure. They can cut down on duplication or round trips to the database. They can also be a nightmare like most cases where logic lives in the wrong place.
- simonw 10mo agoMy hunch is that the problems with stored procedures actually come down to version control, change management and automated tests. If you don't have a good way to keep stored procedures in version control, test them and have them applied consistently across different environments (dev, staging, production) you quickly find yourself in a situation where only the high priests of the database know how anything works, and making changes is painful. Once you have that stuff in git, with the ability to run automated tests and robust scripting to apply changes to all of your environments (I still think Django's migration system is the gold standard for this, though I've not seen that specifically used with stored procedures myself) their drawbacks are a lot less notable.
- tete 10mo ago> My hunch is that the problems with stored procedures actually come down to > version control Git? (and migrations) > change management Again. Just like any other code. > and automated tests. Just write an automated test like you write any other kind of test?
- 0x457 10mo agoA lot of people probably think it's better to keep database "easy to swap". Which is silly, its MUCH easier to change your application layer, than database.
- biophysboy 10mo agoI've been using django & duckdb together, which keeps me from using the ORM. Was this a happy accident for me? For background, I have a scientist background; I don't have as much experience w/ software and designing database apps.
- nacozarina 10mo agoEvery Oracle rep I've ever met said every app should be a SQL app.
- b450 10mo agoORMs come with a lot of baggage that I prefer to avoid, but it probably depends on the domain. Take an e-commerce store with faceted search. You're pretty much going to write your own query builder if you don't use one off the shelf, seems like.
- elevation 10mo agoI once boasted about avoiding ORM until an experienced developer helped me to see that 100% hand‑rolled SQL and customer query builders is just you writing your own ORM by hand. Since then I've embraced ORMs for CRUD. I still double-check its output, and I'm not afraid to bypass it when needed.
- ChromaticPanic 10mo agoExactly, and any good ORM will let you drop down to pure SQL if you need to for the weird cases.
- yawaramin 10mo agoNot really. ORMs have defining characteristics that hand-rolled SQL with mapping code does not. Eg, something like `Users.all.where(age > 45)` create queries from classes and method calls, while hand-rolled SQL queries are...well..hand-written.
- pjmlp 10mo agoIndeed, Dapper, myBatis, jOOQ,...
- swasheck 10mo agoDapper is an unmitigated joy for me. i get to write the best sql needed for the case and then let the micro-orm handle the rest.
- johnh-hn 10mo agoDapper is fantastic, and I'm happy to see it getting some love. It does exactly what I want: provides strongly-typed mapping and protects against SQL injection. It makes it easy to create domain-specific repositories without leaking anything. In contrast, every company I've joined that used Entity Framework had enterprise products that ended up being a tightly coupled mess from IQueryable<T> being passed around like the world's favourite shotgun.
- pjmlp 10mo agoIn my case, it has been either applications where Dapper is possible, or very high abstractions in CMS like Sitecore and Optimizely, where they come with their own approach which then again, needs a whole indexing engine.
- microflash 10mo agoSQL is such a joy to work with compared to all the baggage ORMs bring. I’m not against ORMs but I like to keep them as thin as possible (mostly to map columns to data objects). I’ve been happily using JDBC and Spring Data JDBC (when I needed to use Repository pattern) for a long time in Java.
- danem 10mo agoAnytime this topic comes up, this opinion is invariably at the top of the comments. However I've never seen a non-trivial application made this way. Mind sharing one? More than the query generation, I think people reach for ORMs for static typing, mapping, migrations, transactions, etc. I'm not doubting that it can be done, I'm just curious to see how it's done.
- zanellato19 10mo agoEvery single time. Where are these developers? Orms are a god send 98% of the time. Sure, write some SQL from time to time, but the majority of the time just use the ORM.
- morkalork 10mo agoI worked at a company where we used Dapper with plain SQL. Like the sibling commenter said, simplicity. There were never [ORM] issues to debug and queries could easily be inspected.
- saxenaabhi 10mo agoWe have a POS system where entire blogic is postgres functions. There are many others as well. Sure Rails/Laravel/Django people use the ORM supplied by their framework, but many of us feel it's un-necessary and limiting. Limiting because for example many of them don't support cte queries(rails only added it a couple of years ago). Plus it get weird when sometimes you have to use sql.raw because your ORM can't express what you want. Also transactions are way faster when done in a SQL function than in code. I have also seen people do silly things like call startTransaction in code and the do a network request resulting in table lock for the duration of that call. Some people complain that writing postgres functions make testing harder, but with pglite it's a non issue. As an aside I have seen people in finance/healthcare rely on authorization provided by their db, and just give access to only particular tables/functions to a sql role owned by a specific team.
- tete 10mo ago> Orms are a god send 98% of the time. People who write percentages make shit up 98% of the time. Or in other words: Source?
- bottlepalm 10mo agoI love SQL and use it all day long to answer various business questions, but I would never use raw SQL in my code unless there is a good reason for it (sometimes there is). ORMs are there for maintainability, composability, type safety, migrations, etc.. trying to do all that with raw SQL strings doesn't scale in a large code base. You need something that IDE tools can understand and allow things like 'find all references', 'rename instances', compile time type checks, etc.. Raw SQL strings can't get you that. And managing thousands of raw SQL strings in a code base is not sustainable. ORMs are one of those things that a lot of people think is a replacement for knowing SQL. Or that ORMs are used as a crutch. That has nothing to do with it. Very similar to how people here talked about TypeScript 10 years ago in a very dismissive way. Not really understanding its purpose. Most people haven't used something like Entity Framework either which is game changing level ORM. Massive productivity boost, and LINQ rivals SQL itself in that you can write very small yet powerful queries equivalent to much more complex and powerful SQL.
- dehugger 10mo agoSounds like your issue is writing the complex SQL all as strings in your codebase instead of as functions in the database.
- bottlepalm 10mo agoNo issue at all. There is a place for stored procs and functions in cases where you need to do things an ORM is not capable of. It is an exception, not a rule. Managing procs/functions is overhead and has the same if not more maintenance headaches than raw SQL strings in code.
- fredsmith219 10mo agoI agree and have taken the same path.
- Paul-E 10mo agoI'm curious if you have tried SeaORM? I've used it a little bit (not too extensively) and really like it. It's like sqlalchemy in that you can declare your tables and have a type checked query builder, which is a big win IMO. It's nice to add/change a field and have the compiler tell you everywhere you need to fix things. I've definitely had issues when using sqlalchemy where some REST API type returns an ORM object that ends up performing many queries to pull in a bunch of unnecessary data. I think this is harder to do accidentally with SeaORM because the Rust type system makes hiding queries and connections harder. Most of my usage of SeaORM has been as a type query builder, which is really what I want from an ORM. I don't want to have to deal with lining my "?" or "$1" binds or manually manipulate strings to build a query. IMO a good query builder moves the experience closer to writing actual SQL, without a query builder I find myself writing "scripts" to write SQL.
- brettgriffin 10mo ago> Nah. Just write the good SQL for your database. You may not need to use an ORM, but hand writing SQL, especially CRUD, should be a terminable offense. You _cannot_ write it better than a process that generates it.
- deleted 10mo ago[deleted]
- vedaba 10mo ago[dead]