A Syncthing and SQLite Gotcha

So, I have this little app, Epoch, that I use to keep a journal. It’s a tiny Rust web app that runs as a systemd service and uses SQLite as the database. I use a desktop and a laptop regularly, and use Syncthing to synchronize them, including Epoch’s database. That way I can use the app on both devices without needing a server to synchronize them, the tradeoff being that I have to make sure the sync is finished before performing any mutations. But I had this bug. Say I edit today’s entry on the laptop, come home, wait for Syncthing to finish, then I’d open today’s entry on the desktop, and the text would be missing. It’s not that the server is holding a lock on the file and preventing the sync: opening the database with the sqlite3 command line tool shows the new text is there. If I restart the server, Epoch can read the new text. My mental model was: The rusqlite Connection object points to the database file. Syncthing swaps the file’s contents from under it. Subsequent queries go to…