Database generated events: LiveSync’s database connector vs CDC
Tags: database, dataengineering, architecture
Published: 2024-06-20 08:34:06

[Ably LiveSync is a product we launched last month](https://hubs.la/Q02CzvQ20) to help developers deliver live updates in their applications by automatically keeping their database and frontend clients in sync.

[LiveSync](https://hubs.la/Q02CzvRD0) is made of two components, the [Models SDK](https://hubs.la/Q02CzvS_0) that runs on the client, and the [database connector ](https://hubs.la/Q02CzvSw0)that listens to changes in your database and syncs those changes to your clients.

When we talk about ‘listening to your database’, we’re often referring to Change Data Capture (or CDC). In CDC a component listens to your database and distributes events representing the changes that have happened. Normally the events that are created by CDC represent the changes to each individual underlying row. 

**In this post, we’re going to discuss how the LiveSync database connector works, and how and why it is different from CDC.**

[Change data capture](https://en.wikipedia.org/wiki/Change_data_capture) (or CDC) is a mechanism of listening to the changes in your database and sharing these (typically over a queue) with other consumers. It allows other participants in your system to react to changes that have happened in one part of the system. A classic example is in some ‘event driven’ architecture where an email service will send a confirmation email when some e-commerce ‘order’ is created. Typically the changes that are shared as events by CDC represent changes to each individual underlying database table. For example, a change of email address for a user would have the user record’s ID, and email address before and after the change.

LiveSync’s database connector is different from traditional CDC, and we made some intentional choices about how and why it should be different. Our database connector is based on the ‘outbox pattern’, where a specific table is used to share database changes with the connector. Rows written to the outbox table have a ‘data’ field where the content of that column will be the payload of the event that’s generated and shared over Ably channels by the LiveSync database connector. The rows in the outbox also let you choose which Ably channel a record should be sent to.

So given both CDC and the LiveSync database connector generate events to be shared with some consumers, what’s the difference between the two? Well it’s all about encapsulation and control; two things we wanted to make sure LiveSync baked in, which you might not get with traditional CDC.

In traditional CDC:

- **Joins are hard:** Typically CDC events are generated per-table, this means that for any frontend data model that’s made of more than one database table, where you’d normally be writing a join in your database query, it’s really hard to work with in CDC. Events representing changes to two different tables have to be glued back together after they have been generated by a CDC system (perhaps using transaction ID metadata attached to the events).
- **Encapsulation is hard:** Because traditional CDC operates on the database table level, it’s far too easy to accidentally share some internal data model details with the consumers of your CDC events. Lots of CDC systems do allow you to filter the fields included in the CDC events, but it’s still an easy mistake to make.
- **Access control is hard:** It’s hard to encode into CDC which consumers should get access to which pieces of data. Typically all changes for a specific table go into a specific queue. Some CDC products do allow the partitioning of events into separate queues or topics based on a field of the changed row, but this isn’t always enough. Especially when you need data from a different table to enforce your access requirements (see: ‘joins are hard’). You end up having to create some filtering or mapping somewhere that can be queried server-side to work out if a client is allowed to see a row. (This is exactly what Supabase Realtime does, where the database has to be re-queried on each change event to enforce Row Level Security).

When we built the LiveSync database connector we wanted to make it easy to work with database generated events, and solve the things that made it hard to work with CDC. LiveSync is different firstly because it uses the ‘[outbox pattern](https://en.wikipedia.org/wiki/Inbox_and_outbox_pattern#The_outbox_pattern)’. Using the outbox table, you get to control the content of the events that are shared by the database connector. You also get to control which Ably channel those events are published on. These two differences solve a bunch of the problems with traditional CDC.

- **You can use channels to help with access control:** Ably’s [capabilities](https://hubs.la/Q02CzvWW0) mechanism allows you to authorise clients to only perform specific operations (publish, subscribe, etc) on specific [channels](https://hubs.la/Q02Czw6s0). This allows you to publish only the information relevant to those clients on those channels. And because you get to control the channel a database event is written to at the time the event is created, you can dynamically control which clients get access to which data.
- **You can expose only the data the clients need:** Because you get to specify the content of the event that’s written to an Ably channel, you can publish the exact content that the frontend needs. This solves the problems that CDC has with joining events from multiple tables, and solves the oversharing that can happen with CDC. So enforcing the right encapsulation and joins on the data is much easier.

Traditional CDC products can be faster to ‘plug and play’, as they will connect to your database without any changes to your application and start sharing database changes. But there’s a trade-off, enforcing the access control, encapsulation, and joins that your application needs is much harder.

In LiveSync we make these problems easy, by giving you an outbox table allowing you to choose which events are published, where those events are published, and the content those events carry. You can write events to the outbox transactionally along with the other changes in your database to ensure that the publish of messages to Ably and changes in your database either both happen, or neither happen. The outbox table along with the database connector guarantees that events written to the outbox table will appear in Ably, and will retain their order within each channel you’re writing to.

## Get started with LiveSync now

Sign-up for a free account and [dive into our docs](https://hubs.la/Q02CzwyF0) to give LiveSync a try. If you are interested in being an alpha tester, or would like to provide feedback on the product, please [get in touch](https://docs.google.com/forms/d/e/1FAIpQLSd00n1uxgXWPGvMjKwMVL1UDhFKMeh3bSrP52j9AfXifoU-Pg/viewform) - we’d love to collaborate!