add initial sketches for Store.Insert, Store.Update

Currently extremely bad performance for some reason. Need to investigate.

7 files changed, 390 insertions, 101 deletions

diff --git a/benchmark/benchmark_test.go b/benchmark/benchmark_test.go
index edebc52..d7399b6 100644
--- a/benchmark/benchmark_test.go
+++ b/benchmark/benchmark_test.go
@@ -16,56 +16,65 @@ import (
 	"go.xyrillian.de/oblast/internal/assert"
 )
 
-const totalRecordCount = 1000
+const totalRecordCountForSelect = 10000
 
-func makeTestDB(t testing.TB) (*sql.DB, error) {
+func makeTestDB(t testing.TB, recordCount int) (*sql.DB, error) {
 	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?mode=memory&cache=shared", t.Name()))
 	if err != nil {
 		return nil, err
 	}
-
-	// fill in some random-looking, but deterministic data
-	_, err = db.Exec(`CREATE TABLE entries (id INTEGER, message TEXT)`)
+	_, err = db.Exec(`CREATE TABLE entries (id INTEGER, message TEXT, PRIMARY KEY (id AUTOINCREMENT))`)
 	if err != nil {
 		return nil, err
 	}
-	stmt, err := db.Prepare(`INSERT INTO entries (id, message) VALUES (?, ?)`)
-	if err != nil {
-		return nil, err
-	}
-	for idx := range totalRecordCount {
-		buf := sha256.Sum256([]byte(strconv.Itoa(idx)))
-		_, err = stmt.Exec(idx, fmt.Sprintf("sha256:%x", buf[:]))
+
+	if recordCount > 0 {
+		// fill in some random-looking, but deterministic data
+		stmt, err := db.Prepare(`INSERT INTO entries (id, message) VALUES (?, ?)`)
+		if err != nil {
+			return nil, err
+		}
+		for idx := range recordCount {
+			buf := sha256.Sum256([]byte(strconv.Itoa(idx)))
+			_, err = stmt.Exec(idx, fmt.Sprintf("sha256:%x", buf[:]))
+			if err != nil {
+				return nil, err
+			}
+		}
+		err = stmt.Close()
 		if err != nil {
 			return nil, err
 		}
-	}
-	err = stmt.Close()
-	if err != nil {
-		return nil, err
 	}
 
 	return db, nil
 }
 
+type OblastEntry struct {
+	ID      int    `db:"id,auto"`
+	Message string `db:"message"`
+}
+
+type GorpEntry struct {
+	ID      int    `db:"id"`
+	Message string `db:"message"`
+}
+
 func BenchmarkSelectMany(b *testing.B) {
-	db, err := makeTestDB(b)
+	db, err := makeTestDB(b, totalRecordCountForSelect)
 	if err != nil {
 		b.Fatal(err)
 	}
 
 	// test with different sizes of resultsets (N=1 is an OLTP-like workload,
 	// then the larger N lean more towards the OLAP side of things)
-	for selectedRecordCount := 1; selectedRecordCount < totalRecordCount; selectedRecordCount *= 10 {
+	for selectedRecordCount := 1; selectedRecordCount < totalRecordCountForSelect; selectedRecordCount *= 10 {
 		b.Run("N="+strconv.Itoa(selectedRecordCount), func(b *testing.B) {
 			// prepare the functions that will be benched
-			type record struct {
-				ID      int    `db:"id"`
-				Message string `db:"message"`
-			}
-			store, err := oblast.NewStore[record](
+			store, err := oblast.NewStore[OblastEntry](
 				oblast.SqliteDialect(),
 				oblast.TableNameIs("entries"),
+				oblast.PrimaryKeyIs("id"),
 			)
 			if err != nil {
 				b.Fatal(err)
@@ -91,7 +100,7 @@ func BenchmarkSelectMany(b *testing.B) {
 			}
 
 			selectWithGorp := func(b *testing.B) {
-				var records []record
+				var records []GorpEntry
 				_, err := gdb.Select(&records, query)
 				if err != nil {
 					b.Error(err)
@@ -158,22 +167,19 @@ func BenchmarkSelectMany(b *testing.B) {
 }
 
 func BenchmarkSelectOne(b *testing.B) {
-	db, err := makeTestDB(b)
+	db, err := makeTestDB(b, totalRecordCountForSelect)
 	if err != nil {
 		b.Fatal(err)
 	}
 
 	// grab a "random" record from the DB, not just the first or the last
-	recordID := min(totalRecordCount*2/3, totalRecordCount)
+	recordID := min(totalRecordCountForSelect*2/3, totalRecordCountForSelect)
 
 	// prepare the functions that will be benched
-	type record struct {
-		ID      int    `db:"id"`
-		Message string `db:"message"`
-	}
-	store, err := oblast.NewStore[record](
+	store, err := oblast.NewStore[OblastEntry](
 		oblast.SqliteDialect(),
 		oblast.TableNameIs("entries"),
+		oblast.PrimaryKeyIs("id"),
 	)
 	if err != nil {
 		b.Fatal(err)
@@ -199,7 +205,7 @@ func BenchmarkSelectOne(b *testing.B) {
 	}
 
 	selectWithGorp := func(b *testing.B) {
-		var r record
+		var r GorpEntry
 		err := gdb.SelectOne(&r, query)
 		if err != nil {
 			b.Error(err)
@@ -248,3 +254,86 @@ func BenchmarkSelectOne(b *testing.B) {
 		}
 	})
 }
+
+func BenchmarkInsertAndDeleteOne(b *testing.B) {
+	db, err := makeTestDB(b, 0)
+	if err != nil {
+		b.Fatal(err)
+	}
+
+	// prepare the functions that will be benched
+	store, err := oblast.NewStore[OblastEntry](
+		oblast.SqliteDialect(),
+		oblast.TableNameIs("entries"),
+		oblast.PrimaryKeyIs("id"),
+	)
+	if err != nil {
+		b.Fatal(err)
+	}
+	gdb := gorp.DbMap{Db: db, Dialect: gorp.SqliteDialect{}}
+	gdb.AddTableWithName(GorpEntry{}, "entries").SetKeys(true, "id")
+
+	insertAndDeleteWithOblast := func(b *testing.B) {
+		record := OblastEntry{Message: "hello"}
+		err := store.Insert(db, &record)
+		if err != nil {
+			b.Error(err)
+		}
+		if record.ID == 0 {
+			b.Errorf("ID was not filled!")
+		}
+		err = store.Delete(db, record)
+		if err != nil {
+			b.Error(err)
+		}
+	}
+	insertAndDeleteWithGorp := func(b *testing.B) {
+		record := GorpEntry{Message: "hello"}
+		err := gdb.Insert(&record)
+		if err != nil {
+			b.Error(err)
+		}
+		if record.ID == 0 {
+			b.Errorf("ID was not filled!")
+		}
+		_, err = gdb.Delete(&record)
+		if err != nil {
+			b.Error(err)
+		}
+	}
+	insertAndDeleteWithSqlite := func(b *testing.B) {
+		result, err := db.Exec(`INSERT INTO entries (message) VALUES (?)`, "hello")
+		if err != nil {
+			b.Error(err)
+		}
+		id, err := result.LastInsertId()
+		if err != nil {
+			b.Error(err)
+		}
+		_, err = db.Exec(`DELETE FROM entries WHERE id = ?`, id)
+		if err != nil {
+			b.Error(err)
+		}
+	}
+
+	// run once to prewarm caches
+	insertAndDeleteWithOblast(b)
+	insertAndDeleteWithGorp(b)
+
+	b.Run("via Gorp", func(b *testing.B) {
+		for range b.N {
+			insertAndDeleteWithGorp(b)
+		}
+	})
+	b.Run("via Oblast", func(b *testing.B) {
+		// TODO: extremely bad results for the insert/delete benchmark -> investigate
+		for range b.N {
+			insertAndDeleteWithOblast(b)
+		}
+	})
+	b.Run("via SQLite", func(b *testing.B) {
+		for range b.N {
+			insertAndDeleteWithSqlite(b)
+		}
+	})
+}
diff --git a/internal/plan.go b/internal/plan.go
index 7dc3361..f619a5f 100644
--- a/internal/plan.go
+++ b/internal/plan.go
@@ -22,8 +22,12 @@ type Plan struct {
 	// Argument for reflect.Value.FieldByIndex() for each column name.
 	IndexByColumnName map[string][]int
 
+	// In dialects with UsesLastInsertID() == true, whether the ID column must be written with reflect.Value.SetInt() or reflect.Value.SetUint().
+	FillIDWithSetUint bool
+	FillIDWithSetInt  bool
+
 	// Planned queries.
-	Select PlannedQuery // only `SELECT ... FROM ...` without WHERE or any of the other clauses
+	Select PlannedQuery // only `SELECT ... FROM ... WHERE `; user supplies the rest during Select{,One}Where()
 	Insert PlannedQuery
 	Update PlannedQuery
 	Delete PlannedQuery
@@ -31,12 +35,12 @@ type Plan struct {
 
 // PlannedQuery appears in type Plan.
 type PlannedQuery struct {
-	// Empty if the respective query type is not supported by this Plan
-	// for lack of the required marker types.
+	// Empty if the respective query type is not supported by this Plan for lack of the required marker types.
 	Query string
-	// Arguments for reflect.Value.FieldByIndex() in the correct order
-	// for the query arguments of the above query.
+	// Arguments for reflect.Value.FieldByIndex() in the correct order for the query arguments of the above query.
 	ArgumentIndexes [][]int
+	// Arguments for reflect.Value.FieldByIndex() in the correct order for the Scan() arguments of the above query.
+	ScanIndexes [][]int
 }
 
 // PlanOpts holds additional arguments to BuildPlan().
@@ -118,12 +122,31 @@ func buildPlan(t reflect.Type, dialect Dialect, opts PlanOpts) (Plan, error) {
 		}
 	}
 
-	// validation: LastInsertID() only works if at most one column is auto-filled
-	if dialect.UsesLastInsertID() && len(p.AutoColumnNames) > 1 {
-		return Plan{}, fmt.Errorf(
-			"multiple columns are marked as auto-filled (%s), but this SQL dialect only supports at most one per table",
-			strings.Join(p.AutoColumnNames, ", "),
-		)
+	// validation: LastInsertID() only works if at most one column is auto-filled, and if that column holds an integer type
+	if dialect.UsesLastInsertID() {
+		switch len(p.AutoColumnNames) {
+		case 0:
+			// nothing to check
+		case 1:
+			columnName := p.AutoColumnNames[0]
+			field := t.FieldByIndex(p.IndexByColumnName[columnName])
+			switch field.Type.Kind() { //nolint:exhaustive // false positive
+			case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
+				p.FillIDWithSetInt = true
+			case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
+				p.FillIDWithSetUint = true
+			default:
+				return Plan{}, fmt.Errorf(
+					"column is marked as auto-filled (%s), but this SQL dialect only supports auto-filling struct fields with integer types",
+					strings.Join(p.AutoColumnNames, ", "),
+				)
+			}
+		default:
+			return Plan{}, fmt.Errorf(
+				"multiple columns are marked as auto-filled (%s), but this SQL dialect only supports at most one per table",
+				strings.Join(p.AutoColumnNames, ", "),
+			)
+		}
 	}
 
 	// prepare query strings
@@ -161,11 +184,11 @@ func (p Plan) buildSelectQueryIfPossible(dialect Dialect) PlannedQuery {
 	}
 
 	var (
-		argumentIndexes   = make([][]int, len(p.AllColumnNames))
+		scanIndexes       = make([][]int, len(p.AllColumnNames))
 		quotedColumnNames = make([]string, len(p.AllColumnNames))
 	)
 	for idx, columnName := range p.AllColumnNames {
-		argumentIndexes[idx] = p.IndexByColumnName[columnName]
+		scanIndexes[idx] = p.IndexByColumnName[columnName]
 		quotedColumnNames[idx] = dialect.QuoteIdentifier(columnName)
 	}
 
@@ -174,7 +197,7 @@ func (p Plan) buildSelectQueryIfPossible(dialect Dialect) PlannedQuery {
 		strings.Join(quotedColumnNames, ", "),
 		dialect.QuoteIdentifier(p.TableName),
 	)
-	return PlannedQuery{query, argumentIndexes}
+	return PlannedQuery{query, nil, scanIndexes}
 }
 
 func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery {
@@ -188,6 +211,7 @@ func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery {
 
 	var (
 		argumentIndexes    = make([][]int, len(nonAutoColumnNames))
+		scanIndexes        [][]int
 		quotedColumnNames  = make([]string, len(nonAutoColumnNames))
 		quotedPlaceholders = make([]string, len(nonAutoColumnNames))
 	)
@@ -196,6 +220,14 @@ func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery {
 		quotedColumnNames[idx] = dialect.QuoteIdentifier(columnName)
 		quotedPlaceholders[idx] = dialect.Placeholder(idx)
 	}
+	if len(p.AutoColumnNames) > 0 {
+		// NOTE: This is filled even if dialect.UsesLastInsertID() is false.
+		// We need this index to find the right value on which to run SetInt() or SetUint().
+		scanIndexes = make([][]int, len(p.AutoColumnNames))
+		for idx, columnName := range p.AutoColumnNames {
+			scanIndexes[idx] = p.IndexByColumnName[columnName]
+		}
+	}
 
 	query := fmt.Sprintf(
 		`INSERT INTO %s (%s) VALUES (%s)`,
@@ -206,7 +238,7 @@ func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery {
 	if len(p.AutoColumnNames) > 0 {
 		query += dialect.InsertSuffixForAutoColumns(p.AutoColumnNames)
 	}
-	return PlannedQuery{query, argumentIndexes}
+	return PlannedQuery{query, argumentIndexes, scanIndexes}
 }
 
 func (p Plan) buildUpdateQueryIfPossible(dialect Dialect) PlannedQuery {
@@ -242,7 +274,7 @@ func (p Plan) buildUpdateQueryIfPossible(dialect Dialect) PlannedQuery {
 		strings.Join(setClauses, ", "),
 		strings.Join(whereClauses, " AND "),
 	)
-	return PlannedQuery{query, slices.Concat(setArgumentIndexes, whereArgumentIndexes)}
+	return PlannedQuery{query, slices.Concat(setArgumentIndexes, whereArgumentIndexes), nil}
 }
 
 func (p Plan) buildDeleteQueryIfPossible(dialect Dialect) PlannedQuery {
@@ -264,5 +296,5 @@ func (p Plan) buildDeleteQueryIfPossible(dialect Dialect) PlannedQuery {
 		dialect.QuoteIdentifier(p.TableName),
 		strings.Join(clauses, " AND "),
 	)
-	return PlannedQuery{query, argumentIndexes}
+	return PlannedQuery{query, argumentIndexes, nil}
 }
diff --git a/internal/plan_test.go b/internal/plan_test.go
index db12943..e692556 100644
--- a/internal/plan_test.go
+++ b/internal/plan_test.go
@@ -76,13 +76,17 @@ func TestQueryConstructionBasic(t *testing.T) {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, `SELECT "ID", "Description", "CreatedAt" FROM "basic_records" WHERE `)
-		assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}})
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
 		assert.Equal(t, plan.Insert.Query, `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES ($1, $2) RETURNING "ID"`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}, {2}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}})
 		assert.Equal(t, plan.Update.Query, `UPDATE "basic_records" SET "Description" = $1, "CreatedAt" = $2 WHERE "ID" = $3`)
 		assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}})
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 		assert.Equal(t, plan.Delete.Query, `DELETE FROM "basic_records" WHERE "ID" = $1`)
 		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}})
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 
 	t.Run("SqliteDialect", func(t *testing.T) {
@@ -91,13 +95,17 @@ func TestQueryConstructionBasic(t *testing.T) {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, `SELECT "ID", "Description", "CreatedAt" FROM "basic_records" WHERE `)
-		assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}})
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
 		assert.Equal(t, plan.Insert.Query, `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES (?, ?)`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}, {2}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}})
 		assert.Equal(t, plan.Update.Query, `UPDATE "basic_records" SET "Description" = ?, "CreatedAt" = ? WHERE "ID" = ?`)
 		assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}})
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 		assert.Equal(t, plan.Delete.Query, `DELETE FROM "basic_records" WHERE "ID" = ?`)
 		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}})
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 }
 
