🗄️ SQLAlchemy 2.0 & Alembic Migrations: Let's Learn from ZERO!

Once you understand SQLAlchemy 2.0 + Alembic, you understand how a real Python backend talks to PostgreSQL, stores data, retrieves records, updates them, and safely changes database structure as the app grows. 🚀

No assumptions here. You don't need to know ORMs, sessions, migrations, or database internals. We'll use examples from an Instagram-like SocialFeed app throughout.

1. 🖼️ The Big Picture

SocialFeed stores users, posts, comments, likes, follows, notifications and sessions. All of it must live permanently in PostgreSQL. But your backend is written in Python, while PostgreSQL understands SQL. How do they talk?

🐍 Python / FastAPI → your application code ▼ 🧱 SQLAlchemy 2.0 → converts Python database operations into SQL ▼ 🐘 PostgreSQL → stores and retrieves your data 🔄 Alembic → manages changes to your database structure over time 

• SQLAlchemy helps Python communicate with the database
• Alembic helps you modify the database structure safely over time

🧱 PART 1: SQLAlchemy Fundamentals

2. 🤔 What exactly is SQLAlchemy?

📌 SQLAlchemy is a Python library for interacting with relational databases.

It lets you create tables, insert, retrieve, update and delete records, manage connections and transactions, and work with relationships.

Raw SQL to fetch user 10:

SELECT * FROM users WHERE id = 10; 

With the SQLAlchemy ORM:

user = session.get(User, 10) 

• Raw SQL: you write SQL statements yourself
• SQLAlchemy ORM: you work with Python classes and objects

⚠️ SQLAlchemy doesn't eliminate SQL. It helps you work with SQL through Python.

3. 🪄 What is an ORM?

ORM = Object Relational Mapping.

• Object: Python objects
• Relational: relational databases with tables and relationships
• Mapping: connecting Python objects to database tables

The users table:

A Python class representing it:

class User: def __init__(self, id, username, email): self.id = id self.username = username self.email = email user = User(id=1, username="deepika", email="deepika@gmail.com") 

Python class User (id, username, email) ⇅ ORM mapping Database table users (id, username, email) One User object = one row in the users table 

SQLAlchemy ORM makes this mapping automatic. Instead of writing SQL for every operation you write Python:

user.username = "new_username" 

SQLAlchemy detects the change and generates an SQL UPDATE when the transaction is flushed.

4. ⚔️ SQLAlchemy Core vs SQLAlchemy ORM

🎯 A very important interview and practical concept.

SQLAlchemy offers two ways of working with a database. Both talk to the same database. The difference is how you express operations.

🅰️ SQLAlchemy Core

A SQL expression toolkit. You work with tables, columns, SQL expressions and statements, with no Python object per row.

from sqlalchemy import select statement = select(users_table) result = session.execute(statement) users = result.all() 

• select() creates a SELECT statement
• users_table represents the database table
• execute() runs it
• all() retrieves all matching rows

Inserting with Core:

from sqlalchemy import insert statement = insert(users_table).values( username="deepika", email="deepika@gmail.com", ) session.execute(statement) session.commit() 

🅱️ SQLAlchemy ORM

You work with Python classes representing tables:

statement = select(User) result = session.scalars(statement) users = result.all() for user in users: print(user.username) print(user.email) 

User is an ORM model, and SQLAlchemy returns User objects instead of raw column-oriented rows.

📊 Core vs ORM

💡 Neither is universally better, and you can combine them. A FastAPI app might use the ORM for normal user and post operations, and Core for bulk inserts or complex reports. Learn the ORM thoroughly first, while understanding Core enough to recognize its SQL expression API.

🏗️ PART 2: Declarative Models

5. 📐 What are Declarative Models?

In SQL you'd write:

CREATE TABLE users ( id INTEGER PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL ); 

SQLAlchemy lets you define the same table with a Python class. That's a declarative model.

