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:
$ 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:
$ 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:
$ 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:
$ 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:
$ 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:
$ 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 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 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¶
-
Connection Security: Use environment variables for your database credentials instead of hardcoding them
-
Schema Selection: Be specific about which schemas to include with
--schema
to improve generation speed -
Data Types: Review the generated models to ensure MySQL-specific types are correctly mapped
-
Relationships: Test relationship navigation in the generated models to confirm proper setup
-
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.