TursoDb.Core 1.0.0

TursoDb.Core

A high-level, production-ready .NET library for Turso (libSQL) database operations. Provides comprehensive features including CRUD operations, transactions, batch operations, pagination, aggregation, ORM mapping, query caching, and extensive error handling.

🚀 Features

Core Operations

• ✅ CRUD Operations - Create, Read, Update, Delete with parameterized queries
• ✅ Transactions - ACID-compliant transactions with automatic rollback
• ✅ Batch Operations - Bulk insert and multi-statement execution
• ✅ Pagination - Efficient paginated queries with total count

Advanced Features

• ✅ Aggregation - COUNT, SUM, AVG, MIN, MAX operations
• ✅ ORM Mapping - Entity-based operations with type-safe mappings
• ✅ Query Builder - Fluent API for building complex queries
• ✅ Upsert Operations - Insert-or-update functionality
• ✅ Schema Management - Inspect and validate database schema

Performance & Optimization

• ✅ Query Caching - Built-in caching with TTL and statistics
• ✅ Retry Policies - Exponential backoff and fixed delay strategies
• ✅ Error Handling - Comprehensive exception handling and validation
• ✅ Type Safety - Strongly-typed operations throughout

📦 Installation

dotnet add package TursoDb.Core

Or via NuGet Package Manager:

Install-Package TursoDb.Core

🎯 Quick Start

1. Initialize the Client

using TursoDb.Core;

var db = new TursoDbClient(
    "libsql://your-database-url.turso.io",
    "your-auth-token"
);

2. Basic Query Operations

// Simple SELECT query
var rows = await db.QueryAsync(
    "SELECT * FROM users WHERE age > @age",
    DbParam.Create("age", 18)
);

foreach (var row in rows)
{
    var name = row.Get<string>("name");
    var age = row.Get<int>("age");
    Console.WriteLine($"{name}: {age}");
}

3. CRUD Operations

// INSERT
var affected = await db.ExecuteAsync(
    "INSERT INTO users (name, age) VALUES (@name, @age)",
    DbParam.Create("name", "John"),
    DbParam.Create("age", 30)
);
Console.WriteLine($"Inserted {affected} rows");

// UPDATE
await db.ExecuteAsync(
    "UPDATE users SET age = @age WHERE name = @name",
    DbParam.Create("age", 31),
    DbParam.Create("name", "John")
);

// DELETE
await db.ExecuteAsync(
    "DELETE FROM users WHERE age < @age",
    DbParam.Create("age", 18)
);

4. Transactions

await db.ExecuteInTransactionAsync(async (txn) =>
{
    // Insert first record
    await txn.ExecuteAsync(
        "INSERT INTO users (name, age) VALUES (@name, @age)",
        DbParam.Create("name", "Alice"),
        DbParam.Create("age", 25)
    );
    
    // Insert second record
    await txn.ExecuteAsync(
        "INSERT INTO users (name, age) VALUES (@name, @age)",
        DbParam.Create("name", "Bob"),
        DbParam.Create("age", 28)
    );
    
    // If any operation fails, entire transaction is rolled back
});

5. ORM Operations

// Define entity and mapping
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

public class UserMapping : EntityMappingBase<User>
{
    public override string TableName => "users";
    public override string PrimaryKeyColumn => "id";
    
    public override User MapFromRow(DbRow row)
    {
        return new User
        {
            Id = row.Get<int>("id"),
            Name = row.Get<string>("name"),
            Age = row.Get<int>("age")
        };
    }
    
    public override Dictionary<string, object?> GetValues(User entity)
    {
        return new()
        {
            { "name", entity.Name },
            { "age", entity.Age }
        };
    }
    
    public override object? GetPrimaryKeyValue(User entity) => entity.Id;
}

// Use the mapping
var mapping = new UserMapping();

// Get all users
var users = await db.GetAllAsync(mapping);

// Get user by ID
var user = await db.GetByIdAsync(mapping, 1);

// Insert entity
var newUser = new User { Name = "Charlie", Age = 35 };
await db.InsertAsync(newUser, mapping);

// Update entity
user.Age = 36;
await db.UpdateAsync(user, mapping);

