SoloDB Documentation

dotnet add package SoloDB

public class User
{
    public long Id { get; set; }    // This becomes the primary key
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
}

using SoloDatabase;

// Create or open a database file
using var db = new SoloDB("myapp.db");

// Get a typed collection - creates it automatically if it doesn't exist
var users = db.GetCollection<User>();

// Insert a document
var user = new User
{
    Name = "Alice",
    Email = "alice@example.com",
    CreatedAt = DateTime.UtcNow
};
users.Insert(user);
// user.Id is now set to the auto-generated value (e.g., 1)

// Query with LINQ
var found = users.FirstOrDefault(u => u.Email == "alice@example.com");

// Update
found.Name = "Alice Smith";
users.Update(found);

// Delete
users.Delete(found.Id);

var user = new User { Name = "Alice", Email = "alice@example.com" };
users.Insert(user);

// Conceptually:
// Id: 1
// Value: {"Name":"Alice","Email":"alice@example.com","CreatedAt":"..."}

public class Example
{
    // SERIALIZED - public property with getter
    public string Name { get; set; }

    // SERIALIZED - public property (getter required, setter for deserialization)
    public int Age { get; set; }

    // NOT SERIALIZED - field (even if public)
    public string PublicField;

    // NOT SERIALIZED - private property
    private string Secret { get; set; }

    // NOT SERIALIZED - internal property
    internal string Internal { get; set; }
}

public struct Point
{
    public int X;  // SERIALIZED - public field on struct
    public int Y;  // SERIALIZED - public field on struct

    public double Distance { get; set; }  // SERIALIZED - public property
}

public class Order
{
    public long Id { get; set; }
    public Customer Customer { get; set; }        // Nested object
    public List<OrderItem> Items { get; set; }   // List of objects
    public Address ShippingAddress { get; set; }  // Another nested object
}

public class OrderItem
{
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

public class Product
{
    public long Id { get; set; }  // Auto-detected as primary key
    public string Name { get; set; }
}

var products = db.GetCollection<Product>();
var product = new Product { Name = "Widget" };
products.Insert(product);
// product.Id is now 1, 2, 3, etc.

using SoloDatabase.Attributes;

// Define a custom ID generator that produces string IDs from GUIDs
public class StringGuidIdGenerator : IIdGenerator<Document>
{
    public object GenerateId(ISoloDBCollection<Document> collection, Document item)
    {
        return Guid.NewGuid().ToString("N");  // Returns string like "a1b2c3d4..."
    }

    public bool IsEmpty(object id)
    {
        return string.IsNullOrEmpty(id as string);
    }
}

// Use it in your model
public class Document
{
    [SoloId(typeof(StringGuidIdGenerator))]
    public string Id { get; set; }  // String ID, not Guid

    public string Title { get; set; }
    public string Content { get; set; }
}

public class GuidIdGenerator : IIdGenerator<MyDocument>
{
    public object GenerateId(ISoloDBCollection<MyDocument> collection, MyDocument item)
    {
        return Guid.NewGuid();
    }

