Table Definition

Define a table by subclassing TableBase. Each annotated attribute becomes a column, and Field adds options such as primary keys and timestamp settings.

from iceaxe import TableBase, Field, PostgresDateTime, DBConnection

class Employee(TableBase):
    id: int | None = Field(primary_key=True, default=None)
    name: str
    age: int
    payload: datetime = Field(postgres_config=PostgresDateTime(timezone=True))

The class defines the table in Python. To create it in Postgres, write the SQL yourself or call create_all:

from iceaxe.schemas.cli import create_all

conn = DBConnection(
    await asyncpg.connect(
        host="localhost",
        port=5432,
        user="db_user",
        password="yoursecretpassword",
        database="your_db",
    )
)
await create_all(conn)

By default, create_all creates tables for every imported TableBase subclass. Import all your models before calling it. One way to keep those imports together is to re-export your models from models/__init__.py:

myproject/
├── __init__.py
└── models/
    ├── __init__.py
    ├── employee.py
    └── office.py
# models/__init__.py
from .employee import Employee as Employee
from .office import Office as Office

Importing models now loads both table definitions before create_all runs:

from myproject import models

print(models.Employee)
print(models.Office)

await create_all(conn)

To create only specific tables, pass their classes in models:

await create_all(conn, models=[DemoCustomModel])

Fields

TableBase inherits Pydantic's BaseModel, so your table objects support its validation and serialization features. See the Pydantic documentation for those options and the Iceaxe API docs for database options.

To require an age of at least 16, add a field validator:

from iceaxe import TableBase, Field
from pydantic import field_validator

class Employee(TableBase):
    id: int | None = Field(primary_key=True, default=None)
    name: str
    age: int

    @field_validator("age")
    def validate_age(cls, v):
        if v < 16:
            raise ValueError("You must be at least 16 to receive a tax return")
        return v

# Successful validation
person_1 = Person(name="John Doe", age=16)
print(person_1)

# Raises a pydantic_core._pydantic_core.ValidationError
person_2 = Person(name="John Doe", age=15)
print(person_2)

Structured JSON fields

Field(is_json=True) also supports structured values, including Pydantic models. Iceaxe will infer a JSON column for the field and deserialize rows back into the annotated Python type:

from pydantic import BaseModel

class Preferences(BaseModel):
    theme: str
    notifications: bool

class Employee(TableBase):
    id: int | None = Field(primary_key=True, default=None)
    name: str
    preferences: Preferences = Field(is_json=True)

When you insert, select, update, or refresh an Employee, the preferences attribute remains a Preferences instance instead of a raw dict. If you prefer JSONB, you can still override the storage type with Field(is_json=True, explicit_type=ColumnType.JSONB) after importing ColumnType from iceaxe.sql_types.

Typed identifiers

Prefer NewType for identifiers that should be distinct to a static type checker:

from typing import NewType
from uuid import UUID

from iceaxe import Field, TableBase

EmployeeId = NewType("EmployeeId", UUID)

class Employee(TableBase):
    id: EmployeeId = Field(primary_key=True)
    name: str

The column is stored as a PostgreSQL UUID, and values remain UUID objects in Python. This avoids reconstructing a subclass for every selected ID while retaining normal Pydantic model validation. See choosing an identifier type for primary and foreign key examples and the runtime differences between NewType and subclasses.

Simple scalar subclasses

Iceaxe also accepts simple subclasses of built-in storage types. Use these when you need a distinct runtime class, such as for custom methods or isinstance checks:

from uuid import UUID

class EmployeeId(UUID):
    pass

class Employee(TableBase):
    id: EmployeeId = Field(primary_key=True)
    name: str

The value is stored using the base PostgreSQL type, but reads come back as EmployeeId instances for both full-model selects and direct column selects. This works for simple subclasses of UUID, str, int, float, bytes, date, time, datetime, and timedelta.

Reconstructing these subclasses adds work for each selected value. For identifiers used only for static type checking, prefer NewType as shown above, especially when reading large result sets.