Foreign Key prevents tables from updating

[alex-ong]

class User(DataBaseModel):
    uuid: str = PrimaryKey(default=generate_uuid)
class Step(DataBaseModel):
    """
    A step model, for the database
    Links to an owner
    """

    uuid: int = PrimaryKey(autoincrement=True)
    user_uuid: str = ForeignKey(User, "uuid")
    num_steps: int
    start_ts: int
    end_ts: int

Setup works fine the first time.

After adding one field to User, it fails

class User(DataBaseModel):
    uuid: str = PrimaryKey(default=generate_uuid)
    password: str | None = None

File "test_proj/.venv/lib/python3.11/site-packages/starlette/routing.py", line 677, in lifespan
    async with self.lifespan_context(app) as maybe_state:
  File "test_proj/.venv/lib/python3.11/site-packages/starlette/routing.py", line 566, in __aenter__
    await self._router.startup()
  File "test_proj/.venv/lib/python3.11/site-packages/starlette/routing.py", line 654, in startup
    await handler()
  File "test_proj/main.py", line 38, in db_setup
    await setup_database()
  File "test_proj/xeal_api/db.py", line 18, in setup_database
    await Database.create(DATABASE_URL, tables=TABLES)
  File "test_proj/.venv/lib/python3.11/site-packages/pydbantic/database.py", line 676, in _migrate
    await self.compare_tables_and_migrate()
  File "test_proj/.venv/lib/python3.11/site-packages/pydbantic/database.py", line 458, in compare_tables_and_migrate
    old_table = sqlalchemy.Table(
                ^^^^^^^^^^^^^^^^^
File "<string>", line 2, in __new__
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 606, in __new__
    with util.safe_reraise():
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 602, in __new__
    table._init(name, metadata, *args, **kw)
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 688, in _init
    self._init_items(
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 135, in _init_items
    spwd(self, **kw)

  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/base.py", line 1046, in _set_parent_with_dispatch
    self._set_parent(parent, **kw)
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 1854, in _set_parent
    self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 1873, in _setup_on_memoized_fks
    fn(fk)
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 1854, in <lambda>
    self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
                                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 2449, in _set_remote_table
    self._link_to_col_by_colstring(parenttable, table, colname)
  File "test_proj/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 2341, in _link_to_col_by_colstring
    assert self.constraint._referred_table is table
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AssertionError

This only fails with ForeignKey.
Using

class Step(DataBaseModel):
    """
    A step model, for the database
    Links to an owner
    """

    uuid: int = PrimaryKey(autoincrement=True)
    user_uuid: User
    num_steps: int
    start_ts: int
    end_ts: int

works perfectly fine. I want to eventually use an index on user_uuid / start_ts hence the want for Foreign key, rather than user_uuid: User, which generates an intermediary table.

[codemation]

@alex-ong what DB did you use to reproduce this behavior?

Just FYI
For more granular control, such as Indexes / constraints, it could be worth while to consider using pydbantic with alembic

[alex-ong]

Thanks for the response; i was using sqlite. I'll read those docs!