Bulk writes

For large writes, these skip the model layer and go straight for speed.

InsertMany

InsertManyfunc (t *Table[T]) InsertMany( ctx context.Context, entities []*T, options ...InsertOption, ) (int64, error)InsertMany writes many rows quickly, in multi-row statements inside one transaction, and returns how many were inserted. writes many rows in multi-row statements, inside one transaction, and tells you how many went in. Generated keys come back, except with a conflict clause, where some rows may be skipped. It ignores relations and doesn't track the rows.

n, err := db.Users.InsertMany(ctx, users, ant.BatchSize(500))

It splits the work to fit the database's parameter limit, so a batch size bigger than one statement can hold still works. It's all or nothing, and keys are cleared if it fails.

UpdateMany

UpdateManyfunc (t *Table[T]) UpdateMany( ctx context.Context, entities []*T, fields ...FieldRef, ) (int64, error)UpdateMany writes the named columns of each entity to its own row, in one transaction, and returns how many rows changed. writes a different value per row, for the columns you name. Fields you don't name are left alone.

alice.Age, bob.Age = 31, 18

n, err := db.Users.UpdateMany(ctx, []*User{alice, bob}, ant.F(&U.Age))

Tracked entities keep their snapshots, so prefer SaveChangesfunc (c *Context) SaveChanges(ctx context.Context) errorSaveChanges writes every change to tracked entities in one transaction: inserts and updates with parents first, then deletes with children first. for them.

InsertFrom

InsertFromfunc (t *Table[T]) InsertFrom(ctx context.Context, rows *Projection[T]) (int64, error)InsertFrom inserts the rows of a projection, and returns how many. copies rows with one statement, in the database.

type Archive struct {
	ID    int64
	Title string
	Tag   string
}

var A = ant.Fields[Archive]()

n, err := db.Archives.InsertFrom(ctx, ant.Project[Archive](
	db.Posts.Where(ant.Eq(&P.Published, true)),
	ant.Into(&A.Title, &P.Title),
	ant.IntoExpr(&A.Tag, ant.SQL[string]("'archived'")),
))

Truncate

Truncatefunc (t *Table[T]) Truncate(ctx context.Context) errorTruncate removes every row, faster than Delete, and cannot be used on a table that others refer to unless they are empty too. empties a table, faster than Deletefunc (q *Query[T]) Delete(ctx context.Context) (int64, error)Delete removes every matching row and returns the number removed.. It fails on a table others still refer to, and it doesn't clear the tracker, so call db.ClearTracked()func (c *Context) ClearTracked()ClearTracked stops tracking everything, forgetting unsaved changes. if you hold these rows.

err := db.Posts.Truncate(ctx)

COPY on PostgreSQL

pgxext.Copyfunc Copy[T any](ctx context.Context, h Database, entities []*T) (int64, error)Copy loads entities with COPY, which is by far the fastest way to insert many rows. is the fast path for loading many rows, through PostgreSQL's COPY. Timestamps are filled in, and keys from the database don't come back. It needs the pgx driver.

n, err := pgxext.Copy(ctx, db, users)

Batches in one round trip

pgxext.Batchtype Batch struct{ … }Batch queues statements to send in one round trip, which pays off when a network sits between the application and the database. sends statements together, as one transaction, so a failure undoes all of them. UpdateSQLfunc (q *Query[T]) UpdateSQL(ctx context.Context, set ...Assignment) (string, []any, error)UpdateSQL returns the statement Update would run, for sending it some other way, such as in a pipelined batch. and DeleteSQLfunc (q *Query[T]) DeleteSQL(ctx context.Context) (string, []any, error)DeleteSQL returns the statement Delete would run. build a statement without running it.

var batch pgxext.Batch

batch.Add(db.Users.Where(ant.Eq(&U.ID, alice.ID)).UpdateSQL(ctx, ant.Set(&U.Age, 41)))
batch.Add(db.Users.Where(ant.Eq(&U.ID, bob.ID)).DeleteSQL(ctx))
batch.Exec("UPDATE users SET age = age + 1 WHERE id = $1", alice.ID)

affected, err := batch.Send(ctx, db)

affected holds the rows each statement touched.

See it tested: tests/features/writing/bulk_test.go