DuckDB.NET 2.0 Preview 1: What Changes When You Upgrade

Introduction

The first preview of DuckDB.NET 2.0 is on NuGet. It runs on DuckDB 2.0.0, which came out on October 21, and as a major version it comes with breaking changes. A lot of what changed is behaviour that was wrong or surprising in 1.x and could not be fixed without breaking someone’s code. This post walks through those changes and the new features, so you know what to look for before you upgrade.

You can try it with:

dotnet add package DuckDB.NET.Data.Full --prerelease

Upgrade DuckDB.NET.Data and DuckDB.NET.Bindings together. Data 2.0 needs Bindings 2.0, and the 1.x Data package on top of the 2.0 bindings throws TypeLoadException the first time it appends a string.

The release notes on NuGet list every breaking change. They are long, and most of them only matter if your code depended on the old behaviour. Here I’ll go through the ones you are most likely to hit. Performance work isn’t covered here. The 1.5.6.1 series covers what has already shipped: reading data and appending data, and the final 2.0 release notes will cover the rest.

HUGEINT columns are read as Int128

DuckDB.NET was written before .NET had a 128-bit integer, so HUGEINT and UHUGEINT were read as BigInteger. .NET 7 added Int128 and UInt128, and in 2.0 they are the default types for these columns, so GetValue and ExecuteScalar return an Int128 and GetFieldType reports it too. The same applies to HUGEINT values nested inside lists, maps and structs.

The place this shows up first is SUM. In DuckDB the sum of an integer column is a HUGEINT, so code like this, which worked in 1.x, throws InvalidCastException in 2.0:

command.CommandText = "SELECT SUM(quantity) FROM orders";
var total = (BigInteger)command.ExecuteScalar();

Cast to Int128 instead, or ask for the type you want with GetFieldValue<BigInteger>, which still works. VARINT, which has no fixed size, is still read as BigInteger.

You can also pass Int128 values to the appender and to your scalar and table functions, and use them as command parameters. In 1.x an Int128 parameter was sent to DuckDB as text, and now it is sent as a HUGEINT. Reading works for other columns as well, so you can read any integer column as Int128 as long as the value fits.

One thing to watch for is DataSet.ReadXml. A DataTable with an Int128 column is written by WriteXml without complaint, but ReadXml refuses to read it back unless you add Int128 and UInt128 to the DataSet type allow-list.

Prepared statements

In 1.x, DuckDBCommand.Prepare() was an empty method. In 2.0 it prepares the statement once and keeps it on the connection until the connection closes. Each later execution only binds the parameters and runs. Commands on the same connection with the same text share the prepared statement, so you don’t have to keep one command object around to benefit from it.

using var command = connection.CreateCommand();
command.CommandText = "INSERT INTO events VALUES ($id, $name)";
command.Parameters.Add(new DuckDBParameter("id", 0));
command.Parameters.Add(new DuckDBParameter("name", ""));
command.Prepare();

foreach (var e in events)
{
    command.Parameters["id"].Value = e.Id;
    command.Parameters["name"].Value = e.Name;
    command.ExecuteNonQuery();
}

A command with several statements is not prepared. If you change the schema in a way that changes the type of a parameter, call Prepare() again. Prepare() also needs an open connection now, as it does in other ADO.NET providers.

Stricter conversions when reading

When a value can’t be read as the requested type, 2.0 throws InvalidCastException instead of converting it anyway.

The change most likely to affect you is reading a fractional number as an integer. In 1.x, reading 2.7 from a DOUBLE or DECIMAL column with GetInt32 gave you 3, while in 2.0 it throws:

command.CommandText = "SELECT 2.7::DOUBLE";
using var reader = command.ExecuteReader();
reader.Read();

var value = reader.GetInt32(0);                     // InvalidCastException in 2.0
var rounded = (int)Math.Round(reader.GetDouble(0)); // 3

If you want rounding, cast in SQL with x::INTEGER, or read the value as double or decimal and round it yourself. A whole number such as 3.0 still reads as an integer.

List items, map keys and values, and struct fields now go through the same conversions as a top-level column. A value that reads fine from a column also reads fine from a list, and one that can’t be read throws the same InvalidCastException in both places. In 1.x the nested paths had their own rules. They threw ArgumentException or OverflowException, and they refused some conversions that worked for columns, such as an integer read as BigInteger or an ENUM read as an integer type.

Reading into object gives you the column’s own .NET type everywhere. A DATE read into an object property of a class, or with GetValue<object> inside a function, is a DateOnly instead of a DuckDBDateOnly. The same goes for the other column types, some of which used to throw or come back as an empty object.

BLOB columns are read as byte[] by GetValue, ExecuteScalar and everything else that returns object, and GetFieldType reports byte[]. In 1.x they were a Stream that pointed into the reader’s memory, so the stream you got from ExecuteScalar was already reading freed memory by the time you used it. GetStream and GetFieldValue<Stream> still give you a stream, and GetFieldValue<byte[]> now works for BLOBs in lists, maps and structs as well.

Wide decimals now keep the scale of their column, so a DECIMAL(38,4) holding 1.5 reads as 1.5000, the same as a DECIMAL(18,4) always did. The value is equal either way, but ToString() and serializers will show the trailing zeros.

