Insert
Insert rows using the shared DB API and understand driver-specific insert behavior.
Use db.Insert when you want to execute an insert statement.
Insert is a semantic wrapper around Exec.
It does not generate SQL, inspect the statement, or behave like an ORM. It only makes application code easier to read.
result, err := db.Insert(ctx, conn, insertUserQuery, name, email, active)Insert behavior differs between database engines.
The shared db API stays the same, but generated IDs, placeholders, and data modeling patterns are driver-specific.
Overview
| Driver | Common pattern |
|---|---|
| MySQL | db.Insert and read result.LastInsertId() |
| Postgres | INSERT ... RETURNING id and read the ID with db.Value |
| Scylla | Generate IDs in application code and insert into query tables |
MySQL Insert
MySQL commonly returns the generated auto-increment ID through LastInsertId.
const insertUserQuery = `
INSERT INTO users (name, email, active)
VALUES (?, ?, ?)
`Use db.Insert:
func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (db.Result, error) {
return db.Insert(ctx, conn, insertUserQuery, name, email, active)
}Usage:
result, err := InsertUser(ctx, conn, "Jane Doe", "jane.doe@example.com", true)
if err != nil {
return err
}
fmt.Printf(
"inserted user id=%d rows_affected=%d\n",
result.LastInsertId(),
result.RowsAffected(),
)MySQL uses ? placeholders.
PostgreSQL Insert With RETURNING
PostgreSQL commonly uses RETURNING when you need the generated ID.
const insertUserQuery = `
INSERT INTO users (name, email, active)
VALUES ($1, $2, $3)
RETURNING id
`Use db.Value to read the returned ID.
func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (db.Result, error) {
return db.Insert(ctx, conn, insertUserQuery, name, email, active)
}Usage:
result, err := InsertUser(ctx, conn, "Alice Doe", "alice.doe@example.com", true)
if err != nil {
log.Fatal(err)
}
fmt.Printf("inserted user id=%d rows_affected=%d\n", result.LastInsertId(), result.RowsAffected())PostgreSQL uses numbered placeholders such as $1, $2, and $3.
PostgreSQL Insert Without RETURNING
If you do not need a generated value, you can also use db.Insert.
result, err := db.Insert(ctx, conn, `
INSERT INTO audit_logs (message)
VALUES ($1)
`, message)
if err != nil {
return err
}
fmt.Println(result.RowsAffected())For generated IDs, prefer RETURNING with db.Value.
Scylla Insert
Scylla data models are often query-driven.
A single logical entity may be written into multiple query tables.
For example:
const insertUserByIDQuery = `
INSERT INTO users_by_id (id, email, name, active, created_at)
VALUES (?, ?, ?, ?, ?)
`
const insertUserByEmailQuery = `
INSERT INTO users_by_email (email, id, name, active, created_at)
VALUES (?, ?, ?, ?, ?)
`Generate the ID in application code and write both query tables.
func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (string, error) {
id := gocql.TimeUUID()
createdAt := time.Now().UTC()
if _, err := db.Insert(ctx, conn, insertUserByIDQuery, id, email, name, active, createdAt); err != nil {
return "", err
}
if _, err := db.Insert(ctx, conn, insertUserByEmailQuery, email, id, name, active, createdAt); err != nil {
return "", err
}
return id.String(), nil
}Scylla uses ? placeholders.
This pattern makes reads efficient by writing data into the tables required by your queries.
Scylla Batches
If multiple writes must be grouped, the Scylla driver also supports batches.
Use batches carefully and only when the data model requires grouped writes.
Batch behavior is documented in the Scylla Batches guide.
Insert vs Exec
Insert is equivalent to Exec.
result, err := db.Insert(ctx, conn, query, args...)is equivalent to:
result, err := db.Exec(ctx, conn, query, args...)Use Insert when you want the code to communicate insert intent.
Use Exec when the statement is generic.
Returning Values From Inserts
Use the API that matches your database behavior.
| Need | Recommended API |
|---|---|
| MySQL generated ID | db.Insert + result.LastInsertId() |
| Postgres generated ID | INSERT ... RETURNING id + db.Value |
| Scylla generated ID | generate ID in application code |
| Insert without returned value | db.Insert |
| Dialect-selected insert | db.Dialect + db.ExecQuery or db.ValueQuery |
Dialect Insert
For applications that support multiple drivers, keep insert SQL in db.DialectSQL.
Example:
type Queries struct {
InsertUser db.DialectSQL `json:"InsertUser"`
}Then select the correct query for the active driver:
q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
return err
}
result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
return err
}For PostgreSQL inserts that return an ID, use ValueQuery instead:
q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
return 0, err
}
id, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
return 0, err
}
if !found {
return 0, errors.New("insert did not return id")
}
return id, nilRecommended Style
Use explicit SQL and choose the insert pattern based on the driver.
For MySQL:
result, err := db.Insert(ctx, conn, insertUserQuery, name, email, active)For PostgreSQL when an ID is needed:
result, found, err := db.InsertValue(ctx, conn, insertUserQuery, name, email, active)For Scylla:
id := gocql.TimeUUID()
_, err := db.Insert(ctx, conn, insertUserByIDQuery, id, email, name, active, createdAt)Keep shared application code on db.Conn, but keep SQL behavior explicit.