Mapped Appender

The Mapped Appender provides a type-safe way to bulk load data from .NET objects into DuckDB tables. It uses DuckDBAppenderMap to define mappings between your object properties and table columns, with automatic type validation.

Overview

While the standard DuckDBAppender provides efficient bulk loading, it requires manual column ordering and type management. The Mapped Appender (DuckDBMappedAppender<T, TMap>) adds:

  • Type Safety: Compile-time type checking for property mappings
  • Automatic Validation: Runtime verification that mapped types match table schema
  • Simplified API: Map properties declaratively instead of manual value appending
  • Better Maintainability: Clear mapping definitions separate from business logic

Basic Usage

To use a mapped appender, you need to:

  1. Define your data class
  2. Create an AppenderMap class that defines how properties map to columns
  3. Use CreateAppender<T, TMap>() to create a type-safe appender
  4. Call AppendRecords() to insert your data

Example

using DuckDB.NET.Data;
using DuckDB.NET.Data.Mapping;
using System;

// Define your data class
public class Person
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public float Height { get; set; }
    public DateTime BirthDate { get; set; }
}

// Define the mapping
public class PersonMap : DuckDBAppenderMap<Person>
{
    public PersonMap()
    {
        Map(p => p.Id);        // Maps to column 0
        Map(p => p.Name);      // Maps to column 1
        Map(p => p.Height);    // Maps to column 2
        Map(p => p.BirthDate); // Maps to column 3
    }
}

// Use the mapped appender
using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();

// Create table
using (var command = connection.CreateCommand())
{
    command.CommandText = "CREATE TABLE person(id INTEGER, name VARCHAR, height REAL, birth_date TIMESTAMP);";
    command.ExecuteNonQuery();
}

// Prepare data
var people = new[]
{
    new Person { Id = 1, Name = "Alice", Height = 1.65f, BirthDate = new DateTime(1990, 1, 15) },
    new Person { Id = 2, Name = "Bob", Height = 1.80f, BirthDate = new DateTime(1985, 5, 20) },
    new Person { Id = 3, Name = "Charlie", Height = 1.75f, BirthDate = new DateTime(1992, 8, 30) }
};

// Insert data using mapped appender
using (var appender = connection.CreateAppender<Person, PersonMap>("person"))
{
    appender.AppendRecords(people);
}

// Verify data was inserted
using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT COUNT(*) FROM person";
    var count = command.ExecuteScalar();
    Console.WriteLine($"Inserted {count} records");
}

Creating AppenderMap Classes

An AppenderMap class inherits from DuckDBAppenderMap<T> and defines the mapping in its constructor using these methods:

Map(Func<T, TProperty> getter)

Maps a property to the next column in sequence. The lambda expression extracts the property value from your object.

public class PersonMap : DuckDBAppenderMap<Person>
{
    public PersonMap()
    {
        Map(p => p.Id);        // Maps to column 0
        Map(p => p.Name);      // Maps to column 1
        Map(p => p.Height);    // Maps to column 2
        Map(p => p.BirthDate); // Maps to column 3
    }
}
Important

Mappings must be defined in the exact same order as the table columns. The first Map() call maps to the first column, the second to the second column, etc.

DefaultValue()

Uses the column's default value (defined in the table schema) for the next column.

public class PersonMap : DuckDBAppenderMap<Person>
{
    public PersonMap()
    {
        Map(p => p.Id);
        Map(p => p.Name);
        DefaultValue();  // Uses table's default for column 2
    }
}

NullValue()

Inserts a NULL value for the next column.

public class PersonMap : DuckDBAppenderMap<Person>
{
    public PersonMap()
    {
        Map(p => p.Id);
        Map(p => p.Name);
        NullValue();  // Inserts NULL for column 2
    }
}

Type Validation

The mapped appender validates that your mapped .NET types match the DuckDB column types when the appender is created. This catches type mismatches early, before any data is written.

Supported Type Mappings

The following .NET types are supported for mapping:

.NET Type DuckDB Type
bool BOOLEAN
sbyte TINYINT
short SMALLINT
int INTEGER
long BIGINT
byte UTINYINT
ushort USMALLINT
uint UINTEGER
ulong UBIGINT
float REAL/FLOAT
double DOUBLE
decimal DECIMAL
string VARCHAR/TEXT
DateTime TIMESTAMP
DateTimeOffset TIMESTAMPTZ
TimeSpan INTERVAL
Guid UUID
BigInteger HUGEINT
DateOnly (.NET 6+) DATE
TimeOnly (.NET 6+) TIME
DuckDBDateOnly DATE
DuckDBTimeOnly TIME

