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.