MySQL & MariaDB¶

DBWarden treats MySQL (and its fork MariaDB) as first-class backends: every natively supported feature is reverse-engineered, diffed, and emitted as correct DDL.

First-Class Features¶

"First-class" means the round-trip is verified: reverse-engineer a live database with generate-models, feed the output back into make-migrations, and get zero diff.

# Step 1: reverse-engineer your live MySQL/MariaDB database
$ dbwarden generate-models -d primary

# Step 2: feed the generated models back in, zero diff
$ dbwarden make-migrations -d primary
# -> "No new migrations to generate"  (output is empty; your models match the DB exactly)

The following MySQL/MariaDB features are fully supported in this round-trip:

MariaDB-Specific Features¶

Installation¶

Install with the MySQL driver:

uv add "dbwarden[mysql]"

Or with uv:

uv add "dbwarden[mysql]"

Configuration¶

The MySQL backend is enabled by setting database_type="mysql" (or database_type="mariadb") in your dbwarden config:

from dbwarden import database_config

database_config(
    database_name="primary",
    default=True,
    database_type="mysql",
    database_url_sync="mysql+pymysql://user:password@localhost:3306/mydb",
)

The connection URL uses the mysql+pymysql:// scheme (the pymysql driver is included via the [mysql] extra). You can also use any SQLAlchemy-compatible MySQL driver such as mysql+mysqlconnector://.

Declaring Metadata¶

MySQL/MariaDB metadata is declared in a class Meta inner class on the model. This is the only supported surface: mapped_column(info=...) raises DBWardenConfigError.

Table-Level Meta¶

Inherit from MyTableMeta on your class Meta:

from sqlalchemy import Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from dbwarden.databases.mysql import MyTableMeta

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    email: Mapped[str] = mapped_column(String(255))

    class Meta(MyTableMeta):
        my_engine = "InnoDB"
        my_charset = "utf8mb4"
        my_collate = "utf8mb4_unicode_ci"
        my_row_format = "DYNAMIC"
        my_auto_increment = 1000
        comment = "Core user accounts"

MyTableMeta inherits from TableMeta, which provides common attributes shared across all backends:

MySQL-specific MyTableMeta attributes:

For MariaDB, use MdbTableMeta:

from dbwarden.databases.mariadb import MdbTableMeta

class Meta(MdbTableMeta):
    my_engine = "InnoDB"
    mdb_page_compressed = True
    mdb_page_compression_level = 3

MariaDB-specific MdbTableMeta attributes (in addition to all MyTableMeta attributes):

Column-Level Meta¶

Use MyColumnMeta inner classes for per-column metadata. The inner class must be named after the column. Use my = my.field(...) to set column-level options:

from sqlalchemy import Integer, String, Text, TIMESTAMP
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from dbwarden.databases.mysql import MyTableMeta, MyColumnMeta, my

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    email: Mapped[str] = mapped_column(String(255))
    bio: Mapped[str] = mapped_column(Text)
    updated_at: Mapped[str] = mapped_column(TIMESTAMP)

    class Meta(MyTableMeta):
        class id(MyColumnMeta):
            comment = "Primary key"
            my = my.field(unsigned=True)

        class email(MyColumnMeta):
            my = my.field(charset="utf8mb4", collate="utf8mb4_unicode_ci")

        class updated_at(MyColumnMeta):
            my = my.field(on_update="CURRENT_TIMESTAMP")

MyColumnMeta includes common column attributes shared across all backends:

MySQL-specific MyFieldSpec fields (set via my.field(...)):

For MariaDB, use MdbColumnMeta and mdb.field(...):

from dbwarden.databases.mariadb import MdbColumnMeta
from dbwarden.databases.mariadb import mdb

class Meta(MdbTableMeta):
    class id(MdbColumnMeta):
        mdb = mdb.field(invisible=True)

MariaDB-specific MdbFieldSpec fields (set via mdb.field(...)):

Foreign Key Options¶

Foreign key options (ondelete, onupdate) are captured from the database by generate-models and emitted in the ForeignKey constructor:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column

class OrderItem(Base):
    __tablename__ = "order_items"

    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id", ondelete="CASCADE"), nullable=False)

Model Example (Generated)¶

Here is the complete generated model output for a MySQL table with engine, charset, unsigned PK, ON UPDATE, and per-column charset:

from sqlalchemy import BigInteger, Column, Integer, String, TIMESTAMP, Text, text
from sqlalchemy.orm import DeclarativeBase

Base = declarative_base()

from dbwarden.databases.mysql import MyColumnMeta, MyTableMeta, my

