6 ms·

What's New in SQLAlchemy 2.1?

75 points by hackandthink 2y ago

sickblastoise 2y ago
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.
- Onavo 2y ago
  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 https://atlasgo.io/guides/orms/sqlalchemy)
  - Something1234 2y ago
    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.
  - nhumrich 2y ago
    Alembic is amazing. I haven't found a better tool in any language. I actually use it even when I don't use python as the main language
- kapilvt 2y ago
  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.
  - noufalibrahim 2y ago
    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.
- heavyset_go 2y ago
  Agreed, SQLAlchemy impressed me a decade ago and it still does today.
- mbell 2y ago
  > 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.
  - halfcat 2y ago
    What’s bad about them?
  - Spivak 2y ago
    What's your gripe with identity map specifically? The map is bound to a session which are short lived in SQA and usually map to a single transaction.
benwilber0 2y ago
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? Nah. Just write the good SQL for your database.
- noisy_boy 2y ago
  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.
  - BeefWellington 2y ago
    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.
    - jayknight 2y ago
hakanderyal 2y ago
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.
zzzeek 2y ago
why is someone sharing the "whats new in 2.1" document that has about 5% of what 2.1 is going to have? see that big red "IN DEVELOPMENT" at the top? this is really premature to be posting on hacker news, we're hoping for 2.1 being ready sometime middle of next year if we are lucky
gniting 2y ago
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 https://prisma.io/typedsql

75 points by hackandthink 2y ago

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.

Onavo 2y ago
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 https://atlasgo.io/guides/orms/sqlalchemy)
- Something1234 2y ago
  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.
- nhumrich 2y ago
  Alembic is amazing. I haven't found a better tool in any language. I actually use it even when I don't use python as the main language
kapilvt 2y ago
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.
- noufalibrahim 2y ago
  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.
heavyset_go 2y ago
Agreed, SQLAlchemy impressed me a decade ago and it still does today.
mbell 2y ago
> 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.
- halfcat 2y ago
  What’s bad about them?
- Spivak 2y ago
  What's your gripe with identity map specifically? The map is bound to a session which are short lived in SQA and usually map to a single transaction.

benwilber0 2y ago

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? Nah. Just write the good SQL for your database.

noisy_boy 2y ago
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.
- BeefWellington 2y ago
  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.
  - jayknight 2y ago

hakanderyal 2y ago

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.

zzzeek 2y ago

why is someone sharing the "whats new in 2.1" document that has about 5% of what 2.1 is going to have? see that big red "IN DEVELOPMENT" at the top? this is really premature to be posting on hacker news, we're hoping for 2.1 being ready sometime middle of next year if we are lucky

gniting 2y ago

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 https://prisma.io/typedsql