SQLAlchemy setup with FastAPI

Introduction

We use SQLAlchemy with FastAPI to easily connect and work with databases in a clean and organized way.

You want to save user data from a web form into a database.

You need to read and show data from a database on a website.

You want to update or delete records in a database through an API.

You are building a web app that needs to store and manage data persistently.

Syntax

FastAPI

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"

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

create_engine connects to the database.

SessionLocal creates sessions to talk to the database.

Examples

Use this URL to connect to a local SQLite database file named test.db.

FastAPI

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"

This creates the database engine. The connect_args is needed for SQLite to allow multiple threads.

FastAPI

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

This sets up a session factory to create sessions that manage database transactions.

FastAPI

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

This is the base class for all database models you will create.

FastAPI

Base = declarative_base()

Sample Program

This example shows how to set up SQLAlchemy with FastAPI. It creates a User model, connects to a SQLite database, and adds a POST endpoint to create users.

FastAPI

from fastapi import FastAPI, Depends
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"

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

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

Base.metadata.create_all(bind=engine)

app = FastAPI()

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

@app.post("/users/")
async def create_user(name: str, db: Session = Depends(get_db)):
    user = User(name=name)
    db.add(user)
    db.commit()
    db.refresh(user)
    return {"id": user.id, "name": user.name}

Important Notes

Always close the database session after use to avoid connection leaks.

Use Base.metadata.create_all(bind=engine) once to create tables in the database.

Use dependency injection (Depends) in FastAPI to get a database session in your path functions.

Summary

SQLAlchemy helps connect FastAPI apps to databases easily.

Set up engine, session, and base to start using SQLAlchemy.

Use sessions to add, read, update, or delete data safely.