FastAPI Deep Dive: Exploring PostgreSQL, SQLModel, Alembic, and JWT Integration - Foundations
Tags: fastapi, postgres, jwt, alembic
Published: 2023-12-23 03:36:10

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.

```bash
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

```python
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.

```terminal
uvicorn main:app --reload
```
You should see something like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/azxog2s2gygn1plmrpv5.png)

Now, access this URL in your browser:

[http://127.0.0.1:8000/](http://127.0.0.1:8000/)

You will see something similar to this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7moik3n86jmwowuj2gzn.png)

**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 <u>PostgreSQL</u> 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.

```sql
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

```python
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

2. Blogs Model

```python
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!