Why does Xamarin documentation suggest using singleton for database connection?

Question

I am looking at the official documentation of Xamarin and they seem to encourage using statics/singleton for a Database Connection, which seems weird to me:

HERE https://developer.xamarin.com/guides/xamarin-forms/application-fundamentals/dependency-service/introduction/

This approach creates a single database connection that is kept open while the application runs, therefore avoiding the expense of opening and closing the database file each time a database operation is performed. static TodoItemDatabase database;

public static TodoItemDatabase Database
{
  get
  {
    if (database == null)
    {
      database = new TodoItemDatabase(DependencyService.Get<IFileHelper>().GetLocalFilePath("TodoSQLite.db3"));
    }
    return database;
  }
}

HERE https://developer.xamarin.com/guides/cross-platform/application_fundamentals/building_cross_platform_applications/part_2_-_architecture/

Singleton – The Singleton pattern provides for a way in which only a single instance of a particular object can ever exist. For example, when using SQLite in mobile applications, you only ever want one instance of the database. Using the Singleton pattern is a simple way to ensure this.

AND HERE https://developer.xamarin.com/guides/cross-platform/application_fundamentals/building_cross_platform_applications/case_study-tasky/

The TaskItemDatabase is a singleton, ensuring that all access occurs against the same instance. A lock is used to prevent concurrent access from multiple threads.

public T GetItem<T> (int id) where T : BL.Contracts.IBusinessEntity, new ()
{
    lock (locker) {
        return Table<T>().FirstOrDefault(x => x.ID == id);
    }
}

It seems to me though that this is a widely discouraged idea in general, for instance here on SO: getting db connection through singleton class Is singleton approach right for accessing/maintaining database and internet connection

So, any idea why does Xamarin team promote this approach? Is it different because of some particularity of their framework? And more importantly, if not that, then what is the proper approach?

Answer 1

The most popular SQLite ORM for Xamarin, SQLite-net, is thread-safe and handles the database connection for you.

I prefer to structure my databases similarly, using a BaseDatabase class that retrieves the connection.

Base Database

Note BaseDatabase uses the Xamarin.Essentials NuGet Package to locate the app's data Directory. Be sure to first add the Xamarin.Essentials NuGet Package and follow the Getting Started Instructions.

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));

        SQLiteAsyncConnection DatabaseConnection => _databaseConnectionHolder.Value;

        protected static async Task<SQLiteAsyncConnection> GetDatabaseConnection<T>()
        {
            if (!DatabaseConnection.TableMappings.Any(x => x.MappedType.Name == typeof(T).Name))
            {
                // 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;
        }    
    }
}

Database

namespace MyNamespace
{
    public class OpportunityModelDatabase : BaseDatabase
    {
        public async Task<List<OpportunityModel>> GetAllOpportunityDataAsync()
        {
            var databaseConnection = await GetDatabaseConnection<OpportunityModel>().ConfigureAwait(false);

            return await databaseConnection.Table<OpportunityModel>().ToListAsync().ConfigureAwait(false);
        }
    }
}

Answer 2

SQLite database structure is a single file in your memory (which you set path as "TodoSQLite.db3"). Think of it like access from multiple places of your code to a specific .txt file.

Instead deal with different connection with locked functions (because you just cant run multiple operations at the same time), it's less expensive and cleaner to share the same instance of a connection and that's a singleton.