SqlAlchemy migration from 1.3 to 2.0

SqlAlchemy 2.0 has been in use for a long time and is significantly different from its previous versions. The code that was created several years ago needs to be migrated to the rules in versions 2.0 and above.

However, the original SqlAlchemy documentation for migrating to version 2.0 has an encyclopedic structure and can be intimidating for an average user. It also lacks more tutorial-like examples. In this article, I will try to show you a short example of what you need to do to update the code from version 1.3 to version 2.0 or higher.

What is SqlAlchemy

SQLAlchemy allows you to map your application’s classes and objects to a database. In other words, it is an object-relational mapper between Python code and a relational database. SQLAlchemy allows you to integrate SQL with Python code making it independent of the database used. If you need more information about this popular and complex framework, you can start from its documentation or watch/read many introductory material. However, these are usually for older versions which, as I wrote earlier, are significantly different from the newest one.

SqlAlchemy 1.3 code

I assume that you have a legacy code written for SqlAlchemy version 1.3 or earlier. The code creates a simple one-table database, adds two records and reads them. For this purpose, you declare a table users, which includes the user’s identifier id, name name and registration date denoted as first_seen column. As you want to map your relational database tables into classes in an object-oriented language, you wrap your table into a class called User. You can add your methods here or override the existing ones, e.g. I overwrote __repr__ method, which allows me to present the database record in a custom style when using the print statement. I present the whole code below.

Migration to SqlAlchemy 2.0 code

To upgrade the code to SqlAlchemy 2.0 style we need to:

1. Replace the declarative_base() function with a DeclarativeBase superclass. This class supersedes the use of the declarative_base() function and registry.generate_base() methods; the superclass approach integrates with PEP 484 tools without the use of plugins. See ORM Declarative Models for migration notes.
2. Instead of Column, in the declaration of a database table, we use now Mapped typehint and mapped_column function.
3. session.flush is no longer supported
4. Session use now Session.begin() or with Session() as session: structure.

The updated code is presented below:

Summary

In this post, I presented a migration procedure of code from SqlAlchemy 1.3 to SqlAlchemy 2.0. The presented example is the simplest possible, the database consist, of a single table without relations to other tables. However, as the documentation is quite large and complex on this topic, I have a feeling that a step-by-step tutorial is helpful to start with the code upgrade.