Describe SQLAlchemy setup in FastAPI from database config to endpoint

What's really being asked

This question checks whether you can wire up a real relational database stack in FastAPI without relying on copy-paste boilerplate. The interviewer wants to see that you understand object-relational mapping, connection pooling, dependency injection, and request-scoped session lifecycle. At the senior level, they also care that you know why each piece exists rather than simply naming files.

The full answer

First, the engine. You create a single SQLAlchemy engine using create_engine with a PostgreSQL connection string and configure a connection pool. This engine is a global singleton because it manages TCP connections to the database. Second, the declarative base and models. You define a Base class and a User model with columns, types, and a table name so SQLAlchemy knows how to map Python objects to PostgreSQL rows. Third, the session factory and dependency. You use sessionmaker bound to the engine, then write an async or sync dependency that yields a Session, performs rollback on exception, and always closes the session in a finally block. Fourth, the endpoint. You declare the session as a FastAPI dependency using Depends, run a query such as db.query(User).all(), and return the results. The dependency injection system ensures every request gets its own session.

The mistakes people make

A major red flag is creating a brand new engine inside the endpoint function. Engines are expensive to initialize and manage connection pools, so they belong at module level. Another red flag is using a single global Session object shared by every request, which is not thread-safe or async-safe and will corrupt transactions. Candidates also stumble by forgetting to close sessions, which leaks connections and exhausts the pool. Finally, mixing sync SQLAlchemy with async path operations without using AsyncSession and an async engine shows a lack of attention to FastAPI's concurrency model.

What usually comes next

The interviewer may ask how you would handle database migrations, which opens the door to Alembic. They might ask how to test this endpoint, where the correct pattern is to override the session dependency with a test database. They could also ask about connection pool tuning for production, such as setting pool_size and max_overflow, or how to use lifespan events to create tables on startup instead of doing it manually.

A concrete example

Imagine a PostgreSQL URL of postgresql://user:pass@localhost/db. You define engine = create_engine(DATABASE_URL, pool_size=10) at the top of database.py. You define class User(Base) with id and email columns. You define SessionLocal = sessionmaker(bind=engine). Then you write a get_db dependency that instantiates db = SessionLocal(), yields it, and closes it in a finally block. In the FastAPI endpoint @app.get("/users") you write def read_users(db: Session = Depends(get_db)): return db.query(User).all(). This gives every caller a clean session while reusing the same connection pool.