The "one writer at a time and the rest queue" caveat is fine for most web applications when writes happen in single digit / low 10s of ms
From the webapp angle check out, for example, pocketbase.io which is an open source Go supabase style backend which wraps sqlite. They have benchmarks etc available.
The "one writer at a time and the rest queue" caveat is fine for most web applications when writes happen in single digit / low 10s of ms