Code Traveler

Xamarin: Efficiently Using a SQLite Database

Brandon Minnick -

The most popular SQLite ORM for Xamarin is SQLite-net. Let's explore the most efficient way to use it in our apps!

If you'd like to skip to the completed code, jump to Section 9: Put It All Together.

A completed sample app using this implementation is also available here: https://github.com/brminnick/GitTrends

1. Locate the App Data Directory

Both iOS and Android have a specific/unique file directory for each app, and this is where we'll store the .db3 database file. We can easily locate it using Xamarin.Essentials.FileSystem.

Note: Be sure to first add the Xamarin.Essentials NuGet Package and follow its Getting Started Instructions. 

static readonly string _databasePath = Path.Combine(Xamarin.Essentials.FileSystem.AppDataDirectory, "SqliteDatabase.db3");

2. Use SQLiteOpenFlags

SQLite-net offers an asynchronous API, SQLiteAsyncConnection. This allows us to use async/await when performing database operations.

SQLite also offers a few different options when creating a database connection using SQLiteOpenFlags, and here are the ones I recommend:

  • SQLiteOpenFlags.ReadWrite

    • "The database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system"

  • SQLiteOpenFlags.Create

    • "[The database] is created if it does not already exist"

  • SQLiteOpenFlags.SharedCache
    • Allows for multi-threaded database access

    • "When a thread establishes multiple connections to the same database, the connections share a single data and schema cache. This can significantly reduce the quantity of memory and IO required by the system"

new SQLiteAsyncConnection(_databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache)

3. Use Lazy Initialization

The official Microsoft Docs say it best:

Lazy initialization of an object means that its creation is deferred until it is first used.

Because File IO operations and creating a database can be expensive (i.e., it requires many CPU cycles and can take longer than expected), we don't want to initialize our database until we need it. This avoids creating our database when the app launches, keeping our app's launch time to a minimum.

static readonly Lazy<SQLiteAsyncConnection> _databaseConnectionHolder = new Lazy<SQLiteAsyncConnection>(() => new SQLiteAsyncConnection(_databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache));

static SQLiteAsyncConnection DatabaseConnection => _databaseConnectionHolder.Value;

4. Retrieve the Database Connection

To use the database connection, we must ensure the following:

  1. Ensure Database has been created
    • new SQLiteAsyncConnection
  2. Ensure Tables have been created & mapped
    • CreateTables(CreateFlags createFlags, params Type[] types)

To accomplish both, I created async Task<SQLiteAsyncConnection> GetDatabaseConnection<T>() where T is the Model's type. This method ensures the database table has been created & mapped before returning the database connection.

protected static async Task<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
    if (!DatabaseConnection.TableMappings.Any(static x => x.MappedType == typeof(T)))
    {
        await DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false);
    }
                
   return DatabaseConnection;
}

5. Use Write-Ahead Logging

In SQLite-net v1.6.0+, Write Ahead Logging (WAL), is available which brings many performance advantages:

  1. WAL is significantly faster in most scenarios
  2. WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
  3. Disk I/O operations tends to be more sequential using WAL.
  4. WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.

There are also disadvantages to using WAL, most of which however don't impact Xamarin mobile apps. But be sure to understand both the advantages and disadvantages before enabling WAL in your app.

protected static async Task<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
    if (!DatabaseConnection.TableMappings.Any(static x => x.MappedType == typeof(T)))
    {
        // On sqlite-net v1.6.0+, enabling write-ahead logging allows for faster database execution
        await DatabaseConnection.EnableWriteAheadLoggingAsync().ConfigureAwait(false);
        await DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false);
    }
                
   return DatabaseConnection;
}

6. Always Attempt and Retry

When using multi-threading and async/await to interact with a SQLite database, sometimes this exception is thrown:

SQLite.SQLiteException: database is locked

Anecdotally, I've noticed this exception gets thrown more often in SQLite-net v1.6.0+ when WAL is enabled.

When this exception happens, we just need to retry our SQL query again after the database has been unlocked.

There is an amazing library called Polly that will catch an exception, wait a specified period of time, then try to execute the function again.

For this, I created AttemptAndRetry<T> which will execute a Func<Task<T>>, but if it throws a SQLite.SQLiteException it will attempt to execute it again asynchronously.

Between retries, I recommend using an Exponential Backoff to ensure there are 2n milliseconds in between. This means that if the first attempt fails, the first retry will happen 21 (2) milliseconds later (1 millisecond is a really long time for a CPU). If the second attempt fails, its subsequent retry will happen 22 (4) milliseconds later. And if third attempt fails, its subsequent retry will happen 23 (8) milliseconds later, etc.