    public bool IsEmpty(object id) => id is Guid g && g == Guid.Empty;
}

// Typed collection - name derived from type (recommended)
var users = db.GetCollection<User>();  // Collection name: "User"

// Custom name - useful for multiple collections of same type
var activeUsers = db.GetCollection<User>("ActiveUsers");
var archivedUsers = db.GetCollection<User>("ArchivedUsers");

// Untyped collection for dynamic scenarios
var untypedCollection = db.GetUntypedCollection("MyData");

// Check if a collection exists
bool exists = db.CollectionExists<User>();
bool existsByName = db.CollectionExists("User");

// Drop a collection (deletes all data!)
db.DropCollection<User>();
db.DropCollection("ArchivedUsers");

var users = db.GetCollection<User>();

// Single insert - returns the generated ID
var user = new User { Name = "Alice", Email = "alice@example.com" };
long id = users.Insert(user);
// user.Id is also set to the same value

// Batch insert - much faster for multiple items
var newUsers = new List<User>
{
    new User { Name = "Bob", Email = "bob@example.com" },
    new User { Name = "Charlie", Email = "charlie@example.com" }
};
IList<long> ids = users.InsertBatch(newUsers);

// If a user with this email exists (assuming unique index), replace it
var user = new User { Name = "Alice Updated", Email = "alice@example.com" };
users.InsertOrReplace(user);

// Batch version
users.InsertOrReplaceBatch(manyUsers);

// By ID (throws KeyNotFoundException if not found)
User user = users.GetById(1);

// By ID - returns F# Option
var userOption = users.TryGetById(1);
if (userOption.IsSome())  // Note: In C#, add FSharpOption extensions for .IsSome()
{
    // Access the value through the Option
}

// By custom ID type
Document doc = documents.GetById<string>("doc-abc-123");

// All documents as a list
var allUsers = users.ToList();

// Full document update
var user = users.GetById(1);
user.Name = "New Name";
user.Email = "new@email.com";
users.Update(user);  // Replaces entire document

// Replace matching documents
users.ReplaceOne(u => u.Email == "old@example.com", newUserData);
users.ReplaceMany(u => u.Status == "pending", templateUser);

// Set a single field
int count = users.UpdateMany(
    u => u.Id <= 10,           // Filter
    u => u.IsActive.Set(true)   // Update action
);

// Set multiple fields at once
users.UpdateMany(
    u => u.Status == "pending",
    u => u.Status.Set("approved"),
    u => u.ApprovedAt.Set(DateTime.UtcNow),
    u => u.ApprovedBy.Set("admin")
);

// Append to a collection property
users.UpdateMany(
    u => u.Id == userId,
    u => u.Tags.Append("verified")
);

// By ID - returns count of deleted (0 or 1)
int deleted = users.Delete(1);

// By custom ID
documents.Delete<string>("doc-abc-123");

// By predicate
users.DeleteOne(u => u.Email == "old@example.com");   // First match only
users.DeleteMany(u => u.IsActive == false);           // All matches

// Where clause
var activeUsers = users.Where(u => u.IsActive).ToList();

// Multiple conditions
var thirtyDaysAgo = DateTime.UtcNow.AddDays(-30);
var results = users.Where(u =>
    u.IsActive &&
    u.CreatedAt > thirtyDaysAgo &&
    u.Email.Contains("@company.com")
).ToList();

// First match (throws if none)
var first = users.First(u => u.Email == "admin@example.com");

// First or default (returns null if none)
var admin = users.FirstOrDefault(u => u.Role == "Admin");

// Single (throws if not exactly one)
var unique = users.Single(u => u.Username == "johndoe");

// Check existence
bool hasAdmins = users.Any(u => u.Role == "Admin");
bool allActive = users.All(u => u.IsActive);

// Order by
var sorted = users
    .OrderBy(u => u.Name)
    .ThenByDescending(u => u.CreatedAt)
    .ToList();

// Pagination
int page = 2;
int pageSize = 20;
var pageResults = users
    .OrderBy(u => u.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToList();

// Select specific properties
var emails = users.Select(u => u.Email).ToList();

// Project to anonymous type
var summaries = users.Select(u => new
{
    u.Name,
    u.Email,
    DaysSinceCreated = (DateTime.UtcNow - u.CreatedAt).Days
}).ToList();

// Project to DTO
var dtos = users.Select(u => new UserDto
{
    FullName = u.Name,
    ContactEmail = u.Email
}).ToList();

int totalUsers = users.Count();
int activeCount = users.Count(u => u.IsActive);
long total = users.LongCount();

// Note: Min, Max, Sum, Average are supported on numeric projections
var maxId = users.Max(u => u.Id);

// Contains, StartsWith, EndsWith
var results = users.Where(u =>
    u.Name.Contains("john") ||
    u.Email.StartsWith("admin") ||
    u.Email.EndsWith("@company.com")
).ToList();

// SQL LIKE pattern (via extension)
var pattern = users.Where(u => u.Name.Like("J%n")).ToList();

// Query nested arrays
var tagged = users.Where(u => u.Tags.Contains("premium")).ToList();

// Check if any element matches
var oneWeekAgo = DateTime.UtcNow.AddDays(-7);
var withRecentOrders = users.Where(u =>
    u.Orders.Any(o => o.Date > oneWeekAgo)
).ToList();

using SoloDatabase.Attributes;

public class Product
{
    public long Id { get; set; }  // Always indexed (primary key)

    [Indexed(unique: true)]       // Unique index - no duplicates allowed
    public string SKU { get; set; }

    [Indexed]                     // Non-unique index
    public string Category { get; set; }

    [Indexed]
    public decimal Price { get; set; }

    public string Description { get; set; }  // Not indexed
}

var products = db.GetCollection<Product>();

// Create a non-unique index
products.EnsureIndex(p => p.Category);

// Create a unique index
products.EnsureUniqueAndIndex(p => p.Email);

// Remove an index
products.DropIndexIfExists(p => p.Category);

// Ensure all attribute-defined indexes exist
products.EnsureAddedAttributeIndexes();

// Direct property
products.EnsureIndex(p => p.Category);

// Composite index (tuple)
products.EnsureIndex(p => new { p.Category, p.Price });

// DBRef.Id navigation (indexes the foreign key)
orders.EnsureIndex(o => o.Author.Id);

try
{
    products.Insert(new Product { SKU = "EXISTING-SKU" });
}
catch (Microsoft.Data.Sqlite.SqliteException ex)
    when (ex.Message.Contains("UNIQUE"))
{
    Console.WriteLine("SKU already exists!");
}

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
}

public class Loan
{
    public long Id { get; set; }
    public decimal Amount { get; set; }