@@ -116,13 +124,17 @@ func TestQueryConstructionWithoutPrimaryKey(t *testing.T) {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `)
-		assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}})
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}})
 		assert.Equal(t, plan.Insert.Query, `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES ($1, $2)`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, nil)
 		assert.Equal(t, plan.Update.Query, "")
 		assert.DeepEqual(t, plan.Update.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 		assert.Equal(t, plan.Delete.Query, "")
 		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 
 	t.Run("SqliteDialect", func(t *testing.T) {
@@ -131,13 +143,17 @@ func TestQueryConstructionWithoutPrimaryKey(t *testing.T) {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `)
-		assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}})
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}})
 		assert.Equal(t, plan.Insert.Query, `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?)`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, nil)
 		assert.Equal(t, plan.Update.Query, "")
 		assert.DeepEqual(t, plan.Update.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 		assert.Equal(t, plan.Delete.Query, "")
 		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 }
 
@@ -157,12 +173,16 @@ func TestQueryConstructionImpossble(t *testing.T) {
 
 			assert.Equal(t, plan.Select.Query, "")
 			assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+			assert.DeepEqual(t, plan.Select.ScanIndexes, nil)
 			assert.Equal(t, plan.Insert.Query, "")
 			assert.DeepEqual(t, plan.Insert.ArgumentIndexes, nil)
+			assert.DeepEqual(t, plan.Insert.ScanIndexes, nil)
 			assert.Equal(t, plan.Update.Query, "")
 			assert.DeepEqual(t, plan.Update.ArgumentIndexes, nil)
+			assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 			assert.Equal(t, plan.Delete.Query, "")
 			assert.DeepEqual(t, plan.Delete.ArgumentIndexes, nil)
+			assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 		}
 	}
 