📌 A declarative model is a Python class that describes a database table, including its columns, data types, constraints and relationships.

Step 1: install

pip install sqlalchemy 

Step 2: the imports

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

Step 3: create a Base class

class Base(DeclarativeBase): pass 

Base is the common parent class for your models. Every model inherits from it:

class User(Base): ... class Post(Base): ... class Comment(Base): ... 

Because they inherit from Base, SQLAlchemy knows they're ORM models and tracks their table definitions in shared metadata.

Step 4: create the User model

from sqlalchemy import String from sqlalchemy.orm import Mapped, mapped_column class User(Base): __tablename__ = "users" id: Mapped[int] = mapped_column(primary_key=True) username: Mapped[str] = mapped_column( String(50), nullable=False, ) email: Mapped[str] = mapped_column( String(100), nullable=False, unique=True, ) 

Line by line:

1. class User(Base): a Python class named User that inherits from Base
2. __tablename__ = "users": the actual database table name (class User ↔ table users)
3. id: Mapped[int]: id is a mapped Python attribute of type int
4. mapped_column(primary_key=True): defines the column and makes it the primary key, which uniquely identifies each row. No two rows can share one
5. nullable=False: the database doesn't allow NULL here
6. unique=True: duplicate values aren't allowed (two users can't share an email)

🎨 Common column types

📝 Python annotations and database types are related, but not exactly the same thing.

6. 🔍 Mapped and mapped_column in Depth

You'll see both everywhere in SQLAlchemy 2.0. Why do both exist?

username: Mapped[str] = mapped_column( String(50), nullable=False, ) 

Mapped[str] is the Python-side typing information. It tells developers what type to expect, tells IDEs what to suggest, lets static type checkers check, and tells SQLAlchemy the attribute is mapped.

mapped_column() describes the actual database column: its type, primary key, foreign key, nullability, unique constraints and defaults.

🧠 Mapped[str] = Python-side declaration. mapped_column() = database column configuration.

❓ Optional columns

A user's bio isn't compulsory:

from typing import Optional bio: Mapped[Optional[str]] = mapped_column( String(300), nullable=True, ) 

In modern Python you can write:

bio: Mapped[str | None] = mapped_column( String(300), nullable=True, ) 

The attribute can hold a string or None, and the database column allows NULL.

🎚️ Default values

is_active: Mapped[bool] = mapped_column( default=True, nullable=False, ) 

You can also configure a database-side default:

server_default="true" 

🔗 PART 3: Relationships Between Models

7. 🗝️ What are Foreign Keys?

A user creates multiple posts.

users:

posts:

user_id tells us who created the post. It's a foreign key referencing the id column of users:

from sqlalchemy import ForeignKey user_id: Mapped[int] = mapped_column( ForeignKey("users.id"), nullable=False, ) 

This creates a database-level reference and helps maintain referential integrity. For example, you normally can't insert a post with user_id=999 if no such user exists (assuming foreign key enforcement is on).

8. 🔄 What is relationship()?

A foreign key connects tables. But what if you want convenient access to related Python objects? That's relationship():

from sqlalchemy import ForeignKey, String from sqlalchemy.orm import Mapped, mapped_column, relationship class User(Base): __tablename__ = "users" id: Mapped[int] = mapped_column(primary_key=True) username: Mapped[str] = mapped_column(String(50)) posts: Mapped[list["Post"]] = relationship( back_populates="author", ) class Post(Base): __tablename__ = "posts" id: Mapped[int] = mapped_column(primary_key=True) content: Mapped[str] = mapped_column(String(500)) user_id: Mapped[int] = mapped_column(ForeignKey("users.id")) author: Mapped["User"] = relationship( back_populates="posts", ) 

• User.posts → a user can access their posts
• Post.author → a post can access its author

user = session.get(User, 1) print(user.username) for post in user.posts: print(post.content) post = session.get(Post, 101) print(post.author.username) 

