Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 42 ยท Data and Storage

SQLite Local Database

Add a local SQLite database to a .NET MAUI app with sqlite-net-pcl: models, CreateTableAsync, CRUD, queries, indexes and migrations, plus a runnable repository stand-in.

When data becomes a list of records you search, sort and update, a file of JSON stops being enough. SQLite is a database engine that lives in a single file inside your app, needs no server and is built into every phone. In MAUI the most common way to use it is the sqlite-net-pcl package, a tiny object mapper with an async API. Entity Framework Core also works and is introduced at the end.

The SQLite engine and the sqlite-net package are not available in the in-browser C# runner. The real code is read-only. The runnable parts are a hand-written in-memory stand-in that mimics the sqlite-net API shape (attributes, async methods, simple queries), so you can practice the repository pattern.

Install

Add two NuGet packages to the MAUI project:

TEXT
sqlite-net-pcl
SQLitePCLRaw.bundle_green

The second package brings the native SQLite library for each platform. A common issue on iOS and Mac Catalyst is a missing bundle, which shows up as a "no such provider" error at startup.

A model with attributes

C#
using SQLite;

[Table("todos")]
public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [MaxLength(200), NotNull]
    public string Title { get; set; } = "";

    public bool Done { get; set; }

    [Indexed]
    public DateTime? DueDate { get; set; }

    [Ignore]
    public string DisplayDue => DueDate?.ToString("d") ?? "no date";
}

Each public property becomes a column. [Ignore] skips a property. [Indexed] speeds up searches and sorting on that column. Keep models simple: sqlite-net does not track relationships like EF Core does.

The connection and the repository

C#
public class TodoRepository
{
    SQLiteAsyncConnection? _db;

    async Task InitAsync()
    {
        if (_db is not null) return;

        string path = Path.Combine(FileSystem.AppDataDirectory, "todos.db3");
        _db = new SQLiteAsyncConnection(path,
            SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache);

        await _db.CreateTableAsync<TodoItem>();
    }

    public async Task<List<TodoItem>> GetAllAsync()
    {
        await InitAsync();
        return await _db!.Table<TodoItem>().OrderBy(t => t.Done).ThenBy(t => t.DueDate).ToListAsync();
    }

    public async Task<List<TodoItem>> SearchAsync(string text)
    {
        await InitAsync();
        return await _db!.Table<TodoItem>().Where(t => t.Title.Contains(text)).ToListAsync();
    }

    public async Task<int> SaveAsync(TodoItem item)
    {
        await InitAsync();
        return item.Id == 0 ? await _db!.InsertAsync(item) : await _db!.UpdateAsync(item);
    }

    public async Task<int> DeleteAsync(TodoItem item)
    {
        await InitAsync();
        return await _db!.DeleteAsync(item);
    }
}

Lazy initialization (InitAsync) keeps startup fast, because opening the database happens the first time it is needed. Register the repository as a singleton so the connection is shared:

C#
builder.Services.AddSingleton<TodoRepository>();

CreateTableAsync<T>() creates the table if it does not exist and adds missing columns if you added a property. It does not rename or drop columns, so for those changes you need your own migration.

Raw SQL is available when LINQ is not enough:

C#
var overdue = await _db.QueryAsync<TodoItem>(
    "SELECT * FROM todos WHERE Done = 0 AND DueDate < ?", DateTime.Today);

int count = await _db.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM todos");

Always pass values as ? parameters, never by joining strings, to avoid SQL injection. If you want to practice the SQL itself, the SQL tutorial has a runner.

Try it: a repository stand-in with the same shape

This runnable stand-in reads the [PrimaryKey, AutoIncrement] attributes with reflection, so the repository code you write against it looks like the real thing. It is not SQLite.

C# Stand-in: mini table with auto-increment keys

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Notice the rule the repository follows: Id == 0 means "new, insert", anything else means "existing, update". The real sqlite-net sets the Id on the object after InsertAsync, exactly like the stand-in.

Migrations

When you ship version 2 of the app with a changed schema, users already have a version 1 database. SQLite stores an integer called user_version that you can use to decide which migrations to run:

C#
int version = await _db.ExecuteScalarAsync<int>("PRAGMA user_version");

if (version < 1)
{
    await _db.ExecuteAsync("ALTER TABLE todos ADD COLUMN Priority INTEGER NOT NULL DEFAULT 0");
    await _db.ExecuteAsync("PRAGMA user_version = 1");
}
if (version < 2)
{
    await _db.ExecuteAsync("CREATE INDEX IF NOT EXISTS idx_todos_done ON todos(Done)");
    await _db.ExecuteAsync("PRAGMA user_version = 2");
}

Never edit an old migration after release. Add a new step instead.

Try it: ordered migrations

C# Stand-in: a migration runner

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Good practices

  • Open one connection and reuse it. Do not create a connection per call.
  • Use async methods so the UI thread never waits on the disk.
  • Keep database code behind a repository interface so view models can use a fake in tests (unit testing).
  • Use transactions (RunInTransactionAsync) when several writes must succeed together.
  • Add [Indexed] only to columns you filter or sort on; every index slows writes.
  • Never store the database in CacheDirectory.

Entity Framework Core instead?

EF Core with the SQLite provider also works in MAUI. It adds LINQ queries over related tables, change tracking and strongly typed migrations (dotnet ef migrations add), at the cost of a larger app and slower start-up. Choose sqlite-net for a small, single-table-style app and EF Core when you have many related entities.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does CreateTableAsync<T>() do if the table already exists and the class gained a property?

    sqlite-net adds new columns automatically, but it never renames or removes existing ones.
  2. 2. How does a repository usually decide between insert and update?

    An auto-increment key of 0 means the row is new; a non-zero Id identifies an existing row.
  3. 3. Why pass SQL values as ? parameters?

    Parameters keep user text separate from the SQL command, so it cannot change the command.
  4. 4. Where should the SQLite file be stored?

    AppDataDirectory is private, durable and backed up; the cache may be deleted and Raw is read-only.

Summary

  • sqlite-net-pcl plus SQLitePCLRaw.bundle_green gives a small async SQLite API.
  • Models use attributes such as [PrimaryKey, AutoIncrement] and [Indexed].
  • Wrap the connection in a singleton repository with lazy initialization.
  • Handle schema changes with ordered migrations keyed on user_version.

Next: Consuming REST APIs