Using both SQL-Alchemy Base declarative and SQL Model

[jossefaz]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.
  This could really be related to SQL Alchemy since I cannot see any SQL Model related call in my tranceback bellow, but I did not manage to find why SQL Alchemy is not finding the declared tables from the base. Also I still posted this question because it could be a good point to add to the documentation (?). I also openned a discussion about this subject on the SQL Alchemy github repo

Commit to Help

• I commit to help with one of those options 👆

Example 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)

Description

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.

Steps (see reproducible code sample above) :

• Create a Base declarative object from SQL Alchemy
• Import SQLModel
• Create a table that inherits from Base
• Create a table that inherits from SQLModel with table=True argument
• Define a foreign key from the last table to the first one
• call both create_all from Base.metadata and SQLModel.metadata objects.

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'

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9

Additional Context

No response

[jossefaz]

As I mentioned above, this could really be related to SQL Alchemy since I cannot see any SQL Model related call in my tranceback, but I did not manage to find why SQL Alchemy is not finding the declared tables from the base.

For that reason I also openned a discussion in SQLAlchemy repo
Any help would be blessed !

[jossefaz]

Oka I've found the issue (I think).

Since it is not possible to have two metadata object, then we need to bind the metadata object from SQLModel first to the metadata of the Base, and then execute create_all from the SQLModel only.

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()
SQLModel.metadata = Base.metadata

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

SQLModel.metadata.create_all(engine)

[antont]

SQLModel.metadata = Base.metadata

I guess that can work if they don't conflict.. I don't know the internals of SQLAlchemy. In general that might be be scary but these things were made to work together so maybe ok, dunno. Is easy to see how it solves it for you but would be nice to know if there can be probs.

[bschoenmaeckers]

Sqlmodel should define a default method to share or override the metadata/registry object with sqlalchemy.

Edit: this issue help in the future, #161

[lovetoburnswhen]

Alternatively:

Base = declarative_base(metadata=SQLModel.metadata)

[YuriiMotov]

Runnable code example:

from typing import Optional

import sqlalchemy
from sqlalchemy.orm import declarative_base, relationship
from sqlmodel import Column, Field, Relationship, SQLModel, Text

Base = declarative_base(metadata=SQLModel.metadata)


engine = sqlalchemy.create_engine("sqlite:///", echo=True)

# declare all
class OldModel(Base):
    __tablename__ = "oldmodel"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)

class NewModel(SQLModel, table=True):
    __tablename__ = "newmodel"
    id: Optional[int] = Field(default=None, primary_key=True)
    new_value: str = Field(default=None, sa_column=Column(Text))
    oldmodel_id: Optional[int] = Field(default=None, foreign_key="oldmodel.id")
    oldmodel: Optional["OldModel"] = Relationship(
        sa_relationship=relationship("OldModel", lazy="joined")
    )

# create all
SQLModel.metadata.create_all(engine)

[YuriiMotov]

See also: #1495