SQLite Database with Your Apps

Create a DataStore that lets you initialize and configure your SQLite db and its tables.

public sealed class SqliteDataStore
{
    private readonly SQLiteAsyncConnection database;
    private static Lazy<SqliteDataStore> lazy = null;

    private SqliteDataStore(string path)
    {
        database = new SQLiteAsyncConnection(path);
    }

    public SQLiteAsyncConnection Database => database;

    public static SqliteDataStore Instance
    {
        get
        {
            if (lazy is null)
            {
                throw new InstanceNotCreatedException();
            }
            return lazy.Value;
        }
    }

    private static void CreateSharedDataStore(string path)
    {
        lazy ??= new Lazy<SqliteDataStore>(() => new SqliteDataStore(path));
    }

    public static async Task InitializeDataStoreAsync()
    {
        var databasePath = Path.Combine(
                        Environment.GetFolderPath(
                        Environment.SpecialFolder.LocalApplicationData),
                        DataStoreConstants.DatabaseName
                        );
        CreateSharedDataStore(databasePath);
        await CreateAllTablesAsync();
    }

    private static async Task CreateAllTablesAsync()
    {
        await SqliteDataStore.Instance.Database.CreateTableAsync<GroceryModel>();
    }
}

Create a Constants class that will hold any constants you need for your Database.

public static class DataStoreConstants
{
    public static readonly string DatabaseName = "sqlite.db3";
}

Create a custom exception in case the lazy initialization is failing because of some usage issues. (You can add more)

public class InstanceNotCreatedException : Exception
{
    public InstanceNotCreatedException() : base()
    {
    }


    public InstanceNotCreatedException(string message) : base(message)
    {
    }


    public InstanceNotCreatedException(string message, Exception innerException) : base(message, innerException)
    {
    }
}

Create an interface that lets you access your DataStore and implement the most commonly used SQLite methods.

using System.Collections;
using System.Linq.Expressions;


public interface ISqliteService
{
    Task<bool> ExecuteAsync(string query, params object[] args);


    Task<bool> InsertDataAsync<T>(T Data);


    Task<bool> DeleteDataAsync<T>(object primaryKey);


    Task<bool> UpdateDataAsync<T>(T Data);


    Task<bool> InsertAllDataAsync<T>(IEnumerable<T> Data);


    Task<bool> InsertOrReplace<T>(T Data);


    Task<bool> DeleteAllDataAsync<T>();


    Task<bool> UpdateAllDataAsync(IEnumerable Data);


    Task<List<T>> GetAllDataAsync<T>() where T : new();


    Task<T> GetDataByPkAsync<T>(object pk) where T : new();


    Task<T> GetDataAsync<T>(Expression<Func<T, bool>> predicate) where T : new();


    Task<List<T>> GetAllDataQueryAsync<T>(Expression<Func<T, bool>> predicate = null) where T : new();
}

Create an implementation of this interface which you can then use through your dependency injection service.

public class SqliteService : ISqliteService
{
    // <summary>
    // Deletes all the data from a specific table
    // </summary>
    // <typeparam name="T"> Table that you'd like to clear </typeparam>
    // <returns> a boolean that represents if all the data was deleted or not </returns>
    public async Task<bool> DeleteAllDataAsync<T>()
    {
        return await SqliteDataStore.Instance.Database.DeleteAllAsync<T>() != 0;
    }

    // <summary>
    // Deletes the specific row whos primary key is passed as an argument
    // </summary>
    // <typeparam name="T"> Table that you'd like to delete data from </typeparam>
    // <param name="primaryKey"> primary key of that row entry </param>
    // <returns> a boolean that represents if the deletion was sucessful </returns>
    public async Task<bool> DeleteDataAsync<T>(object primaryKey)
    {
        return await SqliteDataStore.Instance.Database.DeleteAsync<T>(primaryKey) != 0;
    }

    // <summary>
    // Creates an SQLiteCommand of the given command text(SQL) with arguments
    // </summary>
    // <param name="query">the SQLite command you'd like to fire</param>
    // <param name="args">required arguments for that command</param>
    // <returns></returns>
    public async Task<bool> ExecuteAsync(string query, params object[] args)
    {
        return await SqliteDataStore.Instance.Database.ExecuteAsync(query, args) != 0;
    }

    // <summary>
    // Get all data of type
    // </summary>
    // <typeparam name="T"> Table that you want to query </typeparam>
    // <returns> list of your data if exists or an empty collection </returns>
    public async Task<List<T>> GetAllDataAsync<T>() where T : new()
    {
        return await SqliteDataStore.Instance.Database.Table<T>().ToListAsync();
    }

