The SQLAlchemy Session Class

What is the difference between SQLAlchemy ORM and SQLAlchemy Core?

In the first article in this series I introduced SQLAlchemy as one of the most complex and confusing ORMs that is on the market, that statement is only a half truth, mainly because SQLAlchemy is not simply an ORM.

SQLAlchemy is more of a pythonic database toolkit, it is in your hands (or your team lead’s hands) how you would like to use it.

SQLAlchemy Core is more of a hands-on kind of utility kit, it exposes more barebones methods for crafting your queries which often times can be used for query optimization and complex tasks that might be more straightforward to write and read for SQL veterans.

SQLAlchemy ORM on the other hand is great for creating reusable object to interact with when our queries and not as complex or when we have complex relationships in our database that we would like to simplify.

Which should I use?

The neat thing is that using both of these methods usually goes hand-in-hand! you do not have to consider only one of these methods for your application, in-fact I think that with a little bit of planning it’s actually much easier to use both.

You should not be spending time trying to optimize complex and slow queries using the ORM that you can easily craft with Core, just as you should not be writing basic selects with Core that you can easily avoid by using the ORM.

In my experience, since the ORM covers most of what traditional web applications need, it is usually my go to choice, once a more difficult or complex tasks presents themselves I consider whether I should use the ORM or craft my queries by my self using Core.

Lets dive into the Session

Now that we understand the difference between the ORM and Core we can dive into the Session class, which is very beneficial specifically to ORM usage.

Lets first take a second look at the Session class as code

from sqlalchemy.orm import Session
from sqlalchemy import create_engine

# We still need to create an engine
engine = create_engine('sqlite:///:memory:')

# Create a session
with Session(engine) as session:
  pass

When using the Session we typically use it in the scope of a context manager, it might be declared once when we enter our code (such as an API endpoint), or many different times as our program runs.

But what does the Session do?

Aside from establishing a connection to the database and managing it, the session does so much much, every-time we create a session (using the context manager), we are basically creating a container for the ORM, which tracks all of out usage of objects during the lifespan of the Session object.

One of the unique features of the Session, that might be a great cause for confusion if you are unaware of it, is the identity map.

The identity map

In each session we might make many CRUD operations on many different tables, the role of the identity map is to track each object that you pull from your database and map it using its object type and primary key, the next time you might wish to access this object, SQLAlchemy will first consult with the identity map and check it’s existence, in the case that it already exists the result will be fetched from the identity map instead of from the database.

This feature can be real confusing the first time you encounter it, mainly because if you are unaware of the existence of the identity map you might get extra frustrated about why you are not getting the data you are expecting for a specific object.

The Unit of Work pattern

The unit of work pattern is a behavioral pattern in software development, this pattern was defined by Martin Fowler as so:

This interesting pattern is yet another puzzle piece in scalability for large applications, especially those with heavy load, its goal is to minimize roundtrips to the database, if you sprinkle database queries all over your codebase that get executed instantly when they are requested, you instantly make your applications slower.

SQLAlchemy aims to reduce these roundtrips by tracking changes and operations on objects within a session instance, querying local object directly instead of the database, at the end of or during the session when you commit your changes all of these CRUD operations will be persisted in the database all at once.

Flush(ing) our changes

To some of you the term flush might sound as if we would like to dispose of our changes rather than keep them.

The session.flush() function is actually a powerful function that sends changes that exist in our session to the transaction, it does not commit but instead sends our operations to the transaction where other database operations such as triggers might be called as part of the changes that we made in the transaction so far.

The flush() function is always called as part of a commit() function, so you do not have to call both to persist your changes.

An example using flush()

Lets show how flush may be used to ensure that our session creates multiple related objects.

First we will create a couple of table for this demonstration

from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped
from sqlalchemy import ForeignKey, String

class BaseModel(DeclarativeBase):
  pass

class Wizards(BaseModel):
  __tablename__ = 'wizards'
  # Using autoincrement=True instructs the DB to increment the ID automatically
  id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
  name: Mapped[str] = mapped_column(String(45), nullable=False)
  age: Mapped[int]

class Familiars(BaseModel):
  __tablename__ = 'familiars'
  id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
  name: Mapped[str] = mapped_column(String(45), nullable=False)
  beast_type: Mapped[str] = mapped_column(String(150), nullable=False)
  alchemist_id: Mapped[int] = mapped_column(ForeignKey('wizards.id'), nullable=False)

BaseModel.metadata.create_all(engine)

Next lets write a simple Session where we can see flush() in action

with Session(engine) as session:
  # Create a new alchemist
  # Since we have autoincrement=True, we do not pass an ID by ourselves
  albus = Wizards(name='Albus Dumbledore', age=115)
  session.add(albus)
  print("---")
  print('Albus Dumbledore\'s id BEFORE the flush: ' + str(albus.id))
  session.flush()
  print("---")
  print('Albus Dumbledore\'s id AFTER the flush: ' + str(albus.id))
  
  # Create a new familiar
  # Note that we are provding our new object the Id for our Wizard, which is still
  # not persisted in the database, only in the transaction
  fawkes = Familiars(name='Fawkes', beast_type='Phoenix', alchemist_id=albus.id)
  session.add(fawkes)
  session.flush()
  # Commit the changes
  print('---')
  print('Fawkes\'s id: ' + str(fawkes.id))
  print('Fawkes\'s alchemist_id: ' + str(fawkes.alchemist_id))
  session.commit()

In this example we can clearly see how the flush() function changes the way we write our code when working within a session instance, from the logs we can see that before the flush, Dumbledore’s id was None while after the flush it became 1.

We were then able to use that id when creating a Familiar object that requires a Wizard id as a foreign key in the wizard_id column.

About autoflush

The curios amongst you may have already checked out the documentation page about the Session class, and might be wondering about a specific property that is available, specifically autoflush

When checking out the documentation about flushing it starts with

That is an important sentence to read, as SQLAlchemy states that for the most part you probably do not need to flush explicitly, instead, when using the default configuration of the Session (or the sessionmaker that we will soon discuss) autoflush is set to True by default.

But what does it do?

autoflush makes sure that certain operations will always issue a flush before they occur, these operations are:

1. Legacy Query statement
2. Modern SQLAlchemy ORM v2.0 session.execute statements (discussed later in the series)
3. Before a commit statement
4. Before a Session.begin_nested statement which starts a “nested” transaction (issues a SAVEPOINT SQL statement)

In our use case we create multiple objects that rely on one another, and in this case the flush statement is relevant, later when we get to SQLAlchemy ORM relationships we will see how we might avoid using the flush statement directly and rely on relationships to deal with this for us.