Sharing Schema across different services

Question

I have an ETL pipeline that extracts data from several sources and stores it within a database table (which has a fixed schema).

I also have a separate FASTAPI service that allows me to query the database through a REST endpoint, which is called to display data on the frontend (React TS).

The issue now is that my ETL Pipeline, FASTAPI service, and frontend all have a separate version of the schema, and in the case where the data schema needs to be changed, this change has to be done to the schema specifications on all 3 services.

I have thought about creating a python package that contains this schema, but this can only be shared between the services that uses Python, and my frontend still has to keep its own version of the schema.

Is there some sort of "schema service" that I should be having? What can I do to reduce this coupling?

Answer 1

I am fond of this pattern:

import sqlalchemy as sa

...
        meta = sa.MetaData(bind=self.engine)
        my_table = sa.Table('my_table', meta, autoload=True)

That is to say, the on-disk database schema is the Source of Truth, and code always dynamically introspects it to learn about schema details. For example, this gracefully accommodates newly added columns.

With such a table object in hand, you can readily iterate over its columns:

        for col in my_table.columns:
            print(col)  # we can examine details like name and type

That should be enough to let you create a JSON dict, suitable for consumption by react typescript.

Answer 2

For fastAPI + frontend => use swagger, where your schema auto generated for frontend based on your pydantic models (for more consistency you can add version to each API endpoint and change version on schema changes at this endpoint)

For fastAPI + database + elt => in our company we use 'mono repository'. One git repo with ORM database schema in libs folder + folder for fastAPI microservice (with Dockerfile for FastAPI app) + folder for etl service (with enother Dockerfile) and in one commit you can change consistently elt script + fastAPI app + add your database migrations.