Many developers are happy using TPL (Task Parallel Library) with SQLite.Net. It´s not hard to find blog posts, StackOverflow answers and other sites recommending it. The library offers an async connection that can be handy to avoid blocking the UI thread and that´s how I implemented SQLite.Net from day one.
The reason to use SQLiteAsyncConnection is pretty simple: I want my app to stay responsive when querying the database. Big queries take time. If the connection is synchronous my app may become unresponsive for a while until the data is retrieved. We care about users and C# async/await is relatively easy, so let´s use it everywhere!, right?
Well… sqlite is NOT async.
That´s right. Sqlite accesses data synchronously, no matter what. Async connections are fake 💩. Simple as that. Taking a look at the source code, you´ll find out that every method call is wrapped within a Task:
public Task<T> GetAsync<T>(object pk) where T : new()
{
return Task.Factory.StartNew(() =>
{
var conn = GetConnection();
using (conn.Lock())
{
return conn.Get<T>(pk);
}
});
}
Okay… so what´s wrong with it?
You are forced to use async wrappers everywhere
You can´t choose to make something async only when it´s needed. Once you set up an async connection, you will be forced to use these wrappers everywhere unless you create a new sync connection. What if you find out that a query is fast enough to be sync?
Would you stick to async/await anyway?
Would you create a new sync connection for those cases?
If you choose 1, you are complicating things. I mean: you are awaiting a Task rather than a direct and simple method call, forcing your method to be async. I found out that in mobile development (mostly in UI initialization logic), async is harder to manage. Database queries are usually made on-screen initialization, so there you have it. You still can make it worst, by getting the result of a task in a sync fashion: var user = connection.GetAsync<User>(1).Result so you end up using a sync method, wrapped in a Task to be async, and then converted back to sync. Well played! :punch:
If you choose 2, you are complicating things. The best way to work with SQLite in most cases is by reusing a single connection during the whole life cycle.
You need to be careful with concurrency and locking errors
Just take a look here or here or here. The same can happen with sync connections, but it´s easier to fix:
Connection = new SQLiteConnection(path, SQLiteOpenFlags.ReadWrite
| SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex);
I´ve never had a single issue when creating the connection that way.
How many of your queries really take so long to be async?
I´m pretty sure that if you measure the time it takes for most of your queries to run, you´ll realize they are surprisingly fast. Fast enough so that the user won´t notice a delay, therefore async here is useless. Go ahead and try it.
What happens with blocking, time-consuming queries?
In those cases, you can/should just make the async wrapper yourself. Besides, your code will provide more information: other developers working with your code will easily figure out which queries are fast enough (sync) and which are not (async).
Conclusion
KISS is an acronym for "Keep it simple, stupid". The KISS principle states that most systems work best if they are kept simple rather than made complicated; therefore simplicity should be a key goal in design and unnecessary complexity should be avoided.
Wikipedia
