sqlmodel
sqlmodel copied to clipboard
tiangolo
Decoupling data schema from sql schema (pydantic integration)
First Check
- [X] I added a very descriptive title to this issue.
- [X] I used the GitHub search to find a similar issue and didn't find it.
- [X] I searched the SQLModel documentation, with the integrated search.
- [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
- [X] I already read and followed all the tutorial in the docs and didn't find an answer.
- [X] I already checked if it is not related to SQLModel but to Pydantic.
- [X] I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- [X] I commit to help with one of those options 👆
Example Code
from sql model import field, SQLModel
from pydantic import BaseModel
"""
I want to define these separately, and have one of them be extended by the other
"""
UserDataSchema(BaseModel): # Name for comparison
""" Data model, not tied to the database (i.e. sql) itself can be re-used"""
user_id: int
project_id: id
# How can this inherit UserDataSchema without re-definition?
UserModel(SQLModel, table=True): # Name for comparison
""" Data model, not tied to the database (i.e. sql) itself can be re-used"""
user_id: Optional[int] = Field(default=None, foreign_key="user.id")
project_id: Optional[int] = Field(default=None, foreign_key="project.id")
Description
The issue at hand is that I am not seeing a way from the docs to decouple the data schema from the database schema. Say I have a large platform, with multiple libraries and services. In such case, if we have a static data schema (like our use case), its very valuable to define the data schema in place (say schema.py as below:
UserDataSchema(BaseModel): # Name for comparison
""" Data model, not tied to the database (i.e. sql) itself can be re-used"""
user_id: int
project_id: id
The problem, is that I am not seeing a way to seamlessly translate from the pydantic.BaseModel to the standard SQLModel without having to re-define the entire schema and basically not re-using anything (other than perhaps some functions from the parent class)
I think SQL Alchemy has done it gracefully with their integration of attrs and dataclassess here. Which would look ""in theory"", like this
from sqlalchemy import Table, Column, Integer, ForeignKey
User(SQLModel, UserDataSchema, table=True): # Name for comparison
__table__ = Table(
Column("user_id", Integer, ForeignKey("user.id"), primary_key=True),
Column("project_id", Integer, ForeignKey("project.id"), primary_key=True),
)
Am I missing something? is there a straight forward to accomplish something along these lines? Based on the current docs, the only way to do it would be with:
class UserDataSchema(BaseModel):
user_id: int
project_id: int
class User(SQLModel, UserDataSchema, table=True):
user_id: Optional[int] = Field(default=None, primary_key=True)
project_id: Optional[int] = Field(default=None, primary_key=True)
However, that defeats the purpose as we have to redefine each attribute again.
Operating System
Windows
Operating System Details
No response
SQLModel Version
0.0.8
Python Version
3.8
Additional Context
No response
Maybe I don't understand you correctly, but you are trying to do this:
from typing import Optional
from pydantic import BaseModel
from sqlmodel import Field, SQLModel
class UserDataSchema(BaseModel):
user_id: int
project_id: int
class User(SQLModel, UserDataSchema, table=True):
user_id: Optional[int] = Field(default=None, primary_key=True)
project_id: Optional[int] = Field(default=None, primary_key=True)
?
I am trying to avoid doing that. That sort of defeats the purpose of having the data model somewhere else, right (i.e. UserDataSchema)? My question is how can I decouple the data model from the SQL definition, so that I can apply/use the inheritance and maximize re-usability (say my data model is in a library which is used by N micro-services)
I have edited the question for clarity
I think the purpose of SQLModel is to couple the data & database layers (SQLAlchemy & Pydantic) and reuse the same code.
The correct way is to do it the opposite of the way you do it, but you probably know that:
class UserDataSchema(SQLModel):
user_id: Optional[int] = Field(default=None, foreign_key="user.id")
project_id: Optional[int] = Field(default=None, foreign_key="project.id")
class UserModel(UserDataSchema, table=True):
pass
Uhm, I understand. So there is no really built in way of supporting it been decoupled and merging it manually? (i.e. like SQLAlchemy's __table__) the reason why I am asking is that with this approach my data is 100% tied to the database technology.
Not ideal in my opinion (i.e. separation of components/concerns). Ideally I want to define a data layer that I can use both in my database layers and my N other layers (if the data is static/consistent like in my use case)
I am not sure what your thoughts are @meirdev @tiangolo but it would be really useful (IMO) if we could define the data schema as a Pydantic model, and then provide the database definitions somewhere else (i.e. table ~ or something with that concept)
In other words, give the data model (pydantic.BaseModel instances) to the SQLModel to build from or to systematically convert a pydantic model to an sqlmodel. is that available?
I don't know how it is defined, but as an example, it can be defined as follows.
from sqlmodel import SQLModel
from sqlalchemy import ForeignKeyConstraint, PrimaryKeyConstraint
from pydantic import BaseModel
class UserDataSchema(BaseModel):
user_id: int
project_id: int
class UserModel(SQLModel, UserDataSchema, table=True):
__table_args__ = (
ForeignKeyConstraint(["user_id"], ["user.id"]),
ForeignKeyConstraint(["project_id"], ["project.id"]),
PrimaryKeyConstraint("user_id", "project_id"),
)
# print(repr(getattr(UserModel, "__table__")))
"""
Table(
'usermodel', MetaData(),
Column(
'user_id',
Integer(),
ForeignKey('user.id'),
table=<usermodel>,
primary_key=True,
nullable=False
),
Column(
'project_id',
Integer(),
ForeignKey('project.id'),
table=<usermodel>,
primary_key=True,
nullable=False
),
schema=None
)
"""
Great point.
Layer-based architectural styles convince us to point our dependency inwards. It should prevent us from tight our application to third-party libraries. SQLModel, as you can see in the example of usages, is exactly the opposite. We bound ourselves to the data source layer. It looks like a new generation of Django (ORM part) - you are not the owner of your application anymore - the library is.
@tiangolo What do you think about coupling your domain entities with the data source layer?
