Quickstart · 7 min read

AuroraDB

Learn on how to work with Amazon Aurora databases using RepoDB library.

RepoDB is a hybrid .NET ORM library for Amazon Aurora, available for both of its engine editions. The project is hosted at Github and is licensed with Apache 2.0.

Support ships as two separate, engine-specific packages — install only the one that matches your Aurora cluster:

RepoDb.AuroraDb.PostgreSql — for the PostgreSQL-compatible edition, built on top of RepoDb.Connector.AuroraDb.Npgsql. Since Aurora PostgreSQL is PostgreSQL-compatible, the generated SQL matches RepoDb.PostgreSql: double-quoted identifiers, RETURNING for identities, INSERT ... ON CONFLICT for merges, and LIMIT/OFFSET for paging.
RepoDb.AuroraDb.MySqlConnector — for the MySQL-compatible edition, built on top of RepoDb.Connector.AuroraDb.MySqlConnector.

Both packages expose an identically-named AuroraDbConnection and GlobalConfiguration.UseAuroraDb() (in different namespaces, backed by different connectors), so only one of them should ever be referenced by a given project — installing both side by side causes a type ambiguity.

Both connectors are currently prerelease packages. The Npgsql-based connector wraps its driver inside the AWS Advanced .NET Data Provider Wrapper, which probes for Aurora-specific server functions on every connection open — expect that extra round trip (and a logged error) when pointing AuroraDbConnection at a server that isn’t a genuine Aurora cluster, such as a local instance used for development.

Installation

Install the library via NuGet using the Package Manager Console.

PostgreSQL-compatible Edition

> Install-Package RepoDb.AuroraDb.PostgreSql

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

GlobalConfiguration
    .Setup()
    .UseAuroraDb();

To use bulk operations (BulkDelete, BulkDeleteByKey, BulkInsert, BulkMerge and BulkUpdate — see Operations (AuroraDB PostgreSQL)), install the RepoDb.AuroraDb.PostgreSql.BulkOperations package.

> Install-Package RepoDb.AuroraDb.PostgreSql.BulkOperations

MySQL-compatible Edition

> Install-Package RepoDb.AuroraDb.MySqlConnector

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

GlobalConfiguration
    .Setup()
    .UseAuroraDb();

Requires .NET 8.0 or later.

Create a DB Table

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

CREATE TABLE "Person"
(
    "Id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "Name" TEXT,
    "Age" INTEGER,
    "CreatedDateUtc" TIMESTAMP
);

This DDL, and the raw SQL used later under Executing a Query, is written for the PostgreSQL-compatible edition. On the MySQL-compatible edition, adapt it to MySQL syntax (backtick-quoted identifiers, AUTO_INCREMENT, and so on) — the fluent operations below (Insert, Query, Merge, etc.) work identically on both without any code changes.

Create a .NET Model

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

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

Creating a Record

To insert a row, use the Insert method.

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

To insert multiple rows, use the InsertAll operation.

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

Insert returns the identity/primary column value, InsertAll returns the number of rows inserted, and both set the identity/primary property back onto the entity model (if present).

Querying a Record

To query a row, use the Query method.

using (var connection = new AuroraDbConnection(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 AuroraDbConnection(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 AuroraDbConnection(ConnectionString))
{
    var id = connection.Merge(person);
}

By default, the primary column is used as a qualifier. Custom qualifiers can also be specified.

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var id = connection.Merge(person, qualifiers: (p => new { p.Name }));
}

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 AuroraDbConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

Merge returns the identity/primary column value, MergeAll returns the number of rows affected, and both set the identity/primary property back onto the entity (if present).

Deleting a Record

To delete a row, use the Delete method.

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

Other columns can also be used as qualifiers.

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

To delete all rows, use the DeleteAll method.

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

A list of primary keys can also be passed for targeted deletion.

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var primaryKeys = new [] { 10045, 11001, ..., 12011 };
    var deletedRows = connection.DeleteAll<Person>(primaryKeys);
}

Delete and DeleteAll both return the number of rows affected.

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 AuroraDbConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

Specific columns can also be targeted using a dynamic update.

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var updatedRows = connection.Update("Person", new { Id = 1, Name = "James Doe" });
}

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 AuroraDbConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

By default, the primary column is used as a qualifier. Custom qualifiers can also be specified.

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people,
        qualifiers: (p => new { p.Name }));
}

Update and UpdateAll both return the number of rows affected.

Bulk Operations

For 1,000 rows or more, prefer the true bulk operations over their All-suffixed counterparts shown above — BulkInsert, BulkMerge, BulkUpdate, BulkDelete and BulkDeleteByKey. These require the RepoDb.AuroraDb.PostgreSql.BulkOperations package installed alongside the core provider.

Currently only available for the PostgreSQL-compatible edition — there is no bulk operations package yet for RepoDb.AuroraDb.MySqlConnector.

To bulk-insert rows, use the BulkInsert method.

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people);
}

To bulk-merge rows, use the BulkMerge method.

var people = GetPeople(10000).AsList();
people.ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new AuroraDbConnection(ConnectionString))
{
    var mergedRows = connection.BulkMerge(people);
}

To bulk-update rows, use the BulkUpdate method.

var people = GetPeople(10000).AsList();
people.ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new AuroraDbConnection(ConnectionString))
{
    var updatedRows = connection.BulkUpdate(people);
}

To bulk-delete rows via their data entities, use the BulkDelete method.

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var inactivePeople = connection.Query<Person>(e => e.Age > 90).AsList();
    var deletedRows = connection.BulkDelete(inactivePeople);
}

To bulk-delete rows via a list of primary keys, use the BulkDeleteByKey method.

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var primaryKeys = connection.Query<Person>(e => e.Age > 90).Select(e => e.Id);
    var deletedRows = connection.BulkDeleteByKey<Person>(primaryKeys);
}

BulkInsert and BulkMerge can also set the generated identity value back onto each entity via the identityBehavior argument (defaults to not returning it).

Executing a Query

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

using (var connection = new AuroraDbConnection(ConnectionString))
{
    var sql = "DELETE FROM \"Person\" 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 AuroraDbConnection(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 AuroraDbConnection(ConnectionString))
{
    var sql = "SELECT MAX(\"Id\") FROM \"Person\";";
    var maxId = connection.ExecuteScalar<long>(sql);
}

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

using (var connection = new AuroraDbConnection(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 AuroraDbConnection(ConnectionString))
{
    var sql = "SELECT \"Name\" FROM \"Person\";";
    var names = connection.ExecuteQuery<string>(sql);
}

Returns an IEnumerable object.