davidortinau/maui-skills/maui-sqlite-database

<!-- ❌ 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.*" />

// ❌ 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");

// ❌ 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;
}

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