Alembic

Firing up the application with SQLModel models defined and imported will create initial tables, but as projects grow and refactoring takes place, managing databases needs handling with a migrations tool.

Install Alembic

Install Alembic:

pip install alembic

And include it in the non_ver_reqs.txt file:

fastapi
uvicorn[standard]
pydantic-settings
sqlmodel
alembic

Freeze the requirements:

pip freeze > requirements.txt

Configure

First, run the init command to initialize Alembic:

alembic init alembic

That will have created a new directory in the root of your project with a structure like this:

├─ alembic
│  ├── env.py
│  ├── README
│  ├── script.py.mako
│  └── versions
└─ alembic.ini

There are a few things to configure to make this compatible with SQLModel. Edit env.py and add the following imports:

alembic/env.py

from sqlmodel import SQLModel

You also need to import any schema you wish Alembic to manage. It might look like this isn't used but Alembic will pick up the schema for migrations, for example:

alembic/env.py

from db.schemas.blog_schemas import BlogPost

Additionally, update target_metadata to use SQLModel.metadata, for example:

alembic/env.py

target_metadata = SQLModel.metadata

Next, update alembic/script.py.mako to include the following import:

alembic/script.py.mako

import sqlmodel

And finally edit alembic.ini and provide your database URL:

SQLite example:

alembic.ini

sqlalchemy.url = sqlite:///mnt/db/exampleforyou.sqlite3

Revision Init

With Alembic configured, time to create the initial migration:

alembic revision --autogenerate -m "init"

That command creates the SQL ready to apply to the database under alembic/versions.

To commit the changes, run:

alembic upgrade head

Revisions

Moving forward with your project you can make changes to the database schemas in code, and use Alembic to make the changes to the database. Let's add a new column called teaser, for example:

db/schemas/blog_schemas.py

import uuid
from pydantic import AnyUrl
import datetime

from pydantic.types import UUID
from sqlmodel import SQLModel, Field


# Posts Schemas
class BlogPostIn(SQLModel):
    title: str
    teaser: str  # NEW
    content: str
    cover_image: AnyUrl
    published: bool

    class Config:
        json_schema_extra = {
            "example": {
                "title": "Lorem ipsum",
                "teaser": "Lorem ipsum dolor sit amet, consectetur adipiscing elit.",  # NEW
                "content": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.",
                "cover_image": "http://www.exampleforyou.net/static/img/excepteur_sint.jpg",
                "published": True
            }
        }


class BlogPostOut(BlogPostIn):
    id: uuid.UUID
    created_date: datetime.datetime


# SQLModel Database Schemas
class BlogPost(BlogPostIn, table=True):
    id: UUID | None = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False)
    created_date: datetime.datetime | None = Field(
        default_factory=datetime.datetime.utcnow,
        nullable=False)

Create a revision:

alembic revision --autogenerate -m "added teaser"

Examine the SQL that it will apply by looking at the new file under alembic/versions, and apply them once again with:

alembic upgrade head

To rollback and undo that change you can use:

alembic downgrade -1

Tip

There a many approaches to working with databases, in the case of SQLite and PostgreSQL you can use the command line as demonstrated here, alternatively you can use something like https://dbeaver.io/.

Working with SQLite

You can interact with SQLite via the command line, here's how you can do a quick validation of the table creation.

On Fedora, you can install sqlite3 natively with sudo dnf install sqlite.

Open the database file:

sqlite3 ~/volumes/db/exampleforyou.sqlite3

To view the columns in a table:

PRAGMA table_info(blogpost);

To quit:

.q