@@ -187,13 +207,17 @@ func TestQueryConstructionWithMultiplePrimaryKeyColumns(t *testing.T) {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, `SELECT "group_id", "name", "created_at" FROM "complex_records" WHERE `)
-		assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}})
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
 		assert.Equal(t, plan.Insert.Query, `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES ($1, $2, $3)`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}, {2}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, nil)
 		assert.Equal(t, plan.Update.Query, `UPDATE "complex_records" SET "created_at" = $1 WHERE "group_id" = $2 AND "name" = $3`)
 		assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{2}, {0}, {1}})
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 		assert.Equal(t, plan.Delete.Query, `DELETE FROM "complex_records" WHERE "group_id" = $1 AND "name" = $2`)
 		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}, {1}})
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 
 	t.Run("SqliteDialect", func(t *testing.T) {
@@ -202,13 +226,17 @@ func TestQueryConstructionWithMultiplePrimaryKeyColumns(t *testing.T) {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, `SELECT "group_id", "name", "created_at" FROM "complex_records" WHERE `)
-		assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}})
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
 		assert.Equal(t, plan.Insert.Query, `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES (?, ?, ?)`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}, {2}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, nil)
 		assert.Equal(t, plan.Update.Query, `UPDATE "complex_records" SET "created_at" = ? WHERE "group_id" = ? AND "name" = ?`)
 		assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{2}, {0}, {1}})
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 		assert.Equal(t, plan.Delete.Query, `DELETE FROM "complex_records" WHERE "group_id" = ? AND "name" = ?`)
 		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}, {1}})
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 }
 
