Comment by busymom0

Comment by busymom0 17 hours ago

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website (https://limereader.com/), I used Swift for my backend, SQLite for Database, Vapor for web server in the Swift app and am self-hosting the site on an old Mac mini.

A sqlite related issue I ran into had to do with accessing the SQLite database from multiple threads. Found out a solution easily: for multi-threading use, SQLite needed to be initialized with a `SQLITE_OPEN_FULLMUTEX` flag. Since then, the website has been running seamlessly for about 3 weeks now.

maxmcd 17 hours ago

This will block threads while waiting for other threads to write. That might work great for your threading model but I usually end up putting the writer in one thread and then other threads send writes to the writer thread.

Reply View 1 reply

busymom0 17 hours ago
I do open 2 connections:
First one for writing with flags:
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX
Second one for reading with flags:
SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX
As you can note, I have SQLITE_OPEN_FULLMUTEX on both of them. Should I only have it for the writing one?
Reply View | 0 replies

wmanley 17 hours ago

Use a connection per-thread instead. By sharing a connection across threads you’ll be limiting concurrency - and transactions won’t work as you’d expect. SQLite connections are not heavy.

Also: use WAL mode and enable mmap.

Reply View 0 replies

andersmurphy 17 hours ago

You don't need fullmutex if you manage your connections correctly at the application level. I.e ensure each connection is only used from a single thread at a time. I also highly recommend having an MPSC queue for your batch/writes and make them go through a single connection so you don't have to deal with SQLITE_BUSY or SQLITE_LOCKED.

Reply View 0 replies