Maui Sqlite Database

SQLite Database — Gotchas & Best Practices

For full service implementation, constants, data model templates, and common patterns, see references/sqlite-database-api.md.

⚠️ Wrong Package Trap

<!-- ❌ WRONG — these are different libraries with incompatible APIs -->
<PackageReference Include="Microsoft.Data.Sqlite" />
<PackageReference Include="sqlite-net" />
<PackageReference Include="SQLitePCL.raw" />

<!-- ✅ CORRECT — sqlite-net-pcl by praeclarum + its bundle -->
<PackageReference Include="sqlite-net-pcl" Version="1.9.*" />
<PackageReference Include="SQLitePCLRaw.bundle_green" Version="2.1.*" />

Common Mistakes

❌ Using Environment.GetFolderPath for Database Path

// ❌ Not cross-platform safe — fails on some MAUI targets
var path = Path.Combine(Environment.GetFolderPath(
    Environment.SpecialFolder.LocalApplicationData), "app.db3");

// ✅ Use FileSystem.AppDataDirectory for all MAUI platforms
var path = Path.Combine(FileSystem.AppDataDirectory, "app.db3");

❌ Multiple SQLiteAsyncConnection Instances

SQLiteAsyncConnection is not thread-safe for multiple instances pointing at the same file. Use a single instance via DI singleton:

// ❌ Creating new connections per request
public async Task<List<Item>> GetItems()
{
    var db = new SQLiteAsyncConnection(Constants.DatabasePath);
    return await db.Table<Item>().ToListAsync();
}

// ✅ Lazy singleton — one connection, created once
private SQLiteAsyncConnection? _database;
private async Task<SQLiteAsyncConnection> GetDatabaseAsync()
{
    if (_database is not null) return _database;
    _database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
    await _database.ExecuteAsync("PRAGMA journal_mode=WAL;");
    await _database.CreateTableAsync<TodoItem>();
    return _database;
}

❌ Forgetting WAL Mode

Without WAL, readers block writers. Always enable it at initialization:

await _database.ExecuteAsync("PRAGMA journal_mode=WAL;");

❌ File Operations on Open Database

// ❌ Moving/deleting while connection is open — data corruption
File.Delete(Constants.DatabasePath);

// ✅ Always close first
await databaseService.CloseConnectionAsync();
if (File.Exists(Constants.DatabasePath))
    File.Delete(Constants.DatabasePath);

Platform Pitfalls

Decision Framework

Performance Tips

1. Use transactions for batch writes — individual inserts are slow; wrap in RunInTransactionAsync 2. Add `[Indexed]` to frequently queried columns — especially foreign keys 3. WAL mode eliminates reader/writer contention 4. Avoid `ToListAsync()` on large tables — use Where() filtering and pagination 5. Use raw SQL for complex queries — QueryAsync<T> is faster than chained LINQ for joins

Checklist

• [ ] Install sqlite-net-pcl + SQLitePCLRaw.bundle_green (not Microsoft.Data.Sqlite)
• [ ] Database path uses FileSystem.AppDataDirectory
• [ ] Models have [PrimaryKey, AutoIncrement]
• [ ] Single DatabaseService with lazy async init pattern
• [ ] WAL enabled via PRAGMA journal_mode=WAL
• [ ] DatabaseService registered as singleton in DI
• [ ] Connection closed before any file move/copy/delete
• [ ] iOS: DB excluded from iCloud backup if needed

SQLite Database API Reference

NuGet Package

Install sqlite-net-pcl by praeclarum:

<PackageReference Include="sqlite-net-pcl" Version="1.9.*" />
<PackageReference Include="SQLitePCLRaw.bundle_green" Version="2.1.*" />

---

Constants Class

public static class Constants
{
    public const string DatabaseFilename = "app.db3";

    public const SQLite.SQLiteOpenFlags Flags =
        SQLite.SQLiteOpenFlags.ReadWrite |
        SQLite.SQLiteOpenFlags.Create |
        SQLite.SQLiteOpenFlags.SharedCache;

