AuroraDB MySqlConnector

For Aurora MySQL, the underlying implementation is leveraging AuroraDbBulkCopy, a LOAD DATA LOCAL INFILE-based bulk writer from RepoDb.Connector.AuroraDb.MySqlConnector.

For Aurora MySQL, the underlying implementation is leveraging AuroraDbBulkCopy, a LOAD DATA LOCAL INFILE-based bulk writer from RepoDb.Connector.AuroraDb.MySqlConnector.

For BulkInsert, the entities/rows are written straight to the target table — unless AuroraDbBulkImportIdentityBehavior.ReturnIdentity is requested, in which case a staging (pseudo) table is used instead. For BulkDelete, BulkDeleteByKey, BulkMerge and BulkUpdate, a pseudo table is always used. MySQL has neither a native MERGE statement nor a session-scoped identity sequence object, so this family’s mechanics differ meaningfully from a Postgres-wire provider’s: a merge is split into a plain UPDATE ... INNER JOIN (matched rows) plus an INSERT ... SELECT guarded by a LEFT JOIN ... WHERE ... IS NULL anti-join (unmatched rows), and a to-be-returned identity value is pre-assigned into the pseudo table via a session user variable — seeded from MAX(identityColumn) + 1 on the real table — rather than read back from an AUTO_INCREMENT sequence after the fact.

The data is brought together from the client application into the database server (at one-go). It then gets processed together at the same time.

The other bulk operations can be optimized further by targeting the underlying table indexes (via qualifiers). Pass a list of Field objects when calling the operations — an index on the pseudo table’s qualifier columns is also created automatically to speed up the matching statements.

Pseudo Table Type

The AuroraDbBulkImportPseudoTableType enum lets you choose between a session-private temporary table (Memory), an ordinary heap table (Physical), or let the library decide based on row count (Auto, the default — Physical at 5,000 rows or more, otherwise Memory).

As currently implemented, this argument has no effect — every bulk call resolves to Physical regardless of the value passed. See AuroraDbBulkImportPseudoTableType for details.

Supported Objects

Below are the following objects supported by the bulk operations.

• System.DataTable
• System.Data.Common.DbDataReader
• IEnumerable<T>
• ExpandoObject
• IDictionary<string, object>

Operation SQL Statements

Once all the data is in the staging (pseudo) table, the correct SQL statement is used to cascade the changes towards the original table. The pseudo table is always created via DROP TABLE IF EXISTS ...; CREATE TABLE ... ( `__RepoDbBulkRowOrder__` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY) AS SELECT ... FROM real WHERE (1 = 0) — a surrogate ordering column standing in for the row order MySQL has no ROWID-style pseudo-column to express.

BulkInsert writes directly into the target table and skips the staging table entirely — unless identityBehavior is set to ReturnIdentity, in which case a staging table is used first (see below).

For BulkInsert (with ReturnIdentity)

> SET @repodb_seq := (nextIdentityValue - 1);
> UPDATE "PseudoTempTable" SET "Identity" = (@repodb_seq := @repodb_seq + 1);
> INSERT INTO "OriginalTable" (Field1, Field2, ...) SELECT Field1, Field2, ... FROM "PseudoTempTable";
> SELECT "Identity" AS "Result" FROM "PseudoTempTable" ORDER BY "__RepoDbBulkRowOrder__";

For BulkDelete / BulkDeleteByKey

> DELETE T FROM "OriginalTable" T INNER JOIN "PseudoTempTable" S ON (T.QualifierField1 = S.QualifierField1);

For BulkMerge

Without identityBehavior: ReturnIdentity:

> UPDATE "OriginalTable" T INNER JOIN "PseudoTempTable" S ON (T.QualifierField1 = S.QualifierField1)
> SET T.Field3 = S.Field3, T.Field4 = S.Field4;
>
> INSERT INTO "OriginalTable" (Field1, Field2, ...)
> SELECT S.Field1, S.Field2, ... FROM "PseudoTempTable" S
> LEFT JOIN "OriginalTable" T ON (T.QualifierField1 = S.QualifierField1)
> WHERE T.QualifierField1 IS NULL;

With identityBehavior: ReturnIdentity, matched rows first copy their existing identity onto the pseudo row, unmatched rows get a pre-assigned one (same session-variable technique as BulkInsert), then the same update+insert pair runs, followed by a final SELECT ... ORDER BY reporting every row’s identity — see Identity Setting Alignment below.

For BulkUpdate

> UPDATE "OriginalTable" T INNER JOIN "PseudoTempTable" S ON (T.QualifierField1 = S.QualifierField1)
> SET T.Field3 = S.Field3, T.Field4 = S.Field4;

Unlike BulkMerge, staged rows with no matching target row are left as-is, not inserted.

Special Arguments

The arguments below are available on most operations.

Identity Setting Alignment

When identityBehavior is set to ReturnIdentity, MySQL’s lack of a per-row SEQUENCE.NEXTVAL-style call means the identity value cannot simply be read back after the insert the way a Postgres-wire provider does. Instead, it is pre-assigned before the real INSERT:

1. A live SELECT MAX(identityColumn) + 1 FROM real seeds the next value — read fresh off the table’s actual row data each time (not information_schema.TABLES.AUTO_INCREMENT, which MySQL 8 caches for up to information_schema_stats_expiry seconds and can return a stale, already-used counter).
2. SET @repodb_seq := (seed - 1); followed by UPDATE "PseudoTempTable" SET identity = (@repodb_seq := @repodb_seq + 1); assigns a distinct, increasing value to every staged row via a session user variable.
3. The rows are copied into the real table as plain INSERT ... SELECT, carrying their pre-assigned identity values as literals — MySQL always accepts an explicit value into an AUTO_INCREMENT column, and auto-advances its internal counter past it, so this doesn’t create a future collision.
4. A final SELECT identity AS "Result" FROM "PseudoTempTable" ORDER BY "__RepoDbBulkRowOrder__" reports every value back in the original bulk-load order.

This requires AllowUserVariables=True on the connection string — AuroraDbConnection defaults this to false. Plain BulkInsert (without ReturnIdentity), BulkUpdate, BulkDelete and BulkDeleteByKey never touch a session variable and don’t need this flag.

Reading the seed and assigning it are two separate round trips, leaving a small race window against a concurrent writer to the same table — there is no table-level locking here (LOCK TABLES would silently commit any transaction already open on the connection). For strict correctness under concurrent writers, avoid overlapping ReturnIdentity bulk calls against the same table.

BatchSize

All the provided operations have a batchSize argument that lets you override the number of rows wired-up to the server per batch. By default it is null, meaning all items are sent together in one-go.

Use this argument if you wish to optimize the operation based on certain situations.

• Network Latency
• Infrastructure
• No. of Columns
• Type of Data

Async Methods

All the provided synchronous operations have an equivalent asynchronous (Async) counterpart.

BulkDelete

using (var connection = new AuroraDbConnection(connectionString))
{
    var people = connection.Query<Person>(e => e.IsActive == false);
    var deletedRows = connection.BulkDelete<Person>(people);
}

BulkDeleteByKey

using (var connection = new AuroraDbConnection(connectionString))
{
    var primaryKeys = new object[] { 10045, 10046, 10047 };
    var deletedRows = connection.BulkDeleteByKey("Person", primaryKeys);
}

BulkInsert

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

BulkMerge

using (var connection = new AuroraDbConnection(connectionString))
{
    var mergedRows = connection.BulkMerge(people);
}

BulkUpdate

using (var connection = new AuroraDbConnection(connectionString))
{
    var updatedRows = connection.BulkUpdate(people);
}