Practice & Assessment
Test your understanding of Database Integration with SQLAlchemy
Multiple Choice Questions
5Why should you call `db.refresh(db_user)` after `db.commit()` when creating a new record?
What does `--autogenerate` do in `alembic revision --autogenerate -m 'description'`?
In async SQLAlchemy, why is `session.query()` replaced with `select()`?
What is the purpose of `expire_on_commit=False` in `async_sessionmaker`?
Where should database CRUD logic live in a well-structured FastAPI project?
Coding Challenges
1Blog Post CRUD API with SQLAlchemy
Build a FastAPI app connected to SQLite (use `sqlite:///./blog.db`). Define a `Post` ORM model with `id` (auto-increment), `title` (String, not null), `body` (String), `published` (Boolean, default False), `created_at` (DateTime, default now). Create Pydantic schemas `PostCreate`, `PostUpdate` (all optional fields), and `PostOut`. Implement routes: GET `/posts/` (skip, limit query params), GET `/posts/{id}` (404 if missing), POST `/posts/` (201), PATCH `/posts/{id}` updating only provided fields, DELETE `/posts/{id}` (204). CRUD logic must be in a separate `crud.py`. Run `Base.metadata.create_all` on startup. Inputs: JSON bodies per schema. Outputs: PostOut schema for all reads. Estimated time: 25-30 minutes.
Mini Project
User and Article Platform API
Build a multi-entity REST API using all module concepts. Define two ORM models: `User` (id, email unique, hashed_password, is_active, created_at) and `Article` (id, title, content, is_published, owner_id ForeignKey users.id, created_at). Set up SQLAlchemy with a PostgreSQL (or SQLite) connection in `database.py`, define a `get_db` yield dependency, and create Pydantic schemas separating input and output for both models (UserCreate, UserOut, ArticleCreate, ArticleOut includes owner email). Implement CRUD layer in `crud.py`. Routes in separate router files: users router with GET list, GET by id, POST; articles router with GET list (filter by is_published), GET by id, POST (assigns owner_id), PATCH to publish, DELETE. Initialize Alembic and generate the initial migration. All foreign key relationships must be enforced at the database level.