Add SQLite local database storage to .NET MAUI apps using sqlite-net-pcl. Covers data models with ORM attributes, async database service with lazy init, DI registration, WAL mode, and file management. Works with any UI pattern (XAML/MVVM, C# Markup, MauiReactor). USE FOR: "SQLite database", "local database", "sqlite-net-pcl", "offline storage", "CRUD database", "database service", "WAL mode", "CreateTableAsync", "local data persistence", "ORM attributes". DO NOT USE FOR: REST API data fetching (use maui-rest-api), secure credential storage (use maui-secure-storage), or file picking (use maui-file-handling).
For full service implementation, constants, data model templates, and common patterns, see references/sqlite-database-api.md.
<!-- ❌ 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.*" />
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");
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;
}
Without WAL, readers block writers. Always enable it at initialization:
await _database.ExecuteAsync("PRAGMA journal_mode=WAL;");
// ❌ 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 | Pitfall |
|---|---|
| iOS | FileSystem.AppDataDirectory is iCloud-backed — use FileSystem.CacheDirectory to exclude DB from iCloud backup |
| All | Multiple SQLiteAsyncConnection instances to same file → data corruption |
| All | No WAL → readers block writers, poor concurrent performance |
| All | File operations on open DB → corruption |
| Question | Recommendation |
|---|---|
| DI lifetime? | Singleton — one connection, WAL handles concurrent reads |
| WAL mode? | Always enable — no reason not to on mobile |
| Database path? | FileSystem.AppDataDirectory — never Environment.GetFolderPath |
| Save pattern? | Check Id != 0 → Update, else Insert |
| Multiple tables? | Add all CreateTableAsync<T>() calls in lazy init |
| Need to export/backup? | Close connection first, then File.Copy |
RunInTransactionAsync[Indexed] to frequently queried columns — especially foreign keysToListAsync() on large tables — use Where() filtering and paginationQueryAsync<T> is faster than chained LINQ for joinssqlite-net-pcl + SQLitePCLRaw.bundle_green (not Microsoft.Data.Sqlite)FileSystem.AppDataDirectory[PrimaryKey, AutoIncrement]DatabaseService with lazy async init patternPRAGMA journal_mode=WALDatabaseService registered as singleton in DI