Patterns for building with SQLAlchemy / asyncio

[de...@devinfee.com]

One "exit-hatch" for using the SQLAlchemy ORM with asyncio is to use executors. In this model, all database access is confined to a thread that is not the thread where the eventloop is running.

However, it appears that sessions (thus, transactions) should not span more than one executor, meaning that all SQL work must be batched into the same executor – right?.

For a web framework, this is problematic: during the request lifecycle there are many points at which the database might need to be accessed (auth middleware, request handling, etc.) – hence multiple entries into an executor and therefore incompatible with the idea of a session.

A possible alternative then appears to be using something like `aiopg` and confining your work to SQLAlchemy core. However, my problem here is that I'm worried by using SQLAlchemy core, and mapping ResultProxy objects into POPO (plain old python objects, perhaps built on top of the `attrs` library), I'm just ultimately approximating the reinvention of an ORM (without unit of work, without relationships, etc.) – right?.

Can someone help me square into my head whether I should be building an application on top of sqlalchemy.core (perhaps with something like `aiopg`), or whether there is a pattern I'm missing?

Thanks,

Devin

[Mike Bayer]

On Thu, Aug 9, 2018 at 2:16 PM, <de...@devinfee.com> wrote:
> One "exit-hatch" for using the SQLAlchemy ORM with asyncio is to use
> executors. In this model, all database access is confined to a thread that
> is not the thread where the eventloop is running.
>
> However, it appears that sessions (thus, transactions) should not span more
> than one executor, meaning that all SQL work must be batched into the same
> executor – right?.

I don't see why that would be the case, as long as these executors
(which I assume are each individual threads) are not executed
concurrently for a particular session.

>
> For a web framework, this is problematic: during the request lifecycle there
> are many points at which the database might need to be accessed (auth
> middleware, request handling, etc.) – hence multiple entries into an
> executor and therefore incompatible with the idea of a session.

should not be the case, a single web request is still doing each of
its steps in serial so the concept of a transaction / session that
spans the full request is compatible with asyncio. Different
executors run concurrently however each of them are local to different
requests with their own session/transaction so there is no issue.

>
> A possible alternative then appears to be using something like `aiopg` and
> confining your work to SQLAlchemy core. However, my problem here is that I'm
> worried by using SQLAlchemy core, and mapping ResultProxy objects into POPO
> (plain old python objects, perhaps built on top of the `attrs` library), I'm
> just ultimately approximating the reinvention of an ORM (without unit of
> work, without relationships, etc.) – right?.
>
> Can someone help me square into my head whether I should be building an
> application on top of sqlalchemy.core (perhaps with something like `aiopg`),
> or whether there is a pattern I'm missing?

If you know me at all, the only pattern you may be missing is that
there's very little value to using asyncio for just about anything IMO
:). But I know that's likely not constructive for your use case.

See http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/
for my overall take on asyncio which has not changed much since then.



>
> Thanks,
> Devin
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[b...@sfi.ca]

Mike, Thanks for the pointer to your enlightening post http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/