Can't find table in different schema

[angus-clark]

I am using supabase to host my postgres database, which comes with a pre-made schema called 'auth' to handle authenticated users. I am trying to reference a table in this schema but I am getting a sqlalchemy.exc.NoReferencedTableError error.

However, if I add an include_name function to the env.py file I can see the auth.users table. So I don't know why it is giving me this error?

Below is my table definition:

class Account(Base):
    __tablename__ = 'account'

    id = Column(UUID, ForeignKey("auth.users.id", ondelete="CASCADE"), primary_key=True)
    creation_date = Column(DateTime(timezone=True), nullable=False)
    username = Column(
        String,
        CheckConstraint("char_length(username) >= 3"),
        nullable=False,
        unique=True
    )
    full_name = Column(String, nullable=False)
    email = Column(String, nullable=False, unique=True)
    dob = Column(DateTime(timezone=True), nullable=False)

And here is what is printed by include_name when I run autogenerate:

schema - realtime - {}
schema - auth - {}
schema - None - {}
schema - graphql - {}
schema - storage - {}
schema - extensions - {}
table - users - {'schema_name': 'auth', 'schema_qualified_table_name': 'auth.users'}

[zzzeek]

include_name has to do with the autogenerate process, but the NoReferencedTableError would be raised when you first import your model, I would not think this error would be raised during autogenerate (however, if you share a complete stack trace, then I will know much better where this error is occurring).

When you refer to a table in this third party library, you have to either share the MetaData that they are using, or refer to the column in their model using a column object, not a string:

from my_third_party_library.model import users_table

class Account(Base):
    __tablename__ = 'account'

    id = Column(UUID, ForeignKey(users_table.c.id, ondelete="CASCADE"), primary_key=True)

[angus-clark]

