Interacting with Databases in FastAPI applications using FastAPI and SQLModel

In this tutorial, we will explore how to set up a FastAPI application and interact with a database using SQLModel. FastAPI is a modern, fast (high-performance) web framework for building APIs with Python, and SQLModel is a library that provides a declarative syntax for defining database models in Python.

To get started, let’s first create a new virtual environment and install the necessary packages:

$ python -m venv venv
$ source venv/bin/activate
$ pip install fastapi uvicorn sqlmodel

Next, let’s create a new FastAPI application. Create a new file named main.py and add the following code:

from fastapi import FastAPI
from sqlmodel import Field, Session, SQLModel, create_engine

# Create a new FastAPI app
app = FastAPI()

# Configure the database connection
SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"

# Create the database engine
engine = create_engine(SQLALCHEMY_DATABASE_URL)

# Define a model for the database table
class User(SQLModel, table=True):
    id: int = Field(primary_key=True)
    username: str
    email: str

# Create the database table
def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

# Create an endpoint to add a new user to the database
@app.post("/users/")
def create_user(user: User):
    with Session(engine) as session:
        session.add(user)
        session.commit()
        session.refresh(user)
        return user

In this code, we define a User model using SQLModel, which represents a database table with columns for id, username, and email. We also create a create_user endpoint that accepts a User object and adds it to the database.

To create the database table, we call the create_db_and_tables function, which uses the SQLModel.metadata.create_all method to create the table based on our model. We also create a database engine using the create_engine method with the connection URL for an SQLite database.

To run the FastAPI application, use the following command:

$ uvicorn main:app --reload

Now that the application is running, you can test the create_user endpoint using a tool like Postman or cURL. For example, you can send a POST request to http://localhost:8000/users/ with a JSON payload containing the user data:

{
  "username": "johndoe",
  "email": "johndoe@example.com"
}

After sending the request, you should see a response containing the newly created user object with an id assigned by the database.

This is a simple example of how to interact with a database using SQLModel in a FastAPI application. You can extend this by adding more endpoints for CRUD operations, implementing relationships between models, or using different database engines. FastAPI and SQLModel provide a powerful combination for building APIs with Python and interacting with databases in a declarative way.