UDataset.DuckDB 0.14.2

UDataset.DuckDB

UDataset.DuckDB is a DuckDB implementation for UDataset cross-database data access framework. It provides DuckDB-specific implementations of core UDataset interfaces, enabling seamless data operations with DuckDB databases.

Features

• In-Process Database: DuckDB runs as an in-process database, eliminating need for a separate server setup
• Column-Oriented Storage: Optimized for analytical workloads (OLAP) with columnar storage
• File-Based or In-Memory: Supports both persistent file databases (*.db) and in-memory databases
• High-Performance Analytics: Optimized for complex analytical queries and data warehousing
• JSON Support: Native JSON data type support with efficient querying capabilities
• Full UDataset Features: Complete implementation of all UDataset features including CRUD, transactions, and schema management
• SQL Transformation: Automatic transformation for DuckDB-specific SQL syntax
• Connection Management: Efficient connection pooling and management

Installation

dotnet add package UDataset.Core
dotnet add package UDataset.DuckDB

Quick Start

1. Register Provider

using UDataset.Core;
using UDataset.DuckDB;

// Register DuckDB provider at application startup
DuckDbBootstrapper.Register();

2. Create Connection

// File-based database (persistent)
string connectionString = "DataSource=C:\\Data\\my_database.db";
IConnection connection = ProviderFactory.CreateConnection("DuckDB", connectionString);

// In-memory database (data lost after connection closes)
string memoryConnectionString = "DataSource=:memory:";
IConnection memoryConnection = ProviderFactory.CreateConnection("DuckDB", memoryConnectionString);

3. Basic Operations

// Create table
var schemaManager = ProviderFactory.CreateSchemaManager("DuckDB", connectionString);
var table = new Table("sales_data")
    .WithAutoIncrementPK("id")
    .WithoutVersionControl();
table.Attributes.Add(new Column("product_name", DataType.String) { Properties = { [ColumnProperty.Length] = 200 } });
table.Attributes.Add(new Column("amount", DataType.Decimal));
table.Attributes.Add(new Column("sale_date", DataType.DateTime));
schemaManager.Create(table);

// Insert data
var sale = new Row("sales_data")
{
    ["product_name"] = "Product A",
    ["amount"] = 99.99m,
    ["sale_date"] = DateTime.UtcNow
};
await connection.Create(sale);

// Analytical query (DuckDB's strength)
var query = new QueryExpression("sales_data");
query.AddSelect("product_name", "Product");
query.AddSelect("SUM(amount)", "TotalSales");
query.AddSelect("AVG(amount)", "AverageSale");
query.GroupBy = new List<string> { "product_name" };
query.AddOrderBy("TotalSales", SortDirection.Descending);
var results = await connection.Query(query);

Use Cases

DuckDB is ideal for:

• Data Analytics: Processing and analyzing large datasets with complex aggregations
• Data Warehousing: Building analytical data warehouses with columnar storage
• Local Analysis: Running analytical queries on CSV, Parquet, and JSON files
• Embedded Applications: Applications that need database functionality without external dependencies
• ETL Pipelines: Extract, Transform, Load operations with high-performance batch processing
• Prototyping: Quick prototyping and data exploration with zero setup

Compatibility

• DuckDB 1.5.x (DuckDB.NET.Data.Full 1.5.0)
• .NET 8.0
• Windows, Linux, macOS
• File-based and in-memory databases

Performance Characteristics

• Analytical Queries: Optimized for SELECT queries with aggregations, GROUP BY, and joins
• Batch Inserts: Efficient bulk insert operations with automatic parameter batching (max 10,000 parameters)
• Columnar Storage: Reads only required columns, reducing I/O
• Vectorized Execution: Leverages CPU vectorization for faster query processing

Limitations

• DuckDB is not optimized for transactional (OLTP) workloads
• Concurrent writes have limitations compared to traditional databases
• Not suitable for applications requiring high write throughput
• File-based databases are single-user by default

Requirements

• .NET 8.0
• DuckDB.NET.Data.Full 1.5.0

Dependencies

• UDataset.Core
• DuckDB.NET.Data.Full 1.5.0
• Dapper 2.1.66
• System.Data.Common
• System.Text.Json

Version History

0.14.0 (2026-08-06)

• WhenNotExists dispatched to conditional check path
• Full regression passed: 8 functional groups, 0 failures

0.11.0 (2026-04-16)

• Added DM (Dameng) and KingbaseES database support to UDataset framework
• Note: KingbaseES support is experimental and not recommended for production use

For general usage examples and advanced features, please refer to UDataset.Core documentation.