Case Study: AI-Powered SQLAlchemy Upgrade

Upgrading your tech stack is like cleaning out your closet. You know it needs to happen, but, ah, it’s such a headache.

‍

But what if an AI tool could handle the heavy lifting for you? Today we'll go through a case study on how Codegen’s AI can safely automate your migration from SQLAlchemy 1.6 to 2.0.

‍

Why Upgrade to 2.0?

SQLAlchemy 2.0 makes your code cleaner, less error-prone, and more maintainable. By upgrading, you get streamlined ORM functionality for query handling and APIs, AsyncIO support, type annotations, removal of implicit aliasing, and more.

‍

But manually upgrading to 2.0 is hard. Numerous APIs and ORM methods have been deprecated or changed. Overlooking even a small detail can cause messy runtime errors.

‍

How Codegen Automates the Migration

With our proprietary static analysis software, we index your entire codebase and build a map of how everything depends on each other.
Our AI refactors your code to use the latest APIs and patterns from SQLAlchemy 2.0. Thanks to the static analysis, we ensure that changes are comprehensive and won’t break either upstream or downstream dependencies.
We run your unit tests and lint rules against the changes to ensure there were no errors or regressions.

‍

Case Study: Codegen's Automated Migration

This Github repo shows a before-and-after comparison of a Codegen-assisted SQLAlchemy 2.0 migration for a simple book-tracking app. The migration guide breaks it down into a series of Codemods, such as this one that updates all files to adhere to SQLAlchemy2.0 class inheritance protocol:

‍

Fork this codemod on run on any codebase, and it'll generate a diff updating your SQLAlchemy class inheritance protocols.

‍

Here are some highlighted diffs from the complete migration.

‍

Removing implicit autocommit

By adhering to 2.0 syntax, we reduce the chances of unintended side effects and ensure the connection is properly closed

‍

Updating query syntax to use `where()` and `select()` constructs

Instead of query and filter, we use the updated select and where statements.

‍

Using new relationship loading techniques

With lazy="selectin", we invoke a SQLAlchemy 2.0 lazy-loading strategy that loads related objects using a single additional query with an IN clause, instead of executing multiple subqueries.

‍

Personally, I’m getting a migraine just thinking about making all these updates by hand, even for a simple CRUD app like this. If you’re in the same boat, request to try Codegen’s AI and static analysis tools!