If you use SQLite-net (aka sqlite-net-pcl on NuGet) and have seen disk I/O or connection exceptions in your applications, you may be overlooking some features of the library or mixing concerns.

We were.

This is Part 1 of a short series on SQLite-net Async connection gotchas that we have come across and are learning from.

  • Part 1 - Keep It Clean
  • Part 2 - Mixing Connection Types

SQLite-net Async

We have mobile applications that use SQLite for data persistence, and we use SQLite-net as our ORM. SQLite-net has a beautiful and simple API that supports synchronous and asynchronous data access. Because async / await makes writing responsive applications so much easier, we use async anywhere we can --- including our data access code.

Creating Async Connections

When using SQLiteAsyncConnection, if you don't already have a connection, when you request one, SQLite-net opens a new connection to the requested path, then adds that open connection to a connection pool that it manages. That open connection has a handle to the specific file on disk.

This snippet shows how to request a connection:

var conn = new SQLiteAsyncConnection("file1.db");  
await conn.CreateTableAsync<AppData>();  

This code works whether the file exists or not, and whether a previous connection has been requested or not. Just ask and SQLite-net hands a connection back --- either an existing connection from the pool or after opening a new one.

What You Might Not Realize

The SQLiteAsyncConnection API makes grabbing a connection simple. It just works.

However, if you delete, move, or similarly alter your database file at a FileSystem level after a connection has been established, the open connection's handle to the file on disk can be broken. The connection stays alive in the connection pool, but any attempt to use that connection might produce an error.

Common Errors

  • SQLiteException: IOError
  • SQLiteException: ReadOnly
  • disk I/O error

Keep It Clean

To guard against these kinds of errors, SQLite-net provides an escape hatch.

If you need to alter your database files or clean up open connections, simply call the static SQLite.SQLiteAsyncConnection.ResetPool();. This will Dispose all of the connections sitting in the pool. The next time your app requests a connection, a new one will be created like nothing ever happened.

Acknowledgements

I want to thank my entire team for their accommodation and help in making these discoveries. I especially want to thank Ryan, Moose, and Macie.

Hopefully our learnings help someone else.