How to Use FastAPI with a Relational Database on Ubuntu – Step-by-Step Guide

FastAPI has quickly gained popularity as a high-performance, easy-to-use web framework for building APIs with Python. When paired with a relational database, FastAPI can be used to create powerful, scalable applications. This guide will walk you through the process of setting up a FastAPI application with a relational database on an Ubuntu 24.04 machine. We will cover everything from installing the necessary tools to configuring the database and creating API endpoints.

Prerequisites

Before following the steps in this tutorial, you need:

• A server running Ubuntu along with a non-root user with sudo privileges and an active firewall. Please ensure to work with a supported version of Ubuntu.
• Familiarity with the Linux command line.
• Run sudo apt-get update in the Ubuntu terminal to ensure your system has the latest versions and security updates for the software available from the repositories configured on your system.

These instructions are valid for the most recent versions of Ubuntu: Ubuntu 24.04, Ubuntu 22.04, and Ubuntu 20.04. If you are using Ubuntu version <= 18.04, we recommend you upgrade to a more latest version since Ubuntu no longer provides support for these versions.

Step 1 - Python Environment Setup

Ubuntu 24.04 ships Python 3 by default. Open the terminal and run the following command to double-check Python 3 installation:

python3 --version

If Python 3 is already installed on your machine, this command will return the current version of Python 3 installation. In case it is not installed, you can run the following command and get the Python 3 installation:

sudo apt install python3

Next, you need to install the pip and dev package installers on your system. Run the following command in the terminal:

sudo apt install python3-pip python3-dev

In this tutorial, we will be using PostgreSQL as our relational database. To interact with PostgreSQL database, you need to install the libpq-dev using the following command:

sudo apt install libpq-dev

Step 2 - Create and Activate Virtual Environment

If you are using Ubuntu version < 24.04, you do not need to create a virtual environment. You can skip to the next step.

The next step is to create a virtual environment inside your Ubuntu installation to isolate Python packages from your system environment. To do this, go to your working directory and run the following set of commands:

python3 -m venv fastapi-env

This command will create a new virtual environment in a directory named fastapi-env. It will have its own set of dedicated Python packages, isolated from other projects.

To ensure the packages you install from this moment on get installed inside this isolated environment, you need to activate it by running:

source fastapi-env/bin/activate

On execution, you will notice the terminal prompt prefixed with your virtual environment name like this:

Output:

(fastapi-env) ubuntu@user:

Step 3 - Install and Setup PostgreSQL

The next step is to install PostgreSQL (or your choice of relational database).

sudo apt install postgresql postgresql-contrib

Now, it’s time to start and enable PostgreSQL service inside your virtual environment.

sudo systemctl start postgresql

sudo systemctl enable postgresql

You can check the status of PostgreSQL service by running sudo systemctl status postgresql. Once the service is enabled, it is automatically started on system boot.

To install and use PostgreSQL with FastAPI, you need to install a couple of things:

• asyncpg: This is an asynchronous PostgreSQL driver that allows FastAPI to interact with a PostgreSQL database.
• SQLAlchemy: This is an ORM tool that helps you manage database schemas and queries as Python code instead of writing raw SQL.
• databases: This is a database library for async operations required by SQLAlchemy to work asynchronously with FastAPI.

To install these, run the following consolidated command:

pip install asyncpg sqlalchemy databases

Next up, create a Postgres database and user with the required privileges.

sudo -u postgres psql
CREATE DATABASE ;
CREATE ROLE  WITH PASSWORD '';
GRANT ALL PRIVILEGES ON DATABASE  TO ;
exit 

Step 4 - Create Sample Python Application

You need an executable Python application that accesses your database through FastAPI. If you do not have a running Python application, you can quickly create one by following these steps:

Create a new Python file named postgres_db.py in your project directory.

nano postgres_db.py

In the text editor, you will write logic to create database connection and create table inside the database. In this example, we create a PostgreSQL database connection using the databases package and define structure of a books table using SQLAlchemy.

from databases import Database
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Float

DB_URL = "postgresql://username:password@localhost/user_db"

database = Database(DB_URL)
metadata = MetaData()

books = Table (
  "books",
  metadata,
  Column("id", Integer, primary_key=True, index=True),
  Column("title", String, index=True),
  Column("author", String, index=True),
  Column("price", Float),
)

engine = create_engine(DB_URL)
metadata.create_all(engine)

Save and close the file.

Next, create a main.py inside the same directory.

nano main.py

Write the main logic of your application in this file:

from fastapi import FastAPI
from typing import List
from pydantic import BaseModel
from postgres_db import books, database

app = FastAPI()

class BookCreate(BaseModel):
    title: str
    author: str
    price: float

class BookResponse(BaseModel):
    id: int
    title: str
    author: str
    price: float

class Config:
  orm_mode=True

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

@app.post("/books/", response_model=BookResponse)
async def create_book(book: BookCreate):
    query = books.insert().values(title=book.title, author=book.author, price=book.price)
    last_book_id = await database.execute(query)

    query = books.select().where(books.c.id == last_book_id)
    inserted_book = await database.fetch_one(query)
    return inserted_book

@app.get("/books/", response_model=List[BookResponse])
async def get_books():
    query = books.select()
    return await database.fetch_all(query)

This code uses FastAPI to write new book entries in the PostgreSQL database and fetch the collection of books from it.

Step 5 - Install Required Libraries

In your Python application, you are referencing various libraries and packages. Before you run the application, make sure to install the required libraries.

pip install fastapi uvicorn psycopg2

Step 6 - Run Python Application

Now, it’s time to run the application you created.

uvicorn main:app --reload

uvicorn is an Asynchronous Server Gateway Interface (ASGI) that is used to serve FastAPI application. Using uvicorn is a preferred way to run a FastAPI application since FastAPI is an asynchronous web framework itself.

If the above command executes without encountering an error, then you will see an output similar to the following:

Output:

INFO:     Will watch for changes in these directories: ['/path/to/your/project']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [XXXX] using StatReload
INFO:     Started server process [YYYY]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

In a web browser, navigate to http://127.0.0.1:8000 to see the query output.

Step 7 [OPTIONAL] - Test the Endpoints

You can test the endpoints defined in your main.py (i.e. POST and GET) by sending HTTP requests to the server running on http://127.0.0.1:8000.

{
  "title": "The Great Gatsby",
  "author": "F. Scott Fitzgerald",
  "price": 10.99
}

Similarly, you can make a GET call to the same server to retrieve the list of books present in your Postgres database.

Conclusion

In this tutorial, you walked through the process of setting up a simple FastAPI application that interacts with a PostgreSQL database. These steps are also beneficial for AI applications, especially when you need to build a web API to interact with your AI models or manage data related to your AI processes. With this foundation in place, you can now build and expand your FastAPI projects.

Stay tuned for more articles on how to work with FastAPI.