How to Connect to Database in FastAPI: Simple Guide

To connect to a database in FastAPI, use an async database library like SQLAlchemy with Databases or SQLModel. Define your database URL, create an engine, and use dependency injection to access the database session in your routes.

Syntax

Here is the basic syntax to connect FastAPI to a database using SQLAlchemy and Databases library:

• DATABASE_URL: Your database connection string.
• database = Database(DATABASE_URL): Creates a database connection instance.
• engine = create_engine(DATABASE_URL): Creates SQLAlchemy engine for ORM.
• Base = declarative_base(): Base class for your models.
• async def connect_db(): Connects to the database on app startup.
• async def disconnect_db(): Disconnects on shutdown.
• Depends(get_db): Dependency to get a database session in routes.

python

from fastapi import FastAPI, Depends
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from databases import Database

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

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

app = FastAPI()

@app.on_event("startup")
async def connect_db():
    await database.connect()

@app.on_event("shutdown")
async def disconnect_db():
    await database.disconnect()

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

Example

This example shows a FastAPI app connecting to a SQLite database, creating a simple user table, and adding a user via an API endpoint.

python

from fastapi import FastAPI, Depends
from pydantic import BaseModel
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

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

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

app = FastAPI()

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

Base.metadata.create_all(bind=engine)

class UserCreate(BaseModel):
    name: str

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

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

Output

POST /users/ with JSON {"name": "Alice"} returns {"id": 1, "name": "Alice"}

Common Pitfalls

Common mistakes when connecting FastAPI to a database include:

• Not closing the database session, which can cause connection leaks.
• Using synchronous database calls in async routes, blocking the event loop.
• Forgetting to create tables before querying, causing errors.
• Incorrect database URL format.

python

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

# Wrong: Not closing session

def get_db_wrong():
    db = SessionLocal()
    return db  # No close, causes connection leak

# Right: Use try-finally to close session

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

Quick Reference

Tips for connecting FastAPI to databases:

• Use async database libraries like Databases or SQLModel for async support.
• Always close your database sessions to avoid leaks.
• Use dependency injection with Depends to manage sessions cleanly.
• Test your database URL and connection before running the app.

Key Takeaways

Use SQLAlchemy with async support libraries like Databases or SQLModel to connect FastAPI to databases.

Manage database sessions with dependency injection and always close them to prevent leaks.

Create your database tables before querying to avoid runtime errors.

Use async functions for database operations to keep FastAPI responsive.

Validate your database URL format and connection before deploying your app.