get_or_create with SQLAlchemy 2's ORM

Published 33 minutes ago.

I've long loved Django's get_or_create ORM function. If you're not familiar, typical usage looks something like this:

from django.contrib.auth.models import User

user, created = User.objects.get_or_create(
    defaults={"email": ""}

The above code is pretty self-explanatory: if a user with the username johndoe exists, return it, otherwise create it with an email of It's concise and beautiful.

I've had to use SQLAlchemy 2 recently and I was surprised to find that it didn't have the same and examples I found, only worked for SQLAlchemy 1.x.

Here's how you can implement this function with SQLAlchemy 2:

import typing as T

import sqlalchemy as sa
from sqlalchemy.orm import Session
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

class Base(DeclarativeBase):

    __abstract__ = True

    def get_or_create(
        cls, session: Session, defaults=None, commit=True, **kwargs
    ) -> T.Tuple[T.Self, bool]:
        """Django-inspired get_or_create."""
        predicates = [getattr(cls, k) == v for k, v in kwargs.items()]
        instance = session.scalar(*predicates))
        if instance:
            return instance, False

        defaults = defaults or {}
        instance_kwargs = kwargs | defaults
        instance = cls(**instance_kwargs)
        if commit:

        return instance, True

class User(Base):

    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    first_name: Mapped[str] = mapped_column(sa.Text, nullable=False)
    last_name: Mapped[str] = mapped_column(sa.Text, nullable=False)
    email: Mapped[str] = mapped_column(sa.Text, nullable=False)

engine = sa.create_engine("...")
make_session = sessionmaker(bind=engine)
with make_session() as session:
    user, created = User.get_or_create(
          "first_name": "John",
          "last_name": "Doe"
    # created will be True, and the change will be committed to the database

Boo ya.

Mike Sukmanowsky

๐Ÿ‘‹๐Ÿป Hi! I'm Mike, the author of this post and others.

If you'd like to get in touch, you can reach me on Twitter or LinkedIn.