Inserts, Updates, and Upserts

Create or modify TableBase objects in Python, then pass them to DBConnection to save the changes:

  • DBConnection.insert(): Add new rows (INSERT).
  • DBConnection.update(): Save changed fields on existing rows (UPDATE).
  • DBConnection.upsert(): Insert rows or update them when a unique constraint conflicts (INSERT ... ON CONFLICT DO UPDATE).

Inserting data

An employee table needs an ID, a name, and an age:

from iceaxe import DBConnection, select, TableBase, Field

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

With primary_key=True and default=None, Iceaxe treats id as an auto-incrementing primary key. Postgres assigns its value when the row is inserted.

Create employees with the usual Python constructor. TableBase uses Pydantic to validate their fields; an age that cannot be converted to an integer raises a validation error.

employees = [
    Employee(name="Marcus Holloway", age=28),
    Employee(name="Astrid Keller", age=42),
    Employee(name="Jasper Okafor", age=36),
]

employees[0].id
> None

await conn.insert(employees)

employees[0].id
> 1

Before insert, the objects exist only in Python and their IDs are None. After saving the rows, Iceaxe copies the generated IDs back onto those objects.

Updating data

You can edit an object after fetching or inserting it. Iceaxe tracks which fields change but leaves the database untouched until you call update:

from iceaxe import select

query = select(Employee).where(Employee.name == "Marcus Holloway")
results = await conn.exec(query)

marcus = results[0]
marcus.age = 29

await conn.update([marcus])

Fetch the row again to check the saved age:

results = await conn.exec(query)
results[0].age
> 29

Upserting data

An upsert inserts a row if it is new and updates it if a unique constraint conflicts. Checking with SELECT before inserting leaves a race: another request can insert the same value between the two queries. upsert lets Postgres resolve that conflict in one statement.

For this example, we'll identify employees by name. Add a unique constraint so Postgres can detect an existing name:

from iceaxe import UniqueConstraint

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

    table_args = [
        UniqueConstraint(columns=["name"])
    ]
employees = [
    Employee(name="Marcus Holloway", age=28),
    Employee(name="Astrid Keller", age=42),
]

# Upsert based on name, updating age if the name already exists
await conn.upsert(
    employees,
    conflict_fields=(Employee.name,),
    update_fields=(Employee.age,)
)

conflict_fields identifies the unique constraint to check. If "Marcus Holloway" already exists, update_fields limits the update to his age; otherwise Postgres inserts a new employee.

Returning Values

Use returning_fields to retrieve values from the affected rows, including generated IDs:

results = await conn.upsert(
    employees,
    conflict_fields=(Employee.name,),
    update_fields=(Employee.age,),
    returning_fields=(Employee.id, Employee.name)
)
for employee_id, name in results:
    print(f"Upserted employee {name} with ID {employee_id}")

Query updates

To change rows without fetching them first, use the update query builder. Specify the new value with set and select the affected rows with where:

from iceaxe import update

query = update(Employee).set(Employee.name, "Marcus Keller").where(Employee.name == "Marcus Holloway")

query.build()
> ('UPDATE "employee" SET "employee"."name" = $1
    WHERE "employee"."name" = $2', ['Marcus Keller', 'Marcus Holloway'])

Batch updates

Pass objects to insert, update, or upsert in one list instead of calling the method once per object. This lets Iceaxe reduce database round trips by:

  • Splitting up the list of table instances into batches of parameters.
  • Identifying manipulations that change the same keys and can be batched together.
  • Sending separate operations for each underlying database table.

The batches account for two limits:

  • The driver limits the number of parameters in a query to roughly 32,000.
  • Prepared statements can't batch multiple statements in the same query execution.

Dirty objects

When you modify a local object in Python, you'll need to explicitly call conn.update() to sync the changes back to the database. If you don't, the changes will be lost when the object is garbage collected. During development and testing, it can be helpful to track these "dirty" objects - instances that have been modified but not synced to the database.

You can enable this tracking by setting the uncommitted_verbosity parameter when creating your DBConnection:

# Enable tracking with different warning levels
conn = DBConnection(
    pg_connection,
    uncommitted_verbosity="ERROR"
)

# Disable tracking (recommended for production)
conn = DBConnection(
    pg_connection,
    uncommitted_verbosity=None
)

Choose the log level for unsaved changes:

  • ERROR: Logs errors when objects are modified but not committed
  • WARNING: Logs warnings when objects are modified but not committed
  • INFO: Provides informational messages about modified but uncommitted objects
  • None: Disables tracking entirely (default)

For example, with WARNING level enabled:

user = await conn.exec(select(User).where(User.id == 1))
user.name = "New Name"
# Session closes without calling conn.update([user])
# WARNING: Object <User id=1> has uncommitted changes: {'name'}
# File "...", line 10, in <module>
#   user.name = "New Name"

The log includes where the object was first modified so you can find the code that left the change unsaved.

Performance Considerations

Dirty object tracking adds overhead because Iceaxe must:

  1. Track all modifications to objects
  2. Maintain a registry of modified objects and the location of the modification
  3. Check the state of objects when the session closes

Enable tracking during development or when investigating lost updates. Leave uncommitted_verbosity=None in production to avoid that overhead.