Local Storage with SQLite

Analogy categoryAnalogies

Mobile apps need to work when the network doesn't, and Preferences or file storage aren't enough once you have structured, queryable data — a shopping list, an offline cache of API results, or a habit tracker's history. SQLite is the standard answer: a serverless, file-based relational database that ships as a native library on both Android and iOS, and the sqlite-net-pcl NuGet package gives MAUI apps a lightweight ORM over it with attributes like [PrimaryKey] and [AutoIncrement] and a fully async API surface.

🏏

Cricket analogy: It's like a team keeping its own physical scorebook in the dugout rather than depending on the stadium's Wi-Fi to look up figures — SQLite is the app's own local scorebook it can consult with zero network dependency.

Defining Models and Opening a Connection

A SQLite table maps to a plain C# class decorated with [Table("name")], [PrimaryKey], and [AutoIncrement]; the connection itself is an SQLiteAsyncConnection pointed at a file path built from FileSystem.AppDataDirectory, which MAUI guarantees is a writable, sandboxed folder on every platform. You typically wrap the connection in a singleton DatabaseService registered in MauiProgram.cs, and call CreateTableAsync<T>() once on startup so the schema exists before any query runs.

🏏

Cricket analogy: It's like defining a standard scorecard template before the match starts — every innings gets recorded into the same structured columns, just as every row goes into the same typed table.

csharp

[Table("notes")]
public class Note
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [NotNull]
    public string Title { get; set; } = string.Empty;
    public string? Body { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class DatabaseService
{
    private readonly SQLiteAsyncConnection _db;

    public DatabaseService()
    {
        var dbPath = Path.Combine(FileSystem.AppDataDirectory, "app.db3");
        _db = new SQLiteAsyncConnection(dbPath);
        _db.CreateTableAsync<Note>().Wait();
    }

    public Task<List<Note>> GetNotesAsync() =>
        _db.Table<Note>().OrderByDescending(n => n.CreatedAt).ToListAsync();
}

Async CRUD Operations

sqlite-net-pcl exposes CRUD as awaitable methods — InsertAsync, UpdateAsync, DeleteAsync, and LINQ-style queries through Table<T>().Where(...) that get translated into SQL under the hood. Because SQLite writes are serialized per connection, sharing a single SQLiteAsyncConnection instance across the app (rather than opening a new one per call) both avoids file-locking errors and lets the library queue operations safely, which matters more on mobile where multiple pages might touch the database concurrently.

🏏

Cricket analogy: It's like a single official scorer for the match — every run, wicket, and extra goes through that one person so the scorebook never gets two conflicting entries at once, just as one shared connection serializes writes.

Keep the SQLiteAsyncConnection alive for the lifetime of the app by registering DatabaseService as a Singleton in MauiProgram.cs — reopening the connection per page is a common source of 'database is locked' exceptions.

Handling Schema Changes

SQLite doesn't have a first-class migration framework the way EF Core does, so schema evolution in a sqlite-net-pcl app is usually handled manually: CreateTableAsync<T>() is safe to call repeatedly because it only creates a table if it doesn't already exist, but adding a new column to an existing table requires an explicit ALTER TABLE ... ADD COLUMN executed via _db.ExecuteAsync, guarded by checking PRAGMA table_info or a stored schema-version number so it only runs once per device.

🏏

Cricket analogy: It's like a groundstaff upgrading a pitch between series rather than rebuilding the stadium — you alter the existing surface in place instead of tearing down what's already there.

ALTER TABLE in SQLite only supports a limited set of operations (adding columns, renaming tables/columns) — you cannot drop or reorder columns directly. Complex migrations typically require creating a new table, copying data across, then dropping the old one inside a transaction.

SQLite via sqlite-net-pcl gives MAUI apps offline-capable, queryable local storage backed by a single file on-device.
Model classes use [Table], [PrimaryKey], and [AutoIncrement] attributes; store the .db3 file under FileSystem.AppDataDirectory.
Register a single SQLiteAsyncConnection as a Singleton and reuse it app-wide to avoid file-locking errors from concurrent connections.
CRUD operations (InsertAsync, UpdateAsync, DeleteAsync, Table<T>().Where(...)) are fully async and safe to await from view models.
CreateTableAsync<T>() is idempotent, but adding columns to existing tables requires explicit ALTER TABLE statements guarded by a schema-version check.
SQLite's ALTER TABLE support is limited — dropping or reordering columns requires the copy-to-new-table-and-swap pattern.
SQLite is a good fit for structured, queryable local data; simple key-value settings are better served by Microsoft.Maui.Storage.Preferences.

Practice what you learned

1. Where should a MAUI app store its SQLite database file?

2. Why should a MAUI app reuse a single SQLiteAsyncConnection instance instead of opening a new one per query?

3. What does CreateTableAsync<T>() do if the table already exists?

4. What is the recommended approach for a schema change that SQLite's ALTER TABLE cannot express directly, like dropping a column?

Was this page helpful?

Consuming REST APIs

Dependency Injection

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Snowflake Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Topics covered

#Programming#NETMAUIStudyNotes#LocalStorageWithSQLite#Local#Storage#SQLite#Defining#StudyNotes#SkillVeris#ExamPrep

Local Storage with SQLite