FastAPI Deep Dive: Exploring PostgreSQL, SQLModel, Alembic, and JWT Integration - Foundations

Many said Python is slow, but not anymore. Meet FastAPI, our superhero ready to compete with Node.js and Go in a tug of war. Let's see how it makes Python fast and powerful for modern API development.

Introduction

FastAPI - Often hailed as a robust web framework for API development, FastAPI might seem like just another tool in the developer's toolbox at first glance. However, there's more to this powerhouse than meets the eye. The true magic unfolds when you seamlessly integrate FastAPI with the dynamic duo Pydantic and Starlette.

PostgreSQL - An open-source relational database system.

SQLModel - A Python library that simplifies working with databases by providing a declarative way to define and interact with database models.

Alembic - A library used for database migrations. It stores versions of our database design changes and helps developers stay on the same database design throughout the development lifecycle. It is like Git for databases.

JWT - In simple terms, this is a way to securely transmit information between the client and server. The two main scenarios where JWTs are used are Authorization and Information Exchange.

Even though we use Pydantic and Starlette in this post, we don't go deep into them. Our main focus for this post is understanding PostgreSQL, SQLModel, Alembic, and JWT and integrating them into FastAPI to create robust web APIs.

In this series of posts, we will work on developing a blog site - only the backend ;). We test APIs using Swagger(inbuilt to FastAPI).

Project Setup

Prerequisites

• Python 3.9 or above installed
• venv installed
• Basic understanding of APIs

Step 1 - Create a project folder.

mkdir blog-site

Step 2 - Create a Virtual Environment inside the project folder

cd blog-site

python3 -m venv venv

Step 3 - Install necessary dependencies

pip install fastapi uvicorn psycopg2-binary sqlmodel alembic python-jose

Step 4 - Create main.py file

touch main.py

Now, the project structure looks like this:

blog-site/
├─ main.py
├─ venv

Step 5 - Starter code in main.py file

from fastapi import FastAPI

app = FastAPI()

@app.get("/")
def app_get():
    return {'info': "FastAPI Working"}

Save the file. Now run the following command by staying in the project root folder.

uvicorn main:app --reload

You should see something like this:

Now, access this URL in your browser:

http://127.0.0.1:8000/

You will see something similar to this:

Awesome job! You've set up FastAPI and nailed that API endpoint.

Project Structure

As our application gets more complex, it's important to keep things simple for easier development. Make sure the project structure follows this layout:

blog-site/
├─ app/
├─ ├─ api/
├─ ├─ models/
├─ ├─ schemas/
├─ ├─ services/
├─ main.py
├─ venv

Database Setup

We use PostgreSQL for our database setup. Now-a-days PostgreSQL is a popular choice for people who want to get their hands dirty with relational databases. Below are the installation and setup steps for PostgreSQL on a Linux(Ubuntu) machine.

Step 1 - Installation

# Create the file repository configuration:
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

Check if the installation is successful:

psql --version

#You should see something like this
psql (PostgreSQL) 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1)

Step 2 - Install pgAdmin for GUI(Desktop)

pgAdmin provides a User Interface so, that we can easily access our DBs, Tables, and Schemas and we can do a lot more. This also provides a Query Tool where we can perform SQL Queries on the go.

#
# Setup the repository
#

# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

#
# Install pgAdmin
#


# Install for desktop mode only:
sudo apt install pgadmin4-desktop

Now, search for pgAdmin in your applications.

Step 3 - Creating User and Database

In terminal enter the below command to open psql editor

sudo -u postgres psql

Now enter the below command for DB and User creation.

CREATE USER admin WITH PASSWORD 'admin';
CREATE DATABASE blog_db OWNER admin;

The above will create an user admin with password 'admin'. We have also created a database - blog_db, this is where we store all our tables. For this DB we are assigning admin as owner, other users will not be able to perform any operations on this DB.

Step 4 - pgAdmin Setup

1. Launch the pgAdmin application.

2. In the right navigation bar, locate the "Servers" option.

3. Right-click on "Servers" and choose the "Register Server" option.

4. In the "General" tab, provide a name of your preference for the server.

5. Switch to the "Connection" tab.

In the "Host name/address" field, enter "localhost"
In the "Username" field, enter "admin"
In the "Password" field, enter "admin"

Click on the "Save" button to store the server registration.

By following these steps, you have registered a server in pgAdmin with the specified connection details, allowing you to manage and interact with the PostgreSQL server.

Kudos! You've accomplished the setup of the database on your local machine.

SQLModel

SQLModel is a library designed for engaging with SQL databases. If you have experience with SQLAlchemy, you'll find SQLModel to be quite familiar, as it is constructed on top of SQLAlchemy. Its foundation on pydantic enhances its compatibility with FastAPI, contributing to a seamless integration experience.

We will start writing models for our db. Navigate to models folder based on project structure we discussed above and create 2 files users.py and models.py. We will store our models in these 2 files.

1. Users Model

from typing import Optional
from sqlmodel import Field, SQLModel
from datetime import datetime

class Users(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    username: str
    password: str

Users Model has 3 columns: id, username and password

1. Blogs Model

from typing import Optional
from sqlmodel import Field, SQLModel
from datetime import datetime

class Blogs(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str
    description: str
    created_at: datetime = Field(default_factory=datetime.utcnow)

Blogs models has 4 columns:

• id
• title(title of the blog)
• description(Body of the blog)
• created_at(Time when blog posted/created).

Let's delve into declarations and code.

Beginning with Optional, we've applied this to the id column in both models. The use of Optional indicates that the id can hold either a value or None in Python terms.

For the column type String, we use str.

The created_at column signifies when the blog was posted. In this context, we utilize the UTC time zone, and utcnow generates a default value with the current UTC time, without relying on the client or frontend to send it.

In the next blog we will setup alembic and develop protected routes/api using JWT

Happy Coding!