Connector: SQLite [Source/Destination]

[alarbada]

Resource name

SQLite

Resource link

https://www.sqlite.org/

Connector type

Source

Expected connector behavior

It should be able to read and write structured data from / to an sqlite file.

SQLite has very rough limitations for the destination connector, as only one single writer is allowed when writing to it. We can however retry the operation a few times and document this behaviour, add retry count parameters, etc.

It's an interesting connector. Apart from the typical RDB connector usecases, it might be useful to have a way to quickly write structured data from any source into a file, and then be able to query it. For that to happen we should support somehow creating tables from the connector schema, which I have never done yet.

My usecase for my pet projects is to have SQLite as a source, and #2004 as a destination, so that I have a better alternative than using the fts5 extension.

[nickchomey]

This is a great idea - SQLite has become enormously popular in the past couple years, and is even being implemented in a distributed manner nowadays through various tools and services.

As far as the single writer goes, if I'm not mistaken, you can use the WAL to improve concurrenecy - though it might only help with concurrent reads and writes, but not multiple writers.

However, batching is surely the best way to make this happen (and thats probably the case for any destination connector) - the destination connector could use goroutines to process the records (if needed), but then just do a single batch write (or a handful of transactions if there's snapshot, create, update, delete records in the batch received from the pipeline - I implemneted something like this in my WIP surrealdb destination connector

You could probably get inspiration (and perhaps even code) from rqlite's bulk writes and queued writes, as well as marmot. Both are golang.

[hariso]

FYI I've added this (and other connector requests) to our internal connectors' project where we prioritize all connector requests. We'll let you know once we figure when we're going to work on this.