Skip to content

Working with MySQL Databases

This guide demonstrates how to use supabase-pydantic with MySQL databases. As of September 2025, the tool now fully supports generating Pydantic and SQLAlchemy models directly from MySQL databases.

Please note ...

MySQL support is a stable feature. If you encounter any issues specific to MySQL database connections, please report them.

Prerequisites

You will need to have:

  • Python 3.10 or higher
  • A MySQL database
  • The supabase-pydantic package installed

Connecting to MySQL

To generate models from a MySQL database, use the --db-type mysql flag with your connection:

Connect to Remote MySQL Database
$ sb-pydantic gen --type pydantic --framework fastapi --db-type mysql --db-url mysql://username:password@hostname:3306/database_name

2023-09-12 14:30:15 - INFO - MySQL connection opened successfully
2023-09-12 14:30:17 - INFO - Processing database: database_name
2023-09-12 14:30:19 - INFO - MySQL connection closed successfully
2023-09-12 14:30:20 - INFO - Generating FastAPI Pydantic models...
2023-09-12 14:30:22 - INFO - FastAPI Pydantic models generated successfully: /path/to/your/project/entities/fastapi/schemas_latest.py
2023-09-12 14:30:23 - INFO - File formatted successfully: /path/to/your/project/entities/fastapi/schemas_latest.py

Or with a local database:

Connect to Local MySQL Database
$ sb-pydantic gen --type pydantic --framework fastapi --db-type mysql --db-url mysql://root:password@localhost:3306/mydb

2023-09-12 14:30:15 - INFO - MySQL connection opened successfully
2023-09-12 14:30:17 - INFO - Processing database: mydb
2023-09-12 14:30:19 - INFO - MySQL connection closed successfully
2023-09-12 14:30:20 - INFO - Generating FastAPI Pydantic models...
2023-09-12 14:30:22 - INFO - FastAPI Pydantic models generated successfully: /path/to/your/project/entities/fastapi/schemas_latest.py
2023-09-12 14:30:23 - INFO - File formatted successfully: /path/to/your/project/entities/fastapi/schemas_latest.py

Generating Different Model Types

Pydantic Models

Generate Pydantic models from a MySQL database:

Generate Pydantic Models
$ sb-pydantic gen --type pydantic --framework fastapi --db-type mysql --db-url mysql://username:password@hostname:3306/database_name

SQLAlchemy Models

Generate SQLAlchemy ORM models from a MySQL database:

Generate SQLAlchemy Models
$ sb-pydantic gen --type sqlalchemy --db-type mysql --db-url mysql://username:password@hostname:3306/database_name

2023-09-12 14:30:15 - INFO - MySQL connection opened successfully
2023-09-12 14:30:17 - INFO - Processing database: database_name
2023-09-12 14:30:19 - INFO - MySQL connection closed successfully
2023-09-12 14:30:20 - INFO - Generating SQLAlchemy models...
2023-09-12 14:30:22 - INFO - SQLAlchemy models generated successfully: /path/to/your/project/entities/sqlalchemy_latest.py
2023-09-12 14:30:23 - INFO - File formatted successfully: /path/to/your/project/entities/sqlalchemy_latest.py

Customizing Output

You can customize the output directory and specify schemas:

Customize Output Directory and Schema
$ sb-pydantic gen --type pydantic --framework fastapi --db-type mysql --db-url mysql://username:password@hostname:3306/database_name --dir ./my_models --schema main

2023-09-12 14:30:15 - INFO - MySQL connection opened successfully
2023-09-12 14:30:17 - INFO - Processing schema: main
2023-09-12 14:30:19 - INFO - MySQL connection closed successfully
2023-09-12 14:30:20 - INFO - Generating FastAPI Pydantic models...
2023-09-12 14:30:22 - INFO - FastAPI Pydantic models generated successfully: /path/to/your/project/my_models/fastapi/schemas_latest.py
2023-09-12 14:30:23 - INFO - File formatted successfully: /path/to/your/project/my_models/fastapi/schemas_latest.py

Troubleshooting

Type Mapping Errors

If you encounter issues with specific MySQL data types not mapping correctly:

Verbose Mode
$ sb-pydantic gen --type pydantic --framework fastapi --db-type mysql --db-url mysql://username:password@hostname:3306/database_name --debug

Debug mode will show more details about the type mapping process, helping you identify any issues.

Support for MySQL Constraints

The following MySQL constraints are properly detected and mapped to your models:

  • Primary keys (converted to primary key fields)
  • Foreign keys (converted to relationship fields)
  • Unique constraints (annotated with @Field(unique=True))
  • Default values (provided as field defaults)
  • NOT NULL constraints (reflected in model typing)

Example Output

Here's a simplified example of the generated Pydantic model:

Generated Pydantic Model Example
# Generated by supabase-pydantic 0.4.0
# Do not modify this file manually

from datetime import date, datetime
from decimal import Decimal
from typing import List, Optional

from pydantic import BaseModel, Field


class User(BaseModel):
    id: int = Field(primary_key=True)
    username: str = Field(max_length=50, unique=True)
    email: str = Field(max_length=100, unique=True)
    password_hash: str = Field(max_length=255)
    created_at: datetime
    updated_at: Optional[datetime] = None
    is_active: bool = True


class Post(BaseModel):
    id: int = Field(primary_key=True)
    title: str = Field(max_length=100)
    content: str
    user_id: int = Field(foreign_key="user.id")
    published_date: date
    views: Optional[int] = 0
    user: Optional[User] = None

And here's a simplified example of the generated SQLAlchemy model:

Generated SQLAlchemy Model Example
# Generated by supabase-pydantic 0.4.0
# Do not modify this file manually

from datetime import date, datetime
from decimal import Decimal
from typing import Optional

from sqlalchemy import Boolean, Column, Date, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    created_at = Column(DateTime, nullable=False)
    updated_at = Column(DateTime)
    is_active = Column(Boolean, default=True)

    posts = relationship("Post", back_populates="user")


class Post(Base):
    __tablename__ = "post"

    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    content = Column(Text, nullable=False)
    user_id = Column(Integer, ForeignKey("user.id"), nullable=False)
    published_date = Column(Date, nullable=False)
    views = Column(Integer, default=0)

    user = relationship("User", back_populates="posts")

Best Practices

  1. Connection Security: Use environment variables for your database credentials instead of hardcoding them

  2. Schema Selection: Be specific about which schemas to include with --schema to improve generation speed

  3. Data Types: Review the generated models to ensure MySQL-specific types are correctly mapped

  4. Relationships: Test relationship navigation in the generated models to confirm proper setup

  5. Versioning: Keep track of model versions when regenerating after database changes

Conclusion

With MySQL support, supabase-pydantic now offers greater flexibility for developers working with different database systems. You can seamlessly generate type-safe models from your MySQL database and integrate them with FastAPI or other Python frameworks.

Whether you're working with PostgreSQL, Supabase, or MySQL, the workflow remains consistent, allowing you to focus on building your application rather than writing boilerplate code. Report any issues you encounter in our GitHub repository.