    // Unlink only — deleting a loan does not delete the referenced persons
    [SoloRef(OnOwnerDelete = DeletePolicy.Unlink)]
    public DBRef<Person> Borrower { get; set; }

    [SoloRef(OnOwnerDelete = DeletePolicy.Unlink)]
    public DBRef<Person> Guarantor { get; set; }
}

var people = db.GetCollection<Person>();
var loans = db.GetCollection<Loan>();

var aliceId = people.Insert(new Person { Name = "Alice" });
var bobId = people.Insert(new Person { Name = "Bob" });

// Reference existing persons by Id
loans.Insert(new Loan
{
    Amount = 5000,
    Borrower = DBRef<Person>.To(aliceId),
    Guarantor = DBRef<Person>.To(bobId)
});

// Or cascade-insert a new person automatically
loans.Insert(new Loan
{
    Amount = 3000,
    Borrower = DBRef<Person>.To(aliceId),
    Guarantor = DBRef<Person>.From(new Person { Name = "Charlie" })
});

// Query loans by borrower — referenced entity is loaded automatically
var aliceLoans = loans.Where(l => l.Borrower.Id == aliceId).ToList();
Console.WriteLine(aliceLoans[0].Borrower.Value.Name); // "Alice"
Console.WriteLine(aliceLoans[0].Guarantor.Value.Name); // "Bob"

// Query through the referenced entity's properties
var charlieGuaranteed = loans.Where(l =>
    l.Guarantor.Value.Name == "Charlie"
).ToList();
Console.WriteLine(charlieGuaranteed[0].Amount); // 3000

// DBRef.To(id) sets HasValue=true but IsLoaded=false
var borrowerId = people.Insert(new Person { Name = "Alice" });
var loan = new Loan { Amount = 1000, Borrower = DBRef<Person>.To(borrowerId) };
Console.WriteLine(loan.Borrower.HasValue);  // true — reference exists
Console.WriteLine(loan.Borrower.IsLoaded);  // false — entity not loaded yet
// loan.Borrower.Value would throw InvalidOperationException here

var loaded = loans.First(l => l.Amount == 5000);
Console.WriteLine(loaded.Borrower.HasValue);   // true
Console.WriteLine(loaded.Borrower.IsLoaded);   // true
Console.WriteLine(loaded.Borrower.Value.Name); // "Alice" — safe to access

public class Tag
{
    public long Id { get; set; }
    public string Label { get; set; }
}

public class Article
{
    public long Id { get; set; }
    public string Title { get; set; }
    public DBRefMany<Tag> Tags { get; set; } = new();
}

var tags = db.GetCollection<Tag>();
var articles = db.GetCollection<Article>();

var article = new Article { Title = "Breaking News" };
article.Tags.Add(new Tag { Label = "News" });  // Cascade-insert
articles.Insert(article);

// Load and modify
var loaded = articles.First(a => a.Title == "Breaking News");
loaded.Tags.Add(new Tag { Label = "Tech" });  // Add a new tag
loaded.Tags.RemoveAt(0);                       // Remove first tag
articles.Update(loaded);                       // Diff is computed automatically

public class Order
{
    public long Id { get; set; }

    // Block deletion of the referenced Customer if orders reference it
    [SoloRef(OnDelete = DeletePolicy.Restrict)]
    public DBRef<Customer> Customer { get; set; }

