Table of Contents

Insert or update many rows in one call (bulk)

Run one query for many rows in one call, such as a bulk insert or a batch of updates, with a collection method.

Use this when your code holds many items, such as new users, and each item needs the same insert or update. Use it too when a failure must name the item that the database refused.

Insert many rows in one call

-- name: AddUser -> one
-- generator: collection-method
INSERT INTO users (name, email)
VALUES (@name, @email)
RETURNING id;
[SqlSourceGenerate]
public partial class UserRepository(NpgsqlDataSource dataSource);

// elsewhere
var newUsers = signups.Select(signup => new AddUserParams(signup.Name, signup.Email));
AddUserDto[] added = await users.AddUser(newUsers, cancellationToken);

What you get. -- generator: collection-method gives AddUser a second method. It takes the input model of every user, and adds them all in one transaction of its own. It returns their ids in the order of the users.

The rules you need.

The transaction it runs in

Read this to know which transaction a collection method runs in, or to run it in a transaction of your own.

-- name: AddUser -> one
-- generator: collection-method
INSERT INTO users (name, email)
VALUES (@name, @email)
RETURNING id;
// At the output methods
await connection.OpenAsync(cancellationToken);
await using var transaction = await connection.BeginTransactionAsync(cancellationToken);
AddUserDto[] added = await connection.AddUser(newUsers, transaction, cancellationToken: cancellationToken);
// Run the other queries of the transaction here, each with the transaction.
await transaction.CommitAsync(cancellationToken);

What you get. AddUser adds every user in your transaction. It neither commits the transaction nor rolls it back: your code does.

The rules you need.

  • If you give it no transaction, a collection method begins its own. It commits it after the last item, and rolls it back if anything throws, so no item is left. See Transactions.
  • To set the isolation level of that transaction, write it on the same line: -- generator: collection-method transaction=serializable. transaction=off begins no transaction, so a failure leaves the items before it. See transaction.
  • At the output methods, the method uses the transaction that you give it, whatever transaction says. After an exception, what ran stays in your transaction, unless a database error aborted the transaction or rolled it back. On PostgreSQL, every database error aborts the transaction, so roll it back. See Transactions.
  • If a TransactionScope is open, the method begins no transaction, and runs in the scope's. This is how to put a repository's collection method, which takes no transaction, in a larger one. A connection that is not in the scope, such as one opened before it, runs the items in no transaction at all. See Transactions and timeouts.

When one item fails

Read this to find the item that the database refused, such as a user whose row breaks a constraint.

try
{
    await users.AddUser(newUsers, cancellationToken);
}
catch (CollectionItemException exception)
{
    // The database refused one user, so no user was added.
    Console.WriteLine($"User {exception.ItemIndex} was refused: {exception.InnerException?.Message}");
}

What you get. The method's own transaction is rolled back, so no user is added. ItemIndex tells you which user the database refused.

The rules you need.

  • A database error at an item is thrown as CollectionItemException. Its ItemIndex is the position of the item, counted from 0, and its InnerException is the driver's exception, a PostgresException or a SqlException. See What it throws.
  • It derives from DbException, and its SqlState and IsTransient are those of its inner exception. So code that catches DbException, or a retry policy that tests IsTransient, works unchanged. See CollectionItemException.
  • Only a database error at an item is wrapped. An exception of the method's own, such as UnexpectedRowCountException, is thrown as it is. So is an error that no item raised, such as a deferred constraint that the commit checks. See What it throws.
  • The items run 1,000 at a time, so a bad item is found only after the round trips before its own. In your transaction, or under transaction=off, the items of those round trips stay. See Collection methods.

If it does not work

  • NotSupportedException, "Specified method is not supported.", or a database error that names no item. The connection wraps the driver's connection. Give the method the driver's own connection. See the limit.
  • TransactionRolledBackException, on SQL Server. You gave the method a transaction, and a later error of the query rolled it back. The first error is its InnerException, such as the CollectionItemException of the item that failed first. Begin another transaction to go on. See TransactionRolledBackException.
  • SQLSRC297. transaction names an isolation level that the database does not have. Write a level it has. See SQLSRC297.