FastAPI SQLAlchemy 2, Alembic and PostgreSQL Setup Tutorial

In this tutorial, we will guide you through the process of setting up FastAPI with SQLAlchemy 2, Alembic, and PostgreSQL. This setup will allow you to easily build and manage databases for your FastAPI applications.

Step 1: Install FastAPI and SQLAlchemy

First, you need to install FastAPI and SQLAlchemy. You can do this by running the following commands in your terminal:

pip install fastapi
pip install sqlalchemy

Step 2: Create a SQLAlchemy Model

Next, you need to create a SQLAlchemy model that represents your database table. Here is an example model:

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

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

Step 3: Create a Database Connection

You need to create a database connection using SQLAlchemy’s create_engine method. Here is an example of how to do this:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "postgresql://username:password@localhost/dbname"

engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)

Step 4: Create Alembic Migration Scripts

Next, you need to create Alembic migration scripts to manage your database schema. You can do this by running the following command in your terminal:

alembic init alembic

This will create an Alembic directory with a migration script template. You can modify this template to customize your migration scripts.

Step 5: Run Alembic Migrations

Finally, you need to run Alembic migrations to apply your changes to the database. You can do this by running the following command:

alembic upgrade head

This will apply any pending migrations to the database.

That’s it! You now have a fully set up FastAPI application with SQLAlchemy 2, Alembic, and PostgreSQL. You can start building your application and managing your database with ease.