Reading a STRUCT into a C# struct under the JIT used to leave every property at its default value, and in 2.0 the properties get the values of the fields.

Stricter type checks when writing

Some combinations of a .NET type and a column type were accepted by the appender and stored the wrong bytes. Appending a BigInteger to a column other than HUGEINT or UHUGEINT wrote 16 bytes and overwrote the neighbouring values. Appending a TimeOnly to a DATE column, or a DateOnly to a TIME column, stored the raw bytes of one type as the other. All of these throw InvalidOperationException now, as Int128 already did.

A DateTime appended to a TIME column now stores its time of day. In 1.x it stored the microseconds of the whole timestamp, which is more than a day and not a valid time.

Strings with NUL characters used to be cut at the first NUL, and now every write path stores them in full, parameters included.

Apart from these cases, the low-level appender still writes the bytes of whatever .NET type you give it, so the type you append should match the column. 2.0 adds AppendValue overloads for Int128, UInt128, DateOnly and TimeOnly. With more overloads to choose from, an integer constant passed to AppendValue is ambiguous in C# 12, which is the default for net8.0 projects. C# 13 and later resolve it, so setting <LangVersion>13</LangVersion> makes such calls compile as they did in 1.x. Either way, give the constant the type of its column, for example AppendValue((int?)1), because the appender writes the bytes of the overload it picks, and a bare 1 picks sbyte?. If you implement IDuckDBAppenderRow yourself, you need to add the new members.

Scalar functions and NULL arguments

A typed scalar function whose parameters are all non-nullable now returns NULL for a NULL argument without calling your code, for a column as well as a constant. This matches what SQL functions do and saves you a null check in every function.

In projects without nullable reference types, a string or object parameter counts as non-nullable, so a function that used to be called with null is no longer called. If your function should see the NULL, declare the parameter as string? or mark it with [AllowNull], which is new in 2.0:

connection.RegisterScalarFunction<string?, string>("name_or_default",
    s => s ?? "(none)");

Explicit DateOnly and TimeOnly conversions

In 1.x, DuckDBDateOnly and DuckDBTimeOnly converted to DateOnly and TimeOnly implicitly, even though the conversion can throw. A DuckDB DATE can hold infinity and years outside 1 to 9999, and a TIME can hold 24:00:00. In 2.0 the conversions are explicit, so you need a cast wherever your code relied on the implicit one:

var duckDBDate = reader.GetFieldValue<DuckDBDateOnly>(0);

DateOnly date = duckDBDate;              // compiled in 1.x, error CS0266 in 2.0
DateOnly castDate = (DateOnly)duckDBDate; // works in both

Most code that relied on the conversion stops compiling, so it is easy to find. Two calls still compile but behave differently. dateOnly.Equals(duckDBDateOnly) now returns false, and dateOnly.CompareTo(duckDBDateOnly) throws ArgumentException. Assemblies built against 1.x that use the implicit conversion need to be rebuilt.

ADO.NET compliance

Alaric Dailey sent a series of pull requests that bring DuckDB.NET closer to what the ADO.NET documentation says a provider should do. This matters most when you use DuckDB.NET through a library or tool built on top of ADO.NET, because those are written against the documented behaviour.

The change most likely to show up in your code is in ExecuteNonQuery. It still returns the number of rows changed by INSERT, UPDATE, DELETE, MERGE and COPY, but other statements, such as CREATE TABLE, now return -1 instead of 0:

command.CommandText = "CREATE TABLE events (id INTEGER, name VARCHAR)";
var result = command.ExecuteNonQuery(); // 0 in 1.x, -1 in 2.0

If you check the result of ExecuteNonQuery after a DDL statement, you’ll need to update that check.

GetOrdinal now falls back to a case-insensitive match when there is no exact one, so reader.GetOrdinal("name") finds a column called Name. For a column that doesn’t exist, it throws IndexOutOfRangeException, as the documentation requires. The reader also implements GetBytes and GetChars now, so you can copy a BLOB or a string into a buffer of your own instead of getting a new array for every value. The rest of the changes are smaller, and you’ll find them in the release notes.

Other new features

DuckDB.NET 2.0 supports TIME_NS, the time type with nanosecond precision that DuckDB 1.5 added. You can read and write its values as TimeOnly or TimeSpan, also inside lists, maps and structs.

Typed reads of LIST, MAP and STRUCT columns no longer box each value, and lists and dictionaries are created at their final size. Classes derived from List<T> or Dictionary<TKey, TValue> are read through their base class. The common element, key and value types also work under Native AOT, where reading lists of most element types used to throw MissingMethodException.

Changes in DuckDB.NET.Bindings

If you only use DuckDB.NET.Data, you can skip this part. If you call the bindings directly, DuckDBString changed shape. Its value field and the nested union type are gone, so read a string through Length and Data, and build a short one with DuckDBString.TryCreateInlined. DuckDBValue.GetValue<T> used to reinterpret the value’s memory as whatever T you passed, and now throws for a type the value isn’t. DuckDBType has the types DuckDB added in 1.5, and a few C API functions that were missing now have bindings.

Trying the preview

This is a preview, and it’s out so that people can try it on real code before 2.0 is final. The full list of changes is in the release notes on NuGet. If something you depend on broke and the release notes don’t explain why, please open an issue. Questions and suggestions are welcome in the comments, and a star on the repository helps.