Running SQLite in production for our mid-six figure SaaS for a year now. We use it with Go and run it in WAL mode that allows for concurrent reads and serialized writes. Litestream for backups. No issues, and extremely good performance.
Our app is read-heavy so it works great with WAL mode only allowing one writer at a time. If you have a write-heavy workload, I would go with Postgres.
We use Go's mattn/sqlite3 library and open a connection pool for read-only queries, and a single connection for write queries. The read-only connection pool uses DEFERRED transactions, and the write-only connection uses IMMEDIATE transactions.
All tables are STRICT which helps some with type safety, and foreign key checks are always turned on.
Our app is read-heavy so it works great with WAL mode only allowing one writer at a time. If you have a write-heavy workload, I would go with Postgres.
We use Go's mattn/sqlite3 library and open a connection pool for read-only queries, and a single connection for write queries. The read-only connection pool uses DEFERRED transactions, and the write-only connection uses IMMEDIATE transactions.
All tables are STRICT which helps some with type safety, and foreign key checks are always turned on.