SQLAlchemy 2.0 Tutorial: Build a Task Manager from Scratch

If you've worked with SQLAlchemy before 2.0, you know the old syntax — declarative_base(), untyped columns, relationships that worked but didn't give you any real type safety. SQLAlchemy 2.0 changes that with a cleaner, fully typed approach to defining models.

In this tutorial, we'll build a simple Task Manager from scratch — covering everything from setting up your database connection to defining models with relationships, so you'll have a complete, working project by the end, not just isolated code snippets.

What We'll Build

A task manager with three related tables:

• Users — the people using the app
• Projects — each user can have multiple projects
• Tasks — each project can have multiple tasks

By the end, you'll understand how to set up an engine, define typed models, create relationships, and persist them to a real database — using both SQLite (for quick local development) and PostgreSQL (for production).

Table of Contents

1. Setting Up the Connection
2. PostgreSQL Setup, Database Creation, and Driver Installation
3. The Declarative Base Class
4. Defining Your First Model
5. Working with Columns and Data Types
6. Adding Relationships
7. Putting It All Together
8. Creating the Tables
9. Inserting and Querying Data

Project Setup

Before we write any code, create a new folder for this project and add a single file inside it:

mkdir task_manager
cd task_manager
touch models.py

We'll build everything in this one file, models.py — every code block in this tutorial tells you exactly where it goes, so just keep adding to this same file as we go.

1. Setting Up the Connection

Before defining any models, we need an engine — this is what actually manages the connection to your database. Add this in the models.py

from sqlalchemy import create_engine
 
engine = create_engine("sqlite:///tasks.db", echo=True)

• create_engine() takes a connection string. We're using SQLite here since it needs no separate database server — perfect for following along locally.
• echo=True prints every SQL statement SQLAlchemy generates behind the scenes, which is genuinely useful while you're still learning what's happening under the hood. Turn this off in production.

This is enough to get started locally. If you want to use PostgreSQL instead (recommended for any real project), continue to the next section.

2. PostgreSQL Setup, Database Creation, and Driver Installation

SQLite is great for learning, but most real-world projects use PostgreSQL. SQLAlchemy doesn't talk to PostgreSQL directly — it needs a driver (also called a DBAPI) to handle the actual communication.

Installing the driver

Run this in your terminal:

For synchronous code (what we'll use in this tutorial):

pip install psycopg2-binary

For async code (if you're using FastAPI with async routes):

pip install asyncpg

Creating the Database in PostgreSQL First

Before SQLAlchemy can connect to anything, the database itself needs to exist. PostgreSQL doesn't create one for you automatically.

Open a terminal and log into PostgreSQL using psql:

psql -U postgres

This logs you in as the default postgres superuser. You'll be prompted for the password you set during PostgreSQL installation.

Once you're inside the psql shell, create the database:

CREATE DATABASE taskmanager;

Verify it was created:

\l

This lists all databases — you should see taskmanager in the list.

If you also want a dedicated user instead of using postgres directly (recommended for real projects):

CREATE USER taskmanager_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE taskmanager TO taskmanager_user;

Exit psql when you're done:

\q

That's it — the database now exists and is ready for SQLAlchemy to connect to.

Connecting to PostgreSQL

In model.py, go back to the connection code from Section 1 and swap it for this instead (don't keep both):

from sqlalchemy import create_engine
 
engine = create_engine(
    "postgresql+psycopg2://username:password@localhost:5432/taskmanager",
    echo=True
)

Breaking down the connection string:

• postgresql+psycopg2 — tells SQLAlchemy to use PostgreSQL with the psycopg2 driver
• username:password — the user and password you just created (or postgres and your superuser password)
• localhost:5432 — host and port (5432 is PostgreSQL's default port)
• taskmanager — the database name you created above

Async version (if needed)

If you're connecting SQLAlchemy to a FastAPI app specifically, you'll likely want the async setup below — check out my FastAPI + SQLAlchemy tutorial for the full async session setup and dependency injection pattern.

from sqlalchemy.ext.asyncio import create_async_engine
 
engine = create_async_engine(
    "postgresql+asyncpg://username:password@localhost:5432/taskmanager",
    echo=True
)

For the rest of this tutorial, we'll stick with the synchronous SQLite setup to keep things simple — but everything you learn applies directly to PostgreSQL too, just swap the engine line.

3. The Declarative Base Class

In SQLAlchemy 2.0, every model inherits from a base class. This is how SQLAlchemy keeps track of all your tables and their structure. Now add the following code inmodels.py

from sqlalchemy.orm import DeclarativeBase
 
class Base(DeclarativeBase):
    pass

That's it. Base is now the foundation every model in our app will inherit from. This replaces the older declarative_base() function call from SQLAlchemy 1.x.

4. Defining Your First Model

Let's define our User model — the simplest of our three tables.

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

A few things to notice here:

• __tablename__ sets the actual table name in the database
• Mapped[int] and Mapped[str] are type hints that tell SQLAlchemy what Python type — and by extension, what SQL column type — to use
• mapped_column() is used when you need to specify extra column behavior, like primary_key=True

This typed style is the biggest change in 2.0 — your editor and type checker can now understand your models properly, catching mistakes before you even run the code.

5. Working with Columns and Data Types

Update the User class in models.py — replace the version from Section 4 with this expanded one:

Let's expand User with more realistic fields, and introduce common data types.

from datetime import datetime
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] =

Common data type mappings you'll use constantly:

Notice that for int, bool, datetime, and float, SQLAlchemy can usually infer the SQL column type directly from the Python type hint — you only need mapped_column() with an explicit type (like String(50)) when the default inference isn't enough, such as setting a max length.

Making a field optional

This is just an example pattern for now — we'll use it for real in Section 6 when we add Project.description.

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

Optional[str] tells SQLAlchemy this column can be NULL in the database.

6. Adding Relationships

Update models.py again — we're adding a projects field to the existing User class, and introducing the new Project class below it:

Now let's define Project and Task, and connect all three models together.

One-to-Many: User → Projects

A user can have multiple projects, but each project belongs to exactly one user.

from sqlalchemy import ForeignKey
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

Breaking this down:

• ForeignKey("users.id") on Project.user_id is what physically links the two tables in the database
• relationship() is what lets you navigate between objects in Python — some_user.projects gives you a list of that user's projects
• back_populates keeps both sides of the relationship in sync — when you set project.owner, user.projects automatically reflects that too

One-to-Many: Project → Tasks

Update models.py by adding the tasks field to Project (replacing the version from above), and add the new Task class below it:

Same pattern, one level deeper.

class Project(Base):
    __tablename__ = "projects"
 
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"

7. Putting It All Together

Here's the complete, final version — connection setup, base class, and all three models in one file. You can copy and paste this directly into a models.py file and run it as-is.

from datetime import datetime
from typing import Optional
from sqlalchemy import String, ForeignKey, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
 
 
# ── Connection ──
# SQLite (no setup needed, good for local testing)
engine = create_engine("sqlite:///tasks.db", echo=True)
 
# PostgreSQL (uncomment and use this instead — make sure you've created

Run it with:

python models.py

If you're using SQLite, this will create a tasks.db file in your project folder with all three tables ready to go. If you're using PostgreSQL, make sure you've created the taskmanager database first (see Section 2) and uncommented the PostgreSQL engine line.

8. Creating the Tables

Base.metadata.create_all(engine)

This single line reads every model that inherits from Base and issues the CREATE TABLE statements for each one. Run this once when setting up your database — running it again later is safe, since it only creates tables that don't already exist.

This is exactly what happens when you run:

python models.py

If you're using SQLite, this creates a tasks.db file in your project folder with all three tables ready to go. If you're using PostgreSQL, make sure you've created the taskmanager database first (Section 2) and swapped in the PostgreSQL engine line.

9. Inserting and Querying Data

Create a new file in the same folder, called run.py — keep this separate from models.py, since models.py is just for defining the table structure, while run.py is where we actually use them:

touch run.py

Now let's actually use these models. SQLAlchemy 2.0 uses a Session to manage database operations.

Inserting data

Add this to run.py:

from models import User, Project, Task, engine
from sqlalchemy.orm import Session
 
with Session(engine) as session:
    new_user = User(username="usama", email="usama@example.com")
    new_project = Project(name="TraceFlow Blog", owner=new_user)

Notice we only call session.add(new_user) — because of the relationships we defined, SQLAlchemy automatically knows to also save new_project and new_task, since they're connected through owner and project.

Querying data

Add this to the same run.py file, below the insert code:

from sqlalchemy import select
 
with Session(engine) as session:
    stmt = select(User).where(User.username == "usama")
    user = session.scalar(stmt)
 
    print(user.username)
    for project in user.projects:
        print(f"  Project: {project.name

This is the new 2.0 query style — using select() instead of the older session.query() pattern. It's more explicit and works consistently whether you're using synchronous or async sessions.

Run your run.py file to see it in action:

python run.py

Note: Make sure you've run python models.py first (Section 8) to actually create the tables, before running run.py.

Wrapping Up

You now have a complete, working Task Manager built with SQLAlchemy 2.0 — a connection setup for both SQLite and PostgreSQL, typed models with proper data types, relationships connecting all three tables, and the code to actually create tables, insert data, and query it back out.

The typed Mapped[] style is the core shift in 2.0, and once it clicks, it makes your models far easier to work with, since your editor and type checker now understand your database structure just as well as your application code.

If you're ready to plug these models into a real API, check out my FastAPI + SQLAlchemy tutorial for setting up async sessions, dependency injection, and building actual CRUD endpoints on top of what you just built here.