Model Generation¶

The models package in alembic-environment has the ability to create multiple useful python objects from CREATE TABLE statements in in ./database/models/tables.sql.

Declaring Your Table¶

First, open the ./database/models/tables.sql file and declare a table.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    join_date DATE DEFAULT CURRENT_TIMESTAMP
);

Generating Your Model¶

Now that we have our model created, we can run our generate command:

uv run python -m models g

You'll get a message detailing how many models were generated in the command line:

Rendered 1 model(s) from tables.sql

Our models were generated to ./database/models/src/models/users.

It will also automatically write the new imports in the model's __init__.py file.

Attempting to repair C:\Users\miles\PycharmProjects\alembic-environment\database\models\src\models\__init__.py file
Successfully wrote new imports.

Our revision will be generated automatically.

This is a snapshot of our revision in ./database/migrations/src/migrations/versions:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "users",
        sa.Column("user_id", sa.Integer(), nullable=False),
        sa.Column("username", sa.String(length=50), nullable=False),
        sa.Column("email", sa.String(length=100), nullable=True),
        sa.Column(
            "join_date",
            sa.Date(),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=True,
        ),
        sa.PrimaryKeyConstraint("user_id", name="users_pkey"),
        sa.UniqueConstraint("username", name="users_username_key"),
    ) ...

Using Your Generated Models¶

The output in ./database/models/src/models models folder should look like this:

|   __init__.py
|   __main__.py
|   base_model.py
|
+---users
|   |   base.py
|   |   mixin.py
|   |   model.py
|   |   __init__.py

You can find the extendable model at .database/models/src/models/users/mixin.py. This file will not be overwritten by the g command.

class UsersMixin: ...

This class is for extending the model generated at ./database/models/src/models/users/model.py, which is the one to be used in production:

class Users(UsersMixin, SQLModelBase, UsersBase, table=True):
    pass

This class inherits the actual base, which has all of the fields and such. Neither model.py or base.py are to be edited. Here is the base generated at ./database/models/src/models/users/base.py:

class UsersBase(SQLModel):
    __table_args__ = (
        PrimaryKeyConstraint("user_id", name="users_pkey"),
        UniqueConstraint("username", name="users_username_key"),
    )
    user_id: int = Field(sa_column=Column("user_id", Integer, primary_key=True))
    username: str = Field(sa_column=Column("username", String(50), nullable=False))
    email: Optional[str] = Field(default=None, sa_column=Column("email", String(100)))
    join_date: Optional[datetime.date] = Field(
        default=None,
        sa_column=Column("join_date", Date, server_default=text("CURRENT_TIMESTAMP")),
    )

This pattern allows us to add convenience methods or mixins to Users while maintaining clean generation if something changes in tables.sql.

Regenerating your SQL Table¶

Now, let's add a password column to our table:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    password VARCHAR(100),
    join_date DATE DEFAULT CURRENT_TIMESTAMP
);

We can run our generate command again:

uv run python -m models g

As you will see, it has also attached it to the generated python object in base.py:

class UsersBase(SQLModel):
    __table_args__ = (
        PrimaryKeyConstraint("user_id", name="users_pkey"),
        UniqueConstraint("username", name="users_username_key"),
    )
    user_id: int = Field(sa_column=Column("user_id", Integer, primary_key=True))
    username: str = Field(sa_column=Column("username", String(50), nullable=False))
    email: Optional[str] = Field(default=None, sa_column=Column("email", String(100)))
    password: Optional[str] = Field(
        default=None, sa_column=Column("password", String(100))
    )
    join_date: Optional[datetime.date] = Field(
        default=None,
        sa_column=Column("join_date", Date, server_default=text("CURRENT_TIMESTAMP")),
    )

However, it has not changed our mixin.py:

class UsersMixin: ...

This way, if you change your tables in SQL, you can still keep all the custom logic for your objects!

Reverse Generation & ORM Extension¶

