DuckDB.OrmLite 1.0.1

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
• LINQ query expressions
• Transactions
• AutoIncrement with sequences and INSERT...RETURNING
• Complex queries (JOINs, aggregations, subqueries)
• Parameterized queries
• Batch operations

✅ Complete Type Support

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

✅ Production Ready

• 40 comprehensive tests (95% 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();
}

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