Using SQLAlchemy declarative base with SQL Model

Question

In one of our project, we used SQL Alchemy declarative Base for all of our models for years (and there is many). We want to try the new SQLModel library for our latest model declaration.

For that, we tried to declare it separately from the Base object, and call the create_all methods for both.

i.e : Base.metadata.create_all() and SQLModel.metadata.create_all().

But the model declared with SQLModel does not recognize the table declared with the Base.

And at this moment, we cannot change all previous models declaration from Base to SQLModel.

Here is a reproducible code :

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import Integer
from typing import Optional
from sqlmodel import Field, SQLModel
from sqlalchemy import String

# Declarative base object
Base = declarative_base()

class DummySATable(Base):
    __tablename__ = 'dummy_table'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))


class DummyModelTable(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    dummy_table_id : int = Field(default=None, foreign_key='dummy_table.id')
    name: str


Base.metadata.create_all(engine)
SQLModel.metadata.create_all(engine)

Here is the traceback :

NoReferencedTableError                    Traceback (most recent call last)

/tmp/ipykernel_307893/3665898561.py in <module>
     24 
     25 Base.metadata.create_all(engine)
---> 26 SQLModel.metadata.create_all(engine)
     27 

~/project/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py in create_all(self, bind, tables, checkfirst)
   4783         if bind is None:
   4784             bind = _bind_or_error(self)
-> 4785         bind._run_ddl_visitor(
   4786             ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
   4787         )

~/project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _run_ddl_visitor(self, visitorcallable, element, **kwargs)
   3108     def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
   3109         with self.begin() as conn:
-> 3110             conn._run_ddl_visitor(visitorcallable, element, **kwargs)
   3111 
   3112     @util.deprecated_20(

~/project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _run_ddl_visitor(self, visitorcallable, element, **kwargs)
   2111 
   2112         """
-> 2113         visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
   2114 
   2115     @util.deprecated(

~/project/venv/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py in traverse_single(self, obj, **kw)
    522             meth = getattr(v, "visit_%s" % obj.__visit_name__, None)
    523             if meth:
--> 524                 return meth(obj, **kw)
    525 
    526     def iterate(self, obj):

~/project/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py in visit_metadata(self, metadata)
    820             tables = list(metadata.tables.values())
    821 
--> 822         collection = sort_tables_and_constraints(
    823             [t for t in tables if self._can_create_table(t)]
    824         )

~/project/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py in sort_tables_and_constraints(tables, filter_fn, extra_dependencies, _warn_for_cycles)
   1284                     continue
   1285 
-> 1286             dependent_on = fkc.referred_table
   1287             if dependent_on is not table:
   1288                 mutable_dependencies.add((dependent_on, table))

~/project/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py in referred_table(self)
   3703 
   3704         """
-> 3705         return self.elements[0].column.table
   3706 
   3707     def _validate_dest_table(self, table):

~/project/venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py in __get__(self, obj, cls)
   1111         if obj is None:
   1112             return self
-> 1113         obj.__dict__[self.__name__] = result = self.fget(obj)
   1114         return result
   1115 

~/project/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py in column(self)
   2408 
   2409             if tablekey not in parenttable.metadata:
-> 2410                 raise exc.NoReferencedTableError(
   2411                     "Foreign key associated with column '%s' could not find "
   2412                     "table '%s' with which to generate a "

NoReferencedTableError: Foreign key associated with column 'dummymodeltable.dummy_table_id' could not find table 'dummy_table' with which to generate a foreign key to target column 'id'

What did I missed? Is it even possible (is there any workaround ?)

Answer 1

I've finally found a simple way to do that, according to this thread. Since SQLModel inherits the Metadata object from SQLAlchemy, We can simply bind the metadata object of SQLModel to the metadata object from SQLAlchemy :

# Declarative base object
Base = declarative_base()
SQLModel.metadata = Base.metadata

# Table declaration....

SQLModel.metadata.create_all(engine)