Roadmap 2024

[carlsverre]

Welcome to the SQLSync Roadmap. This discussion will be used to keep track of high level goals on the project. If you'd like to discuss please open additional discussions.

Since the initial release of SQLSync, there have been a few changes such as Solid JS support (#37) and server side queries/mutations (#43). Other than that most of the changes have been to address bugs and improve quality of life for early adopters. But, the real work has been happening behind the scenes.

In the coming months, SQLSync will be upgraded to a new content-addressed replication architecture. This system will provide many benefits and unlock new use cases. Here is a taste of what's to come:

• Lazy replication: clients can choose to only pull what they need from the database rather than the entire thing
• Computation offload: queries and mutations can be offloaded to serverless functions on the edge to improve cold start performance
• Permissions: Role based permissions at the table and index level with row level coming later
• More databases: The new storage engine implements transactions over storage directly, allowing for any other in process database to be used. Eventually plugins for all popular storage APIs (Postgres, Mongo, basic K/V, file, graph) will be provided.
• And more... :)

The north start is to support CRUD based applications which wish to leverage optimistic mutations and cache data on the device without needing to roll their own complex replication, conflict, and consistency engine. Leave the hard stuff to SQLSync so you can focus on your business instead.

[ansarizafar]

This feels similar to https://www.skdb.io/book/

[carlsverre]

SKDB is a very cool project and has similar goals to SQLSync, but as always the devil's in the details. I'll mention two differences that may help someone in deciding which solution to use. Take this with a grain of salt since both technologies are very early stage and may change quite a bit between now and production.

Replication strategy
It's hard to be 100% sure without reading the code, but based on SKDB's docs they are replicating logical changes to clients. By logical I mean they are moving around inserts/deletes and applying those changes on devices. It's not clear how they handle conflicts (two inserts with the same unique key for example).

In SQLSync, we replicate physical pages from a centralized leader to all of the followers. Mutations are synced from clients to the server, globally ordered, and then applied. Conflicts are resolved on the leader within the mutations themselves. Think of this like an API server. In a traditional API server, concurrent API calls may conflict - in which case your API server logic would handle the conflict directly and either error or do something else. This approach ensures that the physical database is always correct (no possibility of physical conflicts causing corruption) as well as logical correctness (the API handler and transaction machinery requires that conflicts are handled and provides a place for your business logic to handle them).

The trick to making this approach multiplayer lies in optimistic mutations and rebase. Since mutations can run on both the server and the client, we run them optimistically against the local database. Later, when we receive changes from the server - we check to see which of our mutations have been applied. Clients then only need to re-execute mutations still pending. This simulates a multiplayer experience for clients on top of physical replication from the leader.

Now, physical replication like SQLSync makes permissions more complicated as permissions tend to execute at the logical layer (i.e. you think about which rows someone can see, not which portions of the SQLite file). This is an open area of research for SQLSync, but I have a couple of solutions in mind that don't seem too bad.

SQLite
Another key difference between SKDB and SQLSync is the database engine itself. As far as I can tell SKDB has written a SQL engine from scratch which appears as SQLite but internally is not. This allowed them to make reactive views a reality and generally eliminate a lot of tech debt and backwards compat SQLite has accumulated over the years.

But, it's not SQLite. SQLSync is 100% vanilla SQLite which in my opinion is the best embedded SQL database currently available. SQLSync's strategy is to bet on well tested bulletproof database engines, and figure out how to enable multiplayer without changing their core semantics. And yes, as hinted in the roadmap above - SQLSync's new replication engine will work with other databases and storage layers.

Summary
There are many differences between the technologies, and many similarities. As a database engine, SKDB is extremely impressive considering it's built from scratch using a new programming language created (seemingly) just to build SKDB. On the other hand, SQLSync is focused on providing a general purpose storage and replication engine that brings multiplayer to traditional databases.

[frederikhors]

Hi @carlsverre.

In my company they are suggesting to use RxDB. But I would like to test sqlsync first.

Is there a way to beta-test your news?

[carlsverre]

Hey @frederikhors
I would love to discuss the potential of using SQLSync at your company. I should update this issue though, as I've pivoted off of content addressability for a more scalable alternative. That alternative is close to release, but unlikely to be production ready in 2024. Shooting for a launch early 2025 at this point.

The easiest way to move forward is to book a call with me to talk about what I'm working on and whether or not it would make sense for your application. You can do that direcly via my office hours link here.

[frederikhors]

I cannot talk about company projects unfortunately. What do you think about https://electric-sql.com/blog/2024/12/10/electric-beta-release and https://electric-sql.com/product/pglite?

[carlsverre]

I think that Electric is building a great general purpose logical sync engine! And Pqlite is very cool, would love to make SQLSync work with PQlite as well as SQLite if possible. Happy to talk about it more on a call :)