    // <summary>
    // Get data that matches the pk
    // </summary>
    // <typeparam name="T"> Table that you want to query </typeparam>
    // <returns> data if exists or null </returns>
    public async Task<T> GetDataByPkAsync<T>(object pk) where T : new()
    {
        return await SqliteDataStore.Instance.Database.GetAsync<T>(pk);
    }

    // <summary>
    // Get the first item that macthes the expression.
    // </summary>
    // <typeparam name="T"> Table that you want to query </typeparam>
    // <returns> data if exists or null </returns>
    public async Task<T> GetDataAsync<T>(Expression<Func<T, bool>> predicate) where T : new()
    {
        try
        {
            return await SqliteDataStore.Instance.Database.GetAsync<T>(predicate);
        }
        catch (Exception)
        {
            //Todo: Log this exception.
            return default;
        }
    }

    // <summary>
    // Insert all data into the specific table
    // </summary>
    // <typeparam name="T"> Table that you'd like to insert into </typeparam>
    // <param name="Data"> Data to insert </param>
    // <returns> a boolean that represents if the data was inserted successfully </returns>
    public async Task<bool> InsertAllDataAsync<T>(IEnumerable<T> Data)
    {
        return await SqliteDataStore.Instance.Database.InsertAllAsync(Data, typeof(T), true) != 0;
    }

    // <summary>
    // Insert single row into a specific table
    // </summary>
    // <typeparam name="T"> Table that you'd like to insert into </typeparam>
    // <param name="Data"> Data to insert </param>
    // <returns>
    // a boolean, integer tuple. The boolean represents if the transaction was successful and
    // the integer returns the integer autoincrement primary key if any.
    // </returns>
    public async Task<bool> InsertDataAsync<T>(T Data)
    {
        return await SqliteDataStore.Instance.Database.InsertAsync(Data, typeof(T)) != 0;
    }

    // <summary>
    // Insert single row into a specific table
    // </summary>
    // <typeparam name="T"> Table that you'd like to insert/replace into </typeparam>
    // <param name="Data"> Data to insert </param>
    // <returns>
    // a boolean, integer tuple. The boolean represents if the transaction was successful and
    // the integer returns the integer autoincrement primary key if any.
    // </returns>
    public async Task<bool> InsertOrReplace<T>(T Data)
    {
        return await SqliteDataStore.Instance.Database.InsertOrReplaceAsync(Data, typeof(T)) != 0;
    }

    // <summary>
    // Updates all specified objects
    // </summary>
    // <param name="Data"> </param>
    // <returns> a boolean that represents if the update was successful </returns>
    public async Task<bool> UpdateAllDataAsync(IEnumerable Data)
    {
        return await SqliteDataStore.Instance.Database.UpdateAllAsync(Data, true) != 0;
    }

    // <summary>
    // Updates the specified object
    // </summary>
    // <typeparam name="T"> Table that you'd like to update </typeparam>
    // <param name="Data"> Data you'd like to update </param>
    // <returns> a boolean that represents if the update was successful </returns>
    public async Task<bool> UpdateDataAsync<T>(T Data)
    {
        return await SqliteDataStore.Instance.Database.UpdateAsync(Data, typeof(T)) != 0;
    }

    // <summary>
    // Get all data of type with a predicate LINQ query
    // </summary>
    // <typeparam name="T"> Table that you want to query </typeparam>
    // <typeparam name="predicate"> LINQ predicate query </typeparam>
    // <returns>
    // list of your data if exists or an empty collection based on the result of predicate search
    // </returns>

    public async Task<List<T>> GetAllDataQueryAsync<T>(Expression<Func<T, bool>> predicate = null) where T : new()
    {
        return await SqliteDataStore.Instance.Database.Table<T>().Where(predicate).ToListAsync();
    }
}

Initialize your SQLite DB, in your App class before setting the MainPage. (Especially if your default MainPage uses SQLite)

App()
{      
    InitializeComponent();
    SqliteDataStore.InitializeDataStoreAsync().SafeFireAndForget();
    MainPage = new MainPage();
}

If your MainPage does not use SQLite, then I recommend you instead Initialize it in the OnStart method.

protected override async void OnStart()
{
    base.OnStart();
    await SqliteDataStore.InitializeDatabaseAsync();
}

Example GroceryModel

public class GroceryModel
{
    [PrimaryKey,AutoIncrement]
    public int Id { get; set; }

    public string? Name { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<string> OneToManyAll { get; set; }
}