    // When this order is deleted, unlink items (items survive)
    [SoloRef(OnOwnerDelete = DeletePolicy.Unlink)]
    public DBRefMany<OrderItem> Items { get; set; } = new();
}

// Helper that derives a unique code from the country name
public static class UniqueCountryTag
{
    public static string Get(Country c) => c.Name.Substring(0, 2).ToUpperInvariant();
}

// User-defined generator implementing IIdGenerator<T>
public class CountryCodeGenerator : IIdGenerator<Country>
{
    public object GenerateId(ISoloDBCollection<Country> collection, Country document)
        => UniqueCountryTag.Get(document);  // Derive code from Name
    public bool IsEmpty(object id) => string.IsNullOrEmpty(id as string);
}

public class Country
{
    public long Id { get; set; }      // Internal row Id required by DBRef/DBRefMany
    [SoloId(typeof(CountryCodeGenerator))]
    public string Code { get; set; }  // Auto-generated from Name (e.g. "UN", "GE", "JA")
    public string Name { get; set; }
}

public class Office
{
    public long Id { get; set; }
    public string City { get; set; }
    public DBRef<Country, string> Country { get; set; }
}

var countries = db.GetCollection<Country>();
var offices = db.GetCollection<Office>();

// Code auto-generated via UniqueCountryTag.Get
countries.Insert(new Country { Name = "United States" }); // Code → "UN"
countries.Insert(new Country { Name = "Germany" });        // Code → "GE"

// Reference existing country by generated code
offices.Insert(new Office
{
    City = "New York",
    Country = DBRef<Country, string>.To("UN")
});

// Same country, different office — no duplicate created
offices.Insert(new Office
{
    City = "San Francisco",
    Country = DBRef<Country, string>.To("UN")
});

// This cascade-inserts a new Country — works when "JA" doesn't exist yet
offices.Insert(new Office
{
    City = "Tokyo",
    Country = DBRef<Country, string>.From(new Country { Name = "Japan" }) // Code → "JA"
});

// This FAILS — "UN" already exists (United States), From() tries to insert a duplicate
// Throws InvalidOperationException (UNIQUE constraint failed)
offices.Insert(new Office
{
    City = "Chicago",
    Country = DBRef<Country, string>.From(new Country { Name = "United States" })
});

// Filter by referenced entity's Id
var aliceLoans = loans.Where(l => l.Borrower.Id == aliceId).ToList();

// Filter by DBRefMany content
var taggedArticles = articles.Where(a =>
    a.Tags.Any(t => t.Label == "News")
).ToList();

// LINQ query:
var aliceLoans = loans.Where(l => l.Borrower.Id == aliceId).ToList();

// Translates to SQL:
// SELECT ... FROM Loan WHERE jsonb_extract(Value, '$.Borrower') = @p0

// Without an index: SQLite scans every row, extracting the JSON field per row.
// With an index: SQLite uses the expression index for direct lookup.

// Create the index:
loans.EnsureIndex(l => l.Borrower.Id);

// Composite index — useful for filtering by reference + another field:
loans.EnsureIndex(l => new { l.Borrower.Id, l.Amount });

// Plan before index (owner table scan):
// EXPLAIN QUERY PLAN SELECT ... FROM Loan WHERE jsonb_extract(Value, '$.Borrower') = @p0;
// -> SCAN Loan

loans.EnsureIndex(l => l.Borrower.Id);

// Plan after index (forced owner expression-index lookup):
// EXPLAIN QUERY PLAN SELECT ... FROM Loan INDEXED BY IX_Loan_Borrower
// WHERE jsonb_extract(Value, '$.Borrower') = @p0;
// -> SEARCH Loan USING INDEX IX_Loan_Borrower

// This query JOINs through the link table to the Person table:
var loans = loans.Where(l => l.Borrower.Value.Name == "Alice").ToList();
// Index the TARGET: people.EnsureIndex(p => p.Name);

// DBRefMany queries use EXISTS subqueries:
var tagged = articles.Where(a => a.Tags.Any(t => t.Label == "News")).ToList();
// Index the TARGET: tags.EnsureIndex(t => t.Label);

// Adding a new relation property — link table created automatically
public class Order
{
    public long Id { get; set; }
    public DBRef<Customer> Customer { get; set; }
    public DBRef<Person> SalesRep { get; set; }  // New property — safe to add
}
var orders = db.GetCollection<Order>(); // Link table for SalesRep created here

// Changing a delete policy — takes effect immediately
public class Order
{
    public long Id { get; set; }

