Quickstart
Installation
If you're using uv to manage your dependencies:
uv add iceaxe
Otherwise install with pip:
pip install iceaxe
Usage
Define your models as a TableBase subclass:
from iceaxe import TableBase
class Person(TableBase):
id: int
name: str
age: int
TableBase inherits Pydantic's validation and field customization. Iceaxe's
Field adds database options, such as marking id as the primary key:
from iceaxe import Field
class Person(TableBase):
id: int = Field(primary_key=True)
name: str
age: int
To read and write rows, connect to Postgres with asyncpg and wrap the connection
in Iceaxe's DBConnection:
from iceaxe import DBConnection
import asyncpg
conn = DBConnection(
await asyncpg.connect(
host="localhost",
port=5432,
user="db_user",
password="yoursecretpassword",
database="your_db",
)
)
Defining Person does not create a database table. Create it with SQL, as
below, or generate a migration:
await conn.conn.execute(
"""
CREATE TABLE IF NOT EXISTS person (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT NOT NULL
)
"""
)
Inserting Data
Create Person objects, then pass them to insert to save them:
people = [
Person(name="Alice", age=30),
Person(name="Bob", age=40),
Person(name="Charlie", age=50),
]
await conn.insert(people)
print(people[0].id) # 1
print(people[1].id) # 2
Postgres assigns each row an auto-incrementing ID. Iceaxe writes that ID back to the same Python object after the insert.
Updating data
Change Alice's name on the Python object:
person = people[0]
person.name = "Blice"
The database still holds the old name. You can inspect or validate your changes before saving them:
def ensure_b_letter(person: Person):
if person.name[0].lower() != "b":
raise ValueError("Name must start with 'B'")
ensure_b_letter(person)
Call update to save the changed fields:
await conn.update([person])
If another process updated Alice's age to 31 in the meantime, the database now contains:
id | name | age
----+-------+-----
1 | Blice | 31
2 | Bob | 40
3 | Charlie | 50
Our update changed only name, so it preserved the other process's age change.
Iceaxe tracks fields modified in memory and includes only those fields in the
UPDATE statement.
Selecting data
Use select to build a query, then conn.exec to execute it:
from iceaxe import select
query = select(Person).where(Person.name == "Blice", Person.age > 25)
results = await conn.exec(query)
results is typed as list[Person]. To fetch only the columns you need, select
them as a tuple:
query = select((Person.id, Person.name)).where(Person.age > 25)
results = await conn.exec(query)
The result type becomes list[tuple[int, str]]: each tuple contains an ID and a name.
Query operations are typed too, so your type checker can flag comparisons such
as a string column against an integer. Queries can also combine models and
individual columns. Assuming a FavoriteColor table with a person_id foreign
key, this query joins each person to their favorite colors:
query = select((
Person.id,
FavoriteColor,
)).join(
FavoriteColor,
Person.id == FavoriteColor.person_id,
).where(
Person.age > 25,
Person.name == "Blice",
).order_by(
Person.age.desc(),
).limit(10)
results = await conn.exec(query)
The result is typed as list[tuple[int, FavoriteColor]].