In this example, we will retry 10 times, meaning the final attempt will occur 2 seconds after its previous try, and all 11 attempts (1 attempt + 10 retries) will occur over a total timespan of 4.094 seconds. In other words, if each attempt fails, including the final attempt, the database will throw a SQLite.SQLiteException after approximately 4 seconds.

protected static Task<T> AttemptAndRetry<T>(Func<Task<T>> action, int numRetries = 10)
{
    return Policy.Handle<SQLite.SQLiteException>().WaitAndRetryAsync(numRetries, pollyRetryAttempt).ExecuteAsync(action);

    static TimeSpan pollyRetryAttempt(int attemptNumber) => TimeSpan.FromMilliseconds(Math.Pow(2, attemptNumber));
}

7. Use ConfigureAwait(false)


You may notice that every time I use the await keyword, I also use .ConfigureAwait(false). This is done to increase performance.

ConfigureAwait(false) ensures that the background thread doesn't return to the calling thread (which can be the Main Thread, aka the UI Thread). For Xamarin apps, we want to avoid using the Main Thread for database operations to ensure our app doesn't freeze and remains responsive.

I highly recommend watching this session I delivered at NDC Oslo to learn more: "Correcting Common Async Await Mistakes in .NET".

8. Use ValueTask

New in .NET Standard 2.1 is System.Threading.Tasks.ValueTask.

(If you can't yet upgrade to .NET Standard 2.1, you can still use ValueTask by installing the System.Threading.Tasks.Extensions NuGet Package.)

If you're new to ValueTask, check out this great write-up: Understanding the Whys, Whats, and Whens of ValueTask.

tl;dr VaueTask can be used to improve async/await performance when the hot-path of a method doesn't require the await keyword.

GetDatabaseConnection only uses the await keyword the first time it is called. Thus its hot-path does not use require await, making it the perfect use-case for ValueTask.

To implement ValueTask for GetDatabaseConnection<T>, just replace Task<SQLiteAsyncConnection> with ValueTask<SQLiteAsyncConnection>:

protected static async ValueTask<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
    if (!DatabaseConnection.TableMappings.Any(static x => x.MappedType == typeof(T)))
    {
        // On sqlite-net v1.6.0+, enabling write-ahead logging allows for faster database execution
        await DatabaseConnection.EnableWriteAheadLoggingAsync().ConfigureAwait(false);
        await DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false);
    }
                
   return DatabaseConnection;
}

9. Put It All Together

Putting all of these best practices together, I've created abstract class BaseDatabase. I copy/paste this abstract class into all of my Xamarin applications where I use a database.

A completed sample app using this implementation is available here: https://github.com/brminnick/GitTrends

BaseDatabase.cs

Be sure to first add the following NuGet Packages to your app:

using System.Threading.Tasks;

using SQLite;

using Xamarin.Essentials;
using Xamarin.Forms;

namespace MyNamespace
{
    public abstract class BaseDatabase
    {
        static readonly string _databasePath = Path.Combine(FileSystem.AppDataDirectory, "SqliteDatabase.db3");
        static readonly Lazy<SQLiteAsyncConnection> _databaseConnectionHolder = new Lazy<SQLiteAsyncConnection>(() => new SQLiteAsyncConnection(_databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache));

        static SQLiteAsyncConnection DatabaseConnection => _databaseConnectionHolder.Value;

        protected static async ValueTask<SQLiteAsyncConnection> GetDatabaseConnection<T>()
        {
            if (!DatabaseConnection.TableMappings.Any(static x => x.MappedType == typeof(T)))
            {
                // On sqlite-net v1.6.0+, enabling write-ahead logging allows for faster database execution
                await DatabaseConnection.EnableWriteAheadLoggingAsync().ConfigureAwait(false);
                await DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false);
            }
                
            return DatabaseConnection;
        }
        
        protected static Task<T> AttemptAndRetry<T>(Func<Task<T>> action, int numRetries = 10)
        {
            return Policy.Handle<SQLite.SQLiteException>().WaitAndRetryAsync(numRetries, pollyRetryAttempt).ExecuteAsync(action);

            static TimeSpan pollyRetryAttempt(int attemptNumber) => TimeSpan.FromMilliseconds(Math.Pow(2, attemptNumber));
        }
    }
}

Example: Inheriting from BaseDatabase

namespace MyNamespace
{
	public class CustomerDatabase : BaseDatabase
	{
		public async Task<List<CustomerModel>> GetAllCustomers()
		{
			var databaseConnection = await GetDatabaseConnection<CustomerModel>().ConfigureAwait(false);

			return await AttemptAndRetry(() => databaseConnection.Table<CustomerModel>().ToListAsync()).ConfigureAwait(false);
		}
    }
}

Conclusion

I highly recommend using BaseDatabase.cs in all of your Xamarin apps to take advantage of these awesome performance improvements!