Is dynamic schema supported like in SQLAlchemy?

[aghanti7]

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.

Commit to Help

• I commit to help with one of those options 👆

Example Code

Example code from the blog:

Base = declarative_base()

class MyTableClass(Base):
	__tablename__ = 'myTableName'
	myFirstCol = Column(Integer, primary_key=True)
	mySecondCol = Column(Integer, primary_key=True)


Base.metadata.create_table(engine)

attr_dict = {'__tablename__': 'myTableName',
	     'myFirstCol': Column(Integer, primary_key=True),
	     'mySecondCol': Column(Integer)}

Description

I am looking if SQLModel supports dynamic schema like SQLAlchemy does. Example: https://sparrigan.github.io/sql/sqla/2016/01/03/dynamic-tables.html

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.0

Additional Context

No response

[RobertRosca]

Would PR #43 solve this when merged?

[aghanti7]

I was able to achieve this with the below workaround.

class UserActivityBase(SQLModel):
    # One table per user
    # Created first time when a user logs in perhaps
    created_ts: datetime.datetime = Field(primary_key=True)
    activity_type: str
    activity_value: float


async def create_user_activity_model(uuid: str, engine: AsyncEngine):
    class UserActivity(UserActivityBase, table=True):
        __tablename__ = 'user_activity_' + uuid
        __table_args__ = {'schema': 'user_schema',
                                       'keep_existing': True}

    # Create the table if needed
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)
    return UserActivity


class UserActivityCrud(UserActivityBase):
    pass


async def get_user_activity_model(uuid: str):
    class UserActivityCrud(UserActivityBase, table=True):
        __tablename__ = 'user_activity_' + uuid
        __table_args__ = {'schema': 'user_schema',
                                       'keep_existing': True}

    return UserActivityCrud

UserActivityBase is the base SQLModel. When you want to create the table for a new user, you can call create_user_activity_model() with the user's uuid and the engine instance. Later, when you need to get the model for a particular user, just call get_user_activity_model(uuid=<uuid>).

The only thing I am facing is a warning. When you call get_user_activity_model more than once, the below warning is thrown.

/Users/ghanti/code/proj/venv/lib/python3.9/site-packages/sqlmodel/main.py:367: SAWarning: This declarative base already contains a class with the same class name and module name as app.models.UserActivityCrud, and will be replaced in the string-lookup table.

I feel this can be ignored. @tiangolo can you please confirm?

[tiangolo]

This would be technically not supported by SQLModel, as the idea is that the models are the source of truth for the data shapes. But you can probably do it that way with pure SQLAlchemy. 🤓