sqlmodel

SQLModel for FastAPI

Comprehensive skill for building database-driven FastAPI applications with SQLModel, PostgreSQL, and SQLAlchemy.

Quick Start

Basic Setup

# Install dependencies
pip install sqlmodel psycopg2-binary alembic pytest

# Create database models
from sqlmodel import SQLModel, Field, create_engine
from typing import Optional

class User(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) username: str = Field(unique=True, index=True) email: str = Field(unique=True, index=True)

Create engine and tables

engine = create_engine("postgresql://user:pass@localhost/db") SQLModel.metadata.create_all(engine)

Use in FastAPI

from fastapi import FastAPI, Depends from sqlmodel import Session

app = FastAPI()

def get_session(): with Session(engine) as session: yield session

@app.post("/users") def create_user(user: User, session: Session = Depends(get_session)): session.add(user) session.commit() session.refresh(user) return user

## Reference Documentation

This skill includes comprehensive reference files organized by topic. Read the relevant file based on your needs:

### Core Topics

- **[models.md](references/models.md)** - SQLModel basics, field types, constraints, table configuration, request/response models, computed fields, JSON fields, UUID keys, composite primary keys

- **[relationships.md](references/relationships.md)** - One-to-many, one-to-one, many-to-many relationships, cascade deletes, self-referential relationships, lazy vs eager loading, association object pattern

- **[sessions.md](references/sessions.md)** - Database engine setup, session management, FastAPI dependency injection, connection pooling, async sessions, multiple databases, transaction control

- **[crud.md](references/crud.md)** - Create, read, update, delete operations, bulk operations, upsert patterns, soft deletes, transaction patterns, FastAPI endpoint integration

- **[queries.md](references/queries.md)** - Where clauses, ordering, pagination, aggregations, joins, subqueries, dynamic filtering, full-text search, JSON queries, window functions, exists queries

### Advanced Topics

- **[migrations.md](references/migrations.md)** - Alembic setup and configuration, creating and applying migrations, migration operations, data migrations, branching and merging, production workflow, FastAPI integration

- **[testing.md](references/testing.md)** - Test database setup, FastAPI TestClient integration, testing CRUD operations, testing relationships, fixtures, parametrized tests, database isolation, coverage

- **[performance.md](references/performance.md)** - Connection pooling optimization, query optimization, N+1 problem solutions, indexing strategies, bulk operations, pagination best practices, caching, read replicas, batch processing

- **[integration.md](references/integration.md)** - FastAPI project structure, application lifespan, router implementation, custom dependencies, response models with relationships, error handling, middleware, background tasks, WebSocket integration

- **[advanced.md](references/advanced.md)** - Transaction management, nested transactions, cascading deletes, soft deletes, event listeners, optimistic locking, database constraints, custom field types, security best practices, monitoring and logging

## Common Workflows

### Creating a New Model

1. Define the model in your models file
2. Add relationships if needed
3. Create request/response schemas
4. Generate migration: `alembic revision --autogenerate -m "Add model"`
5. Review and apply migration: `alembic upgrade head`
6. Implement CRUD functions
7. Create API endpoints
8. Write tests

### Setting Up Database

1. Install dependencies: `pip install sqlmodel psycopg2-binary alembic`
2. Create database configuration in `database.py`
3. Define models in `models.py`
4. Initialize Alembic: `alembic init alembic`
5. Configure Alembic for SQLModel (see [migrations.md](references/migrations.md))
6. Create initial migration
7. Set up dependency injection for sessions

### Optimizing Performance

1. Add indexes on frequently queried columns (see [models.md](references/models.md))
2. Use eager loading for relationships (see [relationships.md](references/relationships.md))
3. Configure connection pooling (see [sessions.md](references/sessions.md))
4. Implement pagination (see [queries.md](references/queries.md))
5. Use bulk operations for multiple inserts/updates (see [crud.md](references/crud.md))
6. Add query caching if needed (see [performance.md](references/performance.md))

### Adding Relationships

1. Define foreign key in child model
2. Add `Relationship` field in both models
3. Use `back_populates` to link them
4. For many-to-many, create link table
5. Configure cascade behavior if needed (see [relationships.md](references/relationships.md))
6. Update migrations
7. Test relationship loading

## When to Use Each Reference

- **Starting a new project?** Read: sessions.md → models.md → integration.md
- **Need relationships?** Read: relationships.md
- **Writing queries?** Read: queries.md
- **Performance issues?** Read: performance.md → queries.md
- **Setting up testing?** Read: testing.md
- **Database migrations?** Read: migrations.md
- **Building CRUD endpoints?** Read: crud.md → integration.md
- **Advanced features?** Read: advanced.md

## Best Practices Summary

### Model Design

- Use `Optional[int]` with `default=None` for auto-increment primary keys
- Add indexes to foreign keys and frequently queried fields
- Use enums for status/category fields
- Separate table models from request/response models
- Use mixins for common fields (created_at, updated_at)

### Session Management

- Always use dependency injection in FastAPI endpoints
- Use context managers (`with Session()`) for manual sessions
- Configure connection pooling for production
- Set `pool_pre_ping=True` to handle stale connections

### Queries

- Use eager loading to avoid N+1 queries
- Add appropriate indexes before querying large datasets
- Use cursor-based pagination for large result sets
- Use `select()` for all queries instead of legacy query API

### Migrations

- Always review auto-generated migrations before applying
- Test migrations locally before production
- Make migrations reversible (implement both upgrade and downgrade)
- Use separate migrations for schema and data changes

### Testing

- Use SQLite in-memory database for tests
- Use fixtures for test data
- Override FastAPI dependencies in tests
- Test both success and failure cases

### Performance

- Index foreign keys and frequently queried columns
- Use bulk operations for multiple inserts/updates
- Configure appropriate pool sizes based on load
- Monitor slow queries and optimize them

### Security

- Never use string formatting for queries (use parameterized queries)
- Hash passwords with bcrypt or similar
- Validate all user input with Pydantic
- Use environment variables for database credentials
- Handle database errors gracefully without exposing internals

## Example Project Structure

```text
app/
├── __init__.py
├── main.py              # FastAPI app with lifespan
├── database.py          # Engine and session setup
├── models.py            # SQLModel definitions
├── crud.py              # CRUD operations
├── dependencies.py      # FastAPI dependencies
├── config.py            # Settings with pydantic-settings
├── routers/
│   ├── __init__.py
│   ├── users.py
│   └── posts.py
└── tests/
    ├── __init__.py
    ├── conftest.py      # Test fixtures
    ├── test_users.py
    └── test_posts.py

alembic/
├── versions/
│   └── *.py             # Migration files
├── env.py               # Alembic configuration
└── script.py.mako

.env                     # Environment variables
alembic.ini             # Alembic config
pyproject.toml          # Dependencies

Troubleshooting

Common Issues

Import errors with SQLModel models:

• Ensure all models are imported in alembic/env.py
• Import models before calling SQLModel.metadata.create_all()

N+1 query problems:

• Use selectinload() or joinedload() for relationships
• Check query logs with echo=True on engine

Connection pool exhausted:

• Increase pool_size and max_overflow
• Ensure sessions are properly closed (use context managers)
• Check for long-running transactions

Migration conflicts:

• Use alembic heads to check for multiple heads
• Merge branches with alembic merge
• Resolve conflicts manually in migration files

Slow queries:

• Add indexes on queried columns
• Use EXPLAIN ANALYZE to check query plan
• Consider using read replicas for read-heavy workloads

Additional Resources

For detailed information on specific topics, refer to the reference files in the references/ directory. Each file contains comprehensive examples and patterns for that specific area.