1. We have a schema definition in Google's Protocol Buffers format that we use to generate code for different gRPC implementations (Python, PHP)
2. We have a database (PostreSQL) that is initialized from SQL files which map the Protocol Buffers structure.
Problem: Frequently, the protobuf spec changes. This must be reflected in the database, which is a tedious task and prone to mistakes when done manually.
We recently moved to Alembic Migrations for migrating continuous updates to the (live) database structures when we update the Protocol Buffers specification. However, we still feel like we need to maintain two structure that are basically equal or at least very similar: ProtoBuf and SQL-Code (managed via Alembic Migrations or in plain *.sql files).
I have searched over and over and there is just very little information on how others solve this problem. Ideally, we would like to set up a CI/CD process that converts the protobuf specification (e.g. a message name changed from
knive) to an SQL Schema Definition (e.g.
CREATE TABLE teespoon (...);) or creates SQL Migrations Code from it (e.g.
ALTER TABLE teespoon RENAME TO knive;).
I know it is more complicated in reality than with my simple examples, but I can't even find code that supports half-automated and human supervised conversion. Any advice?
What I have found so far:
- from SQL files, we can create a PostgreSQL DB (e.g. in Docker) and use sqlacodegen to automatically create an SQLAlchemy Model
- from the SQLAlchemy Model, we can use Alembic Migrations to create (half)automatic Schema Migration Code
- we're back to point zero: we need to maintain the base SQL files and protobuf spec
- there is Mercator, but as far as I understand it can only map from SQL to gRPC, not vice versa
- there's also migra, which looks excellent for generating auto-diffs for different PostgreSQL schemas (here's a presentation and the docs)
Maybe there could be something like "Protobuf Migrations", where one can define SQL Migrations for each protobuf spec change?