---
title: "升級 SQLAlchemy 2.0 之路 - Kenji Pa"
tags: PyConTW2023, 2023-organize, 2023-共筆
---

# 升級 SQLAlchemy 2.0 之路 - Kenji Pa

{%hackmd H6-2BguNT8iE7ZUrnoG1Tg %}

<iframe src=https://app.sli.do/event/dXxw2n1GKq2sYsC6a2frua height=450 width=100%></iframe>

> Collaborative writing start from below
> 從這裡開始共筆

Basic stack: marshmallow + Werkzeug + SQLAlchemy

migration to 2.0 is too long

## Why bother upgrating ?

- Performance
- Nice to have... but not primany reason
- Maintainability --> Yes, risk management
- 1.x will be deprecated eventually (main reason)
- Better type checking support looks nice

## Goals

- Minimize impacts to the application
- Application behaviour unaltered
- To ensure we can reason it on code-level:
- All changes must be lexically 1-to-1 mapped
- Things should always work before and after a commit (safely revertible)
- Do not affect team development
- migration should not slow down team development velocity
- When possible, always migrate iteratively
- **Non-goals: Refactor / bug fixes**

## [The 9 steps of migration](https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#x-2-x-migration-overview)

### RemovedIn20Warnings
1. Only emit when code are run => You need to have tests!
2. Interestingly, there is no consolidated list of these removed features
- Search for warn_deprecated_20 in 1.4 branch src
3. Tests *never* cover everything:
a. At one point we got all tests passed, but when we run the application, it's just dead

### Use the future flag on Engine & Session

- Implemented configuration to control these flags:
- So in case abnormal happens we could revert it back very quickly
- Tuned out extremely smooth, likely because
- We have request-scoped & task-scoped session, app code rarely needs to manage sessions (commit/create sessions)

### Convert old query API to new ones
- 2.0: separated query construction and execution

users = (
    session.query(User).filter().all()
)

stmt = select(User)
users = session.execute(stmt)

- Not all queries are selects

### Abstraction is for the weak

- Pagenation
- Offset, limit, order by, total page, etc
- We happen to have a centralized place for all these queries
- Performance reason
- Reporting / batch data processing where *all* is too slow

OLD:

session.query(User).filter_by(...).first()

NEW:

session.scalars(
    select(User).
    filter(...).
    limit(1)
).first()

## After 2.0

- New ORM declarative style
- Explore type checking

Below is the part that speaker updated the talk/tutorial after speech
講者於演講後有更新或勘誤投影片的部份