10 ms·
Migrating to SQLAlchemy 2.0
- grouseway 6y agoLots of major changes, so I hope it doesn't create a schism. Do SQLAlchemy users appreciate how lucky they are? I generally prefer to use c#/.net but the 3 Microsoft ORMs (linq-to-sql, ef, ef.core) are all half baked. I don't know much about ActiveRecord, Django or other ORMs. I wish I could have this sort of feature set and dynamic abilities that I get in sqlalchemy on the .net side. I say that as someone who loves SQL but appreciates the conveniences of a powerful ORM.
- sirn 6y agoThere have been so many times where I started a new project in a new shiny language and ended up coming back to Python because I love SQLAlchemy (and Pyramid) way too much. It's one of the few ORMs out there that doesn't hate you for liking SQL.
- ergo14 6y agoSame here, I'd like to dive into golang. But there is nothing as good as SQLAlchemy. Maybe with generics we will start seeing some projects that can fill the gap.
- nrmitchi 6y ago> doesn't hate you for liking SQL This, just 100% this. I'm not a major fan of SQL or anything, however I get very hesitant to use any ORM that tries to imply that you don't need to understand how SQL or the underlying database actually works. Any time I hear someone say that "SQL won't scale for my app", I assume that some rudimentary query analysis would solve 99% of problems.
- bitexploder 6y agoDjango ORM has grown on me. I think it isn't quite as powerful as SQLAlchemy, but it is really quite decent. SQLAlchemy lets me think closer to SQL if I want to, and that is nice as well. I tend to use the Django ORM the most lately... but all 3 of those are rather full featured and accomplish the average user's needs. SQLA is still my favorite ORM anywhere.
- fernandotakai 6y agosame! i like that SQLAlchemy made me actually learn SQL. but for like, boilerplate stuff, Django's ORM works so well. also, because it's all within the same "framework", every single library can adapt super well to it and more a shitton of dumb code from your hands.
- bitexploder 6y agoI love reading about the thumping and bumping of modern frameworks. Meanwhile tons of great software gets written in rails and Django while startups cargo cult along. Maybe I am being curmudgeonly here, but, I think it’s true.
- deleted 6y ago[deleted]
- radus 6y agoThe description of the new direction for 2.0 sounds great, but as someone who isn't using SQLAlchemy currently (I prefer peewee) I'd be curious to see what it looks like without the assumption that I'm migrating from a previous version. I guess that's not yet fully settled?
- bratao 6y agoMy company is migrating from peewee to SQLAlchemy because the missing async support and we faced many bugs related to multi-threading/multi-processing.
- zzzeek 6y agothat's what the tutorial is for - assumes 2.0 style usage and nothing else: https://docs.sqlalchemy.org/en/14/tutorial/index.html https://docs.sqlalchemy.org/en/14/tutorial/index.html
- aidos 6y agoOT: but I’m seeing a weird layout issue on the new docs on mobile (iOS) where the main content is dropping below the bottom of the sidebar position. https://imgur.com/a/U4AbEOZ https://imgur.com/a/U4AbEOZ
- zzzeek 6y agoi would LOVE if someone could help us get the site to work on mobile. I can point folks to our scss and all of that and get it all going if someone can help. agree mobile is mostly unusable and new layout has probably some more problems since I started using flex layout for which I am unqualified to be touching.
- hambos22 6y agoPlease drop me an email (you can find it on my profile here). SQLA is my go-to library for lots of stuff, I would love to help :)
- avolcano 6y agoOne of the most interesting 1.4/2.0 changes is first-class asyncio support, not just for core (the query builder) but for the ORM layer as well: https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#change-3414 https://docs.sqlalchemy.org/en/14/changelog/migration_14.htm... As this notes, there's several changes you have to make to your assumptions around the ORM interface. SQLAlchemy, for better or worse, supports "lazy loading" of relationships on attribute access - that is, simply accessing `user.friends` would trigger a query to select a user's friends. This kind of magic is at odds with async/await execution models, where you would instead need to run something like `await user.get_friends()` for non-blocking i/o. It looks like they've done some good work in making the ORM layer work reasonably well with these limitations (https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html#preventing-implicit-io-when-using-asyncsession https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.htm...), but I wonder if removing "helpful magic" like this will push more people to stick with the query-builder, rather than the ORM.
- aidos 6y agoIt was really interesting. I saw the discussions about it where zzzeek was like, “oh, what, I could kinda just make this wrapper thing. Wait, what am I missing here? Nope, it works” It was a late entry into the transition rather than a planned thing from what I could tell. Edit: post is here https://gist.github.com/zzzeek/2a8d94b03e46b8676a063a32f78140f1 https://gist.github.com/zzzeek/2a8d94b03e46b8676a063a32f7814...
- orf 6y agoSo the secret sauce was just greenlet?
- parhamn 6y agoI’ve seen quite a few shops which effectively hit every python db/networking/pooling foot gun you could possibly encounter while using SQLAlchemy. People cargo cult the flask intro tutorial and have no clue how session binding works, where the transactions are, how committing works, etc because it’s all tucked away in magical middleware and singletons. As the code base grows so does the mess of blocking txns, accidental cross joins, pool exhaustion, and so on. It’s a great tool from a technical expressiveness perspective but terribly full of operational foot guns. Beware and use Django until you’re sure you AND your team know what you’re doing.
- zzzeek 6y agoSQLAlchemy author here. Your comment states the problem space addressed by SQLAlchemy 2.0, which is what the above document is about, really well! So for your comment to have value, what did you think of SQLAlchemy 2.0's direction in how it seeks to vastly improve the very issues you speak of, "where the transactions are", "how committing works", etc.? For example: "accidental cross joins" - SQLAlchemy 1.4 now warns/ raises for these: https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#built-in-from-linting-will-warn-for-any-potential-cartesian-products-in-a-select-statement https://docs.sqlalchemy.org/en/14/changelog/migration_14.htm... cool huh? The new docs are worth a read before commenting. It sounds like you were relying on flask-sqlalchemy in any case which unfortunately makes some poor decisions in these areas, but in SQLAlchemy 2.0 these things are brought forward unconditionally in all cases so you really can't talk to the database without having your transaction and its scope front and center.
- parhamn 6y agoI'm sorry if my comment struck negatively with you. The library has been very useful to me and teams Ive worked with in the past. In fact, I've lobbied for corporate sponsorship of your work a few times. Thank you. The problem I'm describing isn't really the ORM libraries' fault as its outside the scope (hah). Your library is great. Most of the trouble I see is where the ORM meets the web framework. I think that gets tricky for lower experience developers because it's fundamentally tricky in Python. What the scheduler (e.g. gunicorn) does and how it forks and how sessions are handled at the wsgi layer are where you have to be very careful. Django has brought that in scope and you haven't -- which is why thats a better wholistic webdev experience and SQLAlchemy is the more powerful ORM. With that said I stand by my comment having read the full changelog (commendably thorough btw). Unless you can afford to figure those things out as a team, it would behoove most web devs to reach for Django first.
- psychometry 6y agoI kind of wish some of the SQLAlchemy core devs spent a bit of time using ActiveRecord to appreciate how an ORM can make defining and querying relations straightforward and easy. Right now, using SQLAlchemy creates a "now you have two problems" kind of workflow: first you figure out the SQL need, then you spend at least that long figuring out how to write it with the ORM. I never felt this way about ActiveRecord.
- michelpp 6y ago> I kind of wish some of the SQLAlchemy core devs spent a bit of time using ActiveRecord to appreciate how an ORM can make defining and querying relations straightforward and easy. The main SQLA developer, and the whole team, has been doing this now for almost 3 decades, has presented on and had thousands of serious detailed technical discussions on the subject with a diverse range of industry participants, and I can assure you is WELL aware of how ActiveRecord works and all of the patterns around it.
- qbasic_forever 6y agoAnd? Is it a documentation problem then, that the SQL alchemy devs don't think it's worth the time to explain to devs familiar with active record what they gain using SQLA?
- zzzeek 6y agoyou get to think in terms of SQL and relational algebra is the basic idea. Here's one of my talks that discusses this: https://www.sqlalchemy.org/library.html#handcodedapplicationswithsqlalchemy https://www.sqlalchemy.org/library.html#handcodedapplication... as for "it's hard to translate from SQL to ORM" that's a huge part of what 1.4/2.0 is trying to make more obvious. But to be fair I get very few "how do I write this in SQL" questions these days as things are pretty 1-1 in any case now; the remaining weak spots (awkwardness with unions, support for table-valued expressions) are addressed in 1.4/2.0 and the relatively awkward "session.query()" model is now legacy.
- michelpp 6y ago
- liquidify 6y agoI used their ORM library in a medium sized project. As the project grew, it turned a nightmare scenario. After that experience, I stick to core and raw SQL queries. I hope 2.0 brings some meaningful changes.
- swagonomixxx 6y agoAgreed. I will never recommend an ORM, things simply spiraled out of control for medium to large-ish projects that had more than 2 developers. Even with "best practices", code ended up having a mix of raw SQL and ORM-style queries, and it was hard to reason about the code. Since switching to asyncpg [0] these problems have vanished. It commands a deeper knowledge of actual SQL, but I would argue this knowledge is absolutely necessary and one of the disadvantages of an ORM is that it makes the SQL that is eventually run opaque. Not sure if there are equivalents to asyncpg for other RDBMS's. [0]: https://github.com/MagicStack/asyncpg https://github.com/MagicStack/asyncpg
- qbasic_forever 6y agoModeling their migration off the Python 2 -> 3 migration. Bold move, let's see how that works out for them.
- stilisstuk 6y agoWhy is nobody ever writing raw SQL? I've never understood why ORMs are sine qua min.
- bagol 6y agoBecause you won't use raw SQL on any serious project forever. You'll end up building an ORM yourself or at least a query builder.
- oblio 6y agoDo you mean "sine qua non"? People don't generally like writing raw SQL because you have to map the results to and from your programming language. So at a minimum, you need a query builder that does some minimal and flexible mapping.
- stilisstuk 6y agoHa. Yes. I apologise for the auto correct. Building basic CRUD apps as a hobbyist, I've just never had that problem. My app needs some data. I fire of a query af psocopg2 gives me back my data. I know I'm the least experienced. So I'm not arguing. I just don't understand it (I work mostly be with data / BI, so I'm familiar with SQL)
- ergo14 6y agoImagine be you build a "search users" page with many filters, you will end up inventing query builder at one point. The more dynamic data you need, you will end up reinventing those systems. The worst that can happen is when you start concatenating sql queries together.ORM/query builders save you that headache.
- stilisstuk 6y agoYes..i have concatenated SQL i must admit.
- scrollaway 6y ago
- gigatexal 6y agoI got my start in IT as a DBA. SQL and good table design come naturally. So when it came time to join a company as a developer I sprang for raw SQL only to find this SQLalchemy ORM. I couldn’t wrap my head around it at first. All the ceremony to get to what I wanted to do just got in the way. I felt trapped. But it’s ubiquitous and I have to adapt. So I’m learning. And there’s a whole lot of benefit being able to define a model and have it render on any database. Paired with Alembic migrations can be pretty simple. I miss having 100% control over the queries, knowing exactly how they looked and analyzing each before committing them to main. But nobody has the time to hand craft artisanal queries and leverage every intricate detail of a database when they’re trying to move ever faster and ship features.
- WesleyJohnson 6y agoGot my start in classic ASP and we authored so many queries using string concatenation. When ASP.NET came around, we shifted our database thinking to stored procedures, so I could continue to write "raw" SQL. I'm no DBA, but I learned so much about authoring efficient and complex queries - I genuinely love working with SQL. These days, though, I use Django's ORM. I can often get it to do what I want, bit it sure makes me miss raw queries. Thankfully, we still write the occasional view for complex joins and then just map that to a read-only model in Django - so I sometimes get the best of both worlds.
- gigatexal 6y agoYeah the shop I got my start in shipped .Net on the server and client side with stored procedures tied to that code on the DB side. So it was a sort of API abstraction that slowed devs to call update_users(...) from the app side and get raw sql performance and such. To a newbie like me at the time it may have been an old school approach but it made total sense. Pretty cool to see someone get a similar start to me. I’ve not played with the Django ORM yet. Still getting used to SQLalchemy.
- WesleyJohnson 6y agoExactly! It made total sense at the time, and still does to some degree. I'm sure SQLAlchemy is lovely, but after spending so much time with Django ORM, I'm finding it hard to shift my thinking any time I try to look at SA. I'm sure if I had started using SA first, the opposite would also be true, so we just took different paths. I'm guessing you'll enjoy SA if you love SQL, based on all the other comments I'm seeing.
- luord 6y agoI'm particularly interested in the support for dataclasses. It's going to make modeling the application while decoupling from the data layer itself easier, I think.
- sandGorgon 6y agoSame here. This is something that I believe that Sqlalchemy should fundamentally take a bet on. Dataclasses are now inherently part of python. They are also used across the ecosystem (e.g. pydantic). It makes sense to use them for model declaration. Hope Sqlalchemy becomes dataclasses first...and not just as a compatibility feature.
- throwdbaaway 6y agoGood to see the removal of autocommit, with the detailed discussion of the design decision. I've always felt a little uneasy when going with autocommit=False in my old projects, thinking that the default of autocommit=True was the "blessed" way to use SQLAlchemy. EDIT: Looks like autocommit=True has never been the default, must have been some possibly 3rd party documentation.
- temuze 6y agozzzeek is an absolute beast and the SQLAlchemy codebase is a gem to read.
- closed 6y agoI've been building data analysis tools on top of SQLAlchemy's declarative system over the past couple years. It's got to be the most well documented, carefully designed library I've ever interacted it :). It looks like most of the changes in 2.0 are aimed at the ORM system, which makes sense. I think a lot of complaints that come up have more to do with the complexity of interacting with a SQL database, so appreciate the effort in the docs not just laying out an API, but essentially educating around the problem domain.
- qatanah 6y agoThanks for the hardwork zzzeek! I've been using sqlalchemy for 5+yrs now and it's great to work with! I like SQL and I like the ORM. When things go tough like doing complicated JOINs or OLAP. I just go raw sql. If it's OLTP, updates and simple lookups, ORM makes the code readable. The alembic migration is also great! I can craft the models to design with postgres features (index, multi key index, pk uuid) and etc.. I'd would love sqlalchemy to invest more on scaling. Although scaling is an entire book of discussion. Not much resources are there for handling multiple db, sharding (maybe too much to ask). My opinion is, love SQL and Love the ORM. You'll need both to appreciate it's power. It's like learning VIM, a long term investment tool and hard to :wq
- The_rationalist 6y agoHow does it compare to the JPA and active records?