[Python] Alembic unable to build upon pre-existing data schema

Here is my current setup:

• An API running on fastAPI & AWS ECS
• A postgresql database hosted by Supabase; I'd like to use supabase for some of its on-the-shelf features such as user authentication
• Using sqlalchemy for ORMs and alembic for data migrations

I want to use supabase's auth schema (pre-existing) along with my app's specific schema (created using ORM). However when I try to build my first table (fact_users, using SQLalchemy) and link it to auth.users using a foreign key (auth.users.id = public.fact_users.id), I get the following error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'fact_users.id' could not find table 'auth.users' with which to generate a foreign key to target column 'id'


Here is the model behind fact_users in user_db.py:

from datetime import datetime

from sqlalchemy import Column, DateTime, Integer, String, ForeignKey
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import UniqueConstraint # ForeignKeyConstraint

UserDBBase = declarative_base()


class UserDBModel(UserDBBase):
__tablename__ = "fact_users"

id = Column(UUID(as_uuid=True), ForeignKey("auth.users.id", ondelete="CASCADE"), primary_key=True, nullable=False)
created_at = Column(DateTime(), nullable=True, default=datetime.utcnow)
empire_id = Column(Integer, nullable=False)
status = Column(String(20), nullable=False)

__table_args__ = (UniqueConstraint("empire_id", name="fact_users_empire_id_key"),)

def __repr__(self):
return (
f"<UserDBModel(id={self.id}, created_at={self.created_at}, "
f"empire_id={self.empire_id}, status={self.status})>"
)



env.py:

from logging.config import fileConfig

from sqlalchemy import engine_from_config, pool

from alembic import context

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
from database.talk_request_db import TalkRequestBase
from database.user_db import UserDBBase

config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = [
TalkRequestBase.metadata,
UserDBBase.metadata,
]

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline() -> None:
"""Run migrations in 'offline' mode.

This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.

Calls to context.execute() here emit the given string to the
script output.

"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)

with context.begin_transaction():
context.run_migrations()


def run_migrations_online() -> None:
"""Run migrations in 'online' mode.

In this scenario we need to create an Engine
and associate a connection with the context.

"""
connectable = engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)

with connectable.connect() as connection:
context.configure(connection=connection, target_metadata=target_metadata)

with context.begin_transaction():
context.run_migrations()


if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()


alembic.ini:

# A generic, single database configuration.

[alembic]
# path to migration scripts
script_location = alembic

# template used to generate migration file names; The default value is %%(rev)s_%%(slug)s
# Uncomment the line below if you want the files to be prepended with date and time
# see https://alembic.sqlalchemy.org/en/latest/tutorial.html#editing-the-ini-file
# for all available tokens
# file_template = %%(year)d_%%(month).2d_%%(day).2d_%%(hour).2d%%(minute).2d-%%(rev)s_%%(slug)s

# sys.path path, will be prepended to sys.path if present.
# defaults to the current working directory.
prepend_sys_path = .

# timezone to use when rendering the date within the migration file
# as well as the filename.
# If specified, requires the python-dateutil library that can be
# installed by adding `alembic[tz]` to the pip requirements
# string value is passed to dateutil.tz.gettz()
# leave blank for localtime
# timezone =

# max length of characters to apply to the
# "slug" field
# truncate_slug_length = 40

# set to 'true' to run the environment during
# the 'revision' command, regardless of autogenerate
# revision_environment = false

# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false

# version location specification; This defaults
# to alembic/versions. When using multiple version
# directories, initial revisions must be specified with --version-path.
# The path separator used here should be the separator specified by "version_path_separator" below.
# version_locations = %(here)s/bar:%(here)s/bat:alembic/versions

# version path separator; As mentioned above, this is the character used to split
# version_locations. The default within new alembic.ini files is "os", which uses os.pathsep.
# If this key is omitted entirely, it falls back to the legacy behavior of splitting on spaces and/or commas.
# Valid values for version_path_separator are:
#
# version_path_separator = :
# version_path_separator = ;
# version_path_separator = space
version_path_separator = os # Use os.pathsep. Default configuration used for new projects.

# set to 'true' to search source files recursively
# in each "version_locations" directory
# new in Alembic version 1.10
# recursive_version_locations = false

# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8

sqlalchemy.url = postgresql://app:talkbooking@localhost:5432/talkbooking


[post_write_hooks]
# post_write_hooks defines scripts or Python functions that are run
# on newly generated revision scripts. See the documentation for further
# detail and examples

# format using "black" - use the console_scripts runner, against the "black" entrypoint
# hooks = black
# black.type = console_scripts
# black.entrypoint = black
# black.options = -l 79 REVISION_SCRIPT_FILENAME

# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S



migrations.py:

import pathlib

from alembic import command
from alembic.config import Config
from web_app.config import load_config

BASE_PATH = pathlib.Path(__file__).parent.absolute()


def load_alembic_config(dsn):
alembic_cfg = Config(str(BASE_PATH / "alembic.ini"))
alembic_cfg.set_main_option("script_location", str(BASE_PATH / "alembic"))
alembic_cfg.set_main_option("sqlalchemy.url", dsn)
return alembic_cfg


def upgrade_migrations(dsn: str):
alembic_cfg = load_alembic_config(dsn)
command.upgrade(alembic_cfg, "head")


def downgrade_migrations(dsn: str):
alembic_cfg = load_alembic_config(dsn)
command.downgrade(alembic_cfg, "base")


if __name__ == "__main__":
upgrade_migrations(load_config().SQLALCHEMY_DATABASE_URI)


If i try to build fact_users without the foreign key constraint, and add the foreign key in a manual "alter table" afterwards, it gets dropped by alembic autogenerate in the following migrations... (I would like to not have to edit the autogenerate files each time to keep my foreign key).

I've tried several options such as:

• adding auth.users to the metadata, but I ended up having alembic wanting to re-create auth.users
• trying to have alembic ignore either fact_users, or just the foreign key after creation, but I kept having the same error

Continue reading...