SQL Databases with SQLModel
Part of: FastAPI Basics
Learn to integrate SQL databases with FastAPI using SQLModel for data persistence and CRUD operations.
What You'll Learn
- Learn to set up SQLModel with FastAPI
- Understand database models and table creation
- Implement CRUD operations with SQL databases
- Handle database sessions and connections
- Work with multiple models for security
Theory and Concepts
SQL Databases with SQLModel
🎯 What You'll Learn
- Set up SQLModel with FastAPI for database operations
- Create database models with proper field configurations
- Implement comprehensive CRUD operations (Create, Read, Update, Delete)
- Handle database sessions and connections using dependency injection
- Use multiple models for enhanced security and API design
- Configure database engines and startup events
📚 Theory
SQLModel is a library created by the same author as FastAPI, designed specifically to work seamlessly with FastAPI applications that need SQL databases. It combines the power of SQLAlchemy (for database operations) with Pydantic (for data validation).
Multiple Models Pattern for Security
The official FastAPI approach uses multiple models to enhance security and API design:
1. Base Model with Shared Fields:
[Code Example]
2. Table Model for Database:
[Code Example]
3. Public Model for API Responses:
[Code Example]
4. Create Model for Input Validation:
[Code Example]
5. Update Model for Partial Updates:
[Code Example]
Key Field Configurations:
- primary_key=True: Marks the field as the table's primary key
- index=True: Creates a database index for faster queries
- default=None: Allows the database to auto-generate values (especially for IDs)
2. Database Engine Setup:
[Code Example]
3. Database Session Management:
[Code Example]
4. Database Table Creation:
[Code Example]
CRUD Operations with Multiple Models
Create Operation with Security:
[Code Example]
Read Operations with Public Models:
[Code Example]
Update Operation with Partial Updates:
[Code Example]
Delete Operation:
[Code Example]
🔧 Key Concepts
- Multiple Models Pattern: Use different models for different purposes (security, validation)
- Table Models: Classes with table=True represent database tables (Hero)
- Data Models: Classes without table=True are for API contracts (HeroPublic, HeroCreate)
- Model Inheritance: Use base classes to share common fields (HeroBase)
- Response Models: Use response_model to control what data is returned to clients
- Partial Updates: Use exclude_unset=True to update only provided fields
- Database Engine: Manages database connections and should be created once
- Sessions: Handle individual transactions and should be injected per request
- Dependency Injection: Use Depends() to inject database sessions into endpoints
- Query Builder: Use select() for complex database queries
- Pagination: Implement with offset and limit parameters
💡 Best Practices
- Multiple Models for Security: Never expose sensitive data like secret_name in public APIs
- Use Response Models: Always specify response_model to control API responses
- Model Inheritance: Use base models to avoid duplicating field definitions
- Partial Updates: Use exclude_unset=True for PATCH operations to update only provided fields
- Primary Key Required: Every table model must have a primary key field
- Session Per Request: Use dependency injection for database sessions to ensure thread safety
- Proper Error Handling: Always check if records exist before operations
- Database Startup: Create tables during application startup with @app.on_event("startup")
- Connection Arguments: Use check_same_thread=False for SQLite with FastAPI
- Query Limits: Always limit query results to prevent performance issues
- Transaction Management: Use session.commit() to save changes and session.refresh() to get updated data
🔗 Additional Resources
- SQLModel Documentation
- SQLAlchemy Documentation
- FastAPI SQL Databases Tutorial
Helpful Hint
SQLModel combines SQLAlchemy's power with Pydantic's validation - use table=True for database models and separate data models for API contracts.