Records are immutable. sqlite-net builds objects with a parameterless constructor and writes to their properties through reflection. The two don't naturally agree, and reconciling them is the job of this article.
This is Part 3 of a six-part series building a Quest Journal in Unity 6.3. If you're new here, Part 1 introduced the records and Part 2 got sqlite-net running. By the end of this one, you'll have a project-agnostic SQLiteService plus a SQLiteController that exposes the database to the rest of the Quest Journal, without giving up what makes records records.
The problem
When sqlite-net reads a row from a table and maps it to a C# type, it calls a parameterless constructor to create an empty object, then uses reflection to populate each property one by one. So any C# type we want to use as a table must follow two rules:
The type must have a parameterless constructor.
The properties must be settable via reflection.
Picking up where Part 1 left off, our QuestData looks like this:
public record QuestData(
int Id,
string Title,
string Description,
bool IsActive = false,
bool IsComplete = false
public record QuestData(
int Id,
string Title,
string Description,
bool IsActive = false,
bool IsComplete = false
public record QuestData(
int Id,
string Title,
string Description,
bool IsActive = false,
bool IsComplete = false
public record QuestData(
int Id,
string Title,
string Description,
bool IsActive = false,
bool IsComplete = false
The primary constructor needs every argument up front, so there's no parameterless version. And the properties it generates are init-only, meaning the compiler enforces that they can only be set during construction.
On top of that, sqlite-net comes with a set of attributes we have to apply to fields so it knows how each one maps to the table. None of that lines up with positional records at first glance.
You may be tempted to break the positional record into a more traditional looking class:
public record QuestData
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public bool IsActive { get; set; }
public bool IsComplete { get; set; }
public QuestData
public record QuestData
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public bool IsActive { get; set; }
public bool IsComplete { get; set; }
public QuestData
public record QuestData
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public bool IsActive { get; set; }
public bool IsComplete { get; set; }
public QuestData
public record QuestData
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public bool IsActive { get; set; }
public bool IsComplete { get; set; }
public QuestData
That gives us attribute targets and a parameterless constructor, but at the cost of everything we built up in Part 1: positional construction, init-only properties, and the one-line definition.
The good news is we don't have to choose. A few C# features let records keep their shape while still satisfying sqlite-net.
Attributes and reflection
First, the sqlite-net attributes we'll use for the Quest Journal:
[Table(string)] — class level. Gives the SQLite table a different name from the class. Our QuestData class becomes the Quest table.
[PrimaryKey] — field or property level. Marks the column that stores the unique table ID for each entry.
[AutoIncrement] — field or property level. Tells SQLite to increment this value automatically when a new entry is added.
[Column(string)] — field or property level. Lets the SQLite column have a different name from the C# field, useful when database and code follow different naming conventions.
[Indexed] — field or property level. Marks a column as searchable, useful for foreign keys and frequently queried fields.
There are more ([Unique], [MaxLength], [Ignore], …); check the library documentation for the full list.
Our problem is that we can't put these attributes on the positional parameters of a record, because those parameters aren't properties — they're parameters. But the compiler turns each one into an init-only property. C#'s attribute targets let us aim an attribute at that generated property using [property: …]:
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete
By default, an attribute on a positional record parameter applies to the parameter itself. The property: target redirects it to the generated property — exactly what sqlite-net inspects via reflection.
This works because init is a compile-time restriction. At runtime, sqlite-net constructs the object via the parameterless constructor and then writes each property by reflection, which bypasses the init-only check entirely.
We're still missing that parameterless constructor. We can add one by chaining into the primary constructor with default values:
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete)
{
public QuestData() : this(0, string.Empty, string.Empty, false, false
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete)
{
public QuestData() : this(0, string.Empty, string.Empty, false, false
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete)
{
public QuestData() : this(0, string.Empty, string.Empty, false, false
[Table("Quest")]
public record QuestData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("title")] string Title,
[property: Column("description")] string Description,
[property: Column("is_active")] bool IsActive,
[property: Column("is_complete")] bool IsComplete)
{
public QuestData() : this(0, string.Empty, string.Empty, false, false
When sqlite-net reads a row, those defaults are overwritten immediately via reflection. When we create a new entry in code, they give us a sensible starting state.
This is enough to make records and sqlite-net work together. But before we apply it to the rest of our records, there's something Part 1 left dangling that we should fix first.
Rethinking our records for database use
Early comments on Part 1 rightly pointed out that it was unusual to have both definition and state in the same record object that maps to a single table. I agree. It's better database practice to separate these two concerns into different tables linked by foreign keys, so descriptions stay stable while only state changes.
As things stand, editing the description of a quest means modifying the same row that holds its state — and that state may have changed in the meantime, which creates friction and is prone to errors down the line. Separating into four tables makes the intent clearer.
For our QuestData and ObjectiveData, that means:
public record QuestData(int Id, string Title, string Description);
public record QuestProgressData(int Id, int QuestId, bool IsComplete);
public record ObjectiveData(int Id, int QuestId, string Description, int TargetProgress);
public record ObjectiveProgressData(int Id, int ObjectiveId, int CurrentProgress
public record QuestData(int Id, string Title, string Description);
public record QuestProgressData(int Id, int QuestId, bool IsComplete);
public record ObjectiveData(int Id, int QuestId, string Description, int TargetProgress);
public record ObjectiveProgressData(int Id, int ObjectiveId, int CurrentProgress
public record QuestData(int Id, string Title, string Description);
public record QuestProgressData(int Id, int QuestId, bool IsComplete);
public record ObjectiveData(int Id, int QuestId, string Description, int TargetProgress);
public record ObjectiveProgressData(int Id, int ObjectiveId, int CurrentProgress
public record QuestData(int Id, string Title, string Description);
public record QuestProgressData(int Id, int QuestId, bool IsComplete);
public record ObjectiveData(int Id, int QuestId, string Description, int TargetProgress);
public record ObjectiveProgressData(int Id, int ObjectiveId, int CurrentProgress
QuestData and ObjectiveData now hold only definitions. QuestProgressData exists only when the quest is active, and ObjectiveProgressData exists only while the objective is active and tracks current progress on top of that. IsActive is gone: a quest is active exactly when a QuestProgressData row exists for it.
The database now reflects the current state of the Quest Journal more accurately.
We should also add Enabled and InputDate fields to every record. Enabled lets us soft-delete entries — hide them without removing the data — and InputDate records when each entry was created. Both are common practices for database entries.
Since every record now shares three fields (Id, Enabled, InputDate), this is a good time to define a base record:
using System;
using SQLite;
namespace QuestJournal.SQLData
{
public record SQLiteData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("enabled")] bool Enabled,
[property: Column("input_date")] long InputDate)
{
public SQLiteData() : this(0, true, DateTime.Now.Ticks
using System;
using SQLite;
namespace QuestJournal.SQLData
{
public record SQLiteData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("enabled")] bool Enabled,
[property: Column("input_date")] long InputDate)
{
public SQLiteData() : this(0, true, DateTime.Now.Ticks
using System;
using SQLite;
namespace QuestJournal.SQLData
{
public record SQLiteData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("enabled")] bool Enabled,
[property: Column("input_date")] long InputDate)
{
public SQLiteData() : this(0, true, DateTime.Now.Ticks
using System;
using SQLite;
namespace QuestJournal.SQLData
{
public record SQLiteData(
[property: Column("id"), PrimaryKey, AutoIncrement] int Id,
[property: Column("enabled")] bool Enabled,
[property: Column("input_date")] long InputDate)
{
public SQLiteData() : this(0, true, DateTime.Now.Ticks
Notice this record has no [Table()] attribute — it's the base. The four real tables inherit from it.
Inheritance with records works a bit differently from classes. Here's what our four records look like:
using System;
using SQLite;
namespace QuestJournal.SQLData
{
[Table("Quest")]
public record QuestData(
int Id,
bool Enabled,
long InputDate,
[property: Column("title")] string Title,
[property: Column("description")] string Description
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestData() : this(0, true, DateTime.Now.Ticks, string.Empty, string.Empty) { }
}
[Table("QuestProgress")]
public record QuestProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("is_complete")] bool IsComplete
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestProgressData() : this(0, true, DateTime.Now.Ticks, 0, false) { }
}
[Table("Objective")]
public record ObjectiveData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("description")] string Description,
[property: Column("target_progress")] int TargetProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveData() : this(0, true, DateTime.Now.Ticks, 0, string.Empty, 0) { }
}
[Table("ObjectiveProgress")]
public record ObjectiveProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("objective_id"), Indexed] int ObjectiveId,
[property: Column("current_progress")] int CurrentProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveProgressData() : this(0, true, DateTime.Now.Ticks, 0, 0
using System;
using SQLite;
namespace QuestJournal.SQLData
{
[Table("Quest")]
public record QuestData(
int Id,
bool Enabled,
long InputDate,
[property: Column("title")] string Title,
[property: Column("description")] string Description
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestData() : this(0, true, DateTime.Now.Ticks, string.Empty, string.Empty) { }
}
[Table("QuestProgress")]
public record QuestProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("is_complete")] bool IsComplete
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestProgressData() : this(0, true, DateTime.Now.Ticks, 0, false) { }
}
[Table("Objective")]
public record ObjectiveData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("description")] string Description,
[property: Column("target_progress")] int TargetProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveData() : this(0, true, DateTime.Now.Ticks, 0, string.Empty, 0) { }
}
[Table("ObjectiveProgress")]
public record ObjectiveProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("objective_id"), Indexed] int ObjectiveId,
[property: Column("current_progress")] int CurrentProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveProgressData() : this(0, true, DateTime.Now.Ticks, 0, 0
using System;
using SQLite;
namespace QuestJournal.SQLData
{
[Table("Quest")]
public record QuestData(
int Id,
bool Enabled,
long InputDate,
[property: Column("title")] string Title,
[property: Column("description")] string Description
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestData() : this(0, true, DateTime.Now.Ticks, string.Empty, string.Empty) { }
}
[Table("QuestProgress")]
public record QuestProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("is_complete")] bool IsComplete
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestProgressData() : this(0, true, DateTime.Now.Ticks, 0, false) { }
}
[Table("Objective")]
public record ObjectiveData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("description")] string Description,
[property: Column("target_progress")] int TargetProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveData() : this(0, true, DateTime.Now.Ticks, 0, string.Empty, 0) { }
}
[Table("ObjectiveProgress")]
public record ObjectiveProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("objective_id"), Indexed] int ObjectiveId,
[property: Column("current_progress")] int CurrentProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveProgressData() : this(0, true, DateTime.Now.Ticks, 0, 0
using System;
using SQLite;
namespace QuestJournal.SQLData
{
[Table("Quest")]
public record QuestData(
int Id,
bool Enabled,
long InputDate,
[property: Column("title")] string Title,
[property: Column("description")] string Description
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestData() : this(0, true, DateTime.Now.Ticks, string.Empty, string.Empty) { }
}
[Table("QuestProgress")]
public record QuestProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("is_complete")] bool IsComplete
) : SQLiteData(Id, Enabled, InputDate)
{
public QuestProgressData() : this(0, true, DateTime.Now.Ticks, 0, false) { }
}
[Table("Objective")]
public record ObjectiveData(
int Id,
bool Enabled,
long InputDate,
[property: Column("quest_id"), Indexed] int QuestId,
[property: Column("description")] string Description,
[property: Column("target_progress")] int TargetProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveData() : this(0, true, DateTime.Now.Ticks, 0, string.Empty, 0) { }
}
[Table("ObjectiveProgress")]
public record ObjectiveProgressData(
int Id,
bool Enabled,
long InputDate,
[property: Column("objective_id"), Indexed] int ObjectiveId,
[property: Column("current_progress")] int CurrentProgress
) : SQLiteData(Id, Enabled, InputDate)
{
public ObjectiveProgressData() : this(0, true, DateTime.Now.Ticks, 0, 0
Two things to notice:
First, each record declares Id, Enabled, and InputDate as positional parameters and forwards them to the base via : SQLiteData(Id, Enabled, InputDate). This keeps the init-only flow consistent across the inheritance.
Second, each parameterless constructor passes defaults for every parameter — its own and the base's. Every record builds on the same baseline, which keeps entries consistent across the database.
With our data properly defined, we can return to SQLiteService and turn it into a real data layer.
A reusable SQLiteService
The job of this class is to be the communication layer between the game and the database. It stays low-level, exposing generic operations over our record types. Because every record derives from SQLiteData, we can constrain all generic methods to that base:
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using QuestJournal.SQLData;
using SQLite;
using UnityEngine;
namespace QuestJournal.Services
{
public class SQLiteService : IDisposable
{
private SQLiteConnection connection;
private bool disposed;
public SQLiteService(string databaseName)
{
var fullName = Path.ChangeExtension(databaseName, "db");
var path = Path.Combine(Application.persistentDataPath, fullName);
connection = new SQLiteConnection(path);
}
public void Dispose()
{
if (disposed)
return;
connection?.Close();
connection?.Dispose();
disposed = true;
}
public void CreateTable<T>() where T : SQLiteData, new() =>
connection.CreateTable<T>();
public void Insert<T>(T data) where T : SQLiteData, new() =>
connection.Insert(data);
public List<T> GetAll<T>() where T : SQLiteData, new() =>
connection.Table<T>().ToList();
public List<T> GetAllBy<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Table<T>().Where(filter).ToList();
public T Find<T>(int id) where T : SQLiteData, new() =>
connection.Find<T>(id);
public T Find<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Find<T>(filter);
public void Update<T>(T data) where T : SQLiteData, new() =>
connection.Update(data);
public void UpdateAll<T>(IEnumerable<T> data) where T : SQLiteData, new() =>
connection.UpdateAll(data);
public void Delete<T>(T data) where T : SQLiteData, new() =>
connection.Delete<T>(data.Id);
public void Disable<T>(T data) where T : SQLiteData, new() =>
connection.Update(data with { Enabled = false });
public bool IsTableEmpty<T>() where T : SQLiteData, new() =>
!connection.Table<T>().Any();
public TableQuery<T> GetTableQuery<T>() where T : SQLiteData, new() =>
connection.Table<T
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using QuestJournal.SQLData;
using SQLite;
using UnityEngine;
namespace QuestJournal.Services
{
public class SQLiteService : IDisposable
{
private SQLiteConnection connection;
private bool disposed;
public SQLiteService(string databaseName)
{
var fullName = Path.ChangeExtension(databaseName, "db");
var path = Path.Combine(Application.persistentDataPath, fullName);
connection = new SQLiteConnection(path);
}
public void Dispose()
{
if (disposed)
return;
connection?.Close();
connection?.Dispose();
disposed = true;
}
public void CreateTable<T>() where T : SQLiteData, new() =>
connection.CreateTable<T>();
public void Insert<T>(T data) where T : SQLiteData, new() =>
connection.Insert(data);
public List<T> GetAll<T>() where T : SQLiteData, new() =>
connection.Table<T>().ToList();
public List<T> GetAllBy<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Table<T>().Where(filter).ToList();
public T Find<T>(int id) where T : SQLiteData, new() =>
connection.Find<T>(id);
public T Find<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Find<T>(filter);
public void Update<T>(T data) where T : SQLiteData, new() =>
connection.Update(data);
public void UpdateAll<T>(IEnumerable<T> data) where T : SQLiteData, new() =>
connection.UpdateAll(data);
public void Delete<T>(T data) where T : SQLiteData, new() =>
connection.Delete<T>(data.Id);
public void Disable<T>(T data) where T : SQLiteData, new() =>
connection.Update(data with { Enabled = false });
public bool IsTableEmpty<T>() where T : SQLiteData, new() =>
!connection.Table<T>().Any();
public TableQuery<T> GetTableQuery<T>() where T : SQLiteData, new() =>
connection.Table<T
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using QuestJournal.SQLData;
using SQLite;
using UnityEngine;
namespace QuestJournal.Services
{
public class SQLiteService : IDisposable
{
private SQLiteConnection connection;
private bool disposed;
public SQLiteService(string databaseName)
{
var fullName = Path.ChangeExtension(databaseName, "db");
var path = Path.Combine(Application.persistentDataPath, fullName);
connection = new SQLiteConnection(path);
}
public void Dispose()
{
if (disposed)
return;
connection?.Close();
connection?.Dispose();
disposed = true;
}
public void CreateTable<T>() where T : SQLiteData, new() =>
connection.CreateTable<T>();
public void Insert<T>(T data) where T : SQLiteData, new() =>
connection.Insert(data);
public List<T> GetAll<T>() where T : SQLiteData, new() =>
connection.Table<T>().ToList();
public List<T> GetAllBy<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Table<T>().Where(filter).ToList();
public T Find<T>(int id) where T : SQLiteData, new() =>
connection.Find<T>(id);
public T Find<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Find<T>(filter);
public void Update<T>(T data) where T : SQLiteData, new() =>
connection.Update(data);
public void UpdateAll<T>(IEnumerable<T> data) where T : SQLiteData, new() =>
connection.UpdateAll(data);
public void Delete<T>(T data) where T : SQLiteData, new() =>
connection.Delete<T>(data.Id);
public void Disable<T>(T data) where T : SQLiteData, new() =>
connection.Update(data with { Enabled = false });
public bool IsTableEmpty<T>() where T : SQLiteData, new() =>
!connection.Table<T>().Any();
public TableQuery<T> GetTableQuery<T>() where T : SQLiteData, new() =>
connection.Table<T
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using QuestJournal.SQLData;
using SQLite;
using UnityEngine;
namespace QuestJournal.Services
{
public class SQLiteService : IDisposable
{
private SQLiteConnection connection;
private bool disposed;
public SQLiteService(string databaseName)
{
var fullName = Path.ChangeExtension(databaseName, "db");
var path = Path.Combine(Application.persistentDataPath, fullName);
connection = new SQLiteConnection(path);
}
public void Dispose()
{
if (disposed)
return;
connection?.Close();
connection?.Dispose();
disposed = true;
}
public void CreateTable<T>() where T : SQLiteData, new() =>
connection.CreateTable<T>();
public void Insert<T>(T data) where T : SQLiteData, new() =>
connection.Insert(data);
public List<T> GetAll<T>() where T : SQLiteData, new() =>
connection.Table<T>().ToList();
public List<T> GetAllBy<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Table<T>().Where(filter).ToList();
public T Find<T>(int id) where T : SQLiteData, new() =>
connection.Find<T>(id);
public T Find<T>(Expression<Func<T, bool>> filter) where T : SQLiteData, new() =>
connection.Find<T>(filter);
public void Update<T>(T data) where T : SQLiteData, new() =>
connection.Update(data);
public void UpdateAll<T>(IEnumerable<T> data) where T : SQLiteData, new() =>
connection.UpdateAll(data);
public void Delete<T>(T data) where T : SQLiteData, new() =>
connection.Delete<T>(data.Id);
public void Disable<T>(T data) where T : SQLiteData, new() =>
connection.Update(data with { Enabled = false });
public bool IsTableEmpty<T>() where T : SQLiteData, new() =>
!connection.Table<T>().Any();
public TableQuery<T> GetTableQuery<T>() where T : SQLiteData, new() =>
connection.Table<T
A couple of things worth calling out:
Every generic method is constrained with where T : SQLiteData, new(). The SQLiteData part guarantees the base columns are there; the new() part is what makes the type usable by sqlite-net.
Delete and Disable are intentionally distinct. Delete removes the entry from the database permanently. Disable flips Enabled to false using with, keeping the data but hiding it from any query that filters on Enabled. Both have their place; choosing the right one is up to the caller.
That's a complete SQLiteService, ready to drop into any project. What's left is a layer above it that handles the Quest Journal's specific needs.
The SQLiteController for the Quest Journal
This class lives at the game level. It knows the database name, the record types, and how they relate:
using System.Collections.Generic;
using System.Linq;
using QuestJournal.Services;
using QuestJournal.SQLData;
using UnityEngine;
namespace QuestJournal.Controllers
{
public class SQLiteController : MonoBehaviour
{
private const string DataBaseName = "QuestJournal";
private SQLiteService sqliteService;
void Awake()
{
sqliteService = new SQLiteService(DataBaseName);
CreateTables();
}
void OnDestroy()
{
sqliteService?.Dispose();
}
void CreateTables()
{
sqliteService.CreateTable<QuestData>();
sqliteService.CreateTable<QuestProgressData>();
sqliteService.CreateTable<ObjectiveData>();
sqliteService.CreateTable<ObjectiveProgressData>();
}
public void InsertQuest(QuestData quest) => sqliteService.Insert(quest);
public QuestData GetQuest(int id) => sqliteService.Find<QuestData>(id);
public void InsertObjective(ObjectiveData objective) => sqliteService.Insert(objective);
public List<ObjectiveData> GetObjectivesForQuest(int questId) =>
sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
public bool IsQuestTableEmpty() => sqliteService.IsTableEmpty<QuestData>();
public void StartQuest(QuestData quest)
{
var progress = new QuestProgressData { QuestId = quest.Id };
sqliteService.Insert(progress);
}
public void CompleteQuest(QuestProgressData progress)
{
var completedQuest = progress with { IsComplete = true };
sqliteService.Update(completedQuest);
}
public void StartObjective(ObjectiveData objective)
{
var progress = new ObjectiveProgressData { ObjectiveId = objective.Id };
sqliteService.Insert(progress);
}
public void UpdateObjectiveProgress(ObjectiveProgressData progress, int currentProgress)
{
var updatedProgress = progress with { CurrentProgress = currentProgress };
sqliteService.Update(updatedProgress);
}
public List<QuestData> GetActiveQuests()
{
var activeProgress = sqliteService.GetAllBy<QuestProgressData>(
p => !p.IsComplete && p.Enabled);
if (activeProgress.Count == 0)
return new List<QuestData>();
var activeQuestIds = activeProgress.Select(p => p.QuestId).ToHashSet();
return sqliteService.GetAllBy<QuestData>(q => q.Enabled)
.Where(q => activeQuestIds.Contains(q.Id))
.ToList();
}
public QuestProgressData GetQuestProgressData(int questId) =>
sqliteService.Find<QuestProgressData>(p => p.QuestId == questId && p.Enabled);
public List<(ObjectiveData Objective, ObjectiveProgressData Progress)> GetObjectives(int questId)
{
var objectives = sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
if (objectives.Count == 0)
return new List<(ObjectiveData, ObjectiveProgressData)>();
var progressMap = sqliteService.GetAllBy<ObjectiveProgressData>(p => p.Enabled)
.ToDictionary(p => p.ObjectiveId);
return objectives
.Where(o => progressMap.ContainsKey(o.Id))
.Select(o => (o, progressMap[o.Id]))
.ToList();
}
public bool IsObjectiveComplete(ObjectiveData objective, ObjectiveProgressData progress) =>
progress.CurrentProgress >= objective.TargetProgress
using System.Collections.Generic;
using System.Linq;
using QuestJournal.Services;
using QuestJournal.SQLData;
using UnityEngine;
namespace QuestJournal.Controllers
{
public class SQLiteController : MonoBehaviour
{
private const string DataBaseName = "QuestJournal";
private SQLiteService sqliteService;
void Awake()
{
sqliteService = new SQLiteService(DataBaseName);
CreateTables();
}
void OnDestroy()
{
sqliteService?.Dispose();
}
void CreateTables()
{
sqliteService.CreateTable<QuestData>();
sqliteService.CreateTable<QuestProgressData>();
sqliteService.CreateTable<ObjectiveData>();
sqliteService.CreateTable<ObjectiveProgressData>();
}
public void InsertQuest(QuestData quest) => sqliteService.Insert(quest);
public QuestData GetQuest(int id) => sqliteService.Find<QuestData>(id);
public void InsertObjective(ObjectiveData objective) => sqliteService.Insert(objective);
public List<ObjectiveData> GetObjectivesForQuest(int questId) =>
sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
public bool IsQuestTableEmpty() => sqliteService.IsTableEmpty<QuestData>();
public void StartQuest(QuestData quest)
{
var progress = new QuestProgressData { QuestId = quest.Id };
sqliteService.Insert(progress);
}
public void CompleteQuest(QuestProgressData progress)
{
var completedQuest = progress with { IsComplete = true };
sqliteService.Update(completedQuest);
}
public void StartObjective(ObjectiveData objective)
{
var progress = new ObjectiveProgressData { ObjectiveId = objective.Id };
sqliteService.Insert(progress);
}
public void UpdateObjectiveProgress(ObjectiveProgressData progress, int currentProgress)
{
var updatedProgress = progress with { CurrentProgress = currentProgress };
sqliteService.Update(updatedProgress);
}
public List<QuestData> GetActiveQuests()
{
var activeProgress = sqliteService.GetAllBy<QuestProgressData>(
p => !p.IsComplete && p.Enabled);
if (activeProgress.Count == 0)
return new List<QuestData>();
var activeQuestIds = activeProgress.Select(p => p.QuestId).ToHashSet();
return sqliteService.GetAllBy<QuestData>(q => q.Enabled)
.Where(q => activeQuestIds.Contains(q.Id))
.ToList();
}
public QuestProgressData GetQuestProgressData(int questId) =>
sqliteService.Find<QuestProgressData>(p => p.QuestId == questId && p.Enabled);
public List<(ObjectiveData Objective, ObjectiveProgressData Progress)> GetObjectives(int questId)
{
var objectives = sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
if (objectives.Count == 0)
return new List<(ObjectiveData, ObjectiveProgressData)>();
var progressMap = sqliteService.GetAllBy<ObjectiveProgressData>(p => p.Enabled)
.ToDictionary(p => p.ObjectiveId);
return objectives
.Where(o => progressMap.ContainsKey(o.Id))
.Select(o => (o, progressMap[o.Id]))
.ToList();
}
public bool IsObjectiveComplete(ObjectiveData objective, ObjectiveProgressData progress) =>
progress.CurrentProgress >= objective.TargetProgress
using System.Collections.Generic;
using System.Linq;
using QuestJournal.Services;
using QuestJournal.SQLData;
using UnityEngine;
namespace QuestJournal.Controllers
{
public class SQLiteController : MonoBehaviour
{
private const string DataBaseName = "QuestJournal";
private SQLiteService sqliteService;
void Awake()
{
sqliteService = new SQLiteService(DataBaseName);
CreateTables();
}
void OnDestroy()
{
sqliteService?.Dispose();
}
void CreateTables()
{
sqliteService.CreateTable<QuestData>();
sqliteService.CreateTable<QuestProgressData>();
sqliteService.CreateTable<ObjectiveData>();
sqliteService.CreateTable<ObjectiveProgressData>();
}
public void InsertQuest(QuestData quest) => sqliteService.Insert(quest);
public QuestData GetQuest(int id) => sqliteService.Find<QuestData>(id);
public void InsertObjective(ObjectiveData objective) => sqliteService.Insert(objective);
public List<ObjectiveData> GetObjectivesForQuest(int questId) =>
sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
public bool IsQuestTableEmpty() => sqliteService.IsTableEmpty<QuestData>();
public void StartQuest(QuestData quest)
{
var progress = new QuestProgressData { QuestId = quest.Id };
sqliteService.Insert(progress);
}
public void CompleteQuest(QuestProgressData progress)
{
var completedQuest = progress with { IsComplete = true };
sqliteService.Update(completedQuest);
}
public void StartObjective(ObjectiveData objective)
{
var progress = new ObjectiveProgressData { ObjectiveId = objective.Id };
sqliteService.Insert(progress);
}
public void UpdateObjectiveProgress(ObjectiveProgressData progress, int currentProgress)
{
var updatedProgress = progress with { CurrentProgress = currentProgress };
sqliteService.Update(updatedProgress);
}
public List<QuestData> GetActiveQuests()
{
var activeProgress = sqliteService.GetAllBy<QuestProgressData>(
p => !p.IsComplete && p.Enabled);
if (activeProgress.Count == 0)
return new List<QuestData>();
var activeQuestIds = activeProgress.Select(p => p.QuestId).ToHashSet();
return sqliteService.GetAllBy<QuestData>(q => q.Enabled)
.Where(q => activeQuestIds.Contains(q.Id))
.ToList();
}
public QuestProgressData GetQuestProgressData(int questId) =>
sqliteService.Find<QuestProgressData>(p => p.QuestId == questId && p.Enabled);
public List<(ObjectiveData Objective, ObjectiveProgressData Progress)> GetObjectives(int questId)
{
var objectives = sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
if (objectives.Count == 0)
return new List<(ObjectiveData, ObjectiveProgressData)>();
var progressMap = sqliteService.GetAllBy<ObjectiveProgressData>(p => p.Enabled)
.ToDictionary(p => p.ObjectiveId);
return objectives
.Where(o => progressMap.ContainsKey(o.Id))
.Select(o => (o, progressMap[o.Id]))
.ToList();
}
public bool IsObjectiveComplete(ObjectiveData objective, ObjectiveProgressData progress) =>
progress.CurrentProgress >= objective.TargetProgress
using System.Collections.Generic;
using System.Linq;
using QuestJournal.Services;
using QuestJournal.SQLData;
using UnityEngine;
namespace QuestJournal.Controllers
{
public class SQLiteController : MonoBehaviour
{
private const string DataBaseName = "QuestJournal";
private SQLiteService sqliteService;
void Awake()
{
sqliteService = new SQLiteService(DataBaseName);
CreateTables();
}
void OnDestroy()
{
sqliteService?.Dispose();
}
void CreateTables()
{
sqliteService.CreateTable<QuestData>();
sqliteService.CreateTable<QuestProgressData>();
sqliteService.CreateTable<ObjectiveData>();
sqliteService.CreateTable<ObjectiveProgressData>();
}
public void InsertQuest(QuestData quest) => sqliteService.Insert(quest);
public QuestData GetQuest(int id) => sqliteService.Find<QuestData>(id);
public void InsertObjective(ObjectiveData objective) => sqliteService.Insert(objective);
public List<ObjectiveData> GetObjectivesForQuest(int questId) =>
sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
public bool IsQuestTableEmpty() => sqliteService.IsTableEmpty<QuestData>();
public void StartQuest(QuestData quest)
{
var progress = new QuestProgressData { QuestId = quest.Id };
sqliteService.Insert(progress);
}
public void CompleteQuest(QuestProgressData progress)
{
var completedQuest = progress with { IsComplete = true };
sqliteService.Update(completedQuest);
}
public void StartObjective(ObjectiveData objective)
{
var progress = new ObjectiveProgressData { ObjectiveId = objective.Id };
sqliteService.Insert(progress);
}
public void UpdateObjectiveProgress(ObjectiveProgressData progress, int currentProgress)
{
var updatedProgress = progress with { CurrentProgress = currentProgress };
sqliteService.Update(updatedProgress);
}
public List<QuestData> GetActiveQuests()
{
var activeProgress = sqliteService.GetAllBy<QuestProgressData>(
p => !p.IsComplete && p.Enabled);
if (activeProgress.Count == 0)
return new List<QuestData>();
var activeQuestIds = activeProgress.Select(p => p.QuestId).ToHashSet();
return sqliteService.GetAllBy<QuestData>(q => q.Enabled)
.Where(q => activeQuestIds.Contains(q.Id))
.ToList();
}
public QuestProgressData GetQuestProgressData(int questId) =>
sqliteService.Find<QuestProgressData>(p => p.QuestId == questId && p.Enabled);
public List<(ObjectiveData Objective, ObjectiveProgressData Progress)> GetObjectives(int questId)
{
var objectives = sqliteService.GetAllBy<ObjectiveData>(o => o.QuestId == questId && o.Enabled);
if (objectives.Count == 0)
return new List<(ObjectiveData, ObjectiveProgressData)>();
var progressMap = sqliteService.GetAllBy<ObjectiveProgressData>(p => p.Enabled)
.ToDictionary(p => p.ObjectiveId);
return objectives
.Where(o => progressMap.ContainsKey(o.Id))
.Select(o => (o, progressMap[o.Id]))
.ToList();
}
public bool IsObjectiveComplete(ObjectiveData objective, ObjectiveProgressData progress) =>
progress.CurrentProgress >= objective.TargetProgress
This is the Facade pattern in practice. Every interaction the Quest Journal has with the database goes through SQLiteController. The sqliteService field is private, so nothing outside this class reaches into it directly. Every database operation has exactly one place to look.
The named methods carry meaning. GetActiveQuests encodes the full definition of "active": the quest is enabled, a progress row exists and is enabled, and the quest is not yet complete. The absence of a progress row means the player hasn't started the quest — no flag needed. GetObjectives assembles definition and state together, so callers receive a coherent, ready-to-use result.
The controller grows as the Quest Journal grows. New tables mean new named methods here. The result is a traceable, readable surface rather than raw service calls scattered across the codebase.
Using the SQLiteController
With the four tables and the controller in place, the full pipeline looks like this:
var quest = new QuestData {
Title = "The Lost Artifact",
Description = "Recover the artifact from the ruins."
};
sqliteController.InsertQuest(quest);
var objective = new ObjectiveData {
QuestId = quest.Id,
Description = "Search the east wing.",
TargetProgress = 3
};
sqliteController.InsertObjective(objective);
sqliteController.StartQuest(quest);
var objectives = sqliteController.GetObjectives(quest.Id);
var (objectiveData, objectiveProgress) = objectives[0];
sqliteController.StartObjective(objectiveData);
sqliteController.UpdateObjectiveProgress(objectiveProgress, objectiveProgress.CurrentProgress + 1);
objectives = sqliteController.GetObjectives(quest.Id);
(objectiveData, objectiveProgress) = objectives[0];
var isComplete = sqliteController.IsObjectiveComplete(objectiveData, objectiveProgress);
Debug.Log($"Objective complete: {isComplete}");
var questProgress = sqliteController.GetQuestProgressData(quest.Id);
sqliteController.CompleteQuest(questProgress
var quest = new QuestData {
Title = "The Lost Artifact",
Description = "Recover the artifact from the ruins."
};
sqliteController.InsertQuest(quest);
var objective = new ObjectiveData {
QuestId = quest.Id,
Description = "Search the east wing.",
TargetProgress = 3
};
sqliteController.InsertObjective(objective);
sqliteController.StartQuest(quest);
var objectives = sqliteController.GetObjectives(quest.Id);
var (objectiveData, objectiveProgress) = objectives[0];
sqliteController.StartObjective(objectiveData);
sqliteController.UpdateObjectiveProgress(objectiveProgress, objectiveProgress.CurrentProgress + 1);
objectives = sqliteController.GetObjectives(quest.Id);
(objectiveData, objectiveProgress) = objectives[0];
var isComplete = sqliteController.IsObjectiveComplete(objectiveData, objectiveProgress);
Debug.Log($"Objective complete: {isComplete}");
var questProgress = sqliteController.GetQuestProgressData(quest.Id);
sqliteController.CompleteQuest(questProgress
var quest = new QuestData {
Title = "The Lost Artifact",
Description = "Recover the artifact from the ruins."
};
sqliteController.InsertQuest(quest);
var objective = new ObjectiveData {
QuestId = quest.Id,
Description = "Search the east wing.",
TargetProgress = 3
};
sqliteController.InsertObjective(objective);
sqliteController.StartQuest(quest);
var objectives = sqliteController.GetObjectives(quest.Id);
var (objectiveData, objectiveProgress) = objectives[0];
sqliteController.StartObjective(objectiveData);
sqliteController.UpdateObjectiveProgress(objectiveProgress, objectiveProgress.CurrentProgress + 1);
objectives = sqliteController.GetObjectives(quest.Id);
(objectiveData, objectiveProgress) = objectives[0];
var isComplete = sqliteController.IsObjectiveComplete(objectiveData, objectiveProgress);
Debug.Log($"Objective complete: {isComplete}");
var questProgress = sqliteController.GetQuestProgressData(quest.Id);
sqliteController.CompleteQuest(questProgress
var quest = new QuestData {
Title = "The Lost Artifact",
Description = "Recover the artifact from the ruins."
};
sqliteController.InsertQuest(quest);
var objective = new ObjectiveData {
QuestId = quest.Id,
Description = "Search the east wing.",
TargetProgress = 3
};
sqliteController.InsertObjective(objective);
sqliteController.StartQuest(quest);
var objectives = sqliteController.GetObjectives(quest.Id);
var (objectiveData, objectiveProgress) = objectives[0];
sqliteController.StartObjective(objectiveData);
sqliteController.UpdateObjectiveProgress(objectiveProgress, objectiveProgress.CurrentProgress + 1);
objectives = sqliteController.GetObjectives(quest.Id);
(objectiveData, objectiveProgress) = objectives[0];
var isComplete = sqliteController.IsObjectiveComplete(objectiveData, objectiveProgress);
Debug.Log($"Objective complete: {isComplete}");
var questProgress = sqliteController.GetQuestProgressData(quest.Id);
sqliteController.CompleteQuest(questProgress
Note that after InsertQuest, quest.Id holds the database-assigned ID. sqlite-net writes it back via the same reflection mechanism it uses to populate properties on read, which is why we never need to set Id from our own code. The same instance can be reused later, knowing it carries its assigned ID.
Definition data (titles, descriptions, target values) never changes during these operations. Only progress rows are written to during gameplay. We can pull definitions or progress depending on what we need, with each kept in its own record representation of a SQLite table.
Bottom line
We now have a project-agnostic SQLiteService, plus a generic SQLiteData base record useful in any sqlite-net project. On top of that, the SQLiteController Facade gives the Quest Journal a clean, named surface for everything it asks of the database.
You can already see how we're using the immutability of records while still tracking and modifying game state. Part 4 takes this further: we'll focus on loading quests into the Quest Journal, both in code and in a way designers can work with. We'll populate the full list of quests and objectives, walk through how to modify them, and run small tests on the code-side flow before the UI work begins. The Quest Journal is about to start feeling like a journal.
