Migrations for FK fields

[jmriebold]

I've run into a couple issues with the migrations generated for foreign key fields, specifically that neither db_index nor db_unique are respected for FKs.

For example, the following models (adapted from the docs)

from oxyde import Field, Model


class Author(Model):
    id: int | None = Field(default=None, db_pk=True)
    name: str

    class Meta:
        is_table = True


class Post(Model):
    id: int | None = Field(default=None, db_pk=True)
    title: str
    author: Author = Field(default=None, db_on_delete="CASCADE", db_index=True, db_nullable=False, db_unique=True)

    class Meta:
        is_table = True

result in this migration for Post:

    ctx.create_table(
        "post",
        fields=[
            {
                'name': 'id',
                'python_type': 'int',
                'db_type': None,
                'nullable': True,
                'primary_key': True,
                'unique': False,
                'default': None,
                'auto_increment': False,
                'max_length': None,
                'max_digits': None,
                'decimal_places': None
            },
            {
                'name': 'title',
                'python_type': 'str',
                'db_type': None,
                'nullable': False,
                'primary_key': False,
                'unique': False,
                'default': None,
                'auto_increment': False,
                'max_length': None,
                'max_digits': None,
                'decimal_places': None
            },
            {
                'name': 'author_id',
                'python_type': 'int',
                'db_type': None,
                'nullable': False,
                'primary_key': False,
                'unique': False,
                'default': None,
                'auto_increment': False,
                'max_length': None,
                'max_digits': None,
                'decimal_places': None
            }
        ],
        foreign_keys=[
            {
                'name': 'fk_post_author_id',
                'columns': [
                    'author_id'
                ],
                'ref_table': 'author',
                'ref_columns': [
                    'id'
                ],
                'on_delete': 'CASCADE',
                'on_update': 'CASCADE'
            }
        ],
    )

Post.author should have an index and a unique constraint, but has neither.

[mr-fatalyst]

Thanks for the detailed report! Confirmed, this is a real bug. There are actually two distinct causes here:

• db_unique on a FK field is lost. For a FK field, the real column (author_id) is a synthetic field generated from the relation field (author). When that synthetic column is created, only db_nullable is carried over db_unique (and the index attributes) from the FK field are dropped. So author_id ends up with unique=False. The unique pipeline itself works fine (it works for non-FK fields), it just never receives the flag.
• db_index / db_index_name / db_index_method don't work on any field FK or not. Field-level index attributes are stored on the column metadata but are never turned into an actual index during schema extraction. Only table-level Meta.indexes = [Index(...)] is converted into index definitions. So Field(db_index=True) is silently ignored for migrations across the board, not just for FKs.

Workaround until this is fixed declare the index/uniqueness at the table level on the FK column:

from oxyde import Field, Model, Index


 class Post(Model):
     id: int | None = Field(default=None, db_pk=True)
     title: str
     author: Author = Field(db_on_delete="CASCADE", db_nullable=False)

     class Meta:
         is_table = True
         indexes = [Index(("author_id",), unique=True)]

This generates both the index and the unique constraint on author_id correctly.

[jmriebold]

Sounds good, thank you!