sqlalchemy-v1-to-v2
Migrates SQLAlchemy 1.x codebases to the 2.0 API surface.
Covers the full migration checklist from the official SQLAlchemy 2.0 migration guide: import moves, query API, execution model, ORM mapping, and deprecated keyword removal.
What it does
Fully automated rewrites
1. select([...]) → select(...)
| Before | After |
|---|---|
select([User.id, User.name]) | select(User.id, User.name) |
select([column("id")]) | select(column("id")) |
2. session.query() chains → session.execute(select())
| Before | After |
|---|---|
session.query(User).filter(User.active).all() | session.execute(select(User).where(User.active)).scalars().all() |
session.query(User).get(42) | session.get(User, 42) |
session.query(User).count() | session.execute(select(func.count()).select_from(User)).scalar() |
session.query(User).filter_by(name="x").first() | session.execute(select(User).filter_by(name="x")).scalars().first() |
session.query(User).order_by(User.id).limit(10).all() | session.execute(select(User).order_by(User.id).limit(10)).scalars().all() |
Supported middle methods: .filter(), .filter_by(), .order_by(), .limit(), .offset(), .join(), .distinct().
Supported terminal methods: .all(), .first(), .one(), .one_or_none(), .scalar(), .get(), .count().
3. conn.execute("SQL") → conn.execute(text("SQL"))
| Before | After |
|---|---|
conn.execute("SELECT 1") | conn.execute(text("SELECT 1")) |
engine.execute("INSERT ...") | engine.execute(text("INSERT ...")) |
4. Column(...) → mapped_column(...) in declarative classes
| Before | After |
|---|---|
id = Column(Integer, primary_key=True) | id = mapped_column(Integer, primary_key=True) |
name = Column(String(50), nullable=False) | name = mapped_column(String(50), nullable=False) |
Only transforms Column inside class bodies inheriting from Base. Column inside Table() definitions is left unchanged.
5. DML inline where: table.update(cond) → table.update().where(cond)
| Before | After |
|---|---|
users.update(users.c.id == 1).values(name="x") | users.update().where(users.c.id == 1).values(name="x") |
users.delete(users.c.active == False) | users.delete().where(users.c.active == False) |
6. Import rewriting (ext.declarative → orm)
| Before | After |
|---|---|
from sqlalchemy.ext.declarative import declarative_base | from sqlalchemy.orm import declarative_base |
from sqlalchemy.ext.declarative import declared_attr | from sqlalchemy.orm import declared_attr |
from sqlalchemy.ext.declarative import as_declarative | from sqlalchemy.orm import as_declarative |
Also manages adding new imports (text, select, func, mapped_column) and removing unused ones (Column) as needed by other transforms.
7. case([(cond, val), ...]) → case((cond, val), ...)
| Before | After |
|---|---|
case([(User.role == "admin", 1), (User.role == "user", 2)], else_=0) | case((User.role == "admin", 1), (User.role == "user", 2), else_=0) |
Auto-fix + TODO flag
These transforms apply a code change and insert a # TODO(sqlalchemy-v2): comment for manual review.
8. MetaData(bind=engine) → remove bind=
| Before | After |
|---|---|
meta = MetaData(bind=engine) | # TODO(sqlalchemy-v2): ...<br>meta = MetaData() |
meta = MetaData(bind=engine, schema="public") | # TODO(sqlalchemy-v2): ...<br>meta = MetaData(schema="public") |
Bound metadata is removed in 2.0. Connections must be passed explicitly.
9. Session(autocommit=True) → remove autocommit=
| Before | After |
|---|---|
Session(autocommit=True) | # TODO(sqlalchemy-v2): ...<br>Session() |
sessionmaker(autocommit=True, bind=engine) | # TODO(sqlalchemy-v2): ...<br>sessionmaker(bind=engine) |
Autocommit mode is removed in 2.0. Use AUTOCOMMIT isolation level or explicit begin() blocks.
10. lazy="dynamic" → lazy="write_only"
| Before | After |
|---|---|
relationship("Address", lazy="dynamic") | relationship("Address", lazy="write_only") |
11. session.begin(subtransactions=True) → remove kwarg
| Before | After |
|---|---|
session.begin(subtransactions=True) | # TODO(sqlalchemy-v2): ...<br>session.begin() |
Subtransactions are removed. Restructure to a single begin/commit block.
12. Table(autoload=True) → Table(autoload_with=engine)
| Before | After |
|---|---|
Table("users", meta, autoload=True) | # TODO(sqlalchemy-v2): ...<br>Table("users", meta, autoload_with=engine) |
autoload_with=engine is the required replacement. The engine placeholder must be replaced with the actual engine reference.
TODO flag only
These patterns require structural refactoring that cannot be safely automated. The codemod inserts a # TODO(sqlalchemy-v2): comment above each occurrence.
13. engine.execute() — removed
14. declarative_base() — superseded
15. mapper() — removed
16. from_self() / select_entity_from() — removed
17. String-based loader / join arguments
Covers: joinedload, subqueryload, selectinload, lazyload, immediateload, noload, raiseload, contains_eager, defaultload, with_parent, and .join() with string args.
18. execution_options(autocommit=True) — removed
Patterns intentionally left unchanged (safety)
| Pattern | Why it's skipped |
|---|---|
session.query().join(User, User.id == Addr.user_id) | .join() with explicit ON clause — already v2-compatible |
Column(...) inside Table(...) definitions | Column is still valid in Core table definitions |
session.query() chains with unsupported methods | .group_by(), .having(), .union(), etc. — too complex to safely rewrite |
registry.mapper() | Method call on registry — not the standalone mapper() |
Already-v2 code (select(User), mapped_column(...)) | No-op — safe to run repeatedly |
Usage
From the registry
Dry run (preview changes)
Manual follow-up after running
- Search for
# TODO(sqlalchemy-v2):— address each flagged location manually engineplaceholder — replaceautoload_with=enginewith your actual engine variableDeclarativeBase— migrate fromdeclarative_base()toclass Base(DeclarativeBase): pass- Session / connection lifecycle — adopt the
with Session(engine) as session:/with engine.connect() as conn:context manager pattern - Alembic migrations — these are excluded by default; update migration scripts separately if needed
- Type annotations — consider adding
Mapped[T]type annotations to mapped columns - Run your test suite — verify all queries produce the same results
Metrics
| Metric | Description |
|---|---|
select-list-to-positional | select([...]) → select(...) |
query-chain-to-select | session.query() chains rewritten |
execute-raw-string | Raw string execute() wrapped in text() |
column-to-mapped-column | Column → mapped_column in classes |
dml-inline-where | DML inline condition → .where() |
import-rewrite | ext.declarative imports moved to orm |
case-list-to-positional | case([...]) → case(...) |
metadata-bind-removal | MetaData(bind=) kwarg removed |
session-autocommit-removal | Session(autocommit=) kwarg removed |
lazy-dynamic-to-write-only | lazy="dynamic" → lazy="write_only" |
subtransactions-removal | subtransactions=True kwarg removed |
autoload-to-autoload-with | autoload=True → autoload_with=engine |
flag-engine-execute | engine.execute() flagged |
flag-declarative-base | declarative_base() flagged |
flag-mapper | mapper() flagged |
flag-from-self | from_self() / select_entity_from() flagged |
flag-string-loader | String-based loader/join args flagged |
flag-execution-options | execution_options(autocommit=True) flagged |
License
MIT