Link Search Menu Expand Document

Get Started for ClickHouse

RepoDB is a hybrid .NET ORM library for ClickHouse, an open-source, columnar OLAP database. The project is hosted at Github and is licensed with Apache 2.0.

Support ships as two packages:

ClickHouse is an OLAP database, not a traditional transactional RDBMS. A few things behave differently than SQL Server/MySQL/PostgreSQL:

  • There is no identity/auto-increment/sequence of any kind — key values must always be assigned by the caller.
  • There is no native MERGE/upsert statement — Merge compiles to a plain INSERT, relying on the table engine (e.g. ReplacingMergeTree) for deduplication.
  • UPDATE/DELETE are expressed as ALTER TABLE ... UPDATE/DELETE mutations — asynchronous, background operations rather than immediate row-level changes, and the WHERE clause is mandatory.
  • Transactions are accepted for API compatibility but are not ACID — Commit()/Rollback() are no-ops.

See Operations (ClickHouse) and ClickHouseStatementBuilder for the full detail behind each of these.

Installation

Install the library via NuGet using the Package Manager Console.

> Install-Package RepoDb.ClickHouse

After installation, call the globalized setup method to initialize all dependencies for ClickHouse.

GlobalConfiguration
    .Setup()
    .UseClickHouse();

To use bulk operations, install the RepoDb.ClickHouse.BulkOperations package.

> Install-Package RepoDb.ClickHouse.BulkOperations

Both packages target net8.0, net9.0 and net10.0, and depend on ClickHouse.Driver v1.3.0.

Connection String

Host=127.0.0.1;Port=8123;Username=default;Password=YourPassword;Database=RepoDb;Protocol=http;UseCustomDecimals=false;

Always include UseCustomDecimals=false. Without it, Decimal columns come back as the driver’s own ClickHouseDecimal type instead of a plain .NET decimal, which RepoDB’s compiled reader cannot cast.

Create a Table

The examples below assume the following table exists in the database.

CREATE TABLE IF NOT EXISTS Person
(
    Id UInt64,
    Name String,
    Age Int32,
    CreatedDateUtc DateTime
)
ENGINE = MergeTree
ORDER BY Id;

Create a Model

The examples below assume the following model exists in the application.

public class Person
{
    public ulong Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

Use RepoDb.ClickHouseConnection (namespace RepoDb) throughout your application — not ClickHouse.Driver.ADO.ClickHouseConnection directly. It’s a thin subclass that lets RepoDB’s provider mappings (DbSetting, DbHelper, StatementBuilder) resolve correctly.

Creating a Record

To insert a row, use the Insert method.

var person = new Person
{
    Id = 1,
    Name = "John Doe",
    Age = 54,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new ClickHouseConnection(ConnectionString))
{
    connection.Insert(person);
}

To insert multiple rows, use the InsertAll operation.

var people = GetPeople(100);
using (var connection = new ClickHouseConnection(ConnectionString))
{
    var rowsInserted = connection.InsertAll(people);
}

ClickHouse has no identity/auto-increment mechanism, so Id must always be assigned by the caller before calling Insert/InsertAll. Mapping a property as Identity causes both methods to throw a NotSupportedException, and ClickHouseDbHelper’s GetScopeIdentity always throws for the same reason.

Querying a Record

To query a row, use the Query method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var person = connection.Query<Person>(e => e.Id == 1);
    /* Process the result here */
}

To query all rows, use the QueryAll method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Process the results here */
}

Merging a Record

To merge a row, use the Merge method.

var person = new Person
{
    Id = 1,
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new ClickHouseConnection(ConnectionString))
{
    connection.Merge(person);
}

ClickHouse has no native MERGE/upsert statement. Merge/MergeAll compile to a plain INSERT, identical to Insert/InsertAll — deduplication is deferred to the target table’s engine (e.g. ReplacingMergeTree) and its background merges, which is the idiomatic ClickHouse upsert pattern. For a real, immediate matched/unmatched merge (an UPDATE for matched rows plus an anti-join INSERT for unmatched ones), use BulkMerge from RepoDb.ClickHouse.BulkOperations instead.

To merge multiple rows, use the MergeAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new ClickHouseConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

Deleting a Record

To delete a row, use the Delete method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(1);
}

Other columns can also be used as qualifiers.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(p => p.Name == "John Doe");
}

To delete all rows, use the DeleteAll method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var deletedRows = connection.DeleteAll<Person>();
}

Delete/DeleteAll compile to ALTER TABLE ... DELETE WHERE .... This is an asynchronous mutation in ClickHouse — applied later by background merges, not necessarily before the call returns — and the number of rows reported as affected is not reliable for this statement shape.

Updating a Record

To update a row, use the Update method.

var person = new Person
{
    Id = 1,
    Name = "James Doe",
    Age = 55,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new ClickHouseConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

To update multiple rows, use the UpdateAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new ClickHouseConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

Update/UpdateAll compile to ALTER TABLE ... UPDATE ... WHERE ..., with the same asynchronous-mutation caveat as Delete. The WHERE clause is mandatory — ClickHouse rejects an unconditional mutation.

Executing a Query

To execute a non-query statement, use the ExecuteNonQuery method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var sql = "ALTER TABLE Person DELETE WHERE Id = @Id;";
    var affectedRecords = connection.ExecuteNonQuery(sql, new { Id = 1 });
}

To execute a query and return mapped objects, use the ExecuteQuery method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var sql = "SELECT * FROM Person ORDER BY Id ASC;";
    var people = connection.ExecuteQuery<Person>(sql);
    /* Process the results here */
}

To execute a query and return a scalar value, use the ExecuteScalar method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var sql = "SELECT MAX(Id) FROM Person;";
    var maxId = connection.ExecuteScalar<ulong>(sql);
}

To execute a query and return a DbDataReader, use the ExecuteReader method.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var sql = "SELECT * FROM Person ORDER BY Id ASC;";
    using (var reader = connection.ExecuteReader(sql))
    {
        /* Process the data reader here */
    }
}

Typed Result Execution

Single-column result sets can be mapped to any .NET CLR type via ExecuteQuery.

using (var connection = new ClickHouseConnection(ConnectionString))
{
    var sql = "SELECT Name FROM Person WHERE Id = @Id;";
    var name = connection.ExecuteQuery<string>(sql, new { Id = 1 });
}

The resultset of this operation is an IEnumerable object.