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();

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

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
}

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"
}

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