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