FastAPI Backend Development Bootcamp · Lesson

Connecting FastAPI to PostgreSQL

Set up a connection between your FastAPI application and a PostgreSQL database using SQLAlchemy.

Connecting FastAPI to PostgreSQL is a free FastAPI Backend Development Bootcamp lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the FastAPI Backend Development Bootcamp learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Connect to a Database?

Web applications often need to store and retrieve data persistently. This data could be user profiles, product listings, or transaction records.

Instead of losing data when your app restarts, we connect to a database. Databases provide a structured and efficient way to manage large amounts of information.

Introducing PostgreSQL

PostgreSQL is a powerful, open-source relational database system. It's known for its robustness, feature set, and performance.

Reliable: Ensures data integrity.
Scalable: Handles large data volumes and high user loads.
Extensible: Supports custom data types and functions.

Many FastAPI applications use PostgreSQL as their primary data store.

The Database Connection URL

To connect to any database, you need a connection string or URL. This URL tells SQLAlchemy (our ORM) how to find and authenticate with your database.

For PostgreSQL, a typical URL looks like this:

postgresql://user:password@host:port/database_name

It's best practice to store this URL in an environment variable for security and flexibility.

Creating the SQLAlchemy Engine

The first step in SQLAlchemy is to create an Engine. The Engine is responsible for communicating with the database.

We use create_engine from sqlalchemy to establish this connection. For our runnable example, we'll use SQLite, but the principle for PostgreSQL is the same – just the URL changes.

from sqlalchemy import create_engine

# For PostgreSQL, this would be:
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@localhost/dbname"
# For a runnable example, we'll use SQLite:
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)

print("Engine created successfully!")

Managing Database Sessions

Directly interacting with the database through the engine isn't ideal for every request. Instead, we use Sessions.

A Session is like a temporary workspace for your database operations. It handles transactions and ensures changes are committed or rolled back properly.

We create a SessionLocal class using sessionmaker, which will produce session instances.

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

# Using SQLite for a runnable example
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

print("SessionLocal configured!")

The Declarative Base (Review)

Recall from the previous lesson that declarative base is used to define your SQLAlchemy models. All your ORM models will inherit from this base.

Even though we won't define a new model here, it's an essential part of the setup for any ORM interaction.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

print("Declarative Base initialized.")

FastAPI Dependency: get_db()

FastAPI's powerful Dependency Injection system is perfect for managing database sessions.

We'll create a function, get_db, that creates a new database session for each request, uses it, and then closes it automatically. The yield keyword is key here!

from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Assuming these are defined elsewhere or imported
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

print("get_db dependency function defined.")

Using the Session in an Endpoint

Now, you can inject the database session directly into your FastAPI endpoint functions using Depends.

FastAPI will call get_db(), pass the session to your route, and ensure it's closed after the request is handled.

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session

# For this snippet, we'll use a mock get_db to make it runnable
class MockSession:
    def close(self): pass

def get_db_mock():
    yield MockSession()

app = FastAPI()

@app.get("/db-status")
def read_db_status(db: Session = Depends(get_db_mock)):
    # In a real app, 'db' would be your actual SQLAlchemy session
    return {"message": "Database session received!"}

print("FastAPI endpoint defined using DB dependency.")

Full FastAPI-PostgreSQL Setup

Here's a complete example bringing everything together. This app sets up the connection to a database (using SQLite for local testing, but easily swappable for PostgreSQL) and exposes an endpoint that uses a database session.

To run this with a real PostgreSQL, replace the SQLALCHEMY_DATABASE_URL and ensure your PostgreSQL server is running.

from fastapi import FastAPI, Depends
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.ext.declarative import declarative_base
import uvicorn

# --- Database Configuration (for PostgreSQL, replace URL) ---
# For a runnable example, we'll use SQLite in-memory:
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
# For PostgreSQL, it would be:
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@localhost/dbname"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# --- Dependency to get DB session ---
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# --- FastAPI Application ---
app = FastAPI()

@app.get("/connect-test")
def connect_test(db: Session = Depends(get_db)):
    # We've successfully received a DB session 'db'
    # In a real app, you'd perform DB operations here.
    return {"status": "Connected to DB", "db_type": SQLALCHEMY_DATABASE_URL.split('://')[0]}

# To run this, save as 'main.py' and run 'uvicorn main:app --reload'
# if __name__ == "__main__":
#    uvicorn.run(app, host="0.0.0.0", port=8000)

Quick Check: DB Session

Consider the get_db dependency function:

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

What is the primary reason for using yield db instead of return db in this FastAPI dependency?

Recap: Connecting FastAPI to PostgreSQL

In this lesson, you learned the essential steps to connect your FastAPI application to a database, specifically focusing on PostgreSQL (with runnable SQLite examples for convenience).

We defined the Database URL to locate the database.
We used create_engine to establish the connection.
We set up SessionLocal for managing database sessions.
We created a get_db dependency using yield to inject and manage sessions per request in FastAPI.

Now your FastAPI app is ready to interact with a database! Next, we'll learn how to perform CRUD operations.

Frequently asked questions

Is the “Connecting FastAPI to PostgreSQL” lesson free?

Yes — the full text of “Connecting FastAPI to PostgreSQL” is free to read here on the web, and the FastAPI Backend Development Bootcamp course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the FastAPI Backend Development Bootcamp course, upgrade to CoddyKit PRO.

What will I learn in “Connecting FastAPI to PostgreSQL”?

Set up a connection between your FastAPI application and a PostgreSQL database using SQLAlchemy. You practise FastAPI Backend Development Bootcamp with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start FastAPI Backend Development Bootcamp?

No prior experience is required. FastAPI Backend Development Bootcamp on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Connecting FastAPI to PostgreSQL” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this FastAPI Backend Development Bootcamp lesson?

Yes. Every FastAPI Backend Development Bootcamp lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to FastAPI Backend Development Bootcamp