// Delete entity
await db.DeleteAsync(user, mapping);

6. Batch Operations

// Bulk insert multiple entities
var users = new List<User>
{
    new User { Name = "Alice", Age = 25 },
    new User { Name = "Bob", Age = 28 },
    new User { Name = "Charlie", Age = 35 }
};
var inserted = await db.BulkInsertAsync(users, mapping);
Console.WriteLine($"Inserted {inserted} users");

// Execute batch of statements
var affected = await db.ExecuteBatchAsync(new[]
{
    "UPDATE users SET active = 1 WHERE age > 18",
    "DELETE FROM users WHERE deleted_at IS NOT NULL"
});

7. Pagination

var page = await db.QueryPagedAsync(
    "SELECT * FROM users ORDER BY id",
    pageNumber: 1,
    pageSize: 10
);

Console.WriteLine($"Page {page.PageNumber} of {page.TotalPages}");
Console.WriteLine($"Total items: {page.TotalCount}");
Console.WriteLine($"Items on this page: {page.Items.Count}");
Console.WriteLine($"Has next page: {page.HasNextPage}");
Console.WriteLine($"Has previous page: {page.HasPreviousPage}");

// Get next page
if (page.HasNextPage)
{
    var nextPage = await db.QueryPagedAsync(
        "SELECT * FROM users ORDER BY id",
        pageNumber: page.PageNumber + 1,
        pageSize: 10
    );
}

8. Aggregation Queries

// Count total records
var total = await db.CountAsync("users");

// Count with condition
var adults = await db.CountAsync(
    "users",
    "age >= @age",
    DbParam.Create("age", 18)
);

// Average
var avgAge = await db.AverageAsync<double>(
    "users",
    "age"
);

// Sum
var totalAge = await db.SumAsync<int>(
    "users",
    "age"
);

// Max
var maxAge = await db.MaxAsync<int?>("users", "age");

// Min
var minAge = await db.MinAsync<int?>("users", "age");

9. Query Builder (Fluent API)

var results = await db.CreateQueryBuilder<User>("users")
    .Where("age", ">", 18)
    .And("active", "=", 1)
    .OrderBy("name", ascending: true)
    .ThenBy("age", ascending: false)
    .Limit(10)
    .ExecuteAsync(db);

foreach (var row in results)
{
    Console.WriteLine($"{row.Get<string>("name")}: {row.Get<int>("age")}");
}

10. Query Caching

// Cache query results
var results = await db.QueryCachedAsync(
    cacheKey: "active_users",
    sql: "SELECT * FROM users WHERE active = 1",
    expiration: TimeSpan.FromMinutes(5)
);

// Later: Check cache statistics
var stats = db.QueryCache.GetStatistics();
Console.WriteLine($"Cache entries: {stats.EntryCount}");
Console.WriteLine($"Hit rate: {stats.HitRate:F2}%");

// Invalidate specific cache
await db.InvalidateCacheAsync("active_users");

// Invalidate all caches matching pattern
await db.InvalidateCacheAsync("active_*");

11. Upsert Operations

var user = new User { Name = "John", Age = 30 };

// First upsert (inserts)
await db.UpsertAsync(user, mapping, "name");

// Update the user
user.Age = 31;

// Second upsert (updates)
await db.UpsertAsync(user, mapping, "name");

12. Schema Management

// Get all table names
var tables = await db.GetTableNamesAsync();
Console.WriteLine($"Tables: {string.Join(", ", tables)}");

// Get column names for a table
var columns = await db.GetTableColumnsAsync("users");
Console.WriteLine($"Columns: {string.Join(", ", columns)}");

// Check if table exists
var exists = await db.TableExistsAsync("users");

// Check if column exists
var hasName = await db.ColumnExistsAsync("users", "name");

// Check if record exists
var userExists = await db.ExistsAsync(
    "users",
    "id = @id",
    DbParam.Create("id", 1)
);

📚 Complete API Reference

TursoDbClient - Core Methods

Query Execution:

• QueryAsync(sql, params) → List<DbRow> - Execute SELECT
• ExecuteAsync(sql, params) → int - Execute INSERT/UPDATE/DELETE
• ExecuteScalarAsync<T>(sql, params) → T? - Get single value

