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 4 hours ago | parent | next [-]

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.

	▲	busymom0 4 hours ago \| parent [-]
		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?

▲ wmanley 4 hours ago | parent | prev | next [-]

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.

▲ andersmurphy 4 hours ago | parent | prev [-]

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.