One-to-many relationship User (id = 1) ──▶ Post 101 ──▶ Post 102 ──▶ Post 103 One User → many Posts. Each Post → one User. 

🧠 ForeignKey defines the database-level relationship. relationship() defines the convenient Python-level object relationship. You usually need both.

9. 🧩 Other Relationship Types

One-to-one: one user has one profile.

User 1 ───── Profile 1 

One-to-many: one user has many posts.

User 1 ───── Post 1 ├─── Post 2 └─── Post 3 

Many-to-many: many users like many posts.

User 1 ───┬── Post 1 └── Post 2 User 2 ───┬── Post 1 └── Post 3 

Many-to-many relationships generally use an association table. For SocialFeed, a likes table is a natural example:

🔌 PART 4: Engine and Database Connections

10. ⚙️ What is a SQLAlchemy Engine?

Before Python can execute queries, it needs a way to connect to PostgreSQL. SQLAlchemy uses an Engine, the central database connection manager. It knows which database to connect to, which driver to use, how to create connections, and how to manage the connection pool.

from sqlalchemy import create_engine engine = create_engine( "postgresql+psycopg://user:password@localhost/mydb" ) 

• postgresql → database
• psycopg → driver
• user / password → credentials
• localhost → host
• mydb → database name

📝 Creating the engine doesn't necessarily open a connection right away. Connections are generally obtained when needed.

🏊 What is a connection pool?

100 API requests each need the database. Would you create a brand-new connection every time? That's inefficient. SQLAlchemy uses connection pooling: a collection of reusable connections.

FastAPI requests: A · B · C ▼ SQLAlchemy Engine → manages reusable connections ▼ Connection 1 · Connection 2 · Connection 3 ▼ 🐘 PostgreSQL 

A connection is checked out when needed and returned to the pool when its transaction or usage ends. Pool settings include pool_size, max_overflow, pool_timeout and pool_recycle. You don't need to tune them yet. First understand why the pool exists.

🧑‍💻 PART 5: Session and AsyncSession

🎯 One of the MOST IMPORTANT sections!

11. 🗂️ What is a Session?

A user calls POST /posts. Your backend must connect to the database, insert the post, commit the transaction, and release resources.

📌 A Session is a workspace for interacting with the database through the ORM. It manages ORM objects, database operations, pending changes, transactions and the identity map.

🅰️ Creating a Session

from sqlalchemy.orm import Session, sessionmaker SessionLocal = sessionmaker( bind=engine, class_=Session, ) session = SessionLocal() 

sessionmaker is a factory for creating sessions.

🅱️ Adding a record

user = User( username="deepika", email="deepika@gmail.com", ) session.add(user) session.commit() 

1. Create a Python object. It exists only in Python memory and isn't necessarily in PostgreSQL yet
2. session.add(user). SQLAlchemy tracks the object as pending
3. session.commit(). SQLAlchemy flushes pending changes and commits the transaction. Now the database stores the record

🅲 What is flush()?

🚨 A VERY important distinction!

session.add(user) session.flush() 

SQLAlchemy sends the INSERT to the database within the current transaction, but does NOT commit:

• The SQL has been executed
• The transaction remains uncommitted
• Other transactions generally can't see the change
• You can still roll back

session.add(user) session.flush() print(user.id) # the generated ID is available! session.rollback() 

Why is flush() useful? Sometimes you need the generated ID before committing. For example, insert a user, get their ID, then create another record that references it, all in one transaction.

🅳 commit()

Commits the current transaction and makes changes permanent (subject to database durability).

🅴 rollback()

Something went wrong mid-way and you don't want a half-finished transaction:

session.rollback() 

🅵 close()

session.close() 

Closes the Session and releases its transactional resources. It doesn't destroy your connection pool.

📋 Remember these five methods

12. 🪪 What is the Identity Map?

A Session maintains an identity map: within a Session, the same database identity is represented by the same Python object.

