CRUD Operations

Document Types

Every document type must have a public Id property of type Guid, int, long, or string. The Id is stored in both the SQLite Id column and inside the JSON blob, so query results always include it.

public class User
{
    public string Id { get; set; } = "";
    public string Name { get; set; } = "";
    public int Age { get; set; }
    public string? Email { get; set; }
}

Auto-generation rules

Id CLR Type	Default Value	Auto-Gen Strategy
Guid	Guid.Empty	Guid.NewGuid()
string	null or ""	Guid.NewGuid().ToString("N")
int	0	MAX(CAST(Id AS INTEGER)) + 1 per TypeName
long	0	MAX(CAST(Id AS INTEGER)) + 1 per TypeName

When Set is called with a default Id, the store auto-generates one and writes it back to the object. When a non-default Id is provided, it is used as-is.

Store a document

// Auto-generated ID — written back to the object
var user = new User { Name = "Alice", Age = 25 };
await store.Set(user);
// user.Id is now populated

// Explicit ID
await store.Set(new User { Id = "user-1", Name = "Alice", Age = 25 });

Upsert with JSON Merge Patch

Upsert uses SQLite’s json_patch() (RFC 7396 JSON Merge Patch) to deep-merge a partial patch into an existing document. If the document doesn’t exist, it is inserted as-is. Unlike Set, which replaces the entire document, Upsert only overwrites the fields present in the patch. The document must have a non-default Id.

// Insert a full document
await store.Set(new User { Id = "user-1", Name = "Alice", Age = 25, Email = "alice@test.com" });

// Merge patch — only update Name and Age, preserve Email
await store.Upsert(new User { Id = "user-1", Name = "Alice", Age = 30 });

var user = await store.Get<User>("user-1");
// user.Name == "Alice", user.Age == 30, user.Email == "alice@test.com" (preserved)

How it works:

• On insert (new ID): the patch is stored as the full document.
• On conflict (existing ID): json_patch(existing, patch) deep-merges the patch into the stored JSON. Objects are recursively merged; scalars and arrays are replaced.
• Null properties are excluded from the patch automatically. In C#, unset nullable properties (e.g. string? Email) serialize as null, which would remove the key under RFC 7396. The library strips these so that unset fields are preserved rather than deleted.

Note

For true partial updates, use nullable properties in your patch type so that unset fields are null and excluded from the merge. Non-nullable properties with default initializers (e.g. string Name = "") will always be included in the patch.

Update a single property (SetProperty)

SetProperty updates a single scalar field in-place using SQLite’s json_set() — no deserialization, no full document replacement. Returns true if the document was found and updated, false if not found.

// Update a scalar field
await store.SetProperty<User>("user-1", u => u.Age, 31);

// Update a string field
await store.SetProperty<User>("user-1", u => u.Email, "newemail@test.com");

// Set a field to null
await store.SetProperty<User>("user-1", u => u.Email, null);

// Nested property
await store.SetProperty<Order>("order-1", o => o.ShippingAddress.City, "Portland");

// Check if document existed
bool updated = await store.SetProperty<User>("user-1", u => u.Age, 31);

How it works: The expression u => u.Age is resolved to the JSON path $.age (respecting [JsonPropertyName] attributes and naming policies). The SQL executed is:

UPDATE documents
SET Data = json_set(Data, '$.age', json('31')), UpdatedAt = @now
WHERE Id = @id AND TypeName = @typeName;

Supported value types: string, int, long, double, float, decimal, bool, and null. To replace a collection or nested object, use Set (full replacement) or Upsert (merge patch).

Remove a single property (RemoveProperty)

RemoveProperty strips a field from the stored JSON using SQLite’s json_remove(). Returns true if the document was found and updated, false if not found. The removed field will have its C# default value on next read.

// Remove a nullable field
await store.RemoveProperty<User>("user-1", u => u.Email);

// Remove a nested property
await store.RemoveProperty<Order>("order-1", o => o.ShippingAddress.City);

// Remove a collection property (removes the entire array)
await store.RemoveProperty<Order>("order-1", o => o.Tags);

Unlike SetProperty, RemoveProperty works on any property type — scalar, nested object, or collection — because it simply removes the key from the JSON.

Choosing an update strategy

Operation	Use when	Scope	Collections
SetProperty	Changing one scalar field	Single field via json_set	Scalar values only
RemoveProperty	Stripping a field from the document	Single field via json_remove	Any property type
Upsert	Patching multiple fields at once	Deep merge via json_patch	Replaces arrays (RFC 7396)
Set	Replacing the entire document	Full replacement	Full control

Get a document

var user = await store.Get<User>("user-1");

Get all documents of a type

var users = await store.Query<User>().ToList();

Remove a document

// By ID
bool deleted = await store.Remove<User>("user-1");

Remove documents matching a predicate

// Returns number of deleted rows
int deleted = await store.Query<User>().Where(u => u.Age < 18).ExecuteDelete();

Update documents matching a predicate

// Update a property on matching docs — returns number of updated rows
int updated = await store.Query<User>()
    .Where(u => u.Age < 18)
    .ExecuteUpdate(u => u.Age, 18);

See Querying for more examples of bulk delete and update with expressions.

Clear all documents of a type

int deletedCount = await store.Clear<User>();