Realtime sync from 3rd party databases

[tobiemh]

With the addition of 3rd party database syncing, it should be quicker and easier to get going with SurrealDB.

Any changes made on the 3rd party database will be asynchronously synced into SurrealDB.

MongoDB

For MongoDB we can use change streams to watch all changes to a database.
Document IDs would need to be converted into SurrealDB record IDs.
https://docs.rs/mongodb

RethinkDB

For RethinkDB we can use table change feeds to watch all changes to all tables in a database.
Document IDs would need to be converted into SurrealDB record IDs.
https://docs.rs/reql

[mumoshu]

@tobiemh Good idea!

Would it also make sense to source from RDBMS like MySQL and PostgreSQL?

I'd love it if SurrealDB could be a viable alternative to e.g. Vitess in terms of scalability and ease of data migration (I know SurrealDB has a lot wider feature set and scope so it's like comparing apple vs orange but still- depending on one's requirement those two can be comparable and the ease of data migration can be a great deal?).

For example, I thought Vitess supported migrating from vanilla MySQL with some manual steps, by registering the existing MySQL via an unmanaged tablet.

Similarly, If we could point a SurrealDB command to an existing MySQL to start a long-running process that continuously syncs MySQL data to SurrealDB, that would be great.

Also, MySQL and PostgreSQL might still have more productions users than those NoSQL and NewSQL databases. So perhaps providing migration paths from RDBMSes would help SurrealDB adoption slightly more than others? 🤔

