Upsert and merge

Upsertfunc (t *Table[T]) Upsert(ctx context.Context, e *T, conflict *Conflict) (bool, error)Upsert inserts e, or follows conflict if a unique constraint already holds the row. inserts a row, or follows your instructions if a unique constraint says it's already there.

Upsert

user := &User{
	Name:  "Ada",
	Email: "ada@example.com",
	Age:   36,
}

wrote, err := db.Users.Upsert(ctx, user, ant.OnConflict(&U.Email))

By default, every column except the key and the constraint's own is overwritten, and your struct receives the stored row, key included. A zero value overwrites too: no guessing here either.

Choosing what changes

DoUpdatefunc (c *Conflict) DoUpdate(fields ...FieldRef) *ConflictDoUpdate overwrites only the named columns of the existing row. names the columns to overwrite, and Set assigns values, including ones based on the existing row.

onEmail := ant.OnConflict(&U.Email)

wrote, err := db.Users.Upsert(ctx, user, onEmail.DoUpdate(ant.F(&U.Age)))

wrote, err = db.Users.Upsert(ctx, user, onEmail.Set(ant.Inc(&U.Age, 1)))

Skipping existing rows

DoNothingfunc (c *Conflict) DoNothing() *ConflictDoNothing keeps the existing row. keeps the row and reports false, leaving your struct as it was.

wrote, err := db.Users.Upsert(ctx, user, ant.OnConflict(&U.Email).DoNothing())

A key on several columns

And adds a column, for a constraint on more than one.

conflict := ant.OnConflict(&U.Email).And(ant.F(&U.Name))

Many rows

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. takes a conflict too.

n, err := db.Users.InsertMany(ctx, users, ant.WithConflict(ant.OnConflict(&U.Email).DoNothing()))

On MySQL and MariaDB

ON DUPLICATE KEY has no target, so any unique key triggers it, not only the columns you name. If a different key is the one that collides, Upsert can't find the row by your columns, and reports that it wrote nothing. Name the only unique key that can collide. SQL Server has no upsert: use ant.Mergefunc Merge[T, S any](t *Table[T], source *Query[S], on ...Cond) *Merger[T]Merge combines source rows into T's table, deciding per row what to do, which Upsert cannot: one statement can update, insert, and delete..

Merge

ant.Merge decides, row by row, what a batch of source rows does to a table. One statement can update, insert, and delete, which Upsert can't. Clauses run in the order you add them, and the first one that fits a row wins. It needs PostgreSQL 15, or SQL Server.

type Stock struct {
	ID    int64
	SKU   string `ant:"unique"`
	Count int
}

type Staged struct {
	ID    int64
	SKU   string
	Count int
}

var (
	ST = ant.Fields[Stock]()
	SG = ant.Fields[Staged]()
)

changed, err := ant.Merge(&db.Stock, db.Staged.Query(), ant.EqCol(&ST.SKU, &SG.SKU)).
	WhenMatchedIf(ant.Eq(&SG.Count, 0)).Delete().
	WhenMatched(ant.SetExpr(&ST.Count, ant.Col(&SG.Count))).
	WhenNotMatched(ant.Into(&ST.SKU, &SG.SKU), ant.Into(&ST.Count, &SG.Count)).
	Exec(ctx)

Here a sold-out item is deleted, a known one is updated, and a new one is inserted.

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