Nullable versions of these types (e.g., int?, DateTime?) are also supported and will write NULL when the value is null.

Type Mismatch Example

// Table schema: CREATE TABLE test(id INTEGER, value REAL, date TIMESTAMP);

public class BadMap : DuckDBAppenderMap<MyData>
{
    public BadMap()
    {
        Map(d => d.Id);        // int -> INTEGER ✓
        Map(d => d.Date);      // DateTime -> REAL ✗ TYPE MISMATCH!
        Map(d => d.Value);     // float -> TIMESTAMP ✗ TYPE MISMATCH!
    }
}

// This will throw InvalidOperationException when creating the appender
var appender = connection.CreateAppender<MyData, BadMap>("test");

The error message will indicate exactly which column has the type mismatch:

Type mismatch at column index 1: Mapped type is DateTime (expected DuckDB type: Timestamp) but actual column type is Real

API Reference

CreateAppender Methods

The DuckDBConnection class provides three overloads:

// Simple table name
public DuckDBMappedAppender<T, TMap> CreateAppender<T, TMap>(string table)
    where TMap : DuckDBAppenderMap<T>, new()

// Schema and table
public DuckDBMappedAppender<T, TMap> CreateAppender<T, TMap>(string? schema, string table)
    where TMap : DuckDBAppenderMap<T>, new()

// Catalog, schema, and table
public DuckDBMappedAppender<T, TMap> CreateAppender<T, TMap>(string? catalog, string? schema, string table)
    where TMap : DuckDBAppenderMap<T>, new()

DuckDBMappedAppender<T, TMap> Methods

AppendRecords(IEnumerable<T> records)

Appends multiple records to the table.

var people = new[]
{
    new Person { Id = 1, Name = "Alice" },
    new Person { Id = 2, Name = "Bob" }
};

using var appender = connection.CreateAppender<Person, PersonMap>("people");
appender.AppendRecords(people);

Close()

Closes the appender and flushes any remaining data to the database. This is called automatically when disposing.

appender.Close();

Dispose()

Disposes the appender and releases resources. Always dispose appenders to ensure data is flushed.

using (var appender = connection.CreateAppender<Person, PersonMap>("people"))
{
    appender.AppendRecords(records);
} // Automatically disposed here

Complete Example with Default and Null Values

using DuckDB.NET.Data;
using DuckDB.NET.Data.Mapping;
using System;

// Data class with only some properties
public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
}

// Map with DefaultValue and NullValue
public class EmployeeMap : DuckDBAppenderMap<Employee>
{
    public EmployeeMap()
    {
        Map(e => e.Id);        // Column 0: id (INTEGER)
        Map(e => e.Name);      // Column 1: name (VARCHAR)
        DefaultValue();        // Column 2: department (VARCHAR) - use table default
        NullValue();           // Column 3: manager_id (INTEGER) - insert NULL
    }
}

using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();

// Create table with default value
using (var command = connection.CreateCommand())
{
    command.CommandText = @"
        CREATE TABLE employees(
            id INTEGER, 
            name VARCHAR, 
            department VARCHAR DEFAULT 'General',
            manager_id INTEGER
        );";
    command.ExecuteNonQuery();
}

// Insert data
var employees = new[]
{
    new Employee { Id = 1, Name = "Alice" },
    new Employee { Id = 2, Name = "Bob" },
    new Employee { Id = 3, Name = "Charlie" }
};

using (var appender = connection.CreateAppender<Employee, EmployeeMap>("employees"))
{
    appender.AppendRecords(employees);
}

// Query results
using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT id, name, department, manager_id FROM employees ORDER BY id";
    using var reader = command.ExecuteReader();
    
    Console.WriteLine("Id | Name    | Department | Manager");
    Console.WriteLine("---|---------|------------|--------");
    
    while (reader.Read())
    {
        var id = reader.GetInt32(0);
        var name = reader.GetString(1);
        var department = reader.GetString(2);
        var managerId = reader.IsDBNull(3) ? "NULL" : reader.GetInt32(3).ToString();
        
        Console.WriteLine($"{id,2} | {name,-7} | {department,-10} | {managerId}");
    }
}

// Output:
// Id | Name    | Department | Manager
// ---|---------|------------|--------
//  1 | Alice   | General    | NULL
//  2 | Bob     | General    | NULL
//  3 | Charlie | General    | NULL

Performance Considerations

  • The mapped appender has a tiny overhead compared to the raw appender due to type validation and property accessor invocation
  • Type validation occurs once when the appender is created, not for each record
  • For maximum performance with tens of millions of rows, consider the raw DuckDBAppender
  • For most use cases, the performance difference is negligible and the type safety is worth it
Edit this page