SQLAlchemy Async ORM is Finally Here!

[Tutorial] Here is how to work with the new async SQLAlchemy ORM

I think the whole python web development community has been waiting for this change for a while and I personally was extremely happy to hear about the long awaited async orm functionality in SQLAlchemy when the beta version was released on February 3rd in the start of this year and official release v1.4.0 on 15th of March.

Before we dive into SQLAlchemy’s async orm, there has been a bunch of async orm tools that are worth mentioning: Tortoise ORM, Pony ORM, PeeWee and Gino. While playing with these tools, I would say that Pony ORM was my favorite, specifically that its syntax was really pythonic.

Let’s install the new SQLAlchemy,

$ pip install sqlalchemy asyncpg

We will use dockerand postgres, let’s run our database in the background.

docker run \
 — rm \
 — name postgres \
 -p 5432:5432 \
 -e POSTGRES_USER=postgres \
 -e POSTGRES_PASSWORD=postgres \
 -e POSTGRES_DB=postgres \
 -d postgres

Let’s test accessing the db shell

$ docker exec -it postgres psql -U postgres

You can exit the shell by

postgres=# \q

Great, now we have a working postgres db and installed sqlalchemy .

Let’s create a new file models.py and add the following:

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy import update as sqlalchemy_update
from sqlalchemy.future import select
from sqlalchemy.orm import relationship
class User(Base):
    __tablename__ = "users"
id = Column(Integer, primary_key=True, autoincrement=True)
    full_name = Column(String)
    posts = relationship("Post")
    # required in order to acess columns with server defaults
    # or SQL expression defaults, subsequent to a flush, without
    # triggering an expired load
    __mapper_args__ = {"eager_defaults": True}
def __repr__(self):
        return (
            f"<{self.__class__.__name__}("
            f"id={self.id}, "
            f"full_name={self.full_name}, "
            f"posts={self.posts}, "
            f")>"
        )