DuckDB.OrmLite 1.1.0

DuckDB.OrmLite

DuckDB provider for ServiceStack.OrmLite - A fast, simple, and typed ORM for .NET.

Disclaimer

This is an independent, community-maintained provider for ServiceStack.OrmLite. It is not officially maintained or endorsed by ServiceStack.

About

This package enables ServiceStack.OrmLite to work with DuckDB, an in-process analytical database management system. DuckDB excels at:

• Fast analytical queries - Columnar storage and vectorized execution
• In-process - No separate server process required
• SQL standard - Familiar SQL syntax with PostgreSQL compatibility
• Data processing - Native support for Parquet, CSV, JSON
• OLAP workloads - Aggregations, window functions, complex analytics

Features

✅ Full OrmLite Support

• Complete CRUD operations (sync + async)
• LINQ query expressions
• Transactions
• AutoIncrement with sequences and INSERT...RETURNING
• Complex queries (JOINs, aggregations, subqueries)
• Parameterized queries
• Batch operations
• Async/await support (pseudo-async)

✅ Complete Type Support

• All .NET primitive types
• DateTime, DateTimeOffset, TimeSpan
• Decimal and all integer types
• Guid (UUID)
• byte[] (BLOB)
• Nullable types

✅ Production Ready

• 57 comprehensive tests (100% passing)
• Optimized for DuckDB 1.3.2
• SQL injection prevention
• Robust error handling

Installation

dotnet add package DuckDB.OrmLite

Quick Start

using ServiceStack.OrmLite;
using DuckDB.OrmLite;

// Create connection factory
var dbFactory = new DuckDbOrmLiteConnectionFactory("Data Source=myapp.db");

// Define your models
public class Customer
{
    [AutoIncrement]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    public string Email { get; set; }

    public decimal CreditLimit { get; set; }

    public DateTime RegisteredAt { get; set; }
}

// Use OrmLite
using var db = dbFactory.Open();

// Create table
db.CreateTable<Customer>();

// Insert
var customer = new Customer
{
    Name = "Acme Corp",
    Email = "contact@acme.com",
    CreditLimit = 50000,
    RegisteredAt = DateTime.UtcNow
};
db.Insert(customer);

// Query with LINQ
var highValueCustomers = db.Select<Customer>(c =>
    c.CreditLimit > 10000 && c.RegisteredAt > DateTime.UtcNow.AddMonths(-6)
);

// Update
customer.CreditLimit = 75000;
db.Update(customer);

// Aggregations
var totalCredit = db.Scalar<decimal>(
    db.From<Customer>().Select(c => Sql.Sum(c.CreditLimit))
);

// JOINs
var orders = db.Select(db.From<Order>()
    .Join<Customer>((o, c) => o.CustomerId == c.Id)
    .Where<Customer>(c => c.Name == "Acme Corp")
);

// Transactions
using (var trans = db.OpenTransaction())
{
    db.Insert(customer);
    db.Insert(order);
    trans.Commit();
}

Async/Await Support

DuckDB.OrmLite supports async/await for all operations through ServiceStack.OrmLite's built-in async APIs.

Important: Since DuckDB.NET.Data does not provide native async I/O operations, these are pseudo-async implementations (similar to SQLite). Operations will block the calling thread but provide API compatibility with other OrmLite providers.

Async Examples

using var db = dbFactory.Open();

// SELECT operations
var customers = await db.SelectAsync<Customer>(c => c.CreditLimit > 10000);
var customer = await db.SingleAsync<Customer>(c => c.Id == 1);
var count = await db.CountAsync<Customer>();

// INSERT operations
await db.InsertAsync(new Customer { Name = "New Corp", ... });
await db.InsertAllAsync(customers);

// UPDATE operations
customer.CreditLimit = 75000;
await db.UpdateAsync(customer);
await db.UpdateAllAsync(customers);

