Xamarin: Efficiently Using a SQLite Database
Tags: xamarin, sqlite, csharp
Published: 2020-02-20 22:06:46

The most popular SQLite ORM for Xamarin is [SQLite-net](https://github.com/praeclarum/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](https://www.nuget.org/packages/Xamarin.Essentials) and follow its [Getting Started Instructions](https://docs.microsoft.com/xamarin/essentials/get-started?WT.mc_id=sqlite-devto-bramin). 

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

## 2. Use SQLiteOpenFlags

SQLite-net offers an [asynchronous API](https://github.com/praeclarum/sqlite-net/wiki/Asynchronous-API), `SQLiteAsyncConnection`. This allows us to use async/await when performing database operations.

SQLite also offers a [few different options](https://www.sqlite.org/c3ref/open.html) when creating a database connection using `SQLiteOpenFlags`, and here are the ones I recommend:

- [`SQLiteOpenFlags.ReadWrite`](https://www.sqlite.org/c3ref/open.html)
    - *"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`](https://www.sqlite.org/c3ref/open.html)
    - *"[The database] is created if it does not already exist"*
- [`SQLiteOpenFlags.SharedCache`](https://www.sqlite.org/sharedcache.html)
    - 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"*

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

## 3. Use Lazy Initialization

The [official Microsoft Docs](https://docs.microsoft.com/dotnet/framework/performance/lazy-initialization?WT.mc_id=sqlite-devto-bramin) 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.

```csharp
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.

```csharp
protected static async Task<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
    if (!DatabaseConnection.TableMappings.Any(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+](https://github.com/praeclarum/sqlite-net/blob/91c17adc183487dd3e7a41e90430c286a7add7db/src/SQLiteAsync.cs#L128), Write Ahead Logging (WAL), is available which brings [many performance advantages](https://www.sqlite.org/wal.html):

> 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](https://www.sqlite.org/wal.html), 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.

```csharp
protected static async Task<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
    if (!DatabaseConnection.TableMappings.Any(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](https://www.nuget.org/packages/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](https://wikipedia.org/wiki/Exponential_backoff) to ensure there are 2<sup>n</sup> milliseconds in between. This means that if the first attempt fails, the first retry will happen 2<sup>1</sup> (2) milliseconds later (1 millisecond is a _really_ long time for a CPU). If the second attempt fails, its subsequent retry will happen 2<sup>2</sup> (4) milliseconds later. And if third attempt fails, its subsequent retry will happen 2<sup>3</sup> (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.

```csharp
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"](https://www.youtube.com/watch?v=J0mcYVxJEl0).

[![](https://user-images.githubusercontent.com/13558917/62509892-a32a8a00-b7d2-11e9-9460-267220838b76.png)](https://www.youtube.com/watch?v=J0mcYVxJEl0 "Correcting Common Async Await Mistakes in .NET")

## 8. Use ValueTask

New in .NET Standard 2.1 is [`System.Threading.Tasks.ValueTask`](https://docs.microsoft.com/dotnet/api/system.threading.tasks.valuetask-1?view=netcore-3.0WT.mc_id=sqlite-devto-bramin). 
(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](https://www.nuget.org/packages/System.Threading.Tasks.Extensions/).)

If you're new to `ValueTask`, check out this great write-up: [Understanding the Whys, Whats, and Whens of ValueTask](https://blogs.msdn.microsoft.com/dotnet/2018/11/07/understanding-the-whys-whats-and-whens-of-valuetask?WT.mc_id=sqlite-devto-bramin).

**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>`:

```csharp
protected static async ValueTask<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
    if (!DatabaseConnection.TableMappings.Any(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:
- [SQLite-Net](https://www.nuget.org/packages/sqlite-net-pcl/)
- [Xamarin.Essentials](https://www.nuget.org/packages/Xamarin.Essentials)
    - Used to locate the app's data Directory
	- [Getting Started Instructions](https://docs.microsoft.com/xamarin/essentials/get-started?WT.mc_id=none-stackoverflow-bramin)
- [Polly](https://www.nuget.org/packages/Polly/)
    - Used to attempt database actions and automatically retry if unsuccessful
    - (Polly doesn't require any initialization code)
- [ValueTask](https://www.nuget.org/packages/System.Threading.Tasks.Extensions/) (Not required for .NET Standard 2.1+)

```csharp
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 Task<SQLiteAsyncConnection> GetDatabaseConnection<T>()
        {
            if (!DatabaseConnection.TableMappings.Any(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

```csharp
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!

- **[Lazy Loading](https://docs.microsoft.com/dotnet/framework/performance/lazy-initialization?WT.mc_id=sqlite-devto-bramin)** - ensure your app launches quickly
- **[Write Ahead Logging](https://www.sqlite.org/wal.html)** - faster/more-efficient database writes
- **Attempt And Retry** - use [Polly](https://www.nuget.org/packages/Polly/) to retry database writes
- **[ConfigureAwait(false)](https://www.youtube.com/watch?v=J0mcYVxJEl0)** - avoid unnecessarily returning to the Main Thread
- [ValueTask](https://blogs.msdn.microsoft.com/dotnet/2018/11/07/understanding-the-whys-whats-and-whens-of-valuetask?WT.mc_id=sqlite-devto-bramin) - avoid unnecessary memory allocations