support for mysql source

[docteurklein]

Overview of feature request

I'dl like to observe changes made on multiple mysql instances, and optionally merge them in a single stream (our sharding topolgy is 1 mysql database per tenant, spread over N mysql instances).

Acceptance criteria

being able to construct continuous queries on mysql sources. and ideally push them in a "postgres" reaction (aka sink?).

Additional context

No response

Would you like to support us?

• Yes, I would like to support you

[agentofreality]

Hello @docteurklein,

Thanks for your interest in Drasi and for opening this issue, your requirement sounds interesting and somewhat different to the Sources we have developed so far.

Our current Sources are all implemented such that a single instance of a Source manages a connection to a single database and within Continuous Queries each Source is referenced individually.

The only way to currently deal with data from multiple databases would be to create multiple Source instances (one for each database) and then run the same Continuous Query multiple times but subscribing to a different Source in each instance.

A Reaction can subscribe to multiple Continuous Queries, so in the current implementation, that is where you could unify multiple results streams, and in your case, push them into PostresSQL.

So this may achieve what you want, but in a slightly different way to what you had initially imagined. Do you think this might do what you need?

We have talked internally about a MySQL Source, but it is not a current priority. However, we would definitely be willing to reconsider this and more than happy to collaborate on this if you wanted to build a MySql Source with us.

There might be some challenges inherent in applying the current Source architecture as a way to unify changes from multiple Source databases if this is a critical requirement. But writing a MySql Source that follows the current pattern should not be too difficult.

Here are a few things that might help as you think more about this:

• Our 'Relational' source (https://github.com/drasi-project/drasi-platform/tree/main/sources/relational) currently supports both Postgres and MS SQL. It uses the Debezium library to handle CDC and knex to query the database for Continuous Query bootstrapping. So it might be possible to extend this to add MySql support.
• There is a document, currently excluded from the published Drasi docs that describes how to create a Custom Source. It is excluded because it is currently in draft form, but you can find it is in the doc repo here

Also, we do have a SQL Stored Procedure Reaction that takes the Query Result Change notifications output by a Continuous Query and uses them to execute parameterized Stored Procedure calls. We have used this to push results into PostgreSQL before.

Let us know what you think after reading this.

[docteurklein]

Thanks!
It helps a lot, that's also what I had in mind when looking at the existing source implems.
Thanks for the hints concerning the unification of multiple streams. So it seems that just listing multiple continuous queries in my reaction would be enough to "union all" the streams.

[agentofreality]

Yep, just list the Continuous Queries in the "spec.queries" property of the Reaction configuration. You can see this for the Drasi Debug Reaction in the Getting Started Tutorial.