There’s been a recent flurry of articles about web components, with advice on how to shape them as extensions of HTML. I decided to dig in, and see how these ‘HTML web components’ could become a part of my own workflow. Despite a few rough edges, I’m excited to see…
If you’ve heard about FastAPI, a modern and fast web framework for building APIs with Python, you might be wondering how it compares to Django, the most popular and mature web framework for Python. In this series, I will answer this question by comparing various aspects and features of Django and FastAPI, based on our recent experience converting an internal project from Django to FastAPI.
- FastAPI Path Operations for Django Developers
- SQLAlchemy for Django Developers (this article)
- Testing a FastAPI Application (coming soon)
- How To Use FastAPI Dependency Injection Everywhere (coming soon)
Both Django’s ORM (Object Relational Mapper) and SQLAlchemy are libraries for interacting with relational databases. Django’s ORM is tightly coupled with the Django framework, while SQLAlchemy is a standalone library that can be used with any Python application. I heard about it many years ago, but never had the need to use it because Django’s ORM was good enough for me (to the point that I know surprisingly little SQL). But when I started using FastAPI, SQLAlchemy was the recommended library for interacting with the database. Let’s follow SQLAlchemy’s Quick Start guide to create a simple database while learning about the differences between SQLAlchemy and Django’s ORM.
The first thing I noticed when I started using SQLAlchemy was how similar it is
to Django’s ORM when it comes to defining models as classes that represent
database tables. Here is an example of a
User model in Django:
from django.db import models class User(models.Model): name = models.CharField(max_length=100) fullname = models.TextField()
And here is the same model in SQLAlchemy:
from sqlalchemy import String from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase class User(DeclarativeBase): __tablename__ = "user_account" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(30)) fullname: Mapped[Optional[str]]
Note: this declarative style for model definition is relatively new,
superseding the old
declarative_base function in SQLAlchemy 2.0. You might
still encounter the old style in some codebases.
These two models are similar in that once they get information out of the
database, table columns are accessible as attributes of the model instances such
user.fullname. In the same way, modifying the attributes of
a model instance like
user.name = "Jane" will modify the corresponding
database record when the object is “saved” (which is done differently in
SQLAlchemy as we will see later).
In contrast, these models differ in that SQLAlchemy relies more heavily on type
hints to define the model. In my opinion this results in a more natural and
robust developer experience. For example, the
fullname field only requires the
Mapped[Optional[str]] annotation to be mapped to a nullable text column.
Fields that require further customization that can’t be expressed in a type
hint, like setting a character limit or marking a column as primary key, can use
mapped_column function as seen in the
name fields. In all
cases, by using the
Mapped annotation type checkers have everything they
need to correctly type the model attributes.
SQLAlchemy also supports defining models with the imperative syntax, which is less similar to Django’s ORM. I personally prefer the declarative syntax and find it more readable and intuitive.
In Django, every model comes with a manager exposed as the
This means that as long as you get a reference to the model class, you can
access the manager and perform queries. For example, for the
User model, you
can get all users with
In SQLAlchemy, you need to create an engine and session first before you can perform any queries. You can think of the engine as a factory that provides us with database connections, and sessions map roughly to these individual connections.
from sqlalchemy import create_engine from sqlalchemy.orm import Session engine = create_engine("sqlite:///db.sqlite3") with Session(engine) as session: users = session.execute("SELECT * FROM users").all()
Notice we are using raw SQL here instead of the ORM. We will get to the ORM in the next section.
You don’t need to use a context manager to create a session, but it is
recommended so that the session is automatically closed when you are done with
it. This can get annoying when you are writing FastAPI path operations because
the code for each operation will be inside a context manager. Luckily, you can
create a FastAPI dependency that
yields the session from the context manager
and use it by declaring it as an argument in your path operation.
from fastapi import Depends, FastAPI from sqlalchemy import create_engine from sqlalchemy.orm import Session, sessionmaker engine = create_engine("sqlite:///db.sqlite3") SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) app = FastAPI() def get_session() -> Session: with SessionLocal() as session: yield session @app.get("/users", response_model=...) def get_users(session: Session = Depends(get_session)): return session.execute("SELECT * FROM users").all()
In Django, you can query a model with keyword arguments. For example, if you
want to get a single user with the name “John”, you can do
User.objects.get(name="John"). In SQLAlchemy, you need to use class attributes
from sqlalchemy import select john = session.scalars(select(User).where(User.name == "John")).one()
where method is chained to the
select function and achieves the same as
get methods in Django. However, instead of keyword arguments,
we use an actual comparison of the
name attribute with the string
This caught me off guard at first but I’ve come to appreciate it because I get
help from my text editor when building queries instead of trying to guess the
names of keyword arguments.
More complex queries are also different. Django encourages you to use a double
underscore syntax for more specific lookups. For example, to get all users with
IDs 1, 2, or 3, you use
User.objects.filter(id__in=[1, 2, 3]). SQLAlchemy
achieves this by exposing custom methods as part of the class attributes:
users = session.scalars(select(User).where(User.id.in_([1, 2, 3]))).all()
The trailing underscore in
in_() is needed because
in is a reserved word in
Python, not because of anything specific to SQLAlchemy.
There’s a whole host of interesting methods you can use with model attributes as
explained in the
select function accepts entire model classes or individual columns as
arguments. For example, to get only the
name column, you can do
Finally, notice that we call
all() instead of using separate
filter() methods. This is because the
scalars method returns a
ScalarResult that allows us to chose how to get the results.
In Django, given a model instance
user, you can save it to the database with
user.save(). In SQLAlchemy, you need to commit the session instead:
This actually makes it easier to bundle together multiple “saves”, even if they are for different models, into a single transaction. You can do this by adding multiple objects to the session and then committing the session.
user1 = User(...) user2 = User(...) address1 = Address(...) session.add_all([user1, user2, address1]) session.commit()
In Django terms, the session is like a transaction that you can commit to when you’re ready, and the notion of saving individual model instances by calling one of their methods is not present.
Let’s add an
Address and let each user have multiple addresses. In Django, you
can do this with a
# No changes required to the User model class Address(models.Model): user = models.ForeignKey(User, on_delete=models.CASCADE) email_address = models.TextField()
In SQLAlchemy, you need to define the relationship explicitly:
from sqlalchemy import ForeignKey, String from sqlalchemy.orm import relationship, Mapped, mapped_column, DeclarativeBase class User(DeclarativeBase): # Previous attributes omitted for brevity addresses: Mapped[list["Address"]] = relationship( back_populates="user", cascade="all, delete-orphan" ) class Address(DeclarativeBase): __tablename__ = "address" id: Mapped[int] = mapped_column(primary_key=True) email_address: Mapped[str] user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id")) user: Mapped["User"] = relationship(back_populates="addresses")
Notice you need to define both the
user_id column and the
Address model, something that Django does automatically. Additionally,
you also need to define the “other side” of the relationship as
Once your models are in place you can query the
Address model and join it with
from sqlalchemy import select stmt = ( select(Address) .join(Address.user) .where(User.name == "sandy") ) sandy_addresses = session.scalars(stmt).all()
There is no double underscore syntax like in Django, and no “related managers”
that allow you to get related objects from a given model instance. Instead we
need to use a
join to bring in the
User model and then filter on the
One clear advantage of SQLAlchemy is that you can instantiate models and relate them before they are saved, and then commit them all at once:
spongebob = User( name="spongebob", fullname="SpongeBob SquarePants", addresses=[Address(email_address="firstname.lastname@example.org")], ) sandy = User( name="sandy", fullname="Sandy Cheeks", addresses=[ Address(email_address="email@example.com"), Address(email_address="firstname.lastname@example.org"), ], ) patrick = User(name="patrick", fullname="Patrick Star") session.add_all([spongebob, sandy, patrick]) session.commit()
In Django you would need to save all the users first, then save the addresses
user attribute set to the corresponding user. Here we can nest them
naturally and SQLAlchemy will take care of the rest.
Django comes with a built-in migration system that allows you to create and apply migrations with a single command. This is definitely one of the most important features of Django and I was surprised to find out that SQLAlchemy does not have a built-in alternative. Instead, they maintain a separate package called Alembic to manage migrations (which have their name changed to “revisions”).
We won’t go into details here, but the basic substitutions are:
alembic revision --autogenerate
alembic upgrade head
./manage.py migrate app <migration number>becomes
alembic upgrade <revision hash>if going forward, or
alembic downgrade <revision hash>if going back
SQLAlchemy and Alembic don’t have the concept of “apps” as standalone elements
with their own models and migrations. Instead, they use a single
alembic/versions directory that contains all the revisions for all tables, and
autogenerating revisions will scan the entire database for changes.
To date I don’t know how to manage migrations in databases that have models defined by third-party packages. This is in contrast with Django where third-parties usually ship their own migration history to manage their tables independently from user-defined models.
SQLAlchemy is a powerful library. We have only scratched the surface of what it can do. I think it’s as powerful as Django’s ORM, but it’s definitely not as streamlined and putting the pieces together requires time. There also seems to be a lot of old, pre-2.0 information out there that can be confusing. All in all, I think it’s a library worth learning and keeping in your tool belt, especially if you are using FastAPI.
- Article post type
- Article post type
Do you want to make your website or web app more intuitive for the people who visit? If so, I’d like to introduce you to the world of Object-Oriented UX. As a UX designer at OddBird, using OOUX strategies to identify and avoid unintuitive objects has been key to…
- Article post type
If you’re weighing the performance optimization and device integration opportunities of a native mobile app against the broad reach and lower cost of a responsive web app – and can’t decide which is a higher priority for your digital product – don’t despair. A progressive web app may be just…