Now that we've established the ability to generate SQL into python, what happens if our object in mixin.py has extra fields that weren't declared in SQL? That's what migrations rg is for.

Extending Your Model¶

Let's say we want to create a mixin that adds a basic field.:

from ..base_model import SQLModelBase
from .base import UsersBase
from abc import ABC
from sqlmodel import SQLModel, Field
from typing import Self


class CreateMixin(ABC):
    created_by: str | None = Field()

    @classmethod
    def create(cls, username: str = "Anonymous", **kwargs) -> Self:
        return cls(created_by=username, **kwargs) #type: ignore


class UsersMixin(CreateMixin): ...

Here, I've defined the CreateMixin that allows any number of models to have a created_by field without having to redeclare it multiple times in tables.sql

Pushing back to `tables.sql`¶

Let's go ahead and reverse generate to accomodate our models' extra fields declared in python to table.sql.

uv run python -m models rg

We get the following output in tables.sql:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100),
  password VARCHAR(100),
  join_date DATE DEFAULT CURRENT_TIMESTAMP 
  -- created_by VARCHAR
);

It will also run a migration automatically.

As you can see, we've programmatically added a comment showing that this field came from python, without causing another run of uv run python -m migrations g to redeclare it in base.py.

Extending the Base Model¶

Let's say we want to extend all of our models with CreateMixin, not just one. You may have noticed the SQLModelBase class. This is a class that automatically patches in to each model you generate. That means we can add fields or methods to every model at once, easily.

For example, let's move the CreateMixin to ./database/models/src/models/base_model.py and add a last_modified_by column:

from sqlmodel import SQLModel, Field
from abc import ABC
from typing import Self


class CreateMixin(ABC):
    created_by: str | None = Field()
    last_modified_by: str | None = Field()

    @classmethod
    def create(cls, username: str = "Anonymous", **kwargs) -> Self:
        return cls(created_by=username, **kwargs) #type: ignore

class SQLModelBase(SQLModel, CreateMixin, ABC):
    ...

Let's run a reverse generation to see the new field:

uv run python -m models rg

It appears in tables.sql:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100),
  password VARCHAR(100),
  join_date DATE DEFAULT CURRENT_TIMESTAMP
  -- created_by VARCHAR
  -- last_modified_by VARCHAR
);

Let's go ahead and add another table:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100),
  password VARCHAR(100),
  join_date DATE DEFAULT CURRENT_TIMESTAMP
  -- created_by VARCHAR
  -- last_modified_by VARCHAR
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  title VARCHAR(500),
  description VARCHAR(2000),
  user_id INT NOT NULL REFERENCES users(user_id)
);

Now, let's generate the new model and then reverse generate:

uv run python -m models g | uv run python -m rg

Info

The important thing about keeping the model.py/base.py/mixin.py division, comes to foreign keys. On the above table, we added a reference to users via the user_id column on the orders table.

Our generation modified the model.py to access SQLModel's Relationship functionality.

from typing import TYPE_CHECKING, List, Optional
from sqlmodel import Relationship
from ..base_model import SQLModelBase
from .base import OrdersBase
from .mixin import OrdersMixin

if TYPE_CHECKING:
    from ..users.model import Users


class Orders(OrdersMixin, SQLModelBase, OrdersBase, table=True):
    user: "Users" = Relationship(back_populates="orders")

Relationship allows us to pass raw python objects as attributes to fill in foreign keys. Read about using Relationship here.

The extra field registered on the second table as well, in tables.sql:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100),
  password VARCHAR(100),
  join_date DATE DEFAULT CURRENT_TIMESTAMP
  -- created_by VARCHAR
  -- last_modified_by VARCHAR
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  title VARCHAR(500),
  description VARCHAR(2000),
  user_id INT NOT NULL REFERENCES users (user_id)
  -- created_by VARCHAR
  -- last_modified_by VARCHAR
);