This skill provides comprehensive tools for generating database schemas with proper relations, migrations, and ORM/ODM models for PostgreSQL, MongoDB, and SQLite that integrate seamlessly with FastAPI applications.
When to Use This Skill
Use this skill when you need to:
- Generate database schemas with proper relationships and constraints
- Create ORM/ODM models for SQL (SQLAlchemy) or NoSQL (ODMantic/PyMongo) databases
- Set up database migrations for schema evolution
- Define proper indexing strategies for performance
- Generate FastAPI integration patterns for database operations
- Create database connection pools and session management
Supported Database Types
SQL Databases
- PostgreSQL: Advanced features, JSON support, full-text search
- SQLite: Lightweight, file-based, perfect for development/testing
- MySQL: Traditional SQL with comprehensive feature set (coming soon)
NoSQL Databases
- MongoDB: Document-based with flexible schema and rich query language
Core Workflow
1. Database Type Selection
- Choose between PostgreSQL, MongoDB, or SQLite based on requirements
- Consider factors: scalability, ACID compliance, document flexibility, deployment complexity
2. Schema Design
- Define entities and their relationships
- Plan indexes for optimal query performance
- Consider data normalization vs. denormalization trade-offs
3. Model Generation
- Create appropriate ORM/ODM models based on database type
- Define proper field types and constraints
- Implement relationship mappings
4. Migration Strategy
- Generate migration files for schema evolution
- Plan rollback strategies for safe deployments
- Consider data migration needs
5. FastAPI Integration
- Set up database connection pools
- Implement dependency injection for database sessions
- Create proper error handling for database operations
Database-Specific Patterns
PostgreSQL Schema Generation
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Index
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from database.base import Base
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
email = Column(String, unique=True, index=True, nullable=False)
name = Column(String, nullable=False)
created_at = Column(DateTime(timezone=True), server_default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
# Relationships
posts = relationship("Post", back_populates="author", cascade="all, delete-orphan")
# PostgreSQL-specific indexes
Index("idx_user_email", "email", unique=True)
Index("idx_user_created_at", "created_at")
MongoDB Schema Generation (ODMantic)
from odmantic import Model, Field, Index
from datetime import datetime
from typing import List, Optional
class User(Model):
email: str = Field(unique=True, regex=r"^[\w\.-]+@[\w\.-]+\.\w+$")
name: str
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)
class Config:
collection = "users"
indexes = [Index("email", unique=True), Index("created_at")]
class Post(Model):
title: str
content: str
author_id: str = Field(foreign_key="User.id")
created_at: datetime = Field(default_factory=datetime.utcnow)
# Embedded relationships in MongoDB
tags: List[str] = []
metadata: Optional[dict] = {}
class Config:
collection = "posts"
indexes = [Index("author_id"), Index("created_at"), Index("tags")]
SQLite Schema Generation
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from database.base import Base
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
email = Column(String, unique=True, index=True, nullable=False)
name = Column(String, nullable=False)
created_at = Column(DateTime, default=func.now())
updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
posts = relationship("Post", back_populates="author")
Migration Patterns
Alembic Migration Example
"""Add user profile fields
Revision ID: abc123def456
Revises: 7d5c8b1a2c3d
Create Date: 2023-10-15 10:30:00.000000
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers
revision = "abc123def456"
down_revision = "7d5c8b1a2c3d"
branch_labels = None
depends_on = None
def upgrade():
# Add new columns
op.add_column("users", sa.Column("bio", sa.Text(), nullable=True))
op.add_column("users", sa.Column("avatar_url", sa.String(500), nullable=True))
op.add_column(
"users", sa.Column("is_verified", sa.Boolean(), nullable=True, default=False)
)
# Create indexes
op.create_index("ix_users_bio", "users", ["bio"])
op.create_index("ix_users_is_verified", "users", ["is_verified"])
def downgrade():
# Remove columns (in reverse order)
op.drop_index("ix_users_is_verified")
op.drop_index("ix_users_bio")
op.drop_column("users", "is_verified")
op.drop_column("users", "avatar_url")
op.drop_column("users", "bio")
FastAPI Integration Patterns
Database Dependency
from fastapi import Depends
from sqlalchemy.orm import Session
from database.session import get_db
async def get_current_user(
token: str = Security(oauth2_scheme), db: Session = Depends(get_db)
):
credentials_exception = HTTPException(
status_code=status.HTTP_401_UNAUTHORIZED,
detail="Could not validate credentials",
headers={"WWW-Authenticate": "Bearer"},
)
try:
payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
email: str = payload.get("sub")
if email is None:
raise credentials_exception
except JWTError:
raise credentials_exception
user = db.query(User).filter(User.email == email).first()
if user is None:
raise credentials_exception
return user
Best Practices
Performance Optimization
- Use proper indexing strategies
- Implement connection pooling
- Use eager loading for related data when needed
- Consider caching strategies for read-heavy operations
Security Considerations
- Sanitize all database inputs
- Use parameterized queries to prevent injection
- Implement proper authentication and authorization
- Encrypt sensitive data at rest
Scalability Patterns
- Plan for database sharding if needed
- Use read replicas for read-heavy operations
- Implement proper database connection management
- Consider database-specific optimization techniques
Advanced Features
Relationship Handling
- One-to-Many relationships with proper cascading
- Many-to-Many relationships with join tables
- One-to-One relationships for specialized use cases
- Self-referencing relationships for hierarchical data
Data Validation
- Database-level constraints
- Application-level validation through ORM/ODM
- Custom validation functions
- Data integrity checks
References
- See POSTGRESQL.md for PostgreSQL-specific patterns
- See MONGODB.md for MongoDB schema design
- See SQLITE.md for SQLite optimization
- See MIGRATIONS.md for migration strategies
- See RELATIONS.md for relationship patterns
- See FASTAPI_INTEGRATION.md for FastAPI database patterns