// DELETE operations
await db.DeleteAsync<Customer>(c => c.CreditLimit < 1000);
await db.DeleteByIdAsync<Customer>(customerId);

// SQL operations
var results = await db.SqlListAsync<Customer>("SELECT * FROM Customer WHERE CreditLimit > 50000");
var total = await db.SqlScalarAsync<decimal>("SELECT SUM(CreditLimit) FROM Customer");

// Transactions work with async too
using (var trans = db.OpenTransaction())
{
    await db.InsertAsync(customer);
    await db.InsertAsync(order);
    trans.Commit();
}

Async Limitations

⚠️ Not true async I/O: Operations still block the calling thread internally

• Suitable for maintaining consistent async/await code style
• API-compatible with other OrmLite providers
• Not suitable for high-concurrency scenarios expecting true async I/O benefits
• Consider using synchronous methods if async benefits are not needed

Use Cases

Data Analysis & Reporting

// DuckDB excels at analytical queries
var salesByMonth = db.SqlList<dynamic>(@"
    SELECT
        DATE_TRUNC('month', OrderDate) as Month,
        COUNT(*) as OrderCount,
        SUM(TotalAmount) as Revenue
    FROM Orders
    GROUP BY Month
    ORDER BY Month DESC
");

ETL & Data Processing

// Efficient bulk operations
db.InsertAll(largeDataset);

// Process with SQL
db.ExecuteSql(@"
    INSERT INTO ProcessedOrders
    SELECT * FROM Orders
    WHERE Status = 'completed'
    AND OrderDate > CURRENT_DATE - INTERVAL '30 days'
");

In-Memory Analytics

// Use in-memory database for fast processing
var dbFactory = new DuckDbOrmLiteConnectionFactory("Data Source=:memory:");

Configuration

Connection Strings

File-based database:

"Data Source=myapp.db"

In-memory database:

"Data Source=:memory:"

Read-only mode:

"Data Source=myapp.db;Read Only=true"

Required Setup

DuckDB requires parameter handling that differs slightly from other databases. The provider includes a BeforeExecFilter that handles this automatically:

// Automatically configured by DuckDbOrmLiteConnectionFactory
// Handles:
// - Parameter name conversion ($ prefix handling)
// - 1-based positional parameter indexing
// - DbType.Currency → DbType.Decimal conversion

Dependencies

This package depends on:

• ServiceStack.OrmLite (>= 8.5.2) - The ORM framework
• DuckDB.NET.Data.Full (>= 1.3.0) - .NET bindings for DuckDB

Both dependencies are automatically installed when you add this package.

Compatibility

• .NET: 8.0+
• DuckDB: 1.3.2+
• ServiceStack.OrmLite: 8.5.2+

Performance

DuckDB is optimized for analytical workloads:

• Fast aggregations - Columnar storage enables efficient aggregations
• Vectorized execution - SIMD optimizations for bulk operations
• Memory efficient - Optimized for large datasets
• Zero-copy reads - Direct memory access where possible

Limitations

• TimeSpan: Limited to ~24 hours when using HH:MM:SS format
• Concurrent writes: DuckDB uses single-writer model

Documentation

• DuckDB Official Documentation
• ServiceStack.OrmLite Documentation
• Development Documentation - Implementation details and history

Testing

# Run all tests
dotnet test

# Run specific test category
dotnet test --filter "FullyQualifiedName~AdvancedFeatureTests"

Test coverage:

• 25 core OrmLite functionality tests
• 15 advanced feature tests (JOINs, aggregations, edge cases)
• Production-ready error handling and SQL injection prevention

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.

License

This project is licensed under the MIT License - see the LICENSE.md file for details.

Acknowledgments

• ServiceStack.OrmLite - The excellent ORM framework
• DuckDB - The fast in-process analytical database
• DuckDB.NET - .NET bindings for DuckDB

Support

• Issues: GitHub Issues
• ServiceStack OrmLite: ServiceStack Support
• DuckDB: DuckDB Discord