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 mysql (it's being actively developed right now) https://github.com/conduitio-labs/conduit-connector-mysql

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, redis 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.

[nickchomey]

@gedw99 yes, I already described in the original comment what conduit is and how it would be of use here. The things you've mentioned are certainly possibilities, but far beyond the scope of what is being proposed here - simply be able to continuously import/sync data to surrealdb from other databases. (and, perhaps someday, make a surrealdb source connector that leverages live queries or change feeds to export data from surreal to other databases).

I hope to start doing some basic work on the destination connector today, with the goal of making and syncing 1:1 replicas of mysql tables in surrealdb. When that is working, further functionality can be discussed and implemented (eg somehow replacing primary and foreign keys with surreal record ids, adding indexes etc. The destination connector may or may not be the appropriate place for such things)

[hariso]

Just an FYI, we recently added support for schemas, in case it helps you.;)

[nickchomey]

@hariso Thanks very much. I had read about it in the changelog and even read the schema docs, but it wasnt at all evident to me what it was for. But I just found your announcement blog post which goes into a decent amount of detail. (perhaps a link to that post could be added to the schema docs, along with links to other tutorials that you find to be useful for describing the benefits of avro schemas in a cdc pipeline)

Still, would you mind elaborating a bit on what the schema could do for a destination connector like this? Probably creation of non-existent tables and updating their columns when the source changes. What else could be done?

Thanks!

[hariso]

@nickchomey I'd say that all the general benefits of having a schema (i.e. information about all the types in a record) apply to Conduit. So yes, you could use the schema to create a non-existing table.

Another benefit is that we have more freedom with the types we use in a pipeline, because we're not limited to the serialization mechanism (Protobuf/JSON have a fairly limited set of types they support out of the box). Some examples are timestamps (there's no Protobuf well-known type for timestamps) and integers (integer sizes are preserved, and so an 8-bit integer will arrive to a destination as an 8-bit integer, and not a float, as it would with Protobuf and JSON).

This is important because the destination will get the record as it was originally, and doesn't have to do conversions.

Having schema support also makes it possible to work with other data streaming tools (e.g. Kafka Connect), etc.

[nickchomey]

Just a quick update. A preliminary version of a SurrealDB Conduit Connector is almost ready to share and get feedback on (and hopefully PRs contributed!). I got stuck fixing various important bugs with their alpha Mysql connector, as well as trying to figure out the work-in-progress surrealdb.go sdk.

I just tested importing 30 tables with 4500 rows from Mysql to Surrealdb. With an initial row-by-row insert, it took 17 seconds. Then I implemented a bulk insert mechanism and it takes 0.5 seconds (times are for insertion. It takes maybe 3.5 seconds from startup of Conduit, to extraction from Mysql, to insertion in surrealdb) .

I just need to tidy up the code and add support for real-time Update and Deletions (unfortunately those will be row-by-row rather than bulk, as a PR for bulk Update and Upsert support has been sitting in limbo for a couple of months).

[PackMeister]

I'm VERY interested in using SurrealDB as the all-tier database for some forthcoming offline-first/local-first application projects.

Such apps are designed to work autonomously offline but to synchronize local and cloud-resident changes when a connection is available, perhaps via a websocket.

The ideal would be if local and remote instances of SurrealDB could synchronize as automatically and flexibly as say, CouchDB.

https://couchdb.apache.org/

Now that SurrealDB is at 1.5.* what is the status and/or roadmap for how distinct instances of SurrealDB might synchronize with each other natively or through 3rd party crates for replication and/or synchronization?