class User(Base):
    __tablename__ = 'users'

    id = Column('id', Integer, primary_key=True, nullable=False)
    email = Column('email', String(255), nullable=False)
    bio = Column('bio', Text)
    updated_at = Column('updated_at', TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

    class Meta(MyTableMeta):
        my_engine = 'InnoDB'
        my_charset = 'utf8mb4'
        my_collate = 'utf8mb4_unicode_ci'
        my_row_format = 'DYNAMIC'
        comment = 'Core user accounts'

        class id(MyColumnMeta):
            comment = 'Primary key'
            my = my.field(unsigned=True)

        class email(MyColumnMeta):
            my = my.field(charset='utf8mb4', collate='utf8mb4_unicode_ci')

        class updated_at(MyColumnMeta):
            my = my.field(on_update='CURRENT_TIMESTAMP')

DDL Behavior¶

DDL Is NOT Transactional¶

MySQL and MariaDB DDL is non-transactional: each DDL statement implicitly commits the current transaction. If a migration file contains multiple statements and one fails, the prior DDL cannot be rolled back. This makes MySQL/MariaDB more fragile than PostgreSQL for automated migration runs.

Column Type Changes¶

Emits ALTER TABLE t MODIFY COLUMN c newtype. Unlike PostgreSQL, MySQL requires the full column definition on every MODIFY COLUMN. DBWarden handles this by re-emitting all column attributes (type, unsigned, nullable, default, comment, charset, collate, auto_increment) in a single statement:

ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL COMMENT 'User email';

Column Nullable Changes¶

Emits ALTER TABLE t MODIFY COLUMN c type [NULL | NOT NULL], again with the full column type.

Column Meta Changes¶

When MySQL-specific column metadata changes (unsigned, charset, collate, on_update), DBWarden generates a full MODIFY COLUMN that preserves the column's type, nullable, default, comment, and autoincrement state:

ALTER TABLE users MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

Table Option Changes¶

MySQL table-level option changes generate individual ALTER TABLE statements:

Auto-increment Lifecycle¶

DBWarden supports toggling auto-increment on integer primary key columns. The autoincrement field in your model controls whether a column uses auto-increment:

class User(Base):
    __tablename__ = "users"

    # Autoincrement enabled: same as default behavior
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)

    class Meta(MyTableMeta):
        class id(MyColumnMeta):
            pass  # uses default autoincrement from model

To explicitly disable auto-increment on a PK column:

class User(Base):
    __tablename__ = "users"

    # Plain integer PK: no auto-increment
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=False)

What happens when autoincrement changes:

Comments¶

Unlike PostgreSQL, MySQL has no COMMENT ON syntax. DBWarden generates the correct MySQL syntax:

-- Table comment
ALTER TABLE users COMMENT = 'Core user accounts';

-- Column comment (full MODIFY COLUMN preserving all attributes)
ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL COMMENT 'User email address';

When a comment is cleared, MySQL syntax is used:

ALTER TABLE users COMMENT = '';
ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL COMMENT '';

Snapshot Format¶

When database_type is "mysql" or "mariadb", the snapshot captures MySQL-specific metadata in my_column and my_table blocks.

Column Extras¶

{
  "columns": {
    "id": {
      "name": "id",
      "type": "int",
      "nullable": false,
      "default": null,
      "autoincrement": true,
      "primary_key": true,
      "comment": "Primary key",
      "my_column": {
        "my_unsigned": true,
        "my_charset": null,
        "my_collate": null,
        "my_on_update": null
      }
    },
    "updated_at": {
      "name": "updated_at",
      "type": "timestamp",
      "nullable": false,
      "default": "CURRENT_TIMESTAMP",
      "autoincrement": false,
      "primary_key": false,
      "my_column": {
        "my_unsigned": false,
        "my_on_update": "CURRENT_TIMESTAMP"
      }
    }
  }
}

Table Extras¶

{
  "my_table": {
    "my_engine": "InnoDB",
    "my_charset": "utf8mb4",
    "my_collate": "utf8mb4_unicode_ci",
    "my_row_format": "Dynamic",
    "my_auto_increment": 1000
  }
}

For MariaDB, additional fields appear:

{
  "my_table": {
    "mdb_page_compressed": false,
    "mdb_page_compression_level": null
  }
}

Reverse Engineering¶

generate-models queries information_schema.TABLES and information_schema.COLUMNS to reverse-engineer all MySQL/MariaDB metadata. The emitted model uses class Meta with MyTableMeta and MyColumnMeta inner classes.

$ dbwarden generate-models -d primary

Generated output includes automatic detection of: - Engine, charset, collation, row format from information_schema.TABLES - Column unsigned, charset, collation, on_update from information_schema.COLUMNS - Foreign key options (ON DELETE, ON UPDATE) - Auto-increment columns - Column comments

Safety Classification¶

DBWarden classifies migration changes using the Safety enum:

from dbwarden.engine.safety import Safety

assert Safety.SAFE == "SAFE"
assert Safety.INFO == "INFO"
assert Safety.WARN == "WARN"
assert Safety.CRITICAL == "CRITICAL"