FastAPI – SQLite Databases

Python FastAPI is a modern and fast, web framework for building APIs with Python. FastAPI gained good popularity for its performance and ease of use. When it comes to integrating SQL databases with FastAPI, the framework provides seamless support, making it a good choice for developers for efficient data storage solutions. In this article, we will use SQL databases with FastAPI in Python, with the help of detailed explanations, and good examples.

Connect FastAPI with Database in Python

Let’s understand how we can use SQL Database with FastAPI, for data storing and data retrieving in FastAPI. Here, in this example, we are using an SQLite database. for creating a database model we are using SQLalchemy, which is very compatible with Python when we are doing database-related development. It requires that you have a basic idea of FastAPI to understand the concept more clearly.

Step 1: Installation of Required Libraries

Before starting to code you need to make sure you have FastAPI, pydantic, sqlalchemy, and uvicorn libraries installed in the system. For that simply run the below command in the terminal to install them.

pip install fastapi
pip install pydantic
pip install sqlalchemy
pip install uvicorn

Now you are ready to explore using Database with FastAPI. Our projects contain only one file main.py. This file contains all the code we discussed in the next steps. as we run our code file, new test.db file created which contains data that we store that we will see in other steps.

Project Structure

Step 2: Importing Necessary Libraries and Classes

Let’s start by importing the required modules and classes for building the FastAPI application, working with databases using SQLAlchemy, and also for defining data models with Pydantic. so let’s import fastapi, pydantic and sqlalchemy.

Python3

# Import necessary modules and classes
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import create_engine, Column, Integer, String
import sqlalchemy
from sqlalchemy.orm import sessionmaker, Session
from pydantic import BaseModel

Step 3: Creating a FastAPI Instance and SetUp the Database

Here In this step, We create an instance of the FastAPI application (app). Then we Set up the database connection using SQLite and create a SQLAlchemy engine also Defined a SessionLocal factory for creating database sessions. Here, Base is a declarative base for defining SQLAlchemy models which we use to create our databse model(named Item).

Python3

# FastAPI app instance
app = FastAPI()

# Database setup
DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = sqlalchemy.orm.declarative_base()

Step 4: Defining the Database Model

In this step we will create a database model named Item(using sqlalchemy Base model) in our database, we store all our data into this model. The model named Item represents items with an id, name, and description. here id, name and description are data field of model.

Python3

# Database model
class Item(Base):
    __tablename__ = "items"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    description = Column(String)

Step 5: Creating Database Tables

Let’s create a necessary tables in the database based on the defined models.

Python3

# Create tables
Base.metadata.create_all(bind=engine)

Step 6: Dependency for Getting the Database Session

In this step We will define a dependency function (get_db) to get a database session. It yields the database session to be used in API endpoints and ensures that the database session is properly closed after use by API.

Python3

# Dependency to get the database session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Step 7: Pydantic Model for Request Data and Response Data

Here, We defined a Pydantic model (ItemCreate) to represent the data expected when creating an item. It includes fields for name and description. This pydantic model helping in auto validate the input data with defined pydantic data model.

Python3

# Pydantic model for request data
class ItemCreate(BaseModel):
    name: str
    description: str

Also, Define a Another Pydantic model (ItemResponse) for representing the response data. It includes fields for id, name, and description. It’s used for sending response data to user when he make request to access the data using FastAPI.

Python3

# Pydantic model for response data
class ItemResponse(BaseModel):
    id: int
    name: str
    description: str

Step 8: API Endpoint to Create an Item

Here In this step, we will create simple API endpoints which is responcible for getting item from user and adding a item into the database. we created an API endpoint (/items/) using the @app.post decorator for creating an item. It takes an item as input, validates it using the Pydantic model (ItemCreate), and adds it to the database. The response_model parameter ensures that the response follows the structure we defined in the ItemResponse Pydantic model.

Python3

# API endpoint to create an item
@app.post("/items/", response_model=ItemResponse)
async def create_item(item: ItemCreate, db: Session = Depends(get_db)):
    db_item = Item(**item.model_dump())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

Step 9: API Endpoint to Read an Item by ID

Here we created a API endpoint which is for getting data from database using an item id as input. In simple term when user want to access some data of item from database, he/she will send that item id and in response he/she get all details of that item from databse. so we created Another API endpoint (/items/{item_id}) which is defined using the @app.get decorator for reading an item by its ID. It takes the item_id as a parameter, queries the database, and returns the item. If the item is not found, it raises an HTTP exception with a 404 status.

Python3

# API endpoint to read an item by ID
@app.get("/items/{item_id}", response_model=ItemResponse)
async def read_item(item_id: int, db: Session = Depends(get_db)):
    db_item = db.query(Item).filter(Item.id == item_id).first()
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return db_item

Step 10: Running the FastAPI Application

At last just add the block of code which make sure that the FastAPI application is run only if the script is executed directly (not imported as a module). Basically it uses uvicorn to run the FastAPI application on “http://127.0.0.1:8000“.

Python3

if __name__ == "__main__":
    import uvicorn

    uvicorn.run(app, host="127.0.0.1", port=8000)

Final Code Implementation

In this final code, we created the FastAPI application that is about to interact with an SQLite database through SQLAlchemy. The code establishes the FastAPI instance(web API application), configures the database connection, defines a simple data model for items, and creates corresponding database tables. Also, in the example we use Pydantic models for both request and response data. Two API endpoints are implemented, one for creating items and another for retrieving items by their ID. we will use those API endpoints and see how data changes in the Database.

Python3

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from pydantic import BaseModel

# FastAPI app instance
app = FastAPI()

# Database setup
DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


# Database model
class Item(Base):
    __tablename__ = "items"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    description = Column(String)


# Create tables
Base.metadata.create_all(bind=engine)


# Dependency to get the database session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


# Pydantic model for request data
class ItemCreate(BaseModel):
    name: str
    description: str


# Pydantic model for response data
class ItemResponse(BaseModel):
    id: int
    name: str
    description: str


# API endpoint to create an item
@app.post("/items/", response_model=ItemResponse)
async def create_item(item: ItemCreate, db: Session = Depends(get_db)):
    db_item = Item(**item.dict())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item


# API endpoint to read an item by ID
@app.get("/items/{item_id}", response_model=ItemResponse)
async def read_item(item_id: int, db: Session = Depends(get_db)):
    db_item = db.query(Item).filter(Item.id == item_id).first()
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return db_item


if __name__ == "__main__":
    import uvicorn

    # Run the FastAPI application using Uvicorn
    uvicorn.run(app, host="127.0.0.1", port=8000)

Output:

We can run this code as we run any simple python file. API will be available at “127.0.0.1:8000“, and to use Swagger UI we will acess “127.0.0.1:8000/docs” in browser.

Video Demonstration