@@ -229,13 +257,17 @@ func TestQueryConstructionWithMultipleAutoColumns(t *testing.T) {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, `SELECT "id", "name", "created_at" FROM "autogenerated_records" WHERE `)
-		assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}})
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
 		assert.Equal(t, plan.Insert.Query, `INSERT INTO "autogenerated_records" ("name") VALUES ($1) RETURNING "id", "created_at"`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}, {2}})
 		assert.Equal(t, plan.Update.Query, `UPDATE "autogenerated_records" SET "name" = $1, "created_at" = $2 WHERE "id" = $3`)
 		assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}})
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
 		assert.Equal(t, plan.Delete.Query, `DELETE FROM "autogenerated_records" WHERE "id" = $1`)
 		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}})
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 
 	t.Run("SqliteDialect", func(t *testing.T) {
diff --git a/oblast.go b/oblast.go
index 415b6cb..5a46042 100644
--- a/oblast.go
+++ b/oblast.go
@@ -43,6 +43,7 @@ package oblast // import "go.xyrillian.de/oblast"
 import (
 	"database/sql"
 	"errors"
+	"reflect"
 
 	"go.xyrillian.de/oblast/internal"
 )
@@ -79,3 +80,53 @@ var (
 
 // ErrMultipleRows is returned by [Store.SelectOne] if the query returned multiple rows.
 var ErrMultipleRows = errors.New("sql: multiple rows in result set")
+
+// Store is the main interface of this library.
+//
+// It holds information on how to read and write data into record type R,
+// and can also be used to execute autogenerated queries if the respective [PlanOption] values were provided during [NewStore].
+type Store[R any] struct {
+	dialect Dialect
+	plan    internal.Plan
+}
+
+// NewStore initializes a store for record type R.
+// Returns an error if R is not a struct type.
+//
+// For the purpose of loading and storing records (i.e. instances of type R) into the database,
+// this function establishes a mapping between fields of type R and database columns by inspecting the "db" tag.
+// For example:
+//
+//	type MyRecord struct {
+//		ID     int64  `db:"record_id,auto"`
+//		Foo    string `db:"foo"`
+//		Bar    string
+//		Cache  map[string]any `db:"-"`
+//		action func()
+//	}
+//
+// In this type:
+//   - The fields "ID" and "Foo" correspond to the database columns "record_id" and "foo" because of the declaration in the "db" tag.
+//   - The field "Bar" corresponds to the database column "Bar" because, when no "db" tag is given, the column name is set equal to the field name.
+//   - The field "Cache" is not mapped to any database column because it is declared with a "db" tag of "-". Loads and stores will ignore it.
+//   - The field "action" is private, so loads and stores will ignore it, too.
+//
+// Besides the declaration of a column name, the following extra tags are understood (as a comma-separated list following the column name):
+//   - "auto": During [Store.Insert], do not store this field's value. Instead, the database will auto-generate a value, which will be read back into the record.
+func NewStore[R any](dialect Dialect, opts ...PlanOption) (Store[R], error) {
+	var popts internal.PlanOpts
+	for _, opt := range opts {
+		opt(&popts)
+	}
+	plan, err := internal.BuildPlan(reflect.TypeFor[R](), dialect, popts)
+	return Store[R]{dialect, plan}, err
+}
+
+// MustNewStore is like [NewStore], but panics on error.
+func MustNewStore[R any](dialect Dialect, opts ...PlanOption) Store[R] {
+	store, err := NewStore[R](dialect, opts...)
+	if err != nil {
+		panic(err.Error())
+	}
+	return store
+}
diff --git a/query.go b/query.go
new file mode 100644
index 0000000..8b0d2cd
--- /dev/null
+++ b/query.go
@@ -0,0 +1,120 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package oblast
+
+import (
+	"errors"
+	"fmt"
+	"reflect"
+)
+
+// Insert executes an SQL INSERT statement for each of the provided records.
+//
+// Fields that are declared with the "auto" tag will not be written into the DB,
+// and instead their value (as auto-generated by the DB on insert) will be placed in the record.
+//
+// Returns an error if [NewStore] was called without the [TableNameIs] option, which is required to generate a query for this method.
+func (s Store[R]) Insert(db Handle, records ...*R) (returnedError error) {
+	// NOTE: This function body should be as short as possible to reduce the binary size after monomorphization.
+	//       Any expression that does not depend on type R should be factored out into a reusable function.
+	// TODO: minimize
+
+	if s.plan.Insert.Query == "" {
+		return errors.New("cannot execute Insert() because query could not be autogenerated")
+	}
+
+	var (
+		argumentIndexes = s.plan.Insert.ArgumentIndexes
+		argumentSlots   = make([]any, len(argumentIndexes))
+		scanIndexes     = s.plan.Insert.ScanIndexes
+		scanSlots       []any
+	)
+	if len(scanIndexes) > 0 {
+		scanSlots = make([]any, len(scanIndexes))
+	}
+
+	stmt, err := db.Prepare(s.plan.Insert.Query)
+	if err != nil {
+		return fmt.Errorf("during Prepare(): %w", err)
+	}
+	defer func() {
+		returnedError = mergeCloseError("Stmt", returnedError, stmt.Close())
+	}()
+
+	for idx, r := range records {
+		v := reflect.ValueOf(r).Elem()
+		for idx, index := range argumentIndexes {
+			argumentSlots[idx] = v.FieldByIndex(index).Addr().Interface()
+		}
+
+		if s.dialect.UsesLastInsertID() {
+			result, err := stmt.Exec(argumentSlots...)
+			if err != nil {
+				return fmt.Errorf("during Exec() for record with idx = %d: %w", idx, err)
+			}
+			id, err := result.LastInsertId()
+			if err != nil {
+				return fmt.Errorf("during LastInsertId() for record with idx = %d: %w", idx, err)
+			}
+			if s.plan.FillIDWithSetInt {
+				v.FieldByIndex(scanIndexes[0]).SetInt(id)
+			} else if s.plan.FillIDWithSetUint {
+				if id < 0 {
+					return fmt.Errorf("LastInsertId() = %d for record with idx = %d cannot be converted to uint", id, idx)
+				}
+				v.FieldByIndex(scanIndexes[0]).SetUint(uint64(id))
+			}
+		} else {
+			for idx, index := range scanIndexes {
+				scanSlots[idx] = v.FieldByIndex(index).Addr().Interface()
+			}
+			err := stmt.QueryRow(argumentSlots...).Scan(scanSlots...)
+			if err != nil {
+				return fmt.Errorf("during QueryRow() for record with idx = %d: %w", idx, err)
+			}
+		}
+	}
+
+	return nil
+}
+
+// TODO: update
+
+// Delete executes an SQL DELETE statement for each of the provided records, using their primary keys to locate the respective table rows.
+//
+// Returns an error if [NewStore] was called without the [TableNameIs] or [PrimaryKeyIs] options, which are both required to generate a query for this method.
+func (s Store[R]) Delete(db Handle, records ...R) (returnedError error) {
+	// NOTE: This function body should be as short as possible to reduce the binary size after monomorphization.
+	//       Any expression that does not depend on type R should be factored out into a reusable function.
+	// TODO: minimize
+
+	if s.plan.Delete.Query == "" {
+		return errors.New("cannot execute Delete() because query could not be autogenerated")
+	}
+
+	var (
+		argumentIndexes = s.plan.Delete.ArgumentIndexes
+		argumentSlots   = make([]any, len(argumentIndexes))
+	)
+
+	stmt, err := db.Prepare(s.plan.Delete.Query)
+	if err != nil {
+		return fmt.Errorf("during Prepare(): %w", err)
+	}
+	defer func() {
+		returnedError = mergeCloseError("Stmt", returnedError, stmt.Close())
+	}()
+
+	for idx, r := range records {
+		v := reflect.ValueOf(&r).Elem()
+		for idx, index := range argumentIndexes {
+			argumentSlots[idx] = v.FieldByIndex(index).Addr().Interface()
+		}
+		_, err := stmt.Exec(argumentSlots...)
+		if err != nil {
+			return fmt.Errorf("during Exec() for record with idx = %d: %w", idx, err)
+		}
+	}
+	return nil
+}
diff --git a/select.go b/select.go
index 6616434..d839c13 100644
--- a/select.go
+++ b/select.go
@@ -25,7 +25,7 @@ func (s Store[R]) Select(db Handle, query string, args ...any) (result []R, retu
 		return nil, err
 	}
 	defer func() {
-		returnedError = mergeRowsCloseError(returnedError, rows.Close())
+		returnedError = mergeCloseError("Rows", returnedError, rows.Close())
 	}()
 
 	slots := make([]any, len(indexes))
@@ -63,7 +63,7 @@ func (s Store[R]) SelectWhere(db Handle, partialQuery string, args ...any) (resu
 		return nil, err
 	}
 	defer func() {
-		returnedError = mergeRowsCloseError(returnedError, rows.Close())
+		returnedError = mergeCloseError("Rows", returnedError, rows.Close())
 	}()
 
 	slots := make([]any, len(indexes))
@@ -114,11 +114,11 @@ func startSelectQuery(db Handle, plan internal.Plan, query string, args ...any)
 
 func startSelectWhereQuery(db Handle, plan internal.Plan, partialQuery string, args ...any) (rows *sql.Rows, indexes [][]int, err error) {
 	if plan.Select.Query == "" {
-		return nil, nil, errors.New("cannot execute SelectWhere() because SELECT query could not be autogenerated")
+		return nil, nil, errors.New("cannot execute SelectWhere() because query could not be autogenerated")
 	}
 	query := plan.Select.Query + partialQuery
 	rows, err = db.Query(query, args...)
-	return rows, plan.Select.ArgumentIndexes, err
+	return rows, plan.Select.ScanIndexes, err
 }
 
 func collectRow(rows *sql.Rows, v reflect.Value, slots []any, indexes [][]int) error {
@@ -132,14 +132,14 @@ func collectRow(rows *sql.Rows, v reflect.Value, slots []any, indexes [][]int) e
 	return nil
 }
 
-func mergeRowsCloseError(err, closeErr error) error {
+func mergeCloseError(typeName string, err, closeErr error) error {
 	switch {
 	case closeErr == nil:
 		return err
 	case err == nil:
-		return fmt.Errorf("during rows.Close(): %w", closeErr)
+		return fmt.Errorf("during %s.Close(): %w", typeName, closeErr)
 	default:
-		return fmt.Errorf("%w (additional error during rows.Close(): %s)", err, closeErr.Error())
+		return fmt.Errorf("%w (additional error during %s.Close(): %s)", err, typeName, closeErr.Error())
 	}
 }
 
@@ -184,11 +184,11 @@ func (s Store[R]) SelectOneWhere(db Handle, partialQuery string, args ...any) (r
 
 func selectOneWhere(db Handle, plan internal.Plan, v reflect.Value, partialQuery string, args []any) error {
 	if plan.Select.Query == "" {
-		return errors.New("cannot execute SelectOneWhere() because SELECT query could not be autogenerated")
+		return errors.New("cannot execute SelectOneWhere() because query could not be autogenerated")
 	}
 	query := plan.Select.Query + partialQuery
-	slots := make([]any, len(plan.Select.ArgumentIndexes))
-	for idx, index := range plan.Select.ArgumentIndexes {
+	slots := make([]any, len(plan.Select.ScanIndexes))
+	for idx, index := range plan.Select.ScanIndexes {
 		slots[idx] = v.FieldByIndex(index).Addr().Interface()
 	}
 	return db.QueryRow(query, args...).Scan(slots...)
diff --git a/store.go b/store.go
deleted file mode 100644
index 4ab0f4b..0000000
--- a/store.go
+++ /dev/null
@@ -1,35 +0,0 @@
-// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
-// SPDX-License-Identifier: Apache-2.0
-
-package oblast
-
-import (
-	"reflect"
-
-	"go.xyrillian.de/oblast/internal"
-)
-
-// Store is the main interface of this library.
-// It holds information on how to read and write data into record type R, and can also be used to execute autogenerated queries if the respective [PlanOption]s were provided during [NewStore].
-type Store[R any] struct {
-	plan internal.Plan
-}
-
-// NewStore initializes a store for record type R.
-func NewStore[R any](dialect Dialect, opts ...PlanOption) (Store[R], error) {
-	var popts internal.PlanOpts
-	for _, opt := range opts {
-		opt(&popts)
-	}
-	plan, err := internal.BuildPlan(reflect.TypeFor[R](), dialect, popts)
-	return Store[R]{plan}, err
-}
-
-// MustNewStore is like [NewStore], but panics on error.
-func MustNewStore[R any](dialect Dialect, opts ...PlanOption) Store[R] {
-	store, err := NewStore[R](dialect, opts...)
-	if err != nil {
-		panic(err.Error())
-	}
-	return store
-}