Defining Database Tables with SQLAlchemy ORM

1. ORM Models vs. Pydantic Schemas: A Tale of Two Models

Before we start writing code, it's crucial to understand a core architectural pattern in modern FastAPI applications: the separation of database models (what we're building today) and API schemas (which we'll cover next).

• SQLAlchemy ORM Models: Define the structure of your data in the database. They are concerned with tables, columns, data types (like Integer, String), and relationships (like ForeignKey).
• Pydantic Schemas: Define the shape of data for your API. They are concerned with the JSON data you receive in requests and send in responses, including data validation and serialization.

You might wonder why we need two different kinds of models. This video from Corey Schafer provides an excellent explanation of the reasoning behind this separation.

To summarize the key points:

• Separation of Concerns: Each layer has a different job. The database model knows about database-specific details (like indexes and relationships), while the API schema defines the public contract.
• Flexibility: You can change your internal database structure without breaking your public API. For instance, you can rename a database column but keep the API field name the same.
• Security: You can have fields in your database model (e.g., hashed_password) that you intentionally omit from your API schemas to avoid exposing sensitive data.

2. Defining Your Database Models

Now, let's get practical. We will create the models for a simple blogging application with two tables: users and posts. There will be a relationship between them: one user can have many posts.

Create a new file in your project directory called models.py. This is where we will define our SQLAlchemy ORM models.

The following video provides a step-by-step guide to creating these models using modern SQLAlchemy syntax, which pairs perfectly with your knowledge of Python's type hints.

Hands-On: Creating models.py

Based on the concepts from the video, let's create our models.py file. This code defines a User model and a Post model.

# models.py

from sqlalchemy import Integer, String, ForeignKey, DateTime
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func
from typing import List
from datetime import datetime




# Import the Base class from your database.py file
from database import Base

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    email: Mapped[str] = mapped_column(String(100), unique=True, index=True)
    hashed_password: Mapped[str] = mapped_column(String(255))




    # This creates the one-to-many relationship.
    # The 'back_populates' argument links it to the 'author' attribute in the Post model.
    posts: Mapped[List["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    title: Mapped[str] = mapped_column(String(100), index=True)
    content: Mapped[str] = mapped_column(String(500))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    



    # This column will store the ID of the user who created the post.
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))




    # This creates the many-to-one relationship back to the User.
    # The 'back_populates' argument links it to the 'posts' attribute in the User model.
    author: Mapped["User"] = relationship(back_populates="posts")

For a clear, text-based reference of this modern SQLAlchemy 2.0 syntax, you can review the "Defining Models" section of the following article. It presents a very similar structure.

3. Creating the Tables in the Database

Defining these classes in Python is just a blueprint. We haven't actually created the users and posts tables in our test.db file yet. To do this, we need to instruct SQLAlchemy to take our models (which are tracked by the Base class) and issue the corresponding CREATE TABLE statements to the database.

This is done with the Base.metadata.create_all() method.

A simple way to ensure the tables are created when our application starts is to call this method in our main.py file.

Open your main.py and modify it as follows:

# main.py

from fastapi import FastAPI



# Import the engine and Base from your database.py file
from database import engine
import models




# This line tells SQLAlchemy to create all the tables defined by your models
# It checks for the existence of tables first, so it won't try to recreate them on every startup
models.Base.metadata.create_all(bind=engine)

app = FastAPI()

@app.get("/")
def read_root():
    return {"Hello": "World"}




# ... (add other endpoints later)

Important: We import models so that the model classes are registered with SQLAlchemy's metadata before we call create_all. Then, we call create_all() and pass our engine to it, so it knows which database to create the tables in.

Now, restart your Uvicorn server. When the application starts, SQLAlchemy will connect to test.db, see that the users and posts tables don't exist, and create them for you.

4. Verifying the Result

How can you be sure it worked? You can inspect the database file directly. If you have the sqlite3 command-line tool installed, you can do this from your terminal:

1. Open the database file:

   sqlite3 test.db
   

2. Inside the SQLite prompt, list all tables:

   .tables
   

   You should see users and posts listed.
3. Inspect the schema of a table:

   .schema users
   

   This will show you the CREATE TABLE statement that SQLAlchemy generated, allowing you to verify the columns, types, and constraints.
4. Type .quit to exit.

Many code editors, like VS Code, also have extensions (e.g., "SQLite") that allow you to browse SQLite database files directly from the UI.

Conclusion

Fantastic work! You have now defined the data structure for your application and translated it into actual database tables. This is a massive step forward.

Key Takeaways:

• SQLAlchemy ORM models are Python classes that inherit from declarative_base() and map directly to database tables.
• The __tablename__ attribute specifies the table name in the database.
• Columns are defined as class attributes using Mapped and mapped_column, specifying types and constraints like primary_key=True and unique=True.
• Relationships between tables are defined using ForeignKey and relationship, which allows SQLAlchemy to handle joins for you.
• The command Base.metadata.create_all(bind=engine) is used to create the actual tables in the database based on your models.

In our next lesson, we will bridge the gap between our new database models and our API. We'll learn how to create distinct Pydantic schemas for data validation and data representation, which will allow us to safely accept data from API requests and control what data is sent back in responses.