P.S. I'd happily try to contribute one for MySQL if that's ok for you and you could share me a big picture of this realtime snc feature(like what command to implement for this feature... surreal sync, or surreal replicate --from mysql://$host:$port/$table or etc?) FWIW, implementation-wise, I'd try https://github.com/EasyPost/rust-mysql-binlog first.

[tobiemh]

Hi @mumoshu it would make absolute sense to sync from MySQL and PostgreSQL too yes!

We really want it to be easy to migrate data in to SurrealDB. With the SurrealDB tables, fields, embedded objects and arrays, and with the SQL querying, SurrealDB can easily handle the syncing of data from relational and document (NoSQL) databases.

Similarly, If we could point a SurrealDB command to an existing MySQL to start a long-running process that continuously syncs MySQL data to SurrealDB, that would be great.

Absolutely we want to have the possibility of having SurrealDB sit alongside another database, and syncing everything over to SurrealDB, which would ease application migration of larger, more complex apps.

Also, MySQL and PostgreSQL might still have more productions users than those NoSQL and NewSQL databases. So perhaps providing migration paths from RDBMSes would help SurrealDB adoption slightly more than others? 🤔

Absolutely - this is definitely our intention 😀.

P.S. I'd happily try to contribute one for MySQL if that's ok for you and you could share me a big picture of this realtime snc feature(like what command to implement for this feature... surreal sync, or surreal replicate --from mysql://$host:$port/$table or etc?) FWIW, implementation-wise, I'd try https://github.com/EasyPost/rust-mysql-binlog first.

Awesome @mumoshu! The idea for this feature hasn't been fully scoped out yet really, so I'm happy to brainstorm with you.

The initial intention is to have the syncing built into the command-line app, as you suggested:

surreal sync --from mysql://$host:$port/$database --conn https://cloud.surrealdb.com --ns myns --db mydb

Things to consider:

• How would we differentiate between a one-off sync, and a continuous sync?
• We would have to work out how to specify authentication for the different source databases.
• I don't want the CLI app to have too many dependencies (and I envisage quite a few dependencies for all of the source databases, and any additional libraries needed). Therefore, I'm thinking that it might be good to have all of the syncing logic in a separate library / repository (surreal-sync), and the cli command just instantiates and uses functionality from the library. This would also mean that the syncing functionality can be used as a library.
• What happens if the SurrealDB database is being continuously synced (say from MongoDB), and data in SurrealDB is then edited. Should it sync those changes back to MongoDB?
• Obviously we would have to handle document/table IDs and convert them to SurrealDB record IDs. This is fine for the primary ID, but we would need to convert all ids (any column/field whose value points to a foreign table or foreign collection) in both document and relational database formats. I'm not sure how we would detect that it is a foreign id, as opposed to just an integer for example.

Those are just the initial thoughts from the top of my head.

Happy to hear any thoughts you have!

[mumoshu]

@tobiemh Hey! Thanks a lot for your reply.

surreal sync --from mysql://$host:$port/$database --conn https://cloud.surrealdb.com --ns myns --db mydb

Looks great 👍 If I could add one thing, a flag like --start $GTID to let it start replication from the specific binlog position would be a nice to have.

How would we differentiate between a one-off sync, and a continuous sync?

My quick idea is that we don't need to differentiate between the two?

I imagine that an one-off sync can fail at any time due to various reasons(like a transient network failure). If failed, one would like to "resume" the sync from the previous binlog position anyway, and this basically matches how a continuous sync would work(start form the beginning, resume from the last replicated position on restart). Perhaps a flag to specify if one wants to auto-restart the replication or not would be useful though.

If one knows the final binlog position or the final sortable primary ID to be replicated beforehand, it might be nice if one could let surreal-sync respect it and finish the replication once the sync observed the final pos/ID. Perhaps commands like surreal sync --end-primary-id $ID or surreal sync --end $GTID would work for such use case. Without the definition of "the final position/id", there can be no "one-off sync", as it has no way to know if it should have no way to know that it reached the end of the binlog.

I don't want the CLI app to have too many dependencies (and I envisage quite a few dependencies for all of the source databases, and any additional libraries needed). Therefore, I'm thinking that it might be good to have all of the syncing logic in a separate library / repository (surreal-sync), and the cli command just instantiates and uses functionality from the library. This would also mean that the syncing functionality can be used as a library.

Fully agree 👍

What happens if the SurrealDB database is being continuously synced (say from MongoDB), and data in SurrealDB is then edited. Should it sync those changes back to MongoDB?

My personal belief was that bidirectional syncing has many edge cases so any user should avoid it and it should be the last thing to be implemented in a data syncing system! But I'm still curious about how it would work and eager to know the state-of-the-art in this area.

Obviously we would have to handle document/table IDs and convert them to SurrealDB record IDs. This is fine for the primary ID, but we would need to convert all ids (any column/field whose value points to a foreign table or foreign collection) in both document and relational database formats. I'm not sure how we would detect that it is a foreign id, as opposed to just an integer for example.

I was wondering if we could defer the decision to keep using the primary IDs or to migrate to SurrealDB record IDs to the user.

My idea was that surreal sync would add its own record IDs to the documents created from the MySQL table rows anyway while adding the index to map surreal record IDs to primary IDs of the MySQL rows. This way, surreal sync can use the primary ID index to efficiently fetch the record ID of the row to be updated due to a change obtained from the MySQL binlog. Similarly, app can keep using the ordinary primary IDs for fetching migrated records from SurrealDB, while possibly using SurrealDB record IDs for newer records.

This also has the benefit that the app can read either from MySQL or SurrealDB by using the ordinary primary ID, with no need to implement the primary ID -> record ID mapping in the app while migrating.

[rushmorem]

My personal belief was that bidirectional syncing has many edge cases so any user should avoid it and it should be the last thing to be implemented in a data syncing system!

I totally agree with this. Having a single source of truth would make it easier to implement this more reliably. I think it could be done using something like Conflict-Free Replicated Data Types but I don't know if the effort is worth the added complexity. If the end goal is just migrating data, I don't think it's worth it.

As for having this out of tree, here are some of the viable ways of doing this I can think of:-

1. Make surreal-sync a crate containing a trait and some common data types, if any, that will be used by all the crates implementing this feature. While this works and is the most performant of all the methods I'm going to suggest, I think it only encourages Rust developers to develop such tools.
2. Use a language-neutral data format like Protobufs to encode the information we want all the libraries to implement. With this approach, we would probably require developers to implement an intermediary server, using something like gRPC perhaps. This is heavier than the first option but it is language agnostic.
3. Making surreal sync simply defer to external commands that are named a certain way, just like what Cargo does. For this to work, the command will probably need to be something like surreal sync mysql which would then call surreal-sync-mysql.

[rushmorem]

For RethinkDB we can use table change feeds to watch all changes to all tables in a database.
Document IDs would need to be converted into SurrealDB record IDs.
https://docs.rs/reql

I'm the maintainer of that crate. This is something I'm definitely interested in and something I need. I have just submitted a PR on the nanoid repo that makes it possible to convert anything to a Nano ID. This will help with converting RethinkDB's UUID based document IDs to record IDs.

[rushmorem]

You mean RustExplorer? Yes it's very cool. It supports way more crates than the official playground.

[tobiemh]

Yeah that's cool. No I meant your addition for converting UUIDs to nanoids!

[tobiemh]

Going back to something @mumoshu said up above...

My idea was that surreal sync would add its own record IDs to the documents created from the MySQL table rows anyway while adding the index to map surreal record IDs to primary IDs of the MySQL rows. This way, surreal sync can use the primary ID index to efficiently fetch the record ID of the row to be updated due to a change obtained from the MySQL binlog. Similarly, app can keep using the ordinary primary IDs for fetching migrated records from SurrealDB, while possibly using SurrealDB record IDs for newer records.

Ideally we would keep the same IDs as are found in MySQL / PostgreSQL / RethinkDB / MongoDB.

Document database

So in a document database, a document in the 'person' collection would map from

{
    id: "5349b4ddd2781d08c09890f3",
    name: "Tobie"
}

to

{
    id: "person:5349b4ddd2781d08c09890f3",
    name: "Tobie"
}

Relational database

So in a relational database, a row in the 'person' table would map from

| id     | name     |
| 1      | "Tobie"  |

to

{
    id: "person:1",
    name: "Tobie"
}

This way, working with both databases alongside each other as you move to SurrealDB will be so much easier. It would also allow for easy sync into SurrealDB, and also two-way sync potentially.

[rushmorem]

I'm sorry, I hadn't gone through the entire discussion yet. That was my initial plan too until I tried something like:-

CREATE person:fb2cd733-eec4-4354-b070-3aba18c2a67a;

which gives an error. I didn't give it much thought at the time. I just thought that UUIDs cannot be used unquoted. I didn't realise that UUIDs without hyphens work just fine. That's awesome!

[tobiemh]

Yeah, so the SurrealDB Record IDs support:

1. Integers: person:1, person:1000
2. Strings: person:test, person: 5349b4ddd2781d08c09890f3
3. Complex strings: person:⟨fb2cd733-eec4-4354-b070-3aba18c2a67a⟩ (can also use backticks, but I don't want to deal with Markdown escaping)
4. Arrays: person:['London', '2022-09-01']
5. Objects: person:{ city: 'London', date: '2022-09-01' }

Although SurrealDB does parse and store UUIDs as a separate type, for record IDs they are treated as strings, and sorted accordingly with other strings.

[scheung38]

Hi @here, is this implemented yet? Dont see this flag ?

[rushmorem]

It's not implemented yet @scheung38.

[cope]

Ideally we would keep the same IDs as are found in MySQL / PostgreSQL / RethinkDB / MongoDB.

Ideally, this would only be the default, but we would allow users to pick which field(s) to use as the ID... 💯

For example, if I am migrating my employees table, I would love to use employeeId as the id, instead of the current (meaningless) mongodb ID, especially since we reference employeeIds in other collections.

[nickchomey]

@tobiemh I'd like to suggest a different approach - surrealdb is already extremely ambitious, so don't recreate the wheel for something outside the core competency/focus, and instead just leverage existing CDC tools, like Conduit and Debezium, to implement this. Perhaps Benthos as well, though that's less for database CDC.

Conduit is simpler, more powerful (allows transformation through a pipeline), and has far more sources/connectors than Debezium, so it probably makes sense to support that. All that would be needed is to make a connector that can receive whatever stream has been output from the pipeline, regardless of original source.

https://conduit.io/docs/connectors/connector-list/ (toggle the unreleased connector filter, as that's what most of them are)

Here's vitess/mysql https://github.com/conduitio-labs/conduit-connector-vitess

Postgres https://github.com/ConduitIO/conduit-connector-postgres

Mongo https://github.com/conduitio-labs/conduit-connector-mongo

Neo4j https://github.com/conduitio-labs/conduit-connector-neo4j

There's S3, and so much more. It also integrates with NATS, Kafka etc for distributed event driven architectures.

Also, surrealdb could serve as a source connector as well via its live queries etc - which could then easily feed into NATS, Kafka etc.

I've created an issue to request a conduit connector for surrealdb here.
ConduitIO/conduit#1594

This wouldn't preclude native integrations with 3rd party DBs if you decide to do that someday, but it would be a very low effort way to integrate surreal with essentially all of the popular DBs, data stores, and more.

[gedw99]

thanks @hariso

if you can send me a link to a repo with these examples or others I can try it out and help

[hariso]

@gedw99 If you go to the built-in processors page, you'll find an example for every processor.

Similarly, if you go to the standalone processors page, you'll find a link to examples repository at the end the page.

[gedw99]

Hey @hariso

From the makefile it looks WASI based . This looks very forward thinking. This is a compelling approach because it solves the privacy issue using a wasi sandbox.

[nickchomey]

I just brought this topic up in @tobiemh 's AMA today in Discord. He said that this is indeed a very important topic for surreal (as shown by how this is discussion topic #3), since one of the primary goals of Surreal is to simplify backend architectures by consolidating many databases into one.

However, their focus is currently (quite appropriately) on finishing up their v2 release which will bring more stability, performance etc... He has previously indicated that it should be released in the next few months.

He then said that once v2 is out, they'll be eager to turn their attention to things like this. As such, he will be quite happy to discuss using Conduit for this with the Meroxa team, and collaborate as necessary.

I'll follow up here after v2 has come out. But, in the meantime, perhaps @gedw99 and I could take a stab at a basic connector to get the ball rolling?

[nickchomey]

Also, I just created this discussion in the conduit repo. The idea is that rather than use Benthos as a Conduit source/destination, it should be used as a Conduit Standalone Processor. This would allow conduit to focus on CDC inputs, leverage Benthos' strength/focus on simple stream processing, and then output it to a Conduit Destination (surrealdb in this case).

I think I'll take a stab at it in the coming weeks.

ConduitIO/conduit#1614