From 5954b420d2acff038a79aa0e09d2ba3ab8dc37a9 Mon Sep 17 00:00:00 2001 From: Stefan Majewsky Date: Fri, 24 Apr 2026 16:08:53 +0200 Subject: add Store.Upsert() --- query_test.go | 98 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 96 insertions(+), 2 deletions(-) (limited to 'query_test.go') diff --git a/query_test.go b/query_test.go index 2809f6e..6f73642 100644 --- a/query_test.go +++ b/query_test.go @@ -7,6 +7,7 @@ import ( "database/sql" "strconv" "testing" + "time" "go.xyrillian.de/oblast" "go.xyrillian.de/oblast/internal/testhelpers/assert" @@ -105,6 +106,51 @@ func TestDeleteBasic(t *testing.T) { } } +func TestUpsertBasicWithAutoColumn(t *testing.T) { + md := mock.NewDriver() + db := sql.OpenDB(md) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + md.ForQuery(`INSERT INTO "basic_records" ("name") VALUES (?) RETURNING "id"`). + ExpectQueryWithArgs("first needs insert"). + AndReturnColumns("id"). + WithRow(int64(1)) + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs("second needs update", 2). + AndReturnRowsAffected(1) + md.ForQuery(`INSERT INTO "basic_records" ("name") VALUES (?) RETURNING "id"`). + ExpectQueryWithArgs("third needs insert"). + AndReturnColumns("id"). + WithRow(int64(3)) + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs("fourth needs update", 4). + AndReturnRowsAffected(1) + + records := []*basicRecord{ + {Name: "first needs insert"}, + {ID: 2, Name: "second needs update"}, + {Name: "third needs insert"}, + {ID: 4, Name: "fourth needs update"}, + } + must.Succeed(t, store.Upsert(db, records...)) + + assert.SliceDeepEqual(t, records, + &basicRecord{ID: 1, Name: "first needs insert"}, + &basicRecord{ID: 2, Name: "second needs update"}, + &basicRecord{ID: 3, Name: "third needs insert"}, + &basicRecord{ID: 4, Name: "fourth needs update"}, + ) +} + func TestWriteQueriesNotPossible(t *testing.T) { md := mock.NewDriver() db := sql.OpenDB(md) @@ -122,6 +168,9 @@ func TestWriteQueriesNotPossible(t *testing.T) { err := store.Insert(db, &r) assert.ErrEqual(t, err, "cannot execute Insert() because query could not be autogenerated") + err = store.Upsert(db, &r) + assert.ErrEqual(t, err, "cannot execute Insert() because query could not be autogenerated") + r.ID = 42 err = store.Update(db, r) assert.ErrEqual(t, err, "cannot execute Update() because query could not be autogenerated") @@ -178,7 +227,7 @@ func TestWriteQueriesFailDuringPrepare(t *testing.T) { } } -func TestUpdateFailsOnMissingRecord(t *testing.T) { +func TestUpdateOrUpsertFailsOnMissingRecord(t *testing.T) { md := mock.NewDriver() db := sql.OpenDB(md) @@ -192,6 +241,7 @@ func TestUpdateFailsOnMissingRecord(t *testing.T) { oblast.PrimaryKeyIs("id"), ) + // test Update() md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). ExpectExecWithArgs("changed", 42). AndReturnRowsAffected(0) @@ -199,6 +249,16 @@ func TestUpdateFailsOnMissingRecord(t *testing.T) { assert.ErrEqual(t, err, "could not UPDATE record that does not exist in the database: id = 42") _, hasCorrectType := err.(oblast.MissingRecordError[basicRecord]) //nolint:errorlint // we explicitly do not want a wrapped error assert.Equal(t, hasCorrectType, true) + + // test Upsert() -> this will not try inserting because the strategy + // is chosen based on the fill state of the "auto" field + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs("changed", 42). + AndReturnRowsAffected(0) + err = store.Upsert(db, &basicRecord{ID: 42, Name: "changed"}) + assert.ErrEqual(t, err, "could not UPDATE record that does not exist in the database: id = 42") + _, hasCorrectType = err.(oblast.MissingRecordError[basicRecord]) //nolint:errorlint // we explicitly do not want a wrapped error + assert.Equal(t, hasCorrectType, true) } func TestInsertFailsOnFilledAutoField(t *testing.T) { @@ -223,7 +283,7 @@ func TestInsertFailsOnFilledAutoField(t *testing.T) { assert.ErrEqual(t, err, `refusing to INSERT record with idx = 0 that already has non-zero values in its "auto" columns`) } -func TestInsertWithNoAutoColumns(t *testing.T) { +func TestInsertAndUpsertWithNoAutoColumns(t *testing.T) { md := mock.NewDriver() db := sql.OpenDB(md) @@ -237,8 +297,42 @@ func TestInsertWithNoAutoColumns(t *testing.T) { oblast.PrimaryKeyIs("foo_id", "bar_id"), ) + // test Insert() md.ForQuery(`INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?)`). ExpectExecWithArgs(23, 42). AndReturnRowsAffected(1) must.Succeed(t, store.Insert(db, &relation{23, 42})) + + // test Upsert() + md.ForQuery(`INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?) ON CONFLICT ("foo_id", "bar_id") DO NOTHING`). + ExpectExecWithArgs(1, 2). + AndReturnRowsAffected(1) + md.ForQuery(`INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?) ON CONFLICT ("foo_id", "bar_id") DO NOTHING`). + ExpectExecWithArgs(3, 4). + AndReturnRowsAffected(1) + must.Succeed(t, store.Upsert(db, &relation{1, 2}, &relation{3, 4})) +} + +func TestUpsertFailsOnMixedAutoFieldState(t *testing.T) { + md := mock.NewDriver() + db := sql.OpenDB(md) + + type complexRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + CreatedAt time.Time `db:"created_at,auto"` + } + store := oblast.MustNewStore[complexRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("complex_records"), + oblast.PrimaryKeyIs("id"), + ) + + brokenRecord := complexRecord{ + ID: 42, // this looks like we need to UPDATE + Name: "foo", + CreatedAt: time.Time{}, // this looks like we need to INSERT + } + err := store.Upsert(db, &brokenRecord) + assert.ErrEqual(t, err, `cannot decide whether to INSERT or UPDATE record with idx = 0: some "auto" columns are zero, others are not`) } -- cgit v1.2.3