Support for SQLite connector

[kuatroka]

Any plans to add SQLite? Thanks

[exAspArk]

Hi @kuatroka — thank you for submitting this issue! We're focusing on improving PostgreSQL synchronization and are currently unable to prioritize to SQLite.

In the future, we'd love to add support for other data sources such as SQLite! Would you be able to share more details about your use case? Do you use SQLite as your primary (large?) transactional database or as an intermediate store in data pipelines?

We're going to leave this issue open for now, so others can share their thoughts or 👍

[kuatroka]

Thanks. First of all, some disclosure: I'm new to web development and I'm learning it with the Phoenix LiveView framework. They primarily use Postgres and SQLite as the main database options, which are very tightly integrated with the Elixir database ORM (even though it's more than just that) called Ecto.

My plan was to use SQLite as the main database and DuckDB for all the tables and charts display, as my app is very heavy on analytics. SQLite is the most ubiquitous database out there, and it's just a file. It sits on the same server as the web app, so it's much faster than Postgres for web apps.
My idea would be to use BemiDB for both light transactions (log ins, bookmarks, etc.) and charts/tables to take advantage of DuckDB's massive speedup. Thanks.