Local development environment with Fast API + SQLModel + SQLite + Alembic

It is important to have a working local development environment for quickly prototyping and developing CRUD applications with FastAPI. In this post, we will explore how to quickly set up a local development environment using the following tools to develop REST API endpoints:
Prerequisites:
Code repository:
- Fast API + SQLite + SQLModel + Alembic (Sync version)
- Fast API + SQLite + SQLModel + Alembic (Async version)
Sample API workflow:
Workflow:
- Write database models
- Write database settings
- Write REST API endpoints
- Test APIs
- Setup alembic
- Change database models
- Update APIs
- Run alembic migration
- Test APIs
Project structure:
.
├── app
│ ├── db.py : All the settings related to DB will be here.
│ ├── __init__.py
│ ├── main.py : All endpoints will be defined here.
│ └── models.py : All the data models will be defined here.
└── database.db : This SQLite DB will be created and data will be stored here.
Step 1: Setup development environment
Create a local Conda environment to run the application. Here, we will use Python 3.8. After creating the environment, install the required dependencies.
conda create --name fastapi_sqlmodel python=3.8
conda activate fastapi_sqlmodel
pip install fastapi[all] sqlmodel alembic aiosqlite
Step 2: Create database models in models.py
Here, we will define a database model to store a task name. The task table will later be updated to include a task description, and we will perform a migration to see how these changes are maintained and reflected in the database.
from sqlmodel import SQLModel, Field
class TaskBase(SQLModel):
task_name: str
class Task(TaskBase, table=True):
id: int = Field(default=None, primary_key=True)
class TaskCreate(TaskBase):
pass
Step 3: Create settings related to DB in db.py
For rapid prototyping, we will use a local SQLite database, which is stored in a standalone file. To view its records, we can use the DB Browser for SQLite. When we run the application, a file called database.db will be created in the project root directory.
Sync version:
Here are going to create two functions which will create all the required tables when the FastAPI application starts and will generate a session using which I/O operations will be performed in a DB.
from sqlmodel import Session, SQLModel, create_engine
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
connect_args = {"check_same_thread": False}
engine = create_engine(sqlite_url, echo=True, connect_args=connect_args)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def get_session():
with Session(engine) as session:
yield session
Async version:
The async versions of the functions above will be used in main.py.
from sqlmodel import SQLModel
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker
sqlite_file_name = "database.db"
sqlite_url = f"sqlite+aiosqlite:///{sqlite_file_name}"
engine = create_async_engine(sqlite_url, echo=True, future=True)
async def init_db():
async with engine.begin() as conn:
await conn.run_sync(SQLModel.metadata.create_all)
async def get_session() -> AsyncSession:
async_session = sessionmaker(
engine, class_=AsyncSession, expire_on_commit=False
)
async with async_session() as session:
yield session
Step 4: Define API endpoints in main.py
Here, we will define a POST endpoint that writes task-related information to the database. The endpoint uses TaskCreate as its request model and returns an object of type Task.
Sync version:
from fastapi import FastAPI, Depends
from sqlmodel import Session
from app.db import create_db_and_tables, get_session
from app.models import Task, TaskCreate
app = FastAPI()
@app.on_event("startup")
def on_startup():
create_db_and_tables()
@app.get("/ping")
def pong():
return {"ping": "pong!"}
@app.post("/task/", response_model=Task)
def create_task(task: TaskCreate, session: Session = Depends(get_session)):
db_task = Task.from_orm(task)
session.add(db_task)
session.commit()
session.refresh(db_task)
return db_task
Async version:
from fastapi import FastAPI, Depends
from sqlmodel import Session
from app.db import create_db_and_tables, get_session
from app.models import Task, TaskCreate
app = FastAPI()
@app.on_event("startup")
def on_startup():
create_db_and_tables()
@app.get("/ping")
def pong():
return {"ping": "pong!"}
@app.post("/task/", response_model=Task)
def create_task(task: TaskCreate, session: Session = Depends(get_session)):
db_task = Task.from_orm(task)
session.add(db_task)
session.commit()
session.refresh(db_task)
return db_task
Step 5: Test API endpoints
Here, we will test whether the API endpoints are working correctly. If the curl request returns the expected result and we can verify that the database records were updated, we can consider the APIs to be working correctly.
uvicorn app.main:app --reload
curl -X POST http://127.0.0.1:8000/task/ -H 'accept: application/json' -H 'Content-Type: application/json' -d '{"task_name": "just added task"}'
Note:
- A
database.dbfile will be created in the project root directory. - After making the POST request, verify that the database records were updated using
DB Browser for SQLite.
Step 6: Setup alembic
Step 6a: Generate alembic settings
In this step, we will generate the settings for Alembic migrations. This will create files and folders in the project root directory.
Sync version:
alembic init alembic
Async version:
alembic init -t async alembic
Step 6b: Update alembic settings
After these files are generated, change the DB url in alembic.ini.
Sync version:
- replace
sqlalchemy.url = driver://user:pass@localhost/dbnameinalembic.iniwithsqlite:///database.db.
Async version:
- replace
sqlalchemy.url = driver://user:pass@localhost/dbnameinalembic.iniwithsqlite+aiosqlite:///database.db.
Common changes for Sync & Async version:
- in
alembic/env.pyfile addfrom sqlmodel import SQLModelin the import section. - in
alembic/env.pyfile add the following line in import section,from app.models import Task. - in
alembic/env.pychangetarget_metadata = Nonetotarget_metadata = SQLModel.metadata. - in
alembic/script.py.makoaddimport sqlmodelin the import section.
Step 6c: Generate alembic migration settings
After making all the changes, make first migration.
alembic revision --autogenerate -m "init"
alembic upgrade head
Step 7: Update data models in models.py
Now, lets say there is some changes we need to add to our data model. In this case we are just going to add task_description. task_description will be a optional string column in the Task table. After we make the required changes in the app/models.py and app/main.py, we need to run the migration to add the newly added columns in the DB.
from sqlmodel import SQLModel, Field
from typing import Optional # This is a new add line
class TaskBase(SQLModel):
task_name: str
task_description: Optional[str] = None # This is a new add line
class Task(TaskBase, table=True):
id: int = Field(default=None, primary_key=True)
class TaskCreate(TaskBase):
pass
Step 8: Update API endpoints in main.py
In this case, endpoints remains unchanged but in a realistic scenario endpoints has to be changed to take the new information into account.
Step 9: Run DB migration
Run the following line to update the database tables.
alembic revision --autogenerate -m "add description"
alembic upgrade head
Step 10: Test updated API endpoints
As last step, make a call to the API endpoints which will be using the updated table and check if everything is working fine or not.
uvicorn app.main:app --reload
curl -X POST http://127.0.0.1:8000/task/ -H 'accept: application/json' -H 'Content-Type: application/json' -d '{"task_name": "just added task","task_description":"a newly created task"}'
- after making the post call validate the db records are being updated using
DB browser for SQLite
Conclusion:
This workflow is quite good when in come to rapid prototyping. One can use this inside a docker container as well but make sure that the volume mounting is in place to keep the standalone database file and the other related alembic migration scripts.
Reference:
- FastAPI with Async SQLAlchemy, SQLModel, and Alembic
- Async SQL (Relational) Databases
- FastAPI + SQLModel + Alembic
- SQL (Relational) Databases
- Building a Phone Directory with Python, MySQL, FastAPI, and Angular
- Alembic: Auto Generating Migrations
- Build an async python service with FastAPI & SQLAlchemy
- How to set up FastAPI, Ormar, and Alembic
- tiangolo/uvicorn-gunicorn-fastapi-docker