    public static string DatabasePath =>
        Path.Combine(FileSystem.AppDataDirectory, DatabaseFilename);
}

• ReadWrite | Create | SharedCache — standard flags for mobile apps.
• Use FileSystem.AppDataDirectory (not Environment.GetFolderPath) for

cross-platform correctness on all MAUI targets.

---

Data Model

using SQLite;

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

    [MaxLength(250)]
    public string Name { get; set; } = string.Empty;

    public bool Done { get; set; }
}

ORM Attributes

Key attributes: [PrimaryKey], [AutoIncrement], [MaxLength(n)], [Indexed], [Ignore], [Column("name")], [Unique], [NotNull], [Table("name")].

---

Database Service

Use the lazy async initialization pattern — the connection is created once, on first access, and all callers await the same instance:

using SQLite;

public class DatabaseService
{
    private SQLiteAsyncConnection? _database;

    private async Task<SQLiteAsyncConnection> GetDatabaseAsync()
    {
        if (_database is not null)
            return _database;

        _database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
        await _database.ExecuteAsync("PRAGMA journal_mode=WAL;");
        await _database.CreateTableAsync<TodoItem>();
        return _database;
    }

    public async Task<List<TodoItem>> GetItemsAsync()
    {
        var db = await GetDatabaseAsync();
        return await db.Table<TodoItem>().ToListAsync();
    }

    public async Task<List<TodoItem>> GetItemsAsync(bool done)
    {
        var db = await GetDatabaseAsync();
        return await db.Table<TodoItem>().Where(i => i.Done == done).ToListAsync();
    }

    public async Task<TodoItem?> GetItemAsync(int id)
    {
        var db = await GetDatabaseAsync();
        return await db.Table<TodoItem>().Where(i => i.Id == id).FirstOrDefaultAsync();
    }

    public async Task<int> SaveItemAsync(TodoItem item)
    {
        var db = await GetDatabaseAsync();
        return item.Id != 0
            ? await db.UpdateAsync(item)
            : await db.InsertAsync(item);
    }

    public async Task<int> DeleteItemAsync(TodoItem item)
    {
        var db = await GetDatabaseAsync();
        return await db.DeleteAsync(item);
    }

    public async Task CloseConnectionAsync()
    {
        if (_database is not null)
        {
            await _database.CloseAsync();
            _database = null;
        }
    }
}

---

DI Registration

Register as a singleton in MauiProgram.cs:

builder.Services.AddSingleton<DatabaseService>();

Inject into view models or pages:

public class TodoListViewModel(DatabaseService database)
{
    private readonly DatabaseService _database = database;
}

---

WAL (Write-Ahead Logging)

Enabled in GetDatabaseAsync() via PRAGMA journal_mode=WAL;.

• Readers do not block writers and vice versa.
• Better performance for concurrent read/write workloads.
• Recommended for all MAUI apps.

---

Database File Management

// Delete database
await databaseService.CloseConnectionAsync();
if (File.Exists(Constants.DatabasePath))
    File.Delete(Constants.DatabasePath);

// Export / backup
await databaseService.CloseConnectionAsync();
File.Copy(Constants.DatabasePath,
    Path.Combine(FileSystem.CacheDirectory, "backup.db3"), overwrite: true);

Platform Paths for FileSystem.AppDataDirectory

---

Common Patterns

// Raw SQL
var items = await db.QueryAsync<TodoItem>(
    "SELECT * FROM TodoItem WHERE Done = ?", 1);

// Multiple tables — add in GetDatabaseAsync()
await _database.CreateTableAsync<TodoItem>();
await _database.CreateTableAsync<Category>();

// Transactions
await db.RunInTransactionAsync(conn =>
{
    conn.Insert(item1);
    conn.Insert(item2);
});

// Drop and recreate
await db.DropTableAsync<TodoItem>();
await db.CreateTableAsync<TodoItem>();