I'm writing an app framework for a project, where each app is a set of functions. To describe these functions (parameter schemas, return schemas, plugin info, etc.) I'm using an OpenAPI 3.0-like syntax: https://swagger.io/specification/
These app API descriptions are stored in a PostgreSQL database using SQLAlchemy and serialized/deserialized using Marshmallow.
My question mainly concerns nested objects like the Info object: https://swagger.io/specification/#infoObject
In my mind, I could go about this in one of two ways:
A: Just storing the JSON representation of the object in a column, and validating the schema of that object myself:
class AppApi(Base):
__tablename__ = 'app_api'
id_ = Column(UUIDType(binary=False), primary_key=True, nullable=False, default=uuid4)
info = Column(sqlalchemy_utils.JSONType, nullable=False)
B: Creating a new table for each nested object, and relying on Marshmallow to validate it against the schema during serialization:
class AppApi(Base):
__tablename__ = 'app_api'
id_ = Column(UUIDType(binary=False), primary_key=True, nullable=False, default=uuid4)
info = relationship("Info", cascade="all, delete-orphan", passive_deletes=True)
class ApiInfo(Base):
__tablename__ = 'api_info'
id_ = Column(UUIDType(binary=False), primary_key=True, nullable=False, default=uuid4)
app_api_id = Column(sqlalchemy_utils.UUIDType(binary=False), ForeignKey('app_api.id_', ondelete='CASCADE'))
name = Column(String(), nullable=False)
description = Column(String(), nullable=False)
...etc.
I'm inclined to go for option A since it seems much less involved, but option B feels more "correct." Option A gives me more flexibility and doesn't require me to make models for every single object, but Option B makes it clearer what is being stored in the database.
The app's info object won't be accessed independently of the rest of the app's API, so I'm not sure that there's much value in creating a separate table for it.
What are some other considerations I should be making to choose one or the other?
