Feature: Include foreign keys in opencdc record

[nickchomey]

Feature description

It would be helpful if foreign keys were included in the opencdc record, to allow for automatic recreation of relationships in the destination connector. Not sure if it should be in the metadata key , or a subkey of "Key"

If it isn't already available in the row info that currently gets extracted from mysql, it would probably make sense to just extract it once and store it in the pipeline/schema somehow, rather than redundantly checking for it on each row iteration.

[lovromazgon]

This sounds like you would like to transmit the schema information, is that right? Conduit currently only focuses on transmitting data, it does not make an effort to sync schemas from the source to the destination. To a degree that's possible using the schema support, but that doesn't really include relationships, since we are using Avro to represent the schema, and that doesn't include a way to represent foreign keys. Can you create the expected schema and relationships on the destination in advance before starting the Conduit pipeline?

This seems related to the schema changes discussion: ConduitIO/conduit#1762.

[nickchomey]

Yes, that's the idea. I don't think it would need to be anything other than Avro strings in order to be useful by processors and destination connectors.

Creating the destination schemas in advance is surely possible, but not always. I can foresee use cases in which you just want to connect to the source and have it auto-create the schemas, including relationships.

Having said this, I don't think that I'll personally use this as my source db doesn't really use foreign keys. But I'd like to offer the option in the destination connector, for others to take advantage of.

I'll likely specify relationships in a json file that either the destination connector will read from at runtime, or in a wasm processor. Likely the former though, to avoid the overhead and complexity of a wasm processor.