SQLAlchemy setup with FastAPI - Mini Project: Build & Apply

SQLAlchemy setup with FastAPI

📖 Scenario: You are building a simple web API using FastAPI. You want to store and retrieve user data using a database. To do this, you will set up SQLAlchemy to connect to a SQLite database and integrate it with FastAPI.

🎯 Goal: Create a FastAPI app that connects to a SQLite database using SQLAlchemy. Define a User model, configure the database session, and prepare the app to handle database operations.

📋 What You'll Learn

Create a SQLAlchemy Base class and a User model with id and name fields

Set up a SQLite database URL in a variable called SQLALCHEMY_DATABASE_URL

Create a SQLAlchemy engine using the database URL

Create a SessionLocal class for database sessions

Define a FastAPI app instance called app

💡 Why This Matters

🌍 Real World

Setting up SQLAlchemy with FastAPI is a common first step to build web APIs that interact with databases for storing and retrieving data.

💼 Career

Backend developers often need to integrate ORMs like SQLAlchemy with web frameworks like FastAPI to build scalable and maintainable APIs.

1

Create the SQLAlchemy Base and User model

Write code to import declarative_base from sqlalchemy.orm and create a Base class. Then define a User class that inherits from Base with the table name users. Add two columns: id as an integer primary key and name as a string.

FastAPI

# Import declarative_base and create Base
# Define User model with id and name columns
# Your code here

Need a hint?

Use declarative_base() to create the base class. Define User with __tablename__ and columns using Column.

2

Set up the database URL and engine

Create a variable called SQLALCHEMY_DATABASE_URL and set it to the string "sqlite:///./test.db". Then import create_engine from sqlalchemy and create an engine using this URL with connect_args={"check_same_thread": False}.

FastAPI

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy import create_engine

Base = declarative_base()

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

# Set database URL and create engine
# Your code here

Need a hint?

Use create_engine with the SQLite URL and the connect_args parameter for SQLite threading.

3

Create the SessionLocal class for database sessions

Import sessionmaker from sqlalchemy.orm. Then create a SessionLocal variable by calling sessionmaker with autocommit=False, autoflush=False, and bind=engine.

FastAPI

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

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

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

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

# Create SessionLocal class
# Your code here

Need a hint?

Use sessionmaker with the correct parameters to create SessionLocal.

4

Create the FastAPI app instance

Import FastAPI from fastapi. Then create a variable called app and assign it to FastAPI().

FastAPI

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from fastapi import FastAPI

Base = declarative_base()

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

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)

# Create FastAPI app instance
# Your code here

Need a hint?

Import FastAPI and create an instance called app.