[HN Gopher] Migrating to SQLAlchemy 2.0
___________________________________________________________________
Migrating to SQLAlchemy 2.0
Author : brainless
Score : 138 points
Date : 2021-02-18 17:28 UTC (5 hours ago)
(HTM) web link (docs.sqlalchemy.org)
(TXT) w3m dump (docs.sqlalchemy.org)
| parhamn wrote:
| I'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.
| nodamage wrote:
| Do you mind elaborating on these what these foot guns are and
| how to avoid them?
| ergo14 wrote:
| We are running sqlalchemy in a giant environment - hundreds of
| millions of users. SQLAlchemy has been a blessing for us. Blame
| users that use flask-alchemy or whatever else is cool. This is
| exactly the same type of comment that blames ORM-s for
| ineffective SQL being used because people don't know how to
| write the queries in the first place.
| parhamn wrote:
| > Blame users that use...
|
| I am, and I'm warning them, whats wrong with that?
| TomBombadildoze wrote:
| You put it better than I could. There are certainly naive
| examples of how to use it out in the wild but that's not
| SQLAlchemy's fault. It's a sophisticated tool that accurately
| reflects how a database works.
|
| I caution people _against_ using Django because the ORM makes
| so many weak, simplified assumptions about how a database
| works that don 't bear out in practice. It's fine for a blog.
| It falls apart when you're working with Real Data.
| kennydude wrote:
| I've used Django with "real data", as in accountancy
| software for multiple years of transactions across 200+
| fairly large clients and it's absolutely fine.
|
| Whereas I've used Sqlalchemy and getting it to do a basic
| join, turns out there's 3 different ways of doing it all
| with an insane amount of crap to find the right way of
| doing things. I actually left my old job in part due to
| Sqlalchemy being too painful to work with.
| ergo14 wrote:
| Like... Query.join(), https://docs.sqlalchemy.org/en/13/o
| rm/tutorial.html#querying... .
| BiteCode_dev wrote:
| There is absolutely a lack of a decent SQLA tutorial for
| IRL code.
|
| The doc is very formal, and if you want to make a
| website, a quick script or a data migration, you are left
| on your own to find the proper setup.
| orf wrote:
| What weak assumptions does it make?
| Topgamer7 wrote:
| Yeah our org should really move away from flask-sqlalchemy
| and be more declarative about what session changes should
| actually be made on.
|
| Being more hands on with sqlalchemy is probably the right
| approach.
| danjac wrote:
| Not having touched this stack in a while: what is best
| current practice for wiring together sqlalchemy and flask
| (including unit tests)?
| returningfory2 wrote:
| Potentially don't wire them together? That is, isolate
| them in separate parts of the code.
|
| I always found Flask-SQLAlchemy a strange approach
| because it ties opposite and somewhat orthogonal parts of
| the request lifecycle together. When requests-to-data-
| transactions is a 1-1 relationship it's a convenience,
| but once you step out of that I think it's better to do
| things by hand.
|
| My main app has a simple layer structure of "request >
| business logic > data layer". All of the business logic
| functions start a SQLAlchemy transaction using a Python
| decorator that is ~10 lines of SQLAlchemy code.
| ergo14 wrote:
| Check out how things are wired with in this Pyramid
| cookie cutter.
|
| https://github.com/Pylons/pyramid-cookiecutter-
| starter/blob/...
|
| I think same approach should work well for flask - just
| tie the session to the request object on first access.
|
| Side note: I think we met on IRC few years ago ;-)
| luhn wrote:
| There is a _lot_ going on in that cookie cutter. The
| "transaction" library for transaction management, wired
| into Pyramid with pyramid_tm, wired into SQLAlchemy with
| zope.sqlalchemy. Works nicely for Pyramid, but I don't
| know how that translates to Flask.
| ergo14 wrote:
| Doesn't, I wanted to show how it can be wired to request.
| jononor wrote:
| I passed on flask-sqlalchemy because it was not obvious to
| me how it worked together, and I did not see the value over
| just using Flask and SQLAlchemy with no special
| integration. Has worked now a couple of years in, will see
| how it is in a few years more.
| msluyter wrote:
| Example: a team I was on was using SQLAlchemy and we had a
| couple of services, one that did read only queries, and another
| that did writes and occasionally DDL (rebuilding the table on
| the fly, (which is unwise but that's a topic for another
| post.)) Because we didn't understand SQLAlchemy's default
| semantics, the read side would effectively open a transaction
| and leave it open, meaning that subsequent DDL statements would
| block, then causing the entire table to lock. (I think our
| solution was to use `autocommit=True`, which iirc is now
| deprecated.)
|
| That said, I really like SQLAlchemy.
| ergo14 wrote:
| The trick is to always auto close transactions at the end of
| the request.
| You-Are-Right wrote:
| Please describe footguns and how to avoid. Thanks!
| BiteCode_dev wrote:
| > Beware and use Django until you're sure you AND your team
| know what you're doing.
|
| That's one of my pet peeve in the Python world: people thinks
| Flask is for beginners and Django for advanced users. The Flask
| API is simple, the hello world is 5 lines. By the time you
| render your first Django ViewModel, you have read and tried out
| the full Flask doc.
|
| So it's easy to say, "I'll start with Flask, and check out this
| Django thing later".
|
| But in reality, it's exactly the opposite. Flask requires you
| to know a lot of things and make a lot of decisions, not to
| mention select/write additional libraries for features Django
| packs.
|
| So if you use Flask for anything non trivial, you should show
| your trade or you are going to mess up, big time.
|
| If you are a beginner, you should always start with Django.
| Yes, it's more work up front, but it will give you an idea of
| how important concepts work, plug in to each others, and can be
| organized in a code base.
|
| Django is not perfect, and its ORM is really far from
| SQLAlachemy, but it's good enough for a loads of thing. And it
| will be mostly secure and extensible by default.
| mrRandomGuy wrote:
| 100%
|
| The upfront cost of Django makes people thing it's much more
| complicated than a micro-framework like Flask.
|
| I like Flask, but not because it's a simple easy to use
| framework, especially once you chuck SQLAlchemy into the mix.
| Shit, even now if I don't touch it for a while I end up
| trying to remember how things work
| zzzeek wrote:
| SQLAlchemy 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.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 wrote:
| I'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.
| zzzeek wrote:
| no worries, I'm pretty known for being reactive to comments
| of all kinds, it's both a feature and a bug. SQLAlchemy was
| never meant to be as "simple" as django but I just dont
| want people to be scared off of it; that said, I'm super
| glad everyone who uses django sticks with django because
| they would be very grumpy SQLAlchemy users. the idea is
| supposed to be, make a larger investment upfront, get a
| bigger payoff. it's a tough balloon to float which is why
| I'm always nosing around people's twitter threads.
| luord wrote:
| I'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.
| stilisstuk wrote:
| Why is nobody ever writing raw SQL? I've never understood why
| ORMs are sine qua min.
| oblio wrote:
| Do 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 wrote:
| Ha. 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 wrote:
| Imagine 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 wrote:
| Yes..i have concatenated SQL i must admit.
| scrollaway wrote:
| It's easy to write raw SQL queries. It's a lot harder to
| generate SQL programmatically, correctly, based on a variety of
| requirements your app may have. ORMs do this well.
| baq wrote:
| Raw sql is not composable.
| bityard wrote:
| There is a middle-ground between writing SQL statement
| strings in your code, and a full-blown ORM: query builders.
| At least in my experience with small to medium projects,
| these have far fewer footguns while keeping the code
| composable and readable. Here's one for Python:
| https://github.com/kayak/pypika
| nrmitchi wrote:
| I'll just say that SQLAlchemy can also be used effectively
| as just a query builder in places that you need it.
| evgen wrote:
| You can write direct SQL queries using SQLAlchemy Core without
| engaging the ORM layer at all. It protects you from a few of
| the footguns associated with raw SQL but still provides most of
| the power and features you would want from raw SQL. If you
| decide that some part of your system could use the ORM then it
| is easy to integrate it into your existing Core work or visa
| versa.
| Hamuko wrote:
| If you want to use an SQL database with an OOP language like
| Python, you will end up using an ORM library, or writing one.
| asah wrote:
| For record/object-at-a-time, an ORM saves tons of code.
|
| SQL's design is optimized for processing unordered sets of
| records.
| nodamage wrote:
| How do people who prefer to write raw SQL usually deal with:
|
| 1. Query building, particularly when the query needs to be
| dynamic based on user input? Do you end up concatenating
| strings together or do you use a separate query builder?
|
| 2. Coalescing result sets produced by JOINs back into object
| form? Example: if you want to fetch users along with all their
| posts your query will return multiple rows per user, but when
| working with objects in your app you want each user to have a
| list of posts so you can simply say _users.posts_.
|
| 3. Property change tracking? Example: different parts of your
| app might update different properties for each user. If the
| user's _email_ and _last_login_ changes you need to write one
| query. If the user 's _password_ changes you need to write a
| different query. If the user 's _email_ , _name_ , and
| _location_ changes you need to write another query. An ORM with
| change tracking will figure out exactly which properties have
| been modified and issue the correct SQL to update only the
| changed properties. When working with raw SQL do you simply end
| up writing different queries for each possible permutation of
| changes?
| bagol wrote:
| Because 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.
| [deleted]
| radus wrote:
| The 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 wrote:
| My company is migrating from peewee to SQLAlchemy because the
| missing async support and we faced many bugs related to multi-
| threading/multi-processing.
| zzzeek wrote:
| that's what the tutorial is for - assumes 2.0 style usage and
| nothing else:
|
| https://docs.sqlalchemy.org/en/14/tutorial/index.html
| aidos wrote:
| OT: 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
| zzzeek wrote:
| i 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 wrote:
| Please 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 :)
| zzzeek wrote:
| I'm not seeing an email address can you tweet me on
| twitter?
|
| Basically if someone can show me how to make the sidebar
| vanish on a mobile browser i think that's the main thing.
| i might have looked at this some time ago and given up.
| aidos wrote:
| Happy to help with this. Will take a look tomorrow.
| hambos22 wrote:
| Indeed, I must removed it a while ago. Please ping me at
| me[at]cpoul.dev and I'll check it asap
| 1337shadow wrote:
| This is documentation for 1.4:
|
| https://docs.sqlalchemy.org/en/14/contents.html
|
| If I understand correctly, 2.0 will basically be 1.4 but
| without the DeprecationWarnings, and without the deprecated
| APIs - at least, that's how I've been coding for 2.0 so far,
| initially to benefit from asyncio support.
| avolcano wrote:
| One 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.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.sqlalch
| emy.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 wrote:
| It 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/2a8d94b03e46b8676a063a32f7814...
| orf wrote:
| So the secret sauce was just greenlet?
| psychometry wrote:
| 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.
|
| 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.
| [deleted]
| luhn wrote:
| Honestly that's my favorite part about SQLAlchemy--you need to
| think in SQL. It doesn't try to hide anything behind leaky
| abstractions, it's WYSIWYG. Just gives us the good parts of an
| ORM while sidestepping the whole "ORMs are the Vietnam of
| Computer Science" problem.
| sirn wrote:
| I think this is fundamental design difference between
| SQLAlchemy and ActiveRecord in general. SQLAlchemy uses Data
| Mapper pattern which does not necessary map 1:1 to the row in
| the database, so you have to deal with the whole (Object,
| Mapper, Data) tuple instead of just Object in case of Active
| Record pattern (of which Rails' ActiveRecord were based on).
|
| This means SQLAlchemy does not try to hide away SQL, which is
| beneficial in dealing with complex queries. In Rails
| ActiveRecord you could use arel in such case but being a query
| builder it lose the benefit of ActiveRecord, whereas in
| SQLAlchemy it could be done relatively easily within the ORM
| layer (arel equivalent in SQLAlchemy would be its Expression
| Language). On the other hand, some things that are complicated
| in ActiveRecord can also be trivial to implement in SQLAlchemy
| (e.g., column_property[1])
|
| [1]: https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html
| michelpp wrote:
| > 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 wrote:
| And? 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 wrote:
| you 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#handcodedapplicatio
| n...
|
| 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 wrote:
| Search "sqlalchemy activerecord" and you will find
| tutorials, compare and contrast posts, pros and cons, and
| several implementations for sqlalchemy and many other
| languages and libraries using the activerecord pattern.
|
| SQLAlchemy, and Python in general, is highly extensible, it
| can do the ActiveRecord pattern and many other patterns
| depending on the data, not just the needs of a content
| publication system.
|
| Here's a couple random AR/SQLA implementations I plucked
| from DDG:
|
| https://pypi.org/project/sqlalchemy-mixins/
|
| https://pypi.org/project/Flask-ActiveRecord/
| qbasic_forever wrote:
| Modeling their migration off the Python 2 -> 3 migration. Bold
| move, let's see how that works out for them.
| grouseway wrote:
| Lots 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 wrote:
| There 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 wrote:
| Same 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.
| enjeyw wrote:
| Mmm yeah I had the same experience. My end conclusion for
| Golang was "don't use an ORM".
| nrmitchi wrote:
| > 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 wrote:
| Django 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 wrote:
| same! 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.
| liquidify wrote:
| I 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 wrote:
| Agreed. 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
| gigatexal wrote:
| I 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.
___________________________________________________________________
(page generated 2021-02-18 23:00 UTC)