by joaodlf on 8/14/23, 8:14 AM with 277 comments
by zzzeek on 8/14/23, 2:19 PM
As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL", like persisting and loading data between classes and tables that are joined in various very common ways to represent associations between classes:
https://gist.github.com/zzzeek/5f58d007698c4a0c372edd95ab8e0...
this is why whenever someone writes one of these "just write SQL" comments, or wow here a whole blog post! wow. I just shake my head. Because this is not at all what the ORM is really getting you. Plenty of ORMs let you write raw SQL or something very close to it. The SQL is not really the point. It's about the rows and objects, moving the data from the objects to the INSERT statement, moving the data from the rows you SELECTed back to the objects. Not to mention abstraction over all the other messy things the database drivers do like dealing with datatypes and stuff like that.
It looks like in this blog post, they actually implemented their own nano-ORM that stores one row and queries one table. Well great, now scale that approach up and see how much fun it is to write the same boilerplate XYZRepository / XYZPostgresqlRepository code with the same INSERT / SELECT statement over, and over again. I'd sure want to automate all that tedium. I'd want a one-to-many collection too maybe.
You can use SQLAlchemy (which I wrote) and write all the SQL 100% yourself as literal strings, and still use the ORM, and still be using an enormous amount of automation to deal with the database drivers and moving data between your objects and rows. But why would anyone really want to, writing SQL for CRUD is really repetitive and tedious. Computer can do that for you.
by rtpg on 8/14/23, 9:56 AM
I totally understand people having issues with Django's ORM due to the query laziness making performance tricky to reason about (since an expression might or might not trigger a query depending on the context). In some specialized cases there are some real performance hits from the model creation. But Django is very good at avoiding weird SQL issues, does a lot of things correctly the first time around, and also includes wonderful things like a migration layer.
You might have a database that is _really_ unamenable to something like an ORM (like if most of your tables don't have ID rows), but I wonder how much of the wisdom around ORMs is due to people being burned by half-baked ORMs over the years.
I am curious as to what a larger codebase with "just SQL queries all over" ends up looking like. I have to imagine they all end up with some (granted, specialized) query builder pattern. But I think my bias is influenced by always working on software where there are just so many columns per table that it would be way too much busywork to not use something.
by boxed on 8/14/23, 1:11 PM
ORM queries compose. That's why [Python] programmers prefers them. You can create a QuerySet in Django, and then later add a filter, and then later another filter, and then later take a slice (pagination). This is hugely important for maintainable and composable code.
Another thing that's great about Djangos ORM is that it's THIN. Very thin in fact. The entire implementation is pretty tiny and super easy to read. You can just fork it by copying the entire thing into your DB if you want.
by zknill on 8/14/23, 11:56 AM
Firstly (1); "I want to use the ORM for everything (table definitions, indexes, and queries)"
Then second (2), on the other extreme: "I don't want an ORM, I want to do everything myself, all the SQL and reading the data into objects".
Then thirdly (3) the middle ground: "I want the ORM to do the boring reading/writing data between the database and the code's objects".
The problem with ORMs is that they are often designed to do number 1, and are used to do number 3. This means there's often 'magic' in the ORM, when really all someone wanted to do was generate the code to read/write data from the database. In my experience this pushes engineers to adopt number 2.
I'm a big fan of projects like sqlc[1] which will take SQL that you write, and generate the code for reading/writing that data/objects into and out of the database. It gives you number 3 without any of the magic from number 1.
by NewEntryHN on 8/14/23, 10:22 AM
Think a simple UI switch to sort some result either ascending or descending, which will require you format either an `ASC` or a `DESC` in your SQL string.
The moment you build SQL with string formatting is the moment you're rewriting the SQL formatter from an ORM, meeting plenty of opportunities to shoot yourself in the foot.
by Rudism on 8/14/23, 4:48 PM
If you want to go full-blown SQL you can use something like PostGraphile, which allows you to create all of your business entities as tables or views and then write all your business logic as SQL stored procedures, which get automatically translated into a GraphQL API for your clients to consume, but once you move beyond basic CRUD operations and reporting it becomes incredibly difficult to work with since there aren't really any good IDEs that help you manage and navigate huge pure-SQL code bases.
If you're really dead set against using a powerful ORM, it's probably still a good idea to find and use a lightweight one--something that handles the tedious CRUD operations between your tables and objects, but lets you break out and write your own raw queries when you need to do something more complex. I think there's a sweet spot between writing every line of SQL your application executes and having an ORM take care of boilerplate for you that will probably be different in every case but will never be 100% at one end or the other.
by bbojan on 8/14/23, 10:11 AM
I mean you can just write SQL instead of using the ORM if your project consists of a single table with no indexes that will never change, sure.
by promiseofbeans on 8/14/23, 10:00 AM
Python has built-in support for SQLite in the standard library: https://docs.python.org/3/library/sqlite3.html
by baq on 8/14/23, 10:02 AM
If you don't know what you're doing on the DB-app interface, you're still better off with an ORM most of the time. If you don't know if you know, you don't know (especially if you think you know but details are fuzzy); please go read sqlalchemy docs, no, skimming doesn't count.
If you know what you're doing but are new to Python, use sqlalchemy.core.
PS. zzzeek is a low-key god-tier hacker.
by Jackevansevo on 8/14/23, 10:07 AM
I feel like Django ORM and SQLAlchemy are the de-facto ORMs for Python and have been around for over a decade. If anything I'd recommend juniors to pick one of these over hand rolling their own solution because it's so ubiquitous in the ecosystem.
by ckdot on 8/14/23, 9:47 AM
by dotdi on 8/14/23, 10:17 AM
I've been burned countless times by Hibernate (and consorts) and now I argue in favour of plain SQL wherever I can. I do not imply that Hibernate is in itself bad, I just have collected many years of observations about projects built upon it, and they all had similar problems regarding tech debt and difficult maintenance, and most of them sooner or later ran into situations where Hibernate had to be worked around in very ugly ways.
Yes, I can understand some of the arguments for ORMs, especially when you get a lot of functionality automagically à la Spring Boot repositories.
And since nowadays I have more influence, I do advocate for plain SQL or - the middle ground - projects like jOOQ, but without code generation, without magic, just for type safety. We've been quite happy with this approach for a very large rewrite that is now being used productively with success.
by badcppdev on 8/14/23, 10:25 AM
My vision of the "Just Write SQL" paradigm would be a "class" or equivalent that would take a SQL command and return the response from the server. Obviously the response has a few different forms but if you're "just writing SQL" then those responses are database responses and not models or collections of models.
(For the record I think simple ORM type functionality is actually quite useful as your use case moves past the scale of small utility scripts.
by fmajid on 8/14/23, 1:44 PM
One major benefit of stored procedures, in addition to separation of concerns, is that you can declare them SECURITY DEFINER and give them access to tables the Python process doesn't (in a way reminiscent of setuid), thus improving the security posture dramatically.
One example: you could have a procedure authenticate(login, password) that has access to the table of users and (hashed) passwords, but if the Python app server is compromised it doesn't have access to even the hashed passwords or even the ability to enumerate users of the system.
by tantaman on 8/14/23, 5:22 PM
If you're going to model your data this way... you might as well use an ORM.
A better way is to just just write SQL (or datalog) and model our data, from DB all the way up to the application, as relations rather than objects.
Rather than re-hash, this idea has previously been discussed on HN here: https://news.ycombinator.com/item?id=34948816
by davidthewatson on 8/14/23, 12:15 PM
Coleifer does not get enough credit IMHO:
Peewee has been solid since I began using it a decade ago. Coleifer's stewardship is hard to see at once, but I've interacted with him numerous times back then and the software reflects the mindset of its creator.
by hot_gril on 8/14/23, 5:08 PM
Also, I'm not one of those people who dislike easy things (and will often whine about JS or Python existing). I'm all for ease and focusing on the business goals. It's just that ORMs and bad schema design will make things harder.
by ris on 8/14/23, 6:18 PM
But projects (almost always) grow, and once you need to start conditionally adding filter clauses, conditionally adding joins, things start to get very weird very fast. And no, letting the database connector library do the quoting for you won't save you from SQL injection attacks unless you're just using it to substitute primitive values.
And once all your logic is having to spend more space dealing with conditional string formatting, the clarity of what the query is actually trying to do is long gone.
I'll refrain from digging up the piece of code where I was having to get the escaping correct for a field that was embedded SQL-in-SQL-in-SQL-in-go. And I could hear the echos of the original author's "YAGNI"s haunting me.
by robertlagrant on 8/14/23, 9:47 AM
by WesolyKubeczek on 8/14/23, 2:56 PM
Maybe there's some professional trauma at work, as many of us have been traumatized by shitty databases and shitty code working with them alike, ORM or not. But ORMs do come and go, promise bliss, deliver diddly, and I'm reading the same stuff I've been reading in 2008, as if nothing ever changed since.
by sergioisidoro on 8/14/23, 10:14 AM
Django ORM is amazing for Schema management and migrations, but I dislike their query interfaces (using "filter" instead of "where"). I really like Rails AR way of lightly wrapping SQL, with a almost 1-1, and similar names, but does not have a migration manager - and there is always the chance that your schema and your code will diverge.
If I would get a Schema / migration manager, that would allow to do type checks and that would work well with a language server for autocompletes, but use SQL or a very very thin wrapper around SQL, that would be my Goldilock solution.
by hobbescotch on 8/14/23, 10:14 AM
by ggregoire on 8/14/23, 3:44 PM
With this package, you write the SQL inside SQL files so you can benefit from syntax highlighting, auto formatting, static analysis, etc. At the difference of writing strings of SQL inside Python files. I'm surprised this is not more popular.
by megaman821 on 8/14/23, 2:23 PM
by fb03 on 8/14/23, 2:51 PM
I have seen people write their own custom crazy version of GraphQL ("I've created a JSONified way of fetching only some fields from an API call) over ego or just ignorance. It's never a good path.
Why bother moving away from SQLAlchemy, which will do all of that for you in a simplified, type-checked and portable way? SQLAlchemy is literally one of the best ORMs out there, it's ease of use and maintainability is insane.
People that complain about ORMs might have never really used SQLAlchemy. It is that good. I'm a fan and zzzeek is huge force behind why it is so good.
And as always, if you need an escape hatch, you can use raw sql for that ONE sql statement that the ORM is giving you grief for.
by ak217 on 8/14/23, 7:59 PM
- The ORM is an externally maintained open-source project with a plurality of contributors; "just write SQL" is not
- The ORM is designed to support the full lifecycle of the application including migrations; "just write SQL" is not
- The ORM is documented to be legible to newcomers; "just write SQL" is not (for all but the simplest of applications)
- The ORM is composable and extensible with opinionated and customizable interfaces for doing so (I've lost track of the number of times I've had my mind blown by how elegant and smart Django and SQLAlchemy's query management tooling is)
- The ORM has a security posture that allows you to both reason about your application's security and receive security updates when bugs are found
- The ORM is a platform for many other modules responsible for different layers of the application (DRF, OpenAPI, django-admin, testing utilities, etc. etc.) to plug into and allow the application to grow sustainably
I now try to guide people to a middle ground. Yes, both Django's and SQLAlchemy's ORMs can be annoying, have performance issues, etc. But for large applications maintained by multiple people over time, their benefits usually outweigh the drawbacks. Both have extensible architectures that allow customization and opinionated restriction of the interface that the ORM presents. If you're unhappy with your organization's ORM, I suggest you try that route first.
by p4bl0 on 8/14/23, 9:57 AM
by impulser_ on 8/14/23, 9:29 PM
https://github.com/sqlc-dev/sqlc
It's written in Go and it converts your sql migrations and queries into typesafe code that you use access your database.
It currently has a plugin for Python that's in Beta, but what essentially does something similar to what this post is saying.
https://github.com/sqlc-dev/sqlc-gen-python
You write your migrations, and queries and a config file and it does the rest.
by dep_b on 8/14/23, 2:17 PM
by Improvotter on 8/14/23, 11:07 AM
by waffletower on 8/14/23, 3:51 PM
by atoav on 8/14/23, 4:36 PM
Then I discovered peewee. I am happy now.
by mkl95 on 8/14/23, 2:18 PM
I have used several Python ORMs over the years, both for SQL and NoSQL. SQLAlchemy is the most powerful way of interacting with a relational database I have experienced.
I also write Go, and when I do, I do not use an ORM. But when it comes to Python I know my solution won't be better than SQLAlchemy, so why bother rolling out my own?
by sanderjd on 8/14/23, 6:11 PM
by c120 on 8/14/23, 9:51 AM
So what I do is write SQL commands, but keep all inside a specific file or module of the project. So that I can decide later to refactor it into an ORM.
I think ORMs are great if you write libraries that target more than one database. Or situations, where you have more than one database and need a proper migration part.
If you don't need migration, but in the worst case can start with a fresh, empty database, then write SQL.
But for production system, the no/manual migration might get old quickly. Writing migration code that just adds fields, indexes or tables is easy. But writing code that changes fields or table structures? Not do much.
Still, you don't need an ORM at the beginning of a project, just don't put SQL everywhere.
by Dowwie on 8/14/23, 10:21 AM
by lifewallet_dev on 8/14/23, 3:26 PM
by phatboyslim on 8/14/23, 4:34 PM
by Sparkyte on 8/14/23, 4:33 PM
by ruuda on 8/14/23, 4:57 PM
by sams99 on 8/14/23, 10:24 AM
by stuaxo on 8/14/23, 5:34 PM
As a Django developer it's straightforward to go from one Django project to another, which isn't the case with other stuff as you don't know where everything is going to be.
by molly0 on 8/14/23, 10:22 AM
If your app can work well with static queries then you should not add an ORM.
by slotrans on 8/15/23, 4:48 AM
Never use an (active record) ORM. Ever. For any purpose. They are a disastrous idea that should be un-invented.
I have this discussion with a lot of people who claim they cannot imagine working without an ORM and that "surely" using SQL is so much more work blah blah blah. Yet they have never tried! And aren't willing to try!
You should try it.
by hprotagonist on 8/14/23, 1:14 PM
PugSQL is a simple Python interface for using parameterized SQL, in files, with any SQLAlchemy-supported database.by pharmakom on 8/14/23, 11:33 PM
- immutable record type for each table in the database (could be a data class)
- functions for manipulating the tables that accept or return the immutable record types and directly use SQL
- that's it
you can generate the functions from the database schema if its too much boilerplate.
by timmit on 8/14/23, 10:35 PM
not readable at all, not easy to change, which is a terrible development experience.
I guess if it is simple CRUD, it does not give too much problem, but it will definitely work in a complex case.
by jredwards on 8/14/23, 3:25 PM
by metalforever on 8/14/23, 6:33 PM
by semrekkers on 8/14/23, 10:34 AM
by never_inline on 8/14/23, 10:25 AM
Anecdotally, I haven't seen a place where Go is used without something like gorm or sqlc.
by CodeWriter23 on 8/14/23, 10:12 PM
by bastardoperator on 8/14/23, 7:03 PM
by izoow on 8/14/23, 6:29 PM
by sakex on 8/14/23, 3:03 PM
by bafe on 8/14/23, 7:03 PM
by felipetrz on 8/14/23, 2:05 PM
...
Proceeds to create an ad-hoc ORM.
by ploppyploppy on 8/14/23, 10:58 AM