Any Python ORMs worthy of production?

[nbrad]

I’m hoping for something battle-tested that supports async/await and migrations. If your answer is some variation of “real programmers don’t use ORMs”... fair enough, I suppose. But here are the options as I see them:

- Django: very mature ORM, migration support is great. But it doesn’t support async/await (https://docs.djangoproject.com/en/5.0/topics/async/), and these days FastAPI seems like a better option. FastAPI just needs an ORM, so =>

- SQLModel: from @tiangolo who created FastAPI, clean and good Pydantic support, so this would be my default option, but even though it seems to support async/await the doc page is blank (https://sqlmodel.tiangolo.com/advanced/), and for migrations you resort to the underlying =>

- SQLAlchemy: seems by far the largest/best supported, has async/await, has migrations through Alembic (it seems not as fully-featured as Django’s?), but trying to use it has felt very kludgy/painful/verbose.

- Tortoise: the README claims to fulfill my dreams, but I haven’t met anyone yet using it in prod? Anyone have experience they can share?

- Any others I missed?

Meanwhile in the JS/TS ecosystem, Prisma just added preview support for JOINs 2 weeks ago (https://github.com/prisma/prisma/releases/tag/5.7.0 !?!) and yet it seems ubiquitous (though I hear everyone’s moving to Drizzle).

Advice greatly appreciated

[mattbillenstein]

An ORM in the style of Django leads to patterns where you have a lot of code on the models - and the business logic becomes very tightly intertwined with the DB schema such that it's very hard to ever refactor it. Also, God objects, circular dependencies, etc.

ORMs in general I think are not a great abstraction - you get lazy-loading patterns, performance problems due to this which leads to manually doing eager-loading which leads to even more twisted up queries being sent to the db and hard to debug problems. And most of them rely on running in auto-commit, so you can't have idempotent transactions. YMMV

Generally, I've been writing very thin CRUD db-access modules that use SQL and I run every request in a single transaction that either commits or does a rollback on error. I don't do eagerloading or anything fancy, but if I need to get a list of related items, I just do a bulk query. So there are some small N number of queries per request, but still generally things are fast. I find this type of system easier to debug and optimize - if I see a slow query in the db log, I can just grep my codebase for it which is pretty much impossible with an ORM.

Re migrations, I've used Flyway before, I like the model, but lately I've been using a single python script which more or less does what Flyway does [1]. This supports migrations in SQL or Python, but I almost exclusively just use SQL now as Python migrations can have extra code dependencies not captured in the actual script. Salt to taste.

I think if you do decide to use an ORM, revisit this thread in a few years time and think about if it's saving you time in the long-run. I find the time it saves you in the prototype you end up paying back in spades in maintenance and building new features - again, YMMV.

1. https://gist.github.com/mattbillenstein/270a4d44cbdcb181ac2e...

[nbrad]

interesting, thanks!

[PaulHoule]

You know I have a lot of fun writing asyncio servers in Python for web applications or servers that are simultaneously connected to message queues, web sockets, and other things.

Whenever I talk about them people ask me "are you crazy man?" in that most teams struggle to maintain asyncio servers and if it was just a web server I might consider using Flask if I am planning to hand off the code to anybody but Jane Street. To absolutely avoid blocking the event loop any heavy computation has to be outsourced to something that is more or less a "microservice" which opens the question of are those services aio? are they even written in Python? I'd think of moving everything but the machine learning to the JVM.

That said, my take is that it is not difficult for a database driver or API client to support sync and async because the structure of almost all calls is the same (marshal parameters, make the network call, unmarshal results) so with a little bit of metaprogramming you can build out both implementations from the same source.

[arthurcolle]

ORMs are a critical piece of any modern web application.

Let's be real. Handling migrations and changes to data schema are going to happen all the time. Maybe over time they happen less, but not really. Basically any change to any functionality will realistically require changes to your database schema. ORMs give you a nice way to encapsulate your app model functionality in a way that works pretty well with database features. You're not going to get 1-1 parity in terms of every possible database with every possible ORM, but that's OK.

I like one particular FastAPI boilerplate lib that I use for stuff, and it is almost as good as using Rails with its amazing ORM, ActiveRecord.

[nbrad]

thanks - which FastAPI lib is that?

[arthurcolle]

https://github.com/iam-abbas/FastAPI-Production-Boilerplate

[nbrad]

thanks!

[lormayna]

Have a look to peewee: https://github.com/coleifer/peewee