mahcha

CDC is usually about capturing changes from one database and replicating them to another.

But what if thousands of (offline-first) applications each have their own local database?

We have been exploring “CDC for applications”, capturing changes as the application runs and continuously synchronizing them from local SQLite databases to a central database.

We built SyncLite around this idea and would love feedback from HN on the architecture and the problem itself.

Hey HN,

We building a cross-language library that wraps SQLite and enables it to sync data to a centralized database, with no external service required.

It works with the existing SQLite programming model, so applications can continue using SQLite locally while data is synchronized to a central database.

Happy to receive feedback, criticism, or ideas on where this could be useful.

SyncLite currently supports unidirectional replication/consolidation from embedded database to destination database. Reverse path from destination to local db(with conflict resolution) is on the roadmap.

Each write operation (DDL + DML) is transactionally captured by the synclite logger ( a wrapper over underlying embedded db) and logged into the log file. So, it handles all schema operations create, drop, alter ( add/drop/rename column), rename column/table, in addition to the dml operations insert/update/delete.

There is an interesting pattern SyncLite (https://github.com/syncliteio/SyncLite) attempts to solve to bring the best of both SQLite and Postgres with one or more SQLite databases directly serving the application while SyncLite replicating/consolidating data from all those SQLite databases into a centralized PostgreSQL database..

Hello HN,

Thank you for feedbacks on our earlier introductory post about SyncLite, and we have been incorporating it !

Earlier, we posted about a specific case of replicating app-embedded DuckDBs into a centralized PostgreSQL database.

We would like further highlight SyncLite as a generic data consolidation framework to replicate/consolidate data from edge/mobile applications using various popular embedded databases: SQLite, DuckDB, Apache Derby, H2, HyperSQL into various centralized industry leading databases: PostgreSQL, MySQL, MongoDB, DuckDB SQLite, etc.

We would love to get suggestions for improvements, new features/functionalities, new connectors etc.

Brief summary of SyncLite's core infrastructure:

SyncLite Logger: is a single Java Library (JDBC Driver): SyncLite encapsulates popular embedded databases: SQLite, DuckDB, Apache Derby, H2, HyperSQL(HSQLDB), allowing user applications to perform transactional operations on them while capturing and writing them into log files.

Staging Storage: The log files are continuously staged on a configurable staging storage such as S3, MinIO, Kafka, SFTP, etc.

SyncLite Consolidator: A Java application that continuously scans these log files from the configured staging storage, reads incoming command logs, translates them into change-data-capture logs, and applies them onto one or more configured destination databases. It includes many advanced features such as table/column/value filtering and mapping, trigger installation, fine-tunable writes, support for multiple destinations etc.

While the title mentions about one combination, the framework is generic to handle data consolidation from - numerous applications which may be using one or more popular embedded databases: SQLite, DuckDB, Apache Derby, H2, HyperSQL and - into a wide range of industry leading databases including PostgreSQL, MySQL, MongoDB, DuckDB and more.

A potential use-case for consolidating data from many duckdbs into a destination PostgreSQL + PGVector would be to build Edge first Gen AI,Rag Search applications using DuckDB's vector storage and search capabilities, while enabling real-time data data + embeddings consolidation from all application instances into a centralized PG + PGVector to readily enable global RAG applications.

More details here:

https://www.synclite.io/solutions/gen-ai-search-rag

Thank you @Terretta for your thoughtful feedback on SyncLite! I appreciate your suggestions for a clearer and more practical introduction. Your version definitely captures the core benefits more effectively, and we will be revising the README and documentation to reflect this more straightforward approach.

We will ensure our messaging accurately represents SyncLite's capabilities. Thanks again for your input!

1. Database Replication/Consolidation for embedded databases used in edge/desktop applications, into centralized databases/a data warehouse/a data lake

2. A Kafka Producer and SQL API for streaming data from edge/desktop applications into centralized databases/data warehouses/data lakes.

And then there are tools built on top of this infra: Database ETL tool, IoT data connector tool etc.

The framework is generic to handle data consolidation from - numerous applications which may be using one or more popular embedded databases: SQLite, DuckDB, Apache Derby, H2, HyperSQL and - into a wide range of industry leading databases including PostgreSQL, MySQL, MongoDB, DuckDB and more.

A potential use-case for consolidating data from many duckdbs into a destination PostgreSQL + PGVector would be to empower developers build Edge first Gen AI,Rag Search applications using DuckDB's vector storage and search capabilities, while enabling real-time data data + embeddings consolidation from all application instances into a centralized PG + PGVector to readily enable global RAG applications.

More details here:

https://www.synclite.io/solutions/gen-ai-search-rag

https://medium.com/@mahendra.chavan/synclite-bridging-the-ga...

Thank you @haswell for the feedback!

We will improve this further: Here is a brief summary updated in the README at the start:

SyncLite is an open-source, no-code, no-limits relational data consolidation platform empowering developers to rapidly build data intensive applications for edge, desktop and mobile environments. SyncLite excels at performing real-time, transactional data replication and consolidation from a myriad of sources including edge/desktop applications using popular embedded databases (SQLite, DuckDB, Apache Derby, H2, HyperSQL), data streaming applications, IoT message brokers, traditional database systems(ETL) and more into a diverse array of databases, data warehouses, and data lakes, enabling AI and ML use-cases at all three levels: Edge, Fog and Cloud.

Thanks @buremba

Absolutely agree on "combination of cloud and embedded databases is the future IMO"

Universql looks interesting as well.

SyncLite also provides an ability to send back custom commands from SyncLite consolidator to individual applications(devices) while edge/desktop applications can implement callbacks to be invoked on receiving these commands. A command can be anything and could be a away to tell the application to download data from a cloud hosted data warehouse and use it as a starting point.