ORM Methods:

• InsertAsync<T>(entity, mapping) - Insert entity
• UpdateAsync<T>(entity, mapping) - Update entity
• DeleteAsync<T>(entity, mapping) - Delete entity
• GetAllAsync<T>(mapping) → List<T> - Get all entities
• GetByIdAsync<T>(mapping, id) → T? - Get entity by ID

Transaction Methods:

• ExecuteInTransactionAsync(callback) - Execute in transaction
• BeginTransactionAsync() → TursoDbTransaction - Start transaction

Batch Operations:

• BulkInsertAsync<T>(items, mapping) → int - Bulk insert
• ExecuteBatchAsync(statements) → int - Execute multiple statements
• UpsertAsync<T>(entity, mapping, uniqueColumn) - Upsert entity

Pagination & Caching:

• QueryPagedAsync(sql, pageNumber, pageSize, params) → PagedResult - Paginated query
• QueryCachedAsync(cacheKey, sql, expiration, params) → List<DbRow> - Cached query
• InvalidateCacheAsync(pattern) - Clear cache entries

Aggregation Methods:

• CountAsync(table, condition?, params) → long - Count records
• SumAsync<T>(table, column, condition?, params) → T? - Sum
• AverageAsync<T>(table, column, condition?, params) → T? - Average
• MinAsync<T>(table, column) → T? - Minimum
• MaxAsync<T>(table, column) → T? - Maximum

Schema Methods:

• GetTableNamesAsync() → List<string> - List tables
• GetTableColumnsAsync(tableName) → List<string> - List columns
• TableExistsAsync(tableName) → bool - Check table exists
• ColumnExistsAsync(tableName, columnName) → bool - Check column exists
• ExistsAsync(table, condition, params) → bool - Check record exists

Query Builder:

• CreateQueryBuilder<T>(tableName) → IQueryBuilder<T> - Create builder

DbRow - Row Access

Value Access:

• Get<T>(columnName) → T - Get typed value
• TryGet<T>(columnName, out T value) → bool - Safe typed access
• Get<T?>(columnName) → T? - Get nullable value

Validation:

• HasColumn(columnName) → bool - Check column exists

Conversion:

• ToExpando() → ExpandoObject - Convert to dynamic
• ToDictionary() → Dictionary<string, object?> - Convert to dict

DbParam - Parameters

Creation:

• Create(name, value) → DbParam - Single parameter
• CreateBatch(params DbParam[]) → DbParam[] - Multiple parameters
• CreateFromTuples((name, value)[], ...) → DbParam[] - From tuples

🏗️ Architecture

TursoDb.Core
├── TursoDbClient (Main API)
│   ├── DbRow (Row Container)
│   ├── DbParam (Parameters)
│   ├── TursoDbTransaction (Transactions)
│   ├── IEntityMapping (ORM)
│   ├── IQueryBuilder (Query Builder)
│   ├── IQueryCache (Caching)
│   ├── IRetryPolicy (Resilience)
│   └── Models
│       ├── PagedResult
│       ├── ColumnInfo
│       └── AuditLog

🧪 Examples

Full working examples are available in the TursoTodoApp project:

• ComprehensiveExamples.cs - Demonstrates all 12 features with Bogus mock data
• Interactive usage patterns
• Best practices guide

🔐 Security

• ✅ Parameterized Queries - SQL injection prevention
• ✅ Type-Safe Operations - Compile-time checking
• ✅ Error Handling - Detailed error messages without sensitive data
• ✅ Nullable Reference Types - Null-safety at compile-time

🛠️ Requirements

• .NET 10 or later
• Turso account with database connection string
• Auth token for database access

📝 License

Licensed under the MIT License - see LICENSE file for details.

🤝 Contributing

Contributions are welcome! Areas for contribution:

• Additional features and extensions
• Performance optimizations
• Bug fixes and improvements
• Documentation and examples

📞 Support

For issues, questions, or feature requests, please open an issue on GitHub: GitHub Issues

🙏 Acknowledgments

Built with:

• Turso - Database platform
• libSQL - SQL engine
• .NET 10 - Runtime

Production ready. Fully documented. Type-safe. Performant.