SyncLite: A Novel CDC and Replication Framework for Embedded Databases

SyncLite · Medium ·

5 min read Original article ↗

SyncLite: A Novel CDC and Replication Framework for Embedded Databases

SyncLite

Embedded Databases

Embedded databases are among the most widely deployed software solutions. They are lightweight, efficient, embedded within applications to manage data locally without requiring a separate database server. They are designed to be easy to use and integrate seamlessly into applications, making them ideal for edge, mobile, and desktop environments. Examples of popular embedded databases include:

  • SQLite: A self-contained, serverless, zero-configuration SQL database engine. It is widely used in mobile applications, desktop applications, and IoT devices.
  • DuckDB: An in-process SQL OLAP database management system. It is designed to support analytical queries, making it suitable for data analysis and scientific computing.
  • Apache Derby: An open-source, embedded relational database implemented entirely in Java. It is easy to embed in Java applications and offers a lightweight solution for database management.
  • H2: A Java-based, open-source, lightweight, embedded relational database engine. It offers fast performance and is commonly used for testing, development, and small-scale applications.
  • HyperSQL(HSQLDB): A relational database management system written in Java. It supports a wide range of SQL standards, provides in-memory and disk-based tables, and is often used for development, testing, and lightweight production applications.

The Need for Real-Time Replication and Consolidation

As edge computing scenarios rise, the need for real-time data replication and consolidation from embedded databases into industry-leading cloud-hosted databases has become critical. Edge, mobile, and desktop applications generate vast amounts of data that need to be analyzed, processed, and stored efficiently. Real-time replication ensures that data is always up to date, providing several benefits:

  • Improved Data Availability: Ensures that data is consolidated from all edge applications into one system and made available for analytics and decision-making in real-time.
  • Enhanced Performance: Reduces latency by processing data closer to the source and consolidating it into powerful cloud databases for further analysis.

The Challenges

Despite its importance, real-time replication and consolidation from embedded databases remain a challenging and unsolved problem due to several reasons:

  1. Lack of Built-in Change Data Capture (CDC) Functionalities: Most embedded databases do not have built-in CDC capabilities, making it difficult to track and replicate changes in real-time.
  2. Comprehensive Data Handling: It is challenging to perform replication comprehensively, covering all types of Data Definition Language (DDL) and Data Manipulation Language (DML) operations while preserving transactional semantics.
  3. Framework Complexity: Developing a framework that supports multiple embedded databases at the source and a wide range of databases, data warehouses, and data lakes at the destination adds complexity.

How SyncLite Addresses the Challenge

SyncLite provides a robust solution to these challenges with its innovative approach to embedded database replication and consolidation.

SyncLite EdgeDB aka Logger

  • Single Java Library (JDBC Driver): SyncLite Logger encapsulates various embedded databases such as SQLite, DuckDB, Apache derby, H2 and HyperSQL, allowing user applications to perform transactional SQL operations on their chosen embedded databases, while capturing and writing them into log files.
  • Staging Storage: The log files are continuously staged on a configurable staging storage such as S3, SFTP, MinIO, Kafka, etc.
  • SyncLite Consolidator: A Java application that continuously scans these log files from the configured staging storage, reads incoming command logs, applies them on a replica (embedded) database, 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, data type mapping, EL(T) trigger installation, fine-tunable writes, support for multiple destinations etc.

Press enter or click to view image in full size

Current and Upcoming Support

  • Embedded Databases: Currently supports SQLite, DuckDB, Apache Derby, H2 and HyperSQL, with upcoming support for more SQL, NoSQL and document embedded databases.
  • Destination Databases: Supports PostgreSQL, MySQL, MongoDB, Apache Iceberg tables, and many more industry-leading databases, data warehouses, and data lakes.

Getting Started

SyncLite offers a novel, flexible and powerful solution for real-time data consolidation from your applications using your favorite embedded database(s) into various cloud destinations. SyncLite is open source under Apache License 2.0. Get start with the GitHub repository. SyncLite Logger is available as maven dependency. Add following maven dependency in your application:

<! -  https://mvnrepository.com/artifact/io.synclite/synclite-logger-extended 
 →
 <dependency>
 <groupId>io.synclite</groupId>
 <artifactId>synclite-logger-extended</artifactId>
 <version>#Replace_This_With_Latest_Version#</version>
 </dependency>

SQLite: Following is how you can use SyncLite logger to create and use a SQLite database/device in your application


Class.forName("io.synclite.logger.SQLite");
Path dbPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\test1.sqlite");
Path confPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\synclite_logger.conf");
SQLite.initialize(dbPath, confPath);
try (Connection conn = 
   DriverManager.getConnection("jdbc:synclite_sqlite:" + dbPath) {
     …
 }
SQLite.closeAllDevices();
  • DuckDB: Following is how you can use SyncLite logger to create and use a DuckDB database/device in your application
Class.forName("io.synclite.logger.DuckDB");
Path dbPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\test1.duckdb");
Path confPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\synclite_logger.conf");
DuckDB.initialize(dbPath, confPath);
try (Connection conn = 
    DriverManager.getConnection("jdbc:synclite_duckdb:" + dbPath) {
     …
}
DuckDB.closeAllDevices();
  • Apache Derby: Following is how you can use SyncLite logger to create and use a Derby database/device in your application
Class.forName("io.synclite.logger.Derby");
Path dbPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\test1.derby");
Path confPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\synclite_logger.conf");
Derby.initialize(dbPath, confPath);
try (Connection conn = 
    DriverManager.getConnection("jdbc:synclite_derby:" + dbPath) {
     …
}
Derby.closeAllDevices();
  • H2: Following is how you can use SyncLite logger to create and use a H2 database/device in your application
Class.forName("io.synclite.logger.H2");
Path dbPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\test1.h2");
Path confPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\synclite_logger.conf");
H2.initialize(dbPath, confPath);
try (Connection conn = 
    DriverManager.getConnection("jdbc:synclite_h2:" + dbPath) {
     …
}
H2.closeAllDevices();
  • HyperSQL(HSQLDB): Following is how you can use SyncLite logger to create and use a HyperSQL database/device in your application
Class.forName("io.synclite.logger.HyperSQL");
Path dbPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\test1.hsqldb");
Path confPath = Path.of("C:\\users\\bob\\synclite\\job1\\db\\synclite_logger.conf");
HyperSQL.initialize(dbPath, confPath);
try (Connection conn = 
    DriverManager.getConnection("jdbc:synclite_hsqldb:" + dbPath) {
     …
}
HyperSQL.closeAllDevices();

Explore SyncLite Further

Reach Out and Collaborate

For support, feedback, and collaboration opportunities, contact us at support@synclite.io. SyncLite is poised to transform data-intensive application development, try it out.

Hashtags

#SyncLite #DatabaseReplication #EdgeComputing #EmbeddedDatabases #DataConsolidation #SQLite #DuckDB #ApacheDerby #H2 #CDC #EmbeddedCDC #ApplicationCDC #DataIntegration #RealTimeData #AI #ML #DataEngineering