What's New in SQLAlchemy 2.1?

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.

As with most things, it is a balance.

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.

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

Every time I'm tempted to go raw SQL I always remember that

a) It is tricky to make raw SQL composable in a safe way, and

b) I still need to map all those rows into objects somewhere, but now i have to do it by hand

My suggestion is the following:

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).

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.

SQL seems far more portable.

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.

(There's also Atlas for Python but it isn't much better, a lot of fiddly config files https://atlasgo.io/guides/orms/sqlalchemy)

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.

Agreed, SQLAlchemy impressed me a decade ago and it still does today.

> Sqlalchemy stands out as a library having probably one of the most complete and pragmatic APIs for database access across all languages.

I can't disagree more. Identity map based ORMs are _awful_ to use, in almost every way.