Leapcell

Posted on Oct 19

Build Your Own Forum with FastAPI: Step 2 - Integrating Database

#webdev #programming #backend #python

In the previous article, we started from scratch and quickly built a prototype of a forum using FastAPI. Although its functionality was basic, it already had the core features of a forum: posting and displaying threads.

This prototype has one significant problem: we used a Python list as an in-memory database. This means that whenever the server restarts, all the posts published by users will disappear.

To solve this problem, in this article, we will introduce a real database to our forum: PostgreSQL, and operate it through the SQLAlchemy ORM to achieve persistent data storage.

Let's get started!

Preparing PostgreSQL

Before starting the tutorial, you need to have a PostgreSQL database ready. You can install it locally; instructions can be found on the official PostgreSQL website.

A simpler alternative is to use Leapcell to get a free online database with just one click.

After registering an account on the website, click "Create Database".

Enter a Database name, select a deployment region, and you can create the PostgreSQL database.

On the new page that appears, you will see the information required to connect to the database. A control panel is provided at the bottom, allowing you to read and modify the database directly on the webpage.

Using this connection information, you can directly access the database from various tools without any further local configuration.

Step 1: Install New Dependencies

To allow Python to communicate with PostgreSQL, we need some new libraries. Make sure your virtual environment is activated, then run the following command:

pip install "sqlalchemy[asyncio]" "psycopg[binary]"

sqlalchemy is the most popular Object-Relational Mapping (ORM) tool in the Python ecosystem. It allows us to manipulate the database using Python code instead of writing tedious SQL statements.
psycopg[binary] is used to connect PostgreSQL and Python. SQLAlchemy uses it to communicate with the database.

Step 2: Establish the Database Connection

Create a new file named database.py specifically for handling all database connection-related configurations.

database.py

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase

# 1. Database URL
# Format: "postgresql+psycopg://<user>:<password>@<host>:<port>/<dbname>"
DATABASE_URL = "postgresql+psycopg://your_user:your_password@localhost/fastapi_forum_db"

# 2. Create the database engine
engine = create_async_engine(DATABASE_URL)
SessionLocal = async_sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 3. Create a Base class
# Our ORM models will inherit from this class later
class Base(DeclarativeBase):
    pass

create_async_engine creates a SQLAlchemy engine, which is the core for communicating with the database.
SessionLocal is used to perform database operations (create, read, update, delete).
The Base class will be the base class for all database models (data tables) in this tutorial.

Step 3: Define the Data Table Model

Now we no longer need to use memory as our database. Let's create a SQLAlchemy model to truly define the structure of the posts table in the database.

Create a new file named models.py:

models.py

from sqlalchemy import Column, Integer, String
from .database import Base

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    content = Column(String)

This Post class corresponds directly to the structure of the posts table:

__tablename__ = "posts": Specifies the corresponding table name in the database.
id: An integer primary key, with an index created for it to speed up queries.
title and content: String-type fields.

Note that defining the model does not mean the table already exists in the database. You need to execute SQL or similar commands to create this table manually.

The corresponding SQL is:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR,
    content TEXT
);

If you created your database with Leapcell, you can directly enter the SQL on its webpage to modify the database.

Step 4: Refactor the API to Use the Database

This is the most critical step. We need to completely remove the in-memory db list and modify the API route functions to interact with PostgreSQL through a SQLAlchemy Session.

First, add a dependency function in database.py:

database.py (add function)

# ... previous code remains unchanged ...

# Dependency: Get a database session
async def get_db():
    async with SessionLocal() as session:
        yield session

Now, we can use Depends(get_db) to get a database session in our API path operation functions.

Below is the final, complete version of main.py, which has been fully switched over to using the database.

main.py (Final Complete Version)

from fastapi import FastAPI, Form, Depends
from fastapi.responses import HTMLResponse, RedirectResponse
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, desc
from typing import List

from . import models
from .database import engine, get_db

