SQLAlchemy setup with FastAPI - Step-by-Step Execution

Concept Flow - SQLAlchemy setup with FastAPI

Import SQLAlchemy and FastAPI

↓

Create SQLAlchemy Engine

↓

Define SessionLocal class

↓

Create Base class for models

↓

Define Dependency to get DB session

↓

Use Dependency in FastAPI path operations

↓

Handle DB session lifecycle

This flow shows how FastAPI and SQLAlchemy connect: import, engine setup, session creation, model base, dependency injection, and session cleanup.

Execution Sample

FastAPI

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from fastapi import FastAPI, Depends

engine = create_engine('sqlite:///./test.db')
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)
Base = declarative_base()

This code sets up the database engine, session factory, and base class for models in FastAPI using SQLAlchemy.

Execution Table

Step	Action	Code Line	Result
1	Import SQLAlchemy and FastAPI modules	from sqlalchemy import create_engine ...	Modules ready to use
2	Create engine with SQLite URL	engine = create_engine('sqlite:///./test.db')	Engine connected to SQLite file
3	Create sessionmaker bound to engine	SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)	Session factory ready
4	Create Base class for models	Base = declarative_base()	Base class for ORM models created
5	Define get_db dependency	def get_db(): ...	Function to provide DB session
6	Use get_db in FastAPI route	@app.get('/') ...	Route can access DB session
7	Yield session and close after use	with get_db() as db: ...	Session lifecycle managed
8	Exit	End of setup	Setup complete, ready for DB operations

💡 All setup steps completed, FastAPI app ready to use SQLAlchemy sessions

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
engine	None	Engine object (SQLite)	Engine object	Engine object	Engine object	Engine object
SessionLocal	None	None	Sessionmaker bound to engine	Sessionmaker	Sessionmaker	Sessionmaker
Base	None	None	None	Declarative base class	Declarative base	Declarative base
get_db	None	None	None	None	Dependency function	Dependency function

Key Moments - 3 Insights

Why do we create a sessionmaker instead of using the engine directly?

Sessionmaker creates sessions that manage transactions and queries. The engine only manages the connection. See execution_table step 3 where SessionLocal is created.

What does the declarative_base() function do?

It creates a base class for ORM models to inherit from, linking them to SQLAlchemy. See execution_table step 4.

How does FastAPI get a database session in a route?

Using the get_db dependency function that yields a session and closes it after use. See execution_table steps 5 and 6.

Visual Quiz - 3 Questions

Look at the execution_table, what is the role of the 'engine' variable after step 2?

A It manages database sessions

B It defines ORM models

C It connects to the SQLite database file

D It is a FastAPI app instance

💡 Hint

Check the 'Result' column in step 2 describing the engine

At which step is the function that provides a database session to FastAPI routes defined?

A Step 3

B Step 5

C Step 6

D Step 4

💡 Hint

Look for the step mentioning 'Define get_db dependency'

If we did not close the session after use, what would happen according to the flow?

A The database connection would remain open, causing resource leaks

B The engine would stop working

C FastAPI would crash immediately

D The Base class would not be created

💡 Hint

Refer to execution_table step 7 about session lifecycle management

Concept Snapshot

SQLAlchemy setup with FastAPI:
- Create engine with create_engine()
- Make session factory with sessionmaker(bind=engine, autocommit=False, autoflush=False)
- Define Base with declarative_base()
- Use dependency get_db() to provide sessions
- Close sessions after use to avoid leaks
- Inject DB session in FastAPI routes with Depends

Full Transcript

This visual execution shows how to set up SQLAlchemy with FastAPI. First, import necessary modules. Then create an engine connected to a SQLite database file. Next, create a sessionmaker bound to this engine to generate sessions. Define a Base class for ORM models using declarative_base. Define a dependency function get_db that yields a session and closes it after use. Use this dependency in FastAPI routes to access the database. This setup ensures proper session lifecycle management and clean integration between FastAPI and SQLAlchemy.

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