feat: server default/onupdate

[mplemay]

Hey @piercefreeman ,

On of the features I found myself commonly using in sqlalchemy was the server_default and onupdate for Columns.

Proposal

class MyModel(TableBase):
    id: UUID = Field(default=func.gen_random_uuid(), primary_key=True)
    updated_at: datetime = Field(default=func.now(), onupdate=func.now())

Let me know your thoughts and if you want me to take a first pass at it.

[piercefreeman]

@mplemay Server side defaults are a core capability of Postgres so I'm certainly open to adding them. The most important thing here is to figure out the API and what it means for migration support.

Client-side defaults and server-side defaults are fundamentally different things in my book, so at the very least we would want to separate out the kwargs that create them. Let's take the case of id: UUID = Field(default=func.gen_random_uuid()). The most similar case that we already support would be server-side assigned primary keys.

• default=anything values will try to be constructed at object creation time with pydantic if not otherwise specified. In that case the runtime output of func.gen_random_uuid() would have to conform to a UUID validation. I don't see a clean way of doing that without confusing validators. In that case let's propose a db_default field that can accept these func parameters.
• If db_default is set but default is as well, which takes precedence? Do we throw an exception? Do we typehint two separate signatures to indicate that only one should be provided at a time? Do we automatically force default=None if db_default is provided?
• Assuming db_default is the only thing that's specified, what typehint should that field actually require? On a SELECT from the database we're near guaranteed that the value will be non-null because the db_default should set it on insert time. However, if we add the db_default after initial creation time of the column it's possible some of these existing values will be null. Furthermore, when the object is actually created for the first time in memory we'll need to assign the column to some value. For both of those reasons I think an implicit default=None during usage of db_default is the best choice here. So that means that we would need to force all db_default typehints to be UUID | None with default=None as the value provided.

There's also the question of adding migration support for these columns but that should be pretty straightforward with an ALTER COLUMN SET DEFAULT. Let me know your thoughts on the above questions.

[mplemay]

When considering considering designs like this (an by extension the extensions), I am always curious to see what others are doing. While I think we both have a good grasp of the python ecosystem, I personally don't have a great understanding of the "modern" ORMs available in other languages . Last night, I did a bit of digging an came across drizzle among others. To go on a bit of an unrelated tangent here, what i found interest about drizzle was the way tables were defined (see an example below).

import { ... } from 'drizzle-orm/pg-core';
import { createSelectSchema } from 'drizzle-zod';
import { z } from 'zod';

export const admin = pgRole('admin');

export const users = pgTable('users', {
  id: uuid('id').notNull().defaultRandom(), // .primaryKey()
  parentId: uuid("parent_id"),
  age: integer('age').default(42),
  weight: integer('weight').default(sql`'120'::integer`),
  createdAt: timestamp({ precision: 6, withTimezone: true }).defaultNow(),
  updatedAt: timestamp({ mode: 'date', precision: 3 }).$onUpdate(() => new Date())
  embedding: vector({ dimensions: 3 })
}, (t) => [
  pk: primaryKey({ name: 'pk', columns: [table.id] }),
  fk: foreignKey({ name: "fk", columns: [t.parentId], foreignColumns: [t.parentId]}).onDelete("set null").onUpdate("no action"),
  checkConstraint: check("age_check1", sql`${table.age} > 21`),
  cosine: index('cosine_index').using('hnsw', table.embedding.op('vector_cosine_ops')).concurrently(),
  pgPolicy('policy', {
    as: 'permissive',
    to: admin,
    for: 'delete',
    using: sql``,
    withCheck: sql``,
  }),
]);

export const usersView = pgView("user_view").as((qb) => qb.select().from(user).where(eq(user.age, 42)));

const userSelectSchema = createSelectSchema(users, {
  age: (schema) => schema.max(20), // Extends schema
});

const result = await db.insert(users).values([{ age: 10 }, { parentId: ... }]).$returningId();
// see https://orm.drizzle.team/docs/select for all a bunch of select examples

While there is quite a lot going on here, I think there are a couple of things I personally think are worth taking away from this:

• drizzle uses helper methods to construct columns (ex: integer, timestamp, vector). This ensures that the types have all of their relevant options which a user could chain together (i.e. .notNull().defaultRandom()) all while ensuring type/field construction safety.
• The pgTable by design separates "columns" from "properties" (i.e. rls policies, constraints, indexes, foreign keys, etc.). Which means that 1) check constraints, etc. can use all of the standard sql functions and apply those to fields w/ type checking and 2) in terms of design the separation means there is only one place to look for these "properties"
• The non-postgres validation is separate from the table itself (see userSelectSchema). This allows users to create custom create custom validation based on a given scenario (ex: insert/update/custom view).

While I don't think that there is a one-to-one mapping (or even should be) a mapping with drizzle I wonder if some of the design should carry over. If we were to take the above kitchen sink example and translate some of the core parts of the model into python, it might look like.

from uuid import UUID
from datetime import datetime
from iceaxe import func, uuid, integer, timestamp, TableBase, sql, check, index
from iceaxe.ext import vector

class User(TableBase):
    id: UUID = uuid(default=func.gen_random_uuid(), primary=True)
    parent: UUID | None = uuid(default=None, alias="parent_id")
    age: int = integer(default=42)
    weight: int = integer(default=sql("'120'::integer"))
    created_at: datetime = timestamp(default=datetime.now(), precision=6, timezone=True)
    updated_at: datetime = timestamp(default=func.now(), timezone=True, onupdate=func.now())
    embedding: list[float] = vector(dimensions=3)

   @schema_property
   def age_check(cls: type[User]):
       return check(cls.age > 21)

   @schema_property
   @classmethod
   def embedding_idx(cls: type[User]):
      return index(columns=[cls.embedding], using='hnsw', op=func.op('vector_cosine_ops'), concurrently=True)
   ...

In this example the schema_property acts a lot like model_validator function pydantic offers and the fields (i.e. uuid, timestamp, etc.) are a wrapper around pydantic.Field.

In my mind this has several downstream advantages. The first is, when implementing the field you can do the following

class sql(BaseModel):
    value: Sequence[sql ] | str
    space: bool = Field(default=True)

   def __add__(self, other: sql) -> sql: ...
    
class PostgresFieldBase(BaseModel, ABC, Generic[T]):
     @abstractmethod
     def __call__(self, name: str) -> sql: ...

class PostgresDateTime(PostgresFieldBase):
    desciminator: Literal["timestamp"] = Field(default="timestamp")
    default: datetime | sql | None = Field(default=None)
    precision: int | None = Field(default=Non, ge=0, ...) 
    timezone: bool = False
    onupdate: ...
    
    def __call__(self, name: str) -> sql:
         column = sql(name)
         column += sql(self.descriminator)
         
         if self.precision is not None:
               column += sql(f"(precision)", space=false)

        ....
    
        return sql(column)

Then when it comes to constructing the field you could do the following which would allow for the field to avoid the pitfalls you mentioned while still maintaining pydantic compliance.

import pydantic

def timestamp(...) -> pydantic.FieldInfo:
     field = PostgresDateTime(...)
     return pydantic.Field(..., json_schema_extra=field.model_dump())

And when it comes to generating the migrations, I think it will simplify things because there will no longer be a need for DBColumn, DBConstraint, etc. as far as I can tell.

Note: this architecture also allows for fields, etc. to be extended with new postgres types (ex: pgvector).

This approach would require a good number of changes and definitely needs a bit of refinement. I would be curious to hear your thoughts and critique @piercefreeman .