app = FastAPI()

# --- HTML Template ---
def generate_html_response(posts: List[models.Post]):
    posts_html = ""
    for post in posts:  # No need for reversed() anymore, we can sort in the query
        posts_html += f"""
        <div style="border: 1px solid #ccc; padding: 10px; margin-bottom: 10px;">
            <h3>{post.title} (ID: {post.id})</h3>
            <p>{post.content}</p>
        </div>
        """

    html_content = f"""
    <html>
        <head>
            <title>My FastAPI Forum</title>
            <style>
                body {{ font-family: sans-serif; margin: 2em; }}
                input, textarea {{ width: 100%; padding: 8px; margin-bottom: 10px; box-sizing: border-box; }}
                button {{ padding: 10px 15px; background-color: #007BFF; color: white; border: none; cursor: pointer; }}
                button:hover {{ background-color: #0056b3; }}
            </style>
        </head>
        <body>
            <h1>Welcome to My Forum</h1>
            <h2>Create a New Post</h2>
            <form action="/api/posts" method="post">
                <input type="text" name="title" placeholder="Post Title" required><br>
                <textarea name="content" rows="4" placeholder="Post Content" required></textarea><br>
                <button type="submit">Post</button>
            </form>
            <hr>
            <h2>Post List</h2>
            {posts_html}
        </body>
    </html>
    """
    return HTMLResponse(content=html_content, status_code=200)

# --- Routes ---

@app.get("/", response_class=RedirectResponse)
def read_root():
    return "/posts"

# Route for displaying the page
@app.get("/posts", response_class=HTMLResponse)
async def view_posts(db: AsyncSession = Depends(get_db)):
    # 1. Query all posts from the database
    result = await db.execute(select(models.Post).order_by(desc(models.Post.id)))
    posts = result.scalars().all()
    # 2. Render the HTML
    return generate_html_response(posts)

@app.post("/api/posts")
async def create_post(
    title: str = Form(...),
    content: str = Form(...),
    db: AsyncSession = Depends(get_db)
):
    # 1. Create a new Post object
    new_post = models.Post(title=title, content=content)
    # 2. Add it to the database session
    db.add(new_post)
    # 3. Commit and save to the database
    await db.commit()
    # 4. Refresh the object to get the newly generated ID
    await db.refresh(new_post)

    return RedirectResponse(url="/posts", status_code=303)

The steps above accomplished the following:

Removed the in-memory db list.
All route functions that interact with the database were changed to async def, and await is used before database operations. This is because we chose an asynchronous database driver and engine.
GET /posts and POST /api/posts were modified to read from and write to the database.

Run and Verify

Now, restart your uvicorn server:

uvicorn main:app --reload

Open your browser and visit http://127.0.0.1:8000. You will see an empty list of posts (because the database is brand new).

Try publishing a few new posts. They will be displayed just like before.

Next, let's test data persistence:

Press Ctrl+C in your terminal to shut down the uvicorn server.
Restart the server.
Visit http://127.0.0.1:8000 again.

You will find that the posts you published earlier are still there! Your forum data now resides in PostgreSQL, achieving persistent storage.

Deploying the Project Online

Just like in the first tutorial, you can deploy the results of this step online to let your friends experience the changes and progress of your project.

A simple deployment solution is to use Leapcell.

If you have deployed before, simply push the code to your Git repository, and Leapcell will automatically redeploy the latest code for you.

If you haven't used Leapcell's deployment service before, you can refer to the tutorial in this article.

Summary

In this tutorial, we successfully migrated the forum's backend storage from an unreliable in-memory list to a robust PostgreSQL database.

However, you may have noticed that our main.py file is cluttered with large HTML strings. This makes the code difficult to maintain.

In the next article, we will introduce the concept of a "template engine," using Jinja2 to separate the HTML code into independent template files, which will simplify the readability and maintainability of the frontend code.

Read on our blog

Related Posts: