7 ms·

Show HN: Replicate App-Embedded DuckDBs into PostgreSQL with SyncLite OSS

73 points by syncliteio 2y ago

mahcha 2y ago
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.
syncliteio 2y ago
SyncLite Open Source (https://www.synclite.io/ https://www.synclite.io/) ==> - SyncLite Logger, a wrapper JDBC driver, supporting popular embedded databases (SQLite, DuckDB, Apache Derby, H2, HyperSQL), creates transactional sql logs for embedded dbs, ships them to configured staging storage ( S3/MinIO/SFTP/Kafka/OneDrive/GoogleDriver etc) - Several application instances each creating multiple embedded databases/devices are synchronized in real-time on the staging storage. - SyncLite consolidator, a centralized tool, continuously processes device logs, generates CDC logs, replicates and consolidates staged devices into a wide range of industry leading database, data warehouses and data lakes. Look forward to feedback and suggestions.
matharmin 2y ago
Is this Java/JVM-specific? How are changes captured on the client? From reading through the docs it seems like the CDC work is done in the JDBC driver (SyncLite Logger), but I had to read quite far to see that, and it's difficult to put together how it works and in what types of applications this can be used. For example, can I capture changes to a SQLite or DuckDB database in an Android or iOS application? I'm interested to see the approaches used, since there is very little out there for capturing changes in embedded databases.
- syncliteio 2y ago
  SyncLite Logger is JDBC wrapper/driver, can be consumed in Java and Python applications: Few code samples are shown here: https://github.com/syncliteio/SyncLite https://github.com/syncliteio/SyncLite and https://github.com/syncliteio/SyncLite/tree/main/synclite-code-samples https://github.com/syncliteio/SyncLite/tree/main/synclite-co... A jsp-servlet sample web app is here: https://github.com/syncliteio/synclite-sample-web-app https://github.com/syncliteio/synclite-sample-web-app SyncLite logger transactionally captures the exact sql statements (DDLs and DMls) as executed on the underlying embedded databases, into sqllog files. The log files for each device (i.e. database) are shipped to a configured staging storage(Local FS/SFTP/S3/MinIO/Kafka etc). SyncLite consolidator continuously looks for new devices and new logs for each devices. The received raw sql logs are not directly applicable on final destination as they are logical statements, may involve complex dependencies/joins e.g. insert into select <cols> from t1 JOIN t2 ... Consolidator applies the incoming sql logs transactionally to a native embedded database (SQLite) and in the process generates record level CDC logs by identifying exact changed records and tables The CDC logs are record-level INSERT, UPDATE, DELETE statements, DDL statements : CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE. The CDC log files as generated are applied to final destination databases. A summary of this is here : https://www.synclite.io/synclite/sync-ready-apps https://www.synclite.io/synclite/sync-ready-apps Thank you for the feedback and we are improving the documentation.
  - matharmin 2y ago
    Thanks, that gives a good overview
eknkc 2y ago
I’ve read the initial paragraphs of Readme and I have no idea what it does. I think a technical no buzzword summary would be good.
- syncliteio 2y ago
  SyncLite at the very core offers two key capabilities : 1. A holistic solution for Database Replication/Consolidation for numerous embedded databases used in several instances of edge/desktop applications, into centralized databases/dw/data lake etc. 2. A holistic data streaming solution offering a Kafka Producer and SQL API designed for final destination data delivery.
  - underbiding 2y ago
    "holistics", "edge", "final destination data delivery" are all buzz words.
  - haswell 2y ago
    Please take this as well-intentioned feedback. #1 and #2 are really difficult to parse. A really carefully considered elevator pitch that distills the essence of your product into concrete use cases would go really far in helping people understand what you're building. e.g. I think you're trying to say: SyncLite helps <developer?/dba?/who?> replicate databases from a variety of apps into a centralized location <for what purpose?>. What's not clear is what problem this is solving, or what the ultimate goal is. Same comment for data streaming. I can imagine a few, but would prefer not to imagine. Assume some of your potential users/customers don't know which specific solution they need, but probably know what their problem is. Your description should help someone with a problem your product solves recognize that your product seeks to address that problem. As someone who has worked with ETL products extensively (as a PM for those products and as a developer using those products), it's hard for me to know if your tool is interesting to me even as someone who's been intimately involved with the space. One area where some jargon may be helpful is using more standard ETL terminology. You appear to have a kind of ETL tool, but are using terms that have me unsure about whether or not that's the space you sit in or how to mentally model your product. Based on a quick skim, I suspect a lot of my questions are answered by digging deeper into the docs, and that's good. Just gotta get those details boiled down into a more descriptive summary.
klysm 2y ago
The whole concept here feels backwards to me. Why are we writing into an OLAP database and replicating to an OLTP one?
- mahcha 2y ago
  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://www.synclite.io/solutions/gen-ai-search-rag https://medium.com/@mahendra.chavan/synclite-bridging-the-gap-between-gen-ai-search-rag-applications-for-edge-and-cloud-0f68b46d16d5 https://medium.com/@mahendra.chavan/synclite-bridging-the-ga...
buremba 2y ago
SyncLite looks great for companies that are starting to build from scratch. Ingest data in an embedded database and via CDC move the data into a centralized database. I'm also trying out a similar idea but only it's a bit reverse approach, from Cloud Warehouse -> Embedded DuckDB to reduce the compute cost for BI and embedded analytics use-cases. The combination of cloud and embedded databases is the future IMO. For the project I'm working on, the tech such as Apache Iceberg and embedded DuckDB enables querying Snowflake + BigQuery tables directly from your BI tool, without any compute cost: https://github.com/buremba/universql https://github.com/buremba/universql
- mahcha 2y ago
  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.
poikroequ 2y ago
Why does the title mention specifically duckdb and postgres? It sounds like it supports multiple embedded and remote databases, and I don't see anything special in the README specifically about this pairing...
- mahcha 2y ago
  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 https://www.synclite.io/solutions/gen-ai-search-rag

73 points by syncliteio 2y ago

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.

syncliteio 2y ago

SyncLite Open Source (https://www.synclite.io/ https://www.synclite.io/) ==> - SyncLite Logger, a wrapper JDBC driver, supporting popular embedded databases (SQLite, DuckDB, Apache Derby, H2, HyperSQL), creates transactional sql logs for embedded dbs, ships them to configured staging storage ( S3/MinIO/SFTP/Kafka/OneDrive/GoogleDriver etc) - Several application instances each creating multiple embedded databases/devices are synchronized in real-time on the staging storage. - SyncLite consolidator, a centralized tool, continuously processes device logs, generates CDC logs, replicates and consolidates staged devices into a wide range of industry leading database, data warehouses and data lakes. Look forward to feedback and suggestions.

matharmin 2y ago

Is this Java/JVM-specific? How are changes captured on the client? From reading through the docs it seems like the CDC work is done in the JDBC driver (SyncLite Logger), but I had to read quite far to see that, and it's difficult to put together how it works and in what types of applications this can be used. For example, can I capture changes to a SQLite or DuckDB database in an Android or iOS application? I'm interested to see the approaches used, since there is very little out there for capturing changes in embedded databases.

syncliteio 2y ago
SyncLite Logger is JDBC wrapper/driver, can be consumed in Java and Python applications: Few code samples are shown here: https://github.com/syncliteio/SyncLite https://github.com/syncliteio/SyncLite and https://github.com/syncliteio/SyncLite/tree/main/synclite-code-samples https://github.com/syncliteio/SyncLite/tree/main/synclite-co... A jsp-servlet sample web app is here: https://github.com/syncliteio/synclite-sample-web-app https://github.com/syncliteio/synclite-sample-web-app SyncLite logger transactionally captures the exact sql statements (DDLs and DMls) as executed on the underlying embedded databases, into sqllog files. The log files for each device (i.e. database) are shipped to a configured staging storage(Local FS/SFTP/S3/MinIO/Kafka etc). SyncLite consolidator continuously looks for new devices and new logs for each devices. The received raw sql logs are not directly applicable on final destination as they are logical statements, may involve complex dependencies/joins e.g. insert into select <cols> from t1 JOIN t2 ... Consolidator applies the incoming sql logs transactionally to a native embedded database (SQLite) and in the process generates record level CDC logs by identifying exact changed records and tables The CDC logs are record-level INSERT, UPDATE, DELETE statements, DDL statements : CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE. The CDC log files as generated are applied to final destination databases. A summary of this is here : https://www.synclite.io/synclite/sync-ready-apps https://www.synclite.io/synclite/sync-ready-apps Thank you for the feedback and we are improving the documentation.
- matharmin 2y ago
  Thanks, that gives a good overview

eknkc 2y ago

I’ve read the initial paragraphs of Readme and I have no idea what it does. I think a technical no buzzword summary would be good.

syncliteio 2y ago
SyncLite at the very core offers two key capabilities : 1. A holistic solution for Database Replication/Consolidation for numerous embedded databases used in several instances of edge/desktop applications, into centralized databases/dw/data lake etc. 2. A holistic data streaming solution offering a Kafka Producer and SQL API designed for final destination data delivery.
- underbiding 2y ago
  "holistics", "edge", "final destination data delivery" are all buzz words.
- haswell 2y ago
  Please take this as well-intentioned feedback. #1 and #2 are really difficult to parse. A really carefully considered elevator pitch that distills the essence of your product into concrete use cases would go really far in helping people understand what you're building. e.g. I think you're trying to say: SyncLite helps <developer?/dba?/who?> replicate databases from a variety of apps into a centralized location <for what purpose?>. What's not clear is what problem this is solving, or what the ultimate goal is. Same comment for data streaming. I can imagine a few, but would prefer not to imagine. Assume some of your potential users/customers don't know which specific solution they need, but probably know what their problem is. Your description should help someone with a problem your product solves recognize that your product seeks to address that problem. As someone who has worked with ETL products extensively (as a PM for those products and as a developer using those products), it's hard for me to know if your tool is interesting to me even as someone who's been intimately involved with the space. One area where some jargon may be helpful is using more standard ETL terminology. You appear to have a kind of ETL tool, but are using terms that have me unsure about whether or not that's the space you sit in or how to mentally model your product. Based on a quick skim, I suspect a lot of my questions are answered by digging deeper into the docs, and that's good. Just gotta get those details boiled down into a more descriptive summary.

klysm 2y ago

The whole concept here feels backwards to me. Why are we writing into an OLAP database and replicating to an OLTP one?

mahcha 2y ago
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://www.synclite.io/solutions/gen-ai-search-rag https://medium.com/@mahendra.chavan/synclite-bridging-the-gap-between-gen-ai-search-rag-applications-for-edge-and-cloud-0f68b46d16d5 https://medium.com/@mahendra.chavan/synclite-bridging-the-ga...

buremba 2y ago

SyncLite looks great for companies that are starting to build from scratch. Ingest data in an embedded database and via CDC move the data into a centralized database. I'm also trying out a similar idea but only it's a bit reverse approach, from Cloud Warehouse -> Embedded DuckDB to reduce the compute cost for BI and embedded analytics use-cases. The combination of cloud and embedded databases is the future IMO. For the project I'm working on, the tech such as Apache Iceberg and embedded DuckDB enables querying Snowflake + BigQuery tables directly from your BI tool, without any compute cost: https://github.com/buremba/universql https://github.com/buremba/universql

mahcha 2y ago
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.

poikroequ 2y ago

Why does the title mention specifically duckdb and postgres? It sounds like it supports multiple embedded and remote databases, and I don't see anything special in the README specifically about this pairing...

mahcha 2y ago
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 https://www.synclite.io/solutions/gen-ai-search-rag