How to Use Fast API with a Relational Database on Ubuntu

Introduction

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.

Use FastAPI with PostgreSQL Database

• Python Environment Setup
• Create and Activate Virtual Environment
• Install and Setup PostgreSQL
• Create FastAPI Application
• Install Required Libraries
• Run Python Application
• Test FastAPI Application 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. For guidance on how to set this up, please choose your distribution from this list and follow our initial server setup guide. Please ensure to work with a supported version of Ubuntu.

• Familiarity with the Linux command line. For an introduction or refresher to the command line, you can visit this guide on Linux command line primer.

• 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.

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

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

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

1. asyncpg: This is an asynchronous PostgreSQL driver that allows FastAPI to interact with a PostgreSQL database.
2. SQLAlchemy: This is an ORM tool that helps you manage database schemas and queries as Python code instead of writing raw SQL.
3. 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 <user_db>;
CREATE ROLE <username> WITH PASSWORD '<password>';
GRANT ALL PRIVILEGES ON DATABASE <user_db> TO <username>;
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.