    [SoloRef(OnDelete = DeletePolicy.Cascade)]  // Changed from default Restrict
    public DBRef<Customer> Customer { get; set; }
}
var orders = db.GetCollection<Order>(); // Catalog updated, new policy active

db.WithTransaction(tx =>
{
    var accounts = tx.GetCollection<Account>();

    var from = accounts.GetById(fromAccountId);
    var to = accounts.GetById(toAccountId);

    if (from.Balance < amount)
        throw new InvalidOperationException("Insufficient funds");

    from.Balance -= amount;
    to.Balance += amount;

    accounts.Update(from);
    accounts.Update(to);
});
// If we get here, transaction committed successfully

var orderId = db.WithTransaction(tx =>
{
    var orders = tx.GetCollection<Order>();
    var inventory = tx.GetCollection<InventoryItem>();

    // Create order and update inventory atomically
    var order = new Order { CustomerId = customerId, Total = total };
    orders.Insert(order);

    foreach (var item in orderItems)
    {
        var inv = inventory.Single(i => i.ProductId == item.ProductId);
        inv.Quantity -= item.Quantity;
        inventory.Update(inv);
    }

    return order.Id;
});

try
{
    db.WithTransaction(tx =>
    {
        var users = tx.GetCollection<User>();
        users.Insert(new User { Name = "Test" });

        // This exception causes automatic rollback
        throw new Exception("Something went wrong!");
    });
}
catch (Exception)
{
    // The user was NOT inserted - transaction rolled back
}

var result = await db.WithTransactionAsync(async tx =>
{
    var users = tx.GetCollection<User>();
    users.Insert(new User { Name = "Alice" });
    return users.Count();
});

db.WithTransaction(tx =>
{
    var users = tx.GetCollection<User>();
    users.Insert(new User { Name = "Alice" });

    try
    {
        tx.WithTransaction(inner =>
        {
            var orders = inner.GetCollection<Order>();
            orders.Insert(new Order { Total = -1 });
            throw new Exception("Invalid order");
        });
    }
    catch (Exception)
    {
        // Only the order insert was rolled back
        // Alice's insert is still part of the outer transaction
    }

    users.Insert(new User { Name = "Bob" });
    // Both Alice and Bob are committed when the outer transaction completes
});

// This compiles but throws NotSupportedException at runtime:
db.Events.OnInserted<Order>((ctx, order) =>
{
    ctx.Db.WithTransaction(tx => { ... }); // Throws NotSupportedException
    return SoloDBEventsResult.EventHandled;
});

// Instead, use the event context directly:
db.Events.OnInserted<Order>((ctx, order) =>
{
    var audit = ctx.Db.GetCollection<AuditLog>();
    audit.Insert(new AuditLog { Action = "OrderCreated" });
    return SoloDBEventsResult.EventHandled;
});

// Transactional upload: full rollback on failure
db.WithTransaction(tx =>
{
    var users = tx.GetCollection<User>();
    users.Insert(new User { Name = "Alice" });

    tx.FileSystem.Upload("/avatars/alice.png", imageStream);

    // If anything throws here, both user insert and file upload roll back.
});

// Root upload: each chunk commits independently
// (chosen to avoid long write locks during large uploads)
db.FileSystem.Upload("/public/logo.png", stream);

var users = db.GetCollection<User>();

// Before-insert handler
users.OnInserting(ctx =>
{
    Console.WriteLine($"Inserting user: {ctx.Item.Name}");
    return SoloDBEventsResult.EventHandled;
});

// After-insert handler
users.OnInserted(ctx =>
{
    Console.WriteLine($"Inserted user with ID: {ctx.Item.Id}");
    return SoloDBEventsResult.EventHandled;
});

// Update handler with access to old and new values
users.OnUpdating(ctx =>
{
    Console.WriteLine($"Updating from {ctx.OldItem.Name} to {ctx.Item.Name}");
    return SoloDBEventsResult.EventHandled;
});

// One-time handler that removes itself after first execution
users.OnInserting(ctx =>
{
    Console.WriteLine("This runs only once!");
    return SoloDBEventsResult.RemoveHandler;
});

users.OnInserting(ctx =>
{
    // Access the item being inserted/updated/deleted
    var user = ctx.Item;

    // Access the collection name
    string collectionName = ctx.CollectionName;

    // The context itself implements ISoloDB - use it for related operations
    var logs = ctx.GetCollection<AuditLog>();
    logs.Insert(new AuditLog { Action = "UserCreated", UserId = user.Id });

    return SoloDBEventsResult.EventHandled;
});

users.OnUpdating(ctx =>
{
    var oldUser = ctx.OldItem;  // Value before update
    var newUser = ctx.Item;     // Value after update

    if (oldUser.Email != newUser.Email)
    {
        Console.WriteLine($"Email changed from {oldUser.Email} to {newUser.Email}");
    }

    return SoloDBEventsResult.EventHandled;
});

// Keep a reference to the handler
InsertingHandler<User> myHandler = ctx =>
{
    Console.WriteLine("Handler running");
    return SoloDBEventsResult.EventHandled;
};

// Register it
users.OnInserting(myHandler);

// Later, unregister it
users.Unregister(myHandler);

users.OnInserting(ctx =>
{
    if (ctx.Item.Email == null)
        throw new InvalidOperationException("Email is required");

    return SoloDBEventsResult.EventHandled;
});

try
{
    users.Insert(new User { Name = "Test", Email = null });
}
catch (SqliteException ex)
{
    // Insert was rolled back - user was NOT inserted
    Console.WriteLine(ex.Message);  // Contains "Email is required"
}

users.OnInserting(ctx =>
{
    Console.WriteLine("First handler");
    return SoloDBEventsResult.EventHandled;
});

users.OnInserting(ctx =>
{
    Console.WriteLine("Second handler");
    throw new Exception("Oops!");  // Stops here, third won't run
});

users.OnInserting(ctx =>
{
    Console.WriteLine("Third handler");  // Never reached
    return SoloDBEventsResult.EventHandled;
});

public abstract class Shape
{
    public long Id { get; set; }
    public string Color { get; set; }
    public abstract double CalculateArea();
}

public class Circle : Shape
{
    public double Radius { get; set; }
    public override double CalculateArea() => Math.PI * Radius * Radius;
}

public class Rectangle : Shape
{
    public double Width { get; set; }
    public double Height { get; set; }
    public override double CalculateArea() => Width * Height;
}

var shapes = db.GetCollection<Shape>();

// Insert different types
shapes.Insert(new Circle { Color = "Red", Radius = 5.0 });
shapes.Insert(new Rectangle { Color = "Blue", Width = 4.0, Height = 6.0 });

// Query all shapes (returns properly typed objects)
var allShapes = shapes.ToList();
// allShapes[0] is Circle, allShapes[1] is Rectangle

// Query by base class properties
var blueShapes = shapes.Where(s => s.Color == "Blue").ToList();

// Filter by concrete type using OfType<T>()
var circles = shapes.OfType<Circle>().ToList();
var largeCircles = shapes.OfType<Circle>()
    .Where(c => c.Radius > 3.0)
    .ToList();

// Borrow a connection from the pool
using var conn = db.Connection.Borrow();

using SoloDatabase.SQLiteTools;

// Execute non-query commands (CREATE, INSERT, UPDATE, DELETE)
// Returns number of rows affected
conn.Execute("CREATE TABLE IF NOT EXISTS Logs (Id INTEGER PRIMARY KEY, Message TEXT)");
conn.Execute("INSERT INTO Logs (Message) VALUES (@msg)", new { msg = "Hello" });

// Query multiple rows - returns IEnumerable<T>
var logs = conn.Query<LogEntry>("SELECT * FROM Logs WHERE Id > @id", new { id = 100 });

// Query first row (throws if no results)
var count = conn.QueryFirst<int>("SELECT COUNT(*) FROM Logs");

// Query first row or default (returns null/default if no results)
var log = conn.QueryFirstOrDefault<LogEntry>("SELECT * FROM Logs WHERE Id = @id", new { id = 999 });

// Map to a class
public class LogEntry
{
    public long Id { get; set; }
    public string Message { get; set; }
}
var logs = conn.Query<LogEntry>("SELECT Id, Message FROM Logs");

// Map to anonymous types
var results = conn.Query<dynamic>("SELECT Id, Message FROM Logs");

// Map to primitives
var ids = conn.Query<long>("SELECT Id FROM Logs");

// Documents are stored as JSONB in the 'Value' column
// Use SQLite's json_extract to query specific fields
var rawUsers = conn.Query<dynamic>(
    "SELECT Id, json_extract(Value, '$.Name') as Name FROM User WHERE json_extract(Value, '$.IsActive') = 1"
);

var fs = db.FileSystem;

// Upload from a stream
using (var stream = File.OpenRead("report.pdf"))
{
    fs.Upload("/documents/reports/2024-q4.pdf", stream);
}

// Download to a stream
using (var output = File.Create("downloaded.pdf"))
{
    fs.Download("/documents/reports/2024-q4.pdf", output);
}

// Check existence and delete
bool exists = fs.Exists("/documents/reports/2024-q4.pdf");
fs.DeleteFileAt("/documents/reports/2024-q4.pdf");

// Compare: System.IO file access
using (var fileStream = File.Open("local.txt", FileMode.OpenOrCreate))
{
    fileStream.Write(data, 0, data.Length);
    fileStream.Position = 0;
    fileStream.Read(buffer, 0, buffer.Length);
}

// SoloDB file access - same API!
using (var fileStream = fs.OpenOrCreateAt("/data/log.txt"))
{
    fileStream.Write(data, 0, data.Length);
    fileStream.Position = 0;
    fileStream.Read(buffer, 0, buffer.Length);
}

// Write text
using (var stream = fs.OpenOrCreateAt("/logs/app.log"))
using (var writer = new StreamWriter(stream))
{
    writer.WriteLine($"[{DateTime.UtcNow}] Application started");
    writer.WriteLine($"[{DateTime.UtcNow}] Processing...");
}

// Read text
using (var stream = fs.OpenOrCreateAt("/logs/app.log"))
using (var reader = new StreamReader(stream))
{
    string contents = reader.ReadToEnd();
}

// Write at specific offset (creates sparse file if needed)
byte[] data = GetSomeData();
fs.WriteAt("/data/sparse.bin", 1024 * 1024, data);  // Write at 1MB offset

// Read from specific offset - doesn't load entire file
byte[] chunk = fs.ReadAt("/data/sparse.bin", 1024 * 1024, data.Length);

// Sparse files: unwritten areas read as zeros, don't consume storage
fs.WriteAt("/sparse.dat", 10_000_000, new byte[] { 1, 2, 3 });  // 10MB offset
// File is NOT 10MB on disk - only the written chunks are stored

// Set file metadata (key-value pairs)
fs.SetMetadata("/documents/report.pdf", "Author", "Finance Team");
fs.SetMetadata("/documents/report.pdf", "Department", "Accounting");

// Read file info with metadata
var fileInfo = fs.GetAt("/documents/report.pdf");
Console.WriteLine($"Name: {fileInfo.Name}");
Console.WriteLine($"Size: {fileInfo.Length} bytes");
Console.WriteLine($"Created: {fileInfo.Created}");
Console.WriteLine($"Modified: {fileInfo.Modified}");
Console.WriteLine($"Author: {fileInfo.Metadata["Author"]}");

// Delete specific metadata
fs.DeleteMetadata(fileInfo, "Department");

// Directory metadata works the same way
var dir = fs.GetOrCreateDirAt("/documents/archive");
fs.SetDirectoryMetadata(dir, "RetentionPolicy", "7years");
fs.DeleteDirectoryMetadata(dir, "RetentionPolicy");

// Create directory (creates parent directories automatically)
var dir = fs.GetOrCreateDirAt("/documents/archive/2024");

// Get directory info
var dirInfo = fs.GetDirAt("/documents/archive");

// List files in a directory
var files = fs.ListFilesAt("/documents/reports/");

// List subdirectories
var dirs = fs.ListDirectoriesAt("/documents/");

// Recursive listing (files and directories)
var allEntries = fs.RecursiveListEntriesAt("/documents/");

// Lazy recursive listing (memory efficient for large trees)
var lazyEntries = fs.RecursiveListEntriesAtLazy("/");

// Delete directory (must be empty)
fs.DeleteDirAt("/documents/old");

// Move/rename a file (throws IOException if destination exists)
fs.MoveFile("/documents/draft.pdf", "/documents/final.pdf");

// Move to different directory
fs.MoveFile("/inbox/file.txt", "/archive/2024/file.txt");

// Move and replace if exists
fs.MoveReplaceFile("/temp/new.pdf", "/documents/report.pdf");

var files = new List<BulkFileData>
{
    new("/logs/app1.log", Encoding.UTF8.GetBytes("Log data 1"), null, null),
    new("/logs/app2.log", Encoding.UTF8.GetBytes("Log data 2"), null, null),
    new("/images/logo.png", imageBytes, DateTimeOffset.UtcNow, null)
};

fs.UploadBulk(files);  // Single transaction for all files

// Modified is automatically updated on writes
fs.WriteAt("/data/file.bin", 0, data);  // Modified = now
fs.Upload("/data/file.bin", stream);     // Modified = now

// Manually set timestamps when needed
fs.SetFileCreationDate("/archive/old.txt", DateTimeOffset.UtcNow.AddYears(-1));
fs.SetFileModificationDate("/archive/old.txt", DateTimeOffset.UtcNow.AddDays(-30));

// Read timestamps from file info
var info = fs.GetAt("/archive/old.txt");
Console.WriteLine($"Created: {info.Created}");
Console.WriteLine($"Modified: {info.Modified}");

// File-based (persistent)
using var db = new SoloDB("path/to/database.db");
using var db = new SoloDB("./relative/path.db");
using var db = new SoloDB(@"C:\absolute\path.db");

// In-memory (lost when disposed)
using var db = new SoloDB("memory:my-database");

// Shared in-memory (accessible by name within process)
using var db1 = new SoloDB("memory:shared");
using var db2 = new SoloDB("memory:shared");  // Same database

// Singleton pattern for web apps / services
public static class Database
{
    public static SoloDB Instance { get; } = new SoloDB("app.db");
}

// Usage
var users = Database.Instance.GetCollection<User>();

// Optimize query plans (runs ANALYZE)
db.Optimize();

// Backup to another database
using var backup = new SoloDB("backup.db");
db.BackupTo(backup);

// Vacuum into new file (compacts and defragments)
db.VacuumTo("compacted.db");

// Disable caching (reduces memory, slower repeated queries)
// Sets config.CachingEnabled = false
db.DisableCaching();

// Re-enable caching
// Sets config.CachingEnabled = true
db.EnableCaching();

// Clear the current cache (frees memory, keeps caching enabled)
db.ClearCache();

using SoloDatabase.QueryTranslator;

// Pre-expression handler: Intercept expressions BEFORE the default translator
// Return true to indicate you've handled the expression
QueryTranslator.preExpressionHandler.Add((queryBuilder, expression) =>
{
    // Custom handling for specific expression types
    // Return false to let the default translator handle it
    return false;
});

// Unknown expression handler: Handle expressions the default translator doesn't support
// Called when no built-in handler matches
QueryTranslator.unknownExpressionHandler.Add((queryBuilder, expression) =>
{
    // Handle custom expression types that aren't supported by default
    // Throw or return false if you can't handle it
    return false;
});

// Without index: Full table scan
var user = users.FirstOrDefault(u => u.Email == "test@example.com");

// With index: Fast lookup
[Indexed]
public string Email { get; set; }

// Slow: 1000 individual transactions
foreach (var item in items)
    collection.Insert(item);

// Fast: Single transaction
collection.InsertBatch(items);

// Slow: Each update is a separate transaction
foreach (var user in usersToUpdate)
{
    user.LastSeen = DateTime.UtcNow;
    users.Update(user);
}

// Fast: Single transaction
db.WithTransaction(tx =>
{
    var col = tx.GetCollection<User>();
    foreach (var user in usersToUpdate)
    {
        user.LastSeen = DateTime.UtcNow;
        col.Update(user);
    }
});

// Slow: Loads entire documents
var names = users.ToList().Select(u => u.Name);

// Fast: Only fetches Name field
var names = users.Select(u => u.Name).ToList();

// Slow: Load, modify, save each document
foreach (var user in users.Where(u => u.NeedsUpdate))
{
    var u = users.GetById(user.Id);
    u.Status = "updated";
    users.Update(u);
}

// Fast: Single SQL UPDATE statement
users.UpdateMany(u => u.NeedsUpdate, u => u.Status.Set("updated"));

// Bad: Storing large data in documents
public class Report
{
    public long Id { get; set; }
    public string Title { get; set; }
    public byte[] PdfContent { get; set; }  // Large! Loaded on every access
}

// Good: Store large data in FileSystem, reference by path
public class Report
{
    public long Id { get; set; }
    public string Title { get; set; }
    public string PdfPath { get; set; }  // e.g., "/reports/2024/report-123.pdf"
}

// Use Events to sync FileSystem with document lifecycle
db.Events.OnDeleted<Report>((ctx, report) =>
{
    ctx.FileSystem.DeleteFileAt(report.PdfPath);
});

// Read only what you need from FileSystem
byte[] chunk = fs.ReadAt(report.PdfPath, offset: 0, length: 1024);