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

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

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

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

Hint

Import FastAPI and create an instance called app.

Practice

1. What is the main purpose of SessionLocal in a FastAPI app using SQLAlchemy?

easy

A. To create a new database session for each request

B. To define the database schema

C. To connect directly to the database engine

D. To store user authentication data

Solution

1. Step 1: Understand the role of SessionLocal

   SessionLocal is a session factory that creates new database sessions for each request to ensure safe and isolated database operations.

2. Step 2: Differentiate from other components

   The database schema is defined by models, the engine connects to the database, and user data is unrelated to SessionLocal.

3. Final Answer:

   To create a new database session for each request -> Option A

4. Quick Check:

   SessionLocal creates new sessions per request [OK]

Hint: SessionLocal always means a new session per request [OK]

Common Mistakes:

• Confusing SessionLocal with engine
• Thinking SessionLocal defines schema
• Assuming SessionLocal stores user data

2. Which of the following is the correct way to create the SQLAlchemy engine in FastAPI?

easy

A. engine = create_engine('sqlite:///./test.db', connect_args={'check_same_thread': False})

B. engine = create_engine('sqlite:///:memory:')

C. engine = create_engine('postgresql://user:pass@localhost/db')

D. engine = create_engine('mysql://user@localhost/db')

Solution

1. Step 1: Identify the common FastAPI SQLite engine setup

   FastAPI tutorials often use SQLite with the URL 'sqlite:///./test.db' and the argument to allow multiple threads.

2. Step 2: Check options for correctness

   engine = create_engine('sqlite:///./test.db', connect_args={'check_same_thread': False}) matches the typical FastAPI SQLite setup with connect_args to avoid threading errors.

3. Final Answer:

   engine = create_engine('sqlite:///./test.db', connect_args={'check_same_thread': False}) -> Option A

4. Quick Check:

   SQLite engine with check_same_thread=False [OK]

Hint: SQLite needs check_same_thread=False in FastAPI [OK]

Common Mistakes:

• Omitting connect_args causing threading errors
• Using wrong database URL format
• Confusing in-memory with file-based SQLite

3. Given this FastAPI SQLAlchemy session usage, what will print(user.name) output?

from sqlalchemy.orm import Session

def get_user(db: Session, user_id: int):
    return db.query(User).filter(User.id == user_id).first()

user = get_user(db=session, user_id=1)
print(user.name)

medium

A. None

B. The name of the user with id 1

C. Raises AttributeError

D. Raises SQLAlchemyError

Solution

1. Step 1: Understand the query behavior

   The query filters User by id=1 and returns the first match or None if not found.

2. Step 2: Analyze the print statement

   If a user with id=1 exists, user.name prints the name; otherwise, user is None and accessing name would error.

3. Final Answer:

   The name of the user with id 1 -> Option B

4. Quick Check:

   Query returns user object [OK]

Hint: Query.first() returns object or None; here user exists [OK]

Common Mistakes:

• Assuming print outputs None without checking user
• Expecting an error without verifying user exists
• Confusing filter with filter_by syntax

4. Identify the error in this FastAPI SQLAlchemy session usage:

def create_user(db: Session, user: UserCreate):
    db_user = User(name=user.name, email=user.email)
    db.add(db_user)
    # Missing db.commit()
    return db_user

medium

A. User model is not imported

B. db.add() should be db.insert()

C. Missing call to db.commit() to save changes

D. Function should return None

Solution

1. Step 1: Check session usage for saving data

   Adding an object to the session requires calling db.commit() to persist changes to the database.

2. Step 2: Verify other parts of the code

   db.add() is correct, User model import is assumed, and returning the new user is expected.

3. Final Answer:

   Missing call to db.commit() to save changes -> Option C

4. Quick Check:

   db.commit() needed after db.add() [OK]

Hint: Always commit after adding to session [OK]

Common Mistakes:

• Forgetting db.commit() after db.add()
• Using db.insert() instead of db.add()
• Returning wrong type from function

5. You want to set up SQLAlchemy with FastAPI to support multiple database types (SQLite, PostgreSQL) using environment variables. Which approach correctly configures the engine and session?

hard

A. Use sessionmaker() without binding engine

B. Hardcode SQLite URL in create_engine and ignore env vars

C. Create engine without URL and pass URL to sessionmaker

D. Use DATABASE_URL env var, pass it to create_engine, then create SessionLocal with sessionmaker(bind=engine)

Solution

1. Step 1: Use environment variable for database URL

   Reading DATABASE_URL from environment allows flexible switching between databases.

2. Step 2: Create engine with the URL and bind sessionmaker

   Pass the URL to create_engine, then bind the engine to sessionmaker to create SessionLocal.

3. Final Answer:

   Use DATABASE_URL env var, pass it to create_engine, then create SessionLocal with sessionmaker(bind=engine) -> Option D

4. Quick Check:

   Env var URL + engine + sessionmaker(bind=engine) [OK]

Hint: Always bind engine to sessionmaker using env var URL [OK]

Common Mistakes:

• Hardcoding URLs reduces flexibility
• Not binding engine to sessionmaker causes errors
• Passing URL to sessionmaker instead of create_engine