chiselstore VS sqlite_fdw

it? What's the big deal about not fitting well with SQLx? This isn't a requirement for such a library to be successful. What other problems is it allegedly suffering from?

[1] https://github.com/chiselstrike/chiselstore

I've looked at the https://github.com/chiselstrike/chiselstore/blob/main/proto/... - and calling the service "RPC" is not great if this is going to be bundled with other gRPC services - why not call it "chiselStore" or something more concrete?

on a 2nd note, each method should have it's own Request Response, and ideally they should be suffixed same way. Returning same proto for multiple methods might break future compatibility - e.g. what if AppendEntries needs to return somehing else than Void? - ideally you make it from the start to return an empty AppendEntriesResponse, and then you can extend that proto.

https://developers.google.com/protocol-buffers/docs/proto3#u...

https://github.com/pgspider/sqlite_fdw

Postgres has foreign data wrappers which can kinda achieve this(see also: https://github.com/pgspider/sqlite_fdw ), but ive been surprised by how well sqlite is already supported by many tools : sometimes it comes for free as its used in testing - hooking up stuff like litestream can work really wel l too

Another option might be the SQLite FDW

I've not done this but it's intriguing; potentially a best-of-all-worlds solution.

I think "proper" automatic replication is not possible given the mismatch between Postgres and SQLite - not everything in Postgres maps to a thing that is possible in Postgres.

That said, there are a variety of ways to get data out of Postgres, and a variety of ways to get things into SQLite.

You could periodically export CSVs or whatever from Postgres and periodically import them into SQLite.

Or you could do a more realtime sync by using a Postgres foreign data wrapper like this one: https://github.com/pgspider/sqlite_fdw which would let you write directly to SQLite. Combine that with database triggers on the Postgres side of things and you've got something pretty close to realtime replication.

Those sorts of solutions wouldn't be as robust as "real" replication, but could be very useful for a lot of scenarios. You could have Postgres importing gobs of data, and "replicating" it over to your reporting server which uses SQLite as a data source.

pgadmin4 does not support it, but you can try sqlite_fdw

If you encounter limitations posed by SQLite it will be easy to migrate to PostgreSQL. You can even open the SQLite database in PostgreSQL.