Building a FastAPI App Using PostgreSQL

Building a FastAPI app with PostgreSQL is a great way to create a powerful and performant web application. FastAPI is a modern web framework for building APIs with Python, while PostgreSQL is a powerful relational database that can handle large amounts of data efficiently. In this tutorial, we will walk through the process of setting up a FastAPI app and connecting it to a PostgreSQL database.

Step 1: Set up your project environment

Before you start building your FastAPI app, you will need to set up your project environment. First, make sure you have Python installed on your machine. You can download and install Python from the official website.

Next, create a new directory for your project and navigate to it in your terminal. Then, create a virtual environment for your project using the following command:

python -m venv venv

Activate the virtual environment by running the activate script in the venv directory:

On Windows:

venvScriptsactivate

On macOS and Linux:

source venv/bin/activate

Step 2: Install FastAPI and SQLAlchemy

FastAPI relies on Starlette for the web framework aspects, while SQLAlchemy is a powerful ORM (Object-Relational Mapping) library for Python that will help us interact with our PostgreSQL database. Install FastAPI and SQLAlchemy using pip:

pip install fastapi
pip install sqlalchemy

Step 3: Create a FastAPI app

Now that you have set up your project environment and installed the necessary dependencies, it’s time to create a FastAPI app. Create a new Python file, for example, main.py, and import the necessary modules:

from fastapi import FastAPI

Create a new FastAPI app instance:

app = FastAPI()

Step 4: Set up a PostgreSQL database

Next, you will need to set up a PostgreSQL database to connect to your FastAPI app. Install the asyncpg library, which is a fast PostgreSQL database adapter for Python, using pip:

pip install asyncpg

Step 5: Connect to the PostgreSQL database

To connect to your PostgreSQL database, you will need to provide the connection information in your FastAPI app. You can do this by creating a database URL that contains the necessary credentials. Here’s an example of how you can connect to a local PostgreSQL database:

from fastapi import FastAPI
import asyncpg
import sqlalchemy

app = FastAPI()

DATABASE_URL = "postgresql://username:password@localhost/database_name"

# Create a SQLAlchemy engine
engine = sqlalchemy.create_engine(DATABASE_URL)

# Create a coroutine function to connect to the database
async def get_db():
    async with engine.connect() as connection:
        yield connection

Step 6: Define and create database models

To interact with your PostgreSQL database, you will need to define database models using SQLAlchemy. Create a new Python file, for example, models.py, and define your database models:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    email = Column(String)

Next, create the database tables based on your models by calling the create_all() method on the metadata object:

from models import Base
Base.metadata.create_all(bind=engine)

Step 7: Create endpoints in your FastAPI app

Now that you have set up your PostgreSQL database and defined your database models, you can create endpoints in your FastAPI app to interact with the database. Define endpoint functions in your main.py file and use the get_db() coroutine function to access the database connection:

from fastapi import FastAPI, Depends
from models import User
from sqlalchemy.orm import sessionmaker

app = FastAPI()

Session = sessionmaker(engine)

@app.post("/users/")
async def create_user(name: str, email: str, db=Depends(get_db)):
    async with db.transaction():
        user = User(name=name, email=email)
        db.add(user)
    return {"name": name, "email": email}

@app.get("/users/{user_id}")
async def get_user(user_id: int, db=Depends(get_db)):
    user = await db.fetch_one("SELECT * FROM users WHERE id = $1", user_id)
    return user

Step 8: Run your FastAPI app

To run your FastAPI app, use the uvicorn ASGI server. Install uvicorn using pip:

pip install uvicorn

Run your FastAPI app by providing the name of your FastAPI app instance and the filename where your FastAPI app is defined:

uvicorn main:app --reload

Your FastAPI app should now be running on http://localhost:8000. You can test your endpoints using tools like Postman or curl.

In this tutorial, we covered the process of building a FastAPI app with PostgreSQL from setting up your project environment to creating database models and defining endpoints. FastAPI and PostgreSQL are powerful tools that can help you build fast and efficient web applications. I hope this tutorial helps you get started with building your own FastAPI app with PostgreSQL.