Models¶

A model class describes a table. A model instance represents a row that already exists in that table.

This is the main difference from many ORMs: constructing a model loads a row; it does not create an unsaved object. Use insert() to create rows.

from model import Model
from model.database import SQLiteDatabase

database = SQLiteDatabase("app.sqlite3")


class User(Model):
    table = "user"
    db = database

    id: int = Model.Column(
        type="INT",
        index="PRIMARY",
        auto_increment=True,
    )
    email: str = Model.Column(
        type="VARCHAR",
        length=255,
        index="UNIQUE",
        can_be_null=False,
    )
    display_name: str | None = Model.Column(type="VARCHAR", length=100)

The table must exist before normal model operations run. Create or update it with schema sync.

Define a model¶

Every model needs:

• table, the table name.
• db, a configured MySQL or SQLite database instance.
• A primary key, unique column, or unique composite index that can identify a row.

Annotate column attributes with their Python types to improve editor and type-checker support. Only annotate attributes assigned to Model.Column(). Annotating other class attributes interferes with static type checking and produces incorrect constructor type hints.

Loading a model¶

You can load a model by constructing it with a primary column, a unique column, or every column in a unique MultiIndex.

By default, the primary key column is automatically inferred by static typing as the initializer of the model class.

You can change this default static typing behaviour by using the init parameter of the Model.Column. For example, let's say that you want to use a unique column for initialization instead of the primary key:

class User(Model):
    table = "user"
    db = database

    id: int = Model.Column(
        type="INT",
        index="PRIMARY",
        auto_increment=True,
        init=False, # Set init=False for the primary key
    )
    email: str = Model.Column(
        type="VARCHAR",
        length=255,
        index="UNIQUE",
        can_be_null=False,
        init=True, # Set init=True for the unique key
    )

# Type signature shows User(email: str), and we can load like:
user = User(email="ada@example.com")

Accessing instance data¶

Loaded column values can be accessed directly as attributes:

user = User(id=1)

email = user.email

Column attributes are read-only. To change values, use the update() method instead of assigning to them directly.

A model instance also exposes its currently loaded row as a dict:

row = dict(user)
names = user.keys()
loaded_value_count = len(user)

Querying¶

Model query methods accept a SQL expression without the WHERE keyword:

users = User.find_all(
    "display_name IS NOT NULL ORDER BY id",
)

named_user_count = User.count("display_name IS NOT NULL")

Select only the columns you need¶

You can specify the columns parameter to choose what columns will be fetched initially:

user = User.find_one("id = ?", [1], columns=["email"])
assert user is not None

print(user.email)         # Already loaded
print(user.display_name)  # Lazy loaded with a second query, then cached

This is useful when you have a big row and you want to avoid loading all the columns at once.

Identity columns (i.e. primary/unique columns) are always included automatically, and omitted columns can be loaded later lazily.

Insert, update, and delete rows¶

Use insert() to create a row. It creates the row and returns a model instance:

user = User.insert({
    "email": "ada@example.com",
    "display_name": "Ada",
})

print(user.id)

Provide all required columns that have no default values and are not auto-incrementing. The inserted row must be identifiable by a supplied primary/unique value, a complete unique MultiIndex, or a database-generated auto-incrementing primary key (automatically retrieved when omitted from inserted data).

Use insert_or_update() for an upsert:

setting = Setting.insert_or_update(
    {"key": "theme", "value": "dark"}
)

For updating or deleting:

user = User(id=1)

affected = user.update({"display_name": "Ada Lovelace"})

print(user.display_name) # prints: Ada Lovelace

deleted = user.delete()

Both return the number of affected rows.

After a successful delete(), the instance's column data is cleared and later writes raise ModelRecordDeletedError.

Application relationships with Map¶

Model.Map lazily loads another model from values on the current row:

class Country(Model):
    table = "country"
    db = database

    code: str = Model.Column(
        type="CHAR", length=2, index="UNIQUE", can_be_null=False
    )
    name: str = Model.Column(type="VARCHAR", length=100, can_be_null=False)


class City(Model):
    table = "city"
    db = database

    id: int = Model.Column(type="INT", index="PRIMARY", auto_increment=True)
    country_code: str = Model.Column(type="CHAR", length=2, can_be_null=False)

    country = Model.Map(Country, code=country_code)


city = City(id=1)
print(city.country.name)

The mapping means Country(code=city.country_code). Keyword names identify columns on the target model; keyword values are source columns. Multiple pairs can target a unique composite index.

The related row is loaded on first access and cached until the source instance is updated. Map does not add a foreign key, cascade, join, or schema change. A missing row raises ModelRecordNotFoundError.

Lifecycle hooks¶

Override these no-op methods when needed:

Do not override __init__.

Inheritance¶

Model classes cannot be subclassed. This deliberate constraint keeps each model a clean, explicit representation of a single database table.