Below is the full stack trace:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/config.py", line 590, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/config.py", line 584, in main
    self.run_cmd(cfg, options)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/config.py", line 561, in run_cmd
    fn(
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/command.py", line 229, in revision
    script_directory.run_env()
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 790, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "alembic/env.py", line 118, in <module>
    run_migrations_online()
  File "alembic/env.py", line 112, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 865, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 611, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/command.py", line 205, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/autogenerate/api.py", line 525, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/autogenerate/api.py", line 572, in _run_environment
    compare._populate_migration_script(
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/autogenerate/compare.py", line 55, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/autogenerate/compare.py", line 89, in _produce_net_changes
    comparators.dispatch("schema", autogen_context.dialect.name)(
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/util/langhelpers.py", line 267, in go
    fn(*arg, **kw)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/autogenerate/compare.py", line 125, in _autogen_for_tables
    [(table.schema, table.name) for table in autogen_context.sorted_tables]
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1113, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/alembic/autogenerate/api.py", line 442, in sorted_tables
    result.extend(m.sorted_tables)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 4739, in sorted_tables
    return ddl.sort_tables(
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 1213, in sort_tables
    for (t, fkcs) in sort_tables_and_constraints(
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 1291, in sort_tables_and_constraints
    dependent_on = fkc.referred_table
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 3846, in referred_table
    return self.elements[0].column.table
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1113, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 2532, in column
    return self._resolve_column()
  File "/Users/angusclark/Documents/squadmates/squadmates_database/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 2543, in _resolve_column
    raise exc.NoReferencedTableError(
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'account.id' could not find table 'auth.users' with which to generate a foreign key to target column 'id'

Is theMetaData solution you suggested possible without having to declare all the third party tables myself (as there are a lot of them). Is there a way of letting alembic know that these third party tables exist with me having to define them?

[zzzeek]

oh, you mean the thing you're using is not a SQLAlchemy model, it's just a database.

OK so, if you want that "auth" database represented in your model, then yes you'd have to make some Table objects for it. how you proceed there depends on how you intend to interact with this table. you might want to use something like DeferredReflection if you intend to write ORM queries against these tables.

to just make an Alembic migration alone , referring to CREATE TABLE / ADD FOREIGN KEY directives that refer to these other tables
without creating model metadata for those tables, I would omit the ForeignKey from your model and add the foreign key directive to the Alembic migration file manually.

[angus-clark]

Appreciate your response, thanks :)

[sprutner]

I also am wondering how to do this. @clarky2233 Did you ever figure out how to do this? If so do you have some example code I could grok?

[angus-clark]

I just decided to leave out the foreign key definition in my table definitions. I then set up the foreign key in the supabase dashboard. I think I also then told alembic to ignore my Account table for automigration (sorry its been a bit).

[sprutner]

Okay yeah I think I will also do that, thanks!

[dishwad]

I'm planning to use SQLAlchemy and Supabase. Just curious, is there any reason to use alembic instead of the Supabase CLI's DB migrations functionality?

[sprutner]

I actually gave up trying to use SQLAlchemy and Alembic and just used prisma. You can ask CharGPT to convert your models and it does an ok enough job but still usually needs manual massaging. Since it’s a react app anyways it seems to make more sense. I still prefer SQLAlchemy however.

On Sun, May 14, 2023 at 8:54 AM Alex Leonov ***@***.***> wrote: I'm planning to use SQLAlchemy and Supabase. Just curious, is there any reason to use alembic instead of the Supabase CLI's DB migrations functionality? — Reply to this email directly, view it on GitHub <#1144 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAW7FS3COODYUGHZUSSFA73XGDPZZANCNFSM6AAAAAATD4GOH4> . You are receiving this because you commented.Message ID: ***@***.***>

-- All Good Things, Seth Rutner

[dishwad]

Hmm, I guess this is assuming that you're not using Python and opting to use JS/TS on the server instead? I can't use Prisma because my server-side code is Python with FastAPI.

[sprutner]

I wanted to use Python but the edge functions do not allow you to use Python AFAIK. How are you using Python, do you have some code running outside of Supabase?

…

On Sun, May 14, 2023 at 10:50 AM Alex Leonov ***@***.***> wrote: Hmm, I guess this is assuming that you're not using Python and opting to use JS/TS on the server instead? I can't use Prisma because my server-side code is Python with FastAPI. — Reply to this email directly, view it on GitHub <#1144 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAW7FSYB4TFGINZY2PXFDHTXGELN7ANCNFSM6AAAAAATD4GOH4> . You are receiving this because you commented.Message ID: ***@***.***>

-- All Good Things, Seth Rutner

[dishwad]

Yeah I have a server that's built using FastAPI, an API framework for Python. I'm using the Supabase client library for Python to make calls to Supabase. Supabase is just a regular Postgres DB on steroids, and they give you the option of connecting directly to the Postgres DB, so you can use as many or as little of the Supabase features as you want.

[FacuBozzi]

A bit late @clarky2233 but this youtube video helped me solve this exact issue (referencing a table on the "auth" schema from supabase).

The users table from the auth schema has a lot of content, so to automatically generate the Model in SQLAlchemy, I ran sqlacodegen (someone in this thread referenced it). It automatically built the Users model and referenced it as a table from the auth schema and I just copied and pasted it. That was it.

All I had to do then was change my ForeignKey from "auth.users.id" to reference directly the object of the model I just created (or sqlacodegen created and I just copied) like this:
id = db.Column(UUID(as_uuid=True), db.ForeignKey(Users.id), primary_key=True, unique=True, nullable=False)

[Dangptpt]

the table users in schemas auth isn't a model, it just database, so you can make a FK from a model to a database.
For my suggestion: you create only model, ignore the FK. And after autogenerate, you directly add FK in migrate version.

Ex:

def upgrade() -> None:
    op.create_table('account',
    sa.Column('id', sa.UUID(), nullable=False),
    ### other atributes ....
    sa.ForeignKeyConstraint(['id'], ['auth.users.id'], )
    )

So you can make a FK to auth.users after upgrade