Best practices for keeping protobuf spec in sync with relational database (e.g. PostgreSQL)

Question

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 teaspoon to knife) to an SQL Schema Definition (e.g. CREATE TABLE teaspoon (...);) or creates SQL Migrations Code from it (e.g. ALTER TABLE teaspoon RENAME TO knife;).

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?

Answer 1

There might be another approach to this problem. The Ruby on Rails (RoR) folks approached the problem by creating migrations and defining the diffs there to ensure the SQL schema can be properly migrated without causing data corruption problems. I suspect DB schema has quite a few more constraints than the corresponding proto buffers.

Perhaps extending the RoR migration mechanism to generate appropriate proto buffers would be a cleaner automation process.

Answer 2

I'm with you. I see all this code generation and requirement to maintain a *.proto file kind of a waste of effort when the database schema, in my case MariaDB, holds what is essentially the *.proto schema. I may have to find a way to write a wire protocol to do such a thing by reverse engineering the protocol. What a shame that it was never incorporated into the base tool so simply take the object typing of a PHP array and use it to simply provide a built in serialization function without all the overhead.