user1 = session.get(User, 1) user2 = session.get(User, 1) print(user1 is user2) # typically True 

If the object is still in the identity map and hasn't expired, SQLAlchemy can return the same Python object. This keeps things consistent: you don't get two separate in-memory objects for the same row in the same Session.

📝 An identity map is not a general-purpose query cache. A new query with different filters may still execute SQL.

13. ⚡ What is AsyncSession?

FastAPI supports async endpoints:

@app.get("/users") async def get_users(): ... 

But using a synchronous database driver inside an async endpoint can block the event loop. SQLAlchemy therefore provides AsyncEngine, AsyncSession and asynchronous execution.

🛠️ Creating an AsyncEngine

pip install sqlalchemy asyncpg 

asyncpg is an asynchronous PostgreSQL driver.

from sqlalchemy.ext.asyncio import create_async_engine DATABASE_URL = "postgresql+asyncpg://user:password@localhost/mydb" engine = create_async_engine( DATABASE_URL, echo=True, ) 

• postgresql → database type
• asyncpg → async driver
• echo=True → prints generated SQL to the console (great for development; don't leave sensitive SQL parameters exposed in production logs)

🏭 Creating an AsyncSession factory

from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker AsyncSessionLocal = async_sessionmaker( bind=engine, class_=AsyncSession, expire_on_commit=False, ) 

expire_on_commit=False means ORM objects keep their already-loaded attribute values after a commit instead of automatically expiring. This matters in async code because accessing an expired attribute could require an implicit database load. It does not automatically load relationships or guarantee every attribute is available.

▶️ Using it

async with AsyncSessionLocal() as session: result = await session.execute(select(User)) users = result.scalars().all() 

Why await? Executing a query requires I/O, which takes time. await lets the coroutine suspend while waiting so other tasks can run on the event loop.

14. 🚪 What is async with?

async with AsyncSessionLocal() as session: ... 

This is an asynchronous context manager. It manages the Session's lifecycle, and the Session is closed when the block finishes. It's like:

session = AsyncSessionLocal() try: # Perform database operations pass finally: await session.close() 

⚠️ Closing a session does not automatically commit your changes. Commit explicitly when appropriate.

🧪 A complete AsyncSession example

async def create_user(): async with AsyncSessionLocal() as session: user = User( username="deepika", email="deepika@gmail.com", ) session.add(user) await session.commit() await session.refresh(user) return user 

refresh() reloads an ORM object's attributes from the database. The database may have generated values such as auto-generated IDs, server-side timestamps or database defaults, and refreshing retrieves them.

15. ⏳ What is expire_on_commit=False?

Source：https://dev.to/deepika_pusala/sqlalchemy-20-alembic-migrations-a-complete-beginners-guide-2oo2
My favorites

Related posts

Your own node or someone else's RPC? What a crypto payment watcher should trust

Every crypto checkout has a part nobody screenshots: the process that watches the chain and decides an invoice is paid.

0dev.to

Why Employees Still Rely on Spreadsheets Alongside Enterprise Software

A company invests in an enterprise application to make work faster, more accurate, and easier to manage. The software go

0dev.to

XML-RPC is going away in Odoo 22. Here's what the JSON-2 version looks like

If you have scripts or integrations that talk to Odoo over /xmlrpc/2 or /jsonrpc , Odoo 19 already logs a warning every

0dev.to

Designing a Construction Material Tracking System: RFID, BLE, Barcode, or LoRaWAN?

If you've built inventory software for a warehouse, you might assume a construction site is the same problem with more m

0dev.to

Data Residency and Sovereignty for Enterprise AI: SaaS, On-Prem, and Air-Gapped Deployments

When a team ships its first application backed by a large language model, security tends to live in the prompt. Someone

0dev.to

Upgrade an Image Workflow Only After Testing Its Weakest Brief

A practical way to compare Nano Banana 2.1 with an established Nano Banana 2 process. An image-model update is most inte

0dev.to