DuckDB.OrmLite
1.3.0
See the version list below for details.
dotnet add package DuckDB.OrmLite --version 1.3.0
NuGet\Install-Package DuckDB.OrmLite -Version 1.3.0
<PackageReference Include="DuckDB.OrmLite" Version="1.3.0" />
<PackageVersion Include="DuckDB.OrmLite" Version="1.3.0" />
<PackageReference Include="DuckDB.OrmLite" />
paket add DuckDB.OrmLite --version 1.3.0
#r "nuget: DuckDB.OrmLite, 1.3.0"
#:package DuckDB.OrmLite@1.3.0
#addin nuget:?package=DuckDB.OrmLite&version=1.3.0
#tool nuget:?package=DuckDB.OrmLite&version=1.3.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)
- Multi-database support - Query across multiple DuckDB files transparently
✅ Complete Type Support
- All .NET primitive types
- DateTime, DateTimeOffset, TimeSpan
- Decimal and all integer types
- Guid (UUID)
- byte[] (BLOB)
- Nullable types
✅ Production Ready
- 90 comprehensive tests (100% passing)
- Optimized for DuckDB 1.3.2
- SQL injection prevention
- Robust error handling
- Connection timeout/retry for multi-process scenarios
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
Multi-Database Support
Query across multiple DuckDB database files transparently - perfect for time-series data, archival scenarios, or partitioned datasets.
Basic Configuration
var factory = new DuckDbOrmLiteConnectionFactory("Data Source=main.db")
.WithAdditionalDatabases("archive_2024.db", "archive_2023.db")
.WithMultiDatabaseTables("CmcPrice", "FiatPrice");
// Queries automatically span all databases
using var db = factory.Open();
var allPrices = db.Select<CmcPrice>(x => x.Symbol == "BTC");
// Returns data from main.db, archive_2024.db, and archive_2023.db
// Writes go to main database only
using var writeDb = factory.OpenForWrite();
writeDb.Insert(new CmcPrice { Date = DateTime.Today, Symbol = "ETH", Price = 3500 });
Type-Safe Configuration
// Generic factory - automatically configures multi-database support for CmcPrice
var factory = new DuckDbOrmLiteConnectionFactory<CmcPrice>("Data Source=main.db")
.WithAdditionalDatabases("archive.db");
// Or explicit configuration
var factory = new DuckDbOrmLiteConnectionFactory("Data Source=main.db")
.WithAdditionalDatabases("archive.db")
.WithMultiDatabaseTable<CmcPrice>();
Use Case: Time-Series Data with Daily Updates
// Setup: Current year + archives
var factory = new DuckDbOrmLiteConnectionFactory("Data Source=prices_2025.db")
.WithAdditionalDatabases("prices_2024.db", "prices_2023.db", "prices_2022.db")
.WithMultiDatabaseTables("CmcPrice");
// Read: Query spans all years transparently
using (var db = factory.Open())
{
// Get Bitcoin prices for last 2 years
var btcPrices = db.Select<CmcPrice>(x =>
x.Symbol == "BTC" &&
x.Date >= DateTime.Today.AddYears(-2));
// Aggregations work across all databases
var avgPrice = db.Scalar<decimal>(
db.From<CmcPrice>()
.Where(x => x.Symbol == "BTC")
.Select(x => Sql.Avg(x.Price))
);
}
// Write: New data goes to current year database
using (var writeDb = factory.OpenForWrite())
{
writeDb.Insert(new CmcPrice
{
Date = DateTime.Today,
Symbol = "BTC",
Price = 95000
});
}
How It Works
- Automatic ATTACH: Additional databases are attached on connection open
- Unified Views: Creates
{TableName}_Unified
views withUNION ALL
across all databases - Query Routing: Read queries automatically use unified views; writes go directly to main database
- Zero Code Changes: Application code using
db.Select<T>()
works unchanged
Multi-Database Features
✅ All OrmLite operations work across databases:
- SELECT with WHERE, ORDER BY, LIMIT
- Aggregations (COUNT, SUM, AVG, MAX, MIN)
- JOINs (multi-db table with single-db table)
- Complex LINQ expressions
- Async operations
✅ Smart table detection:
- Only creates views for tables that exist in databases
- Handles tables existing in subset of databases
✅ Flexible configuration:
- Mix multi-db and single-db tables in same factory
- Toggle auto-configuration with
.WithAutoConfigureViews(false)
Multi-Database Limitations
⚠️ Important considerations:
- Schema consistency: Tables must have identical schemas across all databases
- No cross-database transactions: Transactions only work with
OpenForWrite()
(single database) - Read-only archives: Additional databases should be read-only for data consistency
- No automatic deduplication:
UNION ALL
doesn't deduplicate - ensure partitioning prevents duplicates
Connection Timeout and Retry
DuckDB uses an exclusive file lock when opening a database. If another process has the database open, subsequent attempts will fail immediately. The timeout feature provides automatic retry with exponential backoff:
// Default behavior - fail immediately if database is locked
using var db = factory.Open();
// Retry for up to 30 seconds with exponential backoff
using var db = factory.Open(TimeSpan.FromSeconds(30));
// Also available for write connections
using var writeDb = factory.OpenForWrite(TimeSpan.FromSeconds(30));
// Customize retry delays (useful for high-contention scenarios)
var factory = new DuckDbOrmLiteConnectionFactory("Data Source=myapp.db")
{
RetryDelayMs = 100, // Start with 100ms delays (default: 50ms)
MaxRetryDelayMs = 5000 // Cap at 5 seconds (default: 1000ms)
};
How it works:
- Detects lock errors: "Could not set lock", "database is locked", "IO Error"
- Exponential backoff: starts at
RetryDelayMs
, doubles each retry, caps atMaxRetryDelayMs
- Random jitter (up to 25% of delay) to avoid thundering herd
- Throws
TimeoutException
if lock not acquired within timeout
Use cases:
- Multi-process scenarios where processes may temporarily lock the database
- Automated scripts that need to wait for other processes to finish
- Services that need resilient database access
Concurrency notes:
- Within same process: Multiple threads can read/write concurrently (MVCC)
- Across processes: Only one process can open at a time (exclusive lock)
- Use timeout feature for cross-process coordination
Best Practices
Recommended partitioning strategies:
- Time-based: One database per year/month
- Categorical: Separate databases by data type or category
- Archival: Current database + historical archives
Workflow pattern:
// Daily process: Keep current database for writes
using (var writeDb = factory.OpenForWrite())
{
writeDb.InsertAll(todaysData);
}
// Analytics: Query across all time periods
using (var readDb = factory.Open())
{
var historicalTrends = readDb.Select<CmcPrice>(x =>
x.Date >= new DateTime(2020, 1, 1));
}
// Year-end: Rotate current to archive
// 1. Copy prices_2025.db to archive location
// 2. Update factory configuration to include new archive
// 3. Create fresh prices_2026.db for new year
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)
- 17 async/await tests
- 18 multi-database tests
- 11 concurrency tests (thread safety, optimistic concurrency)
- 4 generic factory tests
- 90 total tests (100% passing)
- 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
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 was computed. net9.0-android was computed. net9.0-browser was computed. net9.0-ios was computed. net9.0-maccatalyst was computed. net9.0-macos was computed. net9.0-tvos was computed. net9.0-windows was computed. net10.0 was computed. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
-
net8.0
- DuckDB.NET.Data.Full (>= 1.3.0)
- ServiceStack.OrmLite (>= 8.5.2)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
v1.3.0: Connection timeout/retry with exponential backoff for multi-process scenarios. Generic factory DuckDbOrmLiteConnectionFactory<T> for type-safe config. CRITICAL FIX: BeforeExecFilter now isolated to DuckDB only. 90 tests, 100% passing.