I’m trying to do multi-regional SQLite with Litefs. Currently I’m using postgres and I make all regions create a write connection to the primary so I can write directly to the primary from each region. I don’t think this is possible with SQLite so I’m trying to figure out how to handle this use case. I know normally you should just use fly-replay for this, but I have a use case where that does not work.
In my situation, I have a Session table that keeps track of user sessions and if a user makes any request within two weeks of their expiration time then I update the session expiration. So that’s happening in a regular GET request, I don’t think it would make sense to do a replay there, but maybe that’s the answer?
Also, a related question. With postgres, if I try to write to a read replica, I get an error. Would I get an error like that if I do this with SQLite? Or would it just write and get ignored/overwritten?
Yeah, you could redirect the GET to the primary. Another option is you could have an API on your primary that the replica could call when it needs to refresh the session. That way the GET is still handled by the replica but the update is essentially a separate small request.
With the rollback journal, LiteFS returns EACCES on journal creation which SQLite will convert into a SQLITE_READONLY error code that it returns to your application.
We recently added WAL support on the main branch but it doesn’t have SQLITE_READONLY support yet. I’m still trying to figure out if it’s possible to do. Right now, it returns a generic "input/output error" instead which is not ideal.