6 ms·

SQLite's backward compatibility means many best practices - like WAL mode, foreign key enforcement, and sane busy timeouts - are not enabled by default. The au

SQLite's backward compatibility means many best practices - like WAL mode, foreign key enforcement, and sane busy timeouts - are not enabled by default. The author's Go library, sqlitebp, automates these settings and others (NORMAL synchronous, private cache, tuned page cache, connection pool limits, automatic PRAGMA optimize, and in-memory temp storage) to make high-concurrency, reliable usage safer and easier right out of the box

bob1029 1y ago
The backwards compatibility also means that the frustration over concurrency and synchronization is largely a waste of time. Most SQLite builds are created such that all activity is serialized through a single mutex by default. > In serialized mode, API calls to affect or use any SQLite database connection or any object derived from such a database connection can be made safely from multiple threads. https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html Many libraries get this wrong and make it unsafe to use from multiple threads despite the underlying provider being capable. I think these are effectively bugs that should be resolved. In my C# applications, I use System.Data.SQLite and share a single SQLiteConnection instance across the entire app. This connection instance typically gets injected as the first service, so I can just take a param on it any time I need to talk to SQL. Absolutely no synchronization occurs in my code. I've tried Microsoft.Data.Sqlite but it seems to have rare problems with sharing connections between threads.
- ncruces 1y ago
  In Go, a database/sql “connection” is actually a pool, and Go makes sure that it only calls driver methods serially for an actual driver connection from a single goroutine. So your point (which is not very clear to me, with my limited knowledge of C# and SDS) is largely moot in Go terms.

bob1029 1y ago

The backwards compatibility also means that the frustration over concurrency and synchronization is largely a waste of time. Most SQLite builds are created such that all activity is serialized through a single mutex by default. > In serialized mode, API calls to affect or use any SQLite database connection or any object derived from such a database connection can be made safely from multiple threads. https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html Many libraries get this wrong and make it unsafe to use from multiple threads despite the underlying provider being capable. I think these are effectively bugs that should be resolved. In my C# applications, I use System.Data.SQLite and share a single SQLiteConnection instance across the entire app. This connection instance typically gets injected as the first service, so I can just take a param on it any time I need to talk to SQL. Absolutely no synchronization occurs in my code. I've tried Microsoft.Data.Sqlite but it seems to have rare problems with sharing connections between threads.

ncruces 1y ago
In Go, a database/sql “connection” is actually a pool, and Go makes sure that it only calls driver methods serially for an actual driver connection from a single goroutine. So your point (which is not very clear to me, with my limited knowledge of C# and SDS) is largely moot in Go terms.