I've loved and used 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?
I wrote loads of SQLs at the beginning of my career with focus on performance tuning and worked with ORMs much later - so it was the other way round for me. For some use cases, ORMs can indeed save a bunch of effort - if I have to do a straightforward join with a simple filter condition, it is orders of magnitude simpler to use the ORM. The caveat is to verify the SQL being executed (most frameworks provide features to log the SQL) to ensure that it is what is expected. However, if you are doing complex connection, direct SQL would be the best choice - it is deterministic and you can tune it perfectly for what you need.
In my experience ORMs are great for simple CRUD but terrible once you do anything analytical in nature. Even stuff like account summaries often go sideways and it's better to handcraft.
For the CRUD side it's worth it I think, but yeah definitely a balancing act between the two.
One approach we use is to put complicated stuff in views, then use the orm to perform simple queries against those. That's especially useful for complex relationships between tables.
We do this too. Also allows for making changes purely at SQL level without having to do code changes - as long as the select clause signature is the same, the application doesn't know any better. The downside is that you have to be careful not to a build an impenetrable nesting of its-views-all-the-way-down.
We have an in-house migration management system that allows us to update views pretty safely and reliably, even with nested dependencies. Basically, on every release, all of the views are dropped and recreated, so we don't have to worry as much about that. (But still be very careful with nesting your views)
Start with whatever you want, write raw SQL or use an ORM, your choice.
If you find yourself doing a bunch of string formatting and concatenation, then use more ORM features. The more loops, if-statements, and regexes[0] involved in your "raw SQL", the more important it is that you start using a proper ORM.
If you find yourself struggling to get the ORM to do exactly what you want, and you're repeatedly reviewing the SQL it generates, then use less ORM and more hand written SQL. Check to see if your ORM allows writing only a portion of the SQL by hand (SQLAlchemy does).
This seems very situation specific. If you write lots of fun queries, I see how it may not be that useful. But the most common case for people is honestly going to be User.find(id=params.id) and Things.find(owner=user). That's like 99.9% of the internet. Saving the time of writing that trivial query and dealing with the result is perfect for ORMs.
> And they're always lazy, so you never really know when the N+1s are going to happen.
There are tools to detect this. Please use them regardless. Even if you're writing queries but hand, enough functions on the stack can hide an N+1
I'm not talking about trivial applications. PK lookups are easy no matter what you use. I'm talking about non-trivial applications.
> > And they're always lazy, so you never really know when the N+1s are going to happen.
> There are tools to detect this. Please use them regardless. Even if you're writing queries but hand, enough functions on the stack can hide an N+1
I don't want to have to use a tool to "detect" anything. I want my queries to return exactly what they're expected to return, or my code will fail. No surprises.
Writing the query is only half of the work, and it's the easiest since SQL is perfectly fine as a query language (duh... who would have guessed ?). There's also a M in ORM. Good ORMs will give you nice abstractions for querying but will also let you do the query yourself if you ever need it while still mapping these queries to your objects.
I often find myself knowing how to write a query in SQL but spending sometimes hours figuring out how to write it in a specific ORM, even worse is I'm probably not going to know what the ORM code does in 6 months time.
Sqlalchemy stands out as a library having probably one of the most complete and pragmatic APIs for database access across all languages.
It is no small feat to create compatibility for modern Python features like type hints and async in a library that has its roots in Python 2, it has absolutely exceeded expectations in that regard.
Sqlalchemy in general is great but the data class integration feels non pythonic to me, due perhaps to catering first to the typing crowd instead of the ergonomic one.
I felt that too but over time decided that it compromised the theoretical pythonicity for the practical compromise of being flexible enough to work properly with SQL.
One other reason for its popularity and success is how engaged the orginal developer is with the overall community.
What they need is a proper migration diff and generation tool with strong defaults. Alembic is meh and the DX is poor. Prisma and Django's migration/diff tools are the gold standard.
Prisma defeats the entire point of sql with it's weird almost graphql like thing. Apparently developers can't be trusted with it cause you can do big dumb in it.
SqlAlchemy was the reason I've stayed with Python all those years developing line-of-business apps. When you are pumping out an app every 2 weeks, you don't have time to manually write SQL. It's comprehensive, it's sane, it's evolving, it's good. It taught me SQL, and how a good ORM should not limit you but empower you.
Since Entity Framework Core matured enough and I've discovered LINQ, I said my goodbyes and switched to C#.
Thanks, Mike Bayer and all maintainers, for helping me earn my living using your software all those years.
Prisma team member here... We just released "TypedSQL" which gives users the ability to write fully typesafe raw SQL. Sharing this here as the info seems pertinent for those who love SQL.
Link: https://prisma.io/typedsql
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.