Creating Tables in FastAPI with SQLModel

What and Why of SQLModel:

Picture this: You're building your dream API, everything's going smooth with SQLAlchemy, and then you realize you need to create Pydantic models for request/response validation AND SQLAlchemy models for database operations. That's double work! I remember spending an entire weekend creating duplicate models for a project - one set for SQLAlchemy, another for Pydantic. I felt like I was copy-pasting my life away! 😅

Then SQLModel came along and said, "Hold my beer!" ⚡

What's SQLModel Anyway?

(I believe, It's an idea that popped to Sebastián while watching Dragon balls)

SQLModel is like the superhero child of SQLAlchemy and Pydantic.

It lets you define your database models ONCE and use them for both database operations AND API request/response validation. No more duplicate models, no more headaches!

Building Our Base Model

I think every table we are going to create, some fields are going to be common. For instance every table will have a primary key id, created_at, updated_at field. Create a new file database/models/base.py:

from datetime import datetime
from sqlmodel import SQLModel, Field


class BaseModel(SQLModel):
    id: int = Field(default=None, primary_key=True)
    created_at: datetime = Field(default_factory=datetime.now)
    updated_at: datetime = Field(default_factory=datetime.now)

What's happening here?

We're inheriting from SQLModel instead of SQLAlchemy's declarative base
Field is doing double duty - it's both a Pydantic field AND an SQLAlchemy column!
default_factory=datetime.now means every time a new record is created, it'll automatically set the current timestamp
This BaseModel will be inherited by all our other models, giving them common fields

Creating Our User Model

This Model/Table will be used for authentication and authorization. More on that in next section. Let's create our User model in database/models/user.py:

from typing import List
from database.models.base import BaseModel
from sqlmodel import Field, Relationship


class User(BaseModel, table=True):
    username: str = Field(unique=True, index=True)
    email: str = Field(unique=True, index=True)
    full_name: str
    password: str
    
    blogs: List["Blog"] = Relationship(back_populates="user")

The magic ingredients:

table=True - This tells SQLModel "Hey, this is a database table, not just a Pydantic model!"
Field(unique=True, index=True) - Username and email will be unique across the table, and we're creating database indexes for faster queries
Relationship(back_populates="user") - This creates a one-to-many relationship. One user can have multiple blogs
Notice the quotes around "Blog"? That's a forward reference because Python hasn't seen the Blog class yet!

The Blog Model

Create database/models/blog.py:

from sqlmodel import Field, Relationship
from database.models.base import BaseModel


class Blog(BaseModel, table=True):
    title: str = Field(max_length=200)
    slug: str = Field(max_length=200, unique=True)
    content: str
    is_active: bool = Field(default=True)
    user_id: int = Field(foreign_key="user.id")
    
    # Relationship with User
    user: "User" = Relationship(back_populates="blogs")

Breaking it down:

max_length=200 - Blog titles can't exceed 200 characters
default=True - New blogs are active by default
foreign_key="user.id" - This creates a foreign key relationship to the user table
back_populates="blogs" - This completes the relationship circle we started in the User model

Here's where SQLModel shows its true colors. The relationship between User and Blog is bidirectional:

From User side: user.blogs gives you all blogs by that user
From Blog side: blog.user gives you the user who wrote that blog

(Unpopular opinion of Mine: I still like seprate file for models and schemas for large projects. I have seen people including myself, starting to add more and more SQLModel classes for request/response data validation. After some thousand lines, It becomes very difficult for me to understand the relationships between models.)

In the next tutorial we will be actually creating the tables in the database using a migration tool named alembic.