Writing Data

Each write statement has its own builder. Exec(ctx) runs it and returns the rows affected (int64). ToSQL() returns the statement instead of running it, so the same code works with db.Use(...).

Start with	Builder	Accepts
conn.Insert("t")	*db.InsertBuilder	Values, Rows, Ignore, Using, Upsert, Returning, With*
conn.Update("t")	*db.UpdateBuilder	Set*, Increment*, Where*, Join*, OrderBy*, Limit, Returning, AllRows, With*
conn.Delete("t")	*db.DeleteBuilder	Where*, Join*, OrderBy*, Limit, Returning, AllRows, With*

Updates and deletes do not accept GroupBy, Having, Union, locks or Offset. Inserts do not accept Where, Join, OrderBy or Limit.

Truncate and UpdateOrInsert are methods on *db.DB.

On this page: Insert · Upsert · Update · Increment and decrement · Delete · Truncate · RETURNING · The WHERE guard · Build-only forms

Insert

n, err := conn.Insert("users").
    Values(
        map[string]any{"name": "Eve", "email": "eve@example.com"},
        map[string]any{"name": "Fay", "email": "fay@example.com"},
    ).
    Exec(ctx)
// INSERT INTO "users" ("email", "name") VALUES ($1, $2), ($3, $4)

• Columns are the sorted keys of the first row, so SQL is deterministic.
• Every row must have exactly the same keys, otherwise db.ErrInvalidArgs.
• Structs work too: Rows(User{...}, User{...}) reads columns from db tags. See Struct Mapping. Values and Rows append.
• No rows is a successful no-op for Exec (0, nil). ToSQL returns db.ErrNoValues.
• One empty map inserts defaults: INSERT INTO "t" DEFAULT VALUES (() VALUES () on MySQL).
• A value may be db.Raw("now()") (inlined) or a *db.SelectBuilder (scalar subquery).

Insert and get the id

id, err := conn.Insert("users").Values(map[string]any{"name": "Gus"}).ExecGetID(ctx)
id, err  = conn.Insert("users").Values(row).ExecGetID(ctx, "user_id") // PostgreSQL: another id column

PostgreSQL appends RETURNING "id" (or your column). MySQL and SQLite read the driver's last insert id. The id must be an integer.

Insert and ignore conflicts

n, err := conn.Insert("users").Values(map[string]any{"email": "ann@example.com"}).Ignore().Exec(ctx)

Dialect	SQL
PostgreSQL	INSERT ... ON CONFLICT DO NOTHING
MySQL, MariaDB	INSERT IGNORE INTO ...
SQLite	INSERT OR IGNORE INTO ...

The count is rows actually inserted, so a skipped conflict reports 0.

Insert from a query

n, err := conn.Insert("archive").
    Using([]string{"id", "name"}, conn.Select("id", "name").From("users").Where("active", false)).
    Exec(ctx)
// INSERT INTO "archive" ("id", "name") SELECT "id", "name" FROM "users" WHERE "active" = $1

An empty column list, or []string{"*"}, omits the column list. Add .Ignore() to skip conflicts.

Upsert

rows := []map[string]any{
    {"email": "ann@example.com", "name": "Ann"},
    {"email": "bob@example.com", "name": "Bob"},
}
n, err := conn.Insert("users").Values(rows...).Upsert([]string{"email"}, []string{"name"}).Exec(ctx)

Argument	Meaning
rows	Set with Values or Rows, as for any insert
uniqueBy	The conflict target. Required on PostgreSQL and SQLite, ignored on MySQL
update	Columns that take the inserted value on conflict. nil means every inserted column. An empty non-nil slice is an error

PostgreSQL and SQLite render ON CONFLICT ("email") DO UPDATE SET "name" = "excluded"."name". MySQL renders ON DUPLICATE KEY UPDATE `name` = VALUES(`name`).

MySQL reports 2 affected rows for each row it updated, not 1.

Update

n, err := conn.Update("users").
    SetMap(map[string]any{"name": "Anna", "age": 31}).
    Where("id", 7).
    Exec(ctx)
// UPDATE "users" SET "age" = $1, "name" = $2 WHERE "id" = $3

n, err = conn.Update("users").Set("name", "Anna").Set("age", 31).Where("id", 7).Exec(ctx)
// UPDATE "users" SET "name" = $1, "age" = $2 WHERE "id" = $3

• SetMap sorts the columns. Set keeps your call order, and setting a column again replaces its value.
• Values may be db.Raw(...) or a *db.SelectBuilder subquery.
• SetRow(struct) takes columns from a struct, see Struct Mapping.
• Placeholders are numbered in statement order: values first, then WHERE arguments.
• Without a WHERE or LIMIT, Exec and ToSQL are refused, see the guard.
• An update with nothing to set is db.ErrNoValues.

Update with joins or a limit

conn.Update("users as u").
    Join("orders as o", "o.user_id", "u.id").
    Where("o.total", ">", 100).
    Set("u.vip", true).
    Exec(ctx)

conn.Update("jobs").Where("state", "queued").OrderBy("id").Limit(10).Set("state", "taken").Exec(ctx)

Dialect	Behaviour
MySQL, MariaDB	Native UPDATE ... JOIN ... SET. ORDER BY ... LIMIT works on a single table. Join plus ORDER BY/LIMIT returns ErrUnsupported rather than dropping the limit and touching every joined row
PostgreSQL	Rewritten to WHERE "ctid" IN (SELECT ...) so join conditions and limit still apply. Qualified SET columns such as "u.vip" lose their qualifier
SQLite	Same idea, using "rowid"

Update from another table (PostgreSQL)

conn.Update("users").
    Join("accounts", "accounts.user_id", "users.id").
    FromJoins().
    Where("accounts.active", true).
    Set("plan", db.Raw(`"accounts"."plan"`)).
    Exec(ctx)
// UPDATE "users" SET "plan" = "accounts"."plan" FROM "accounts"
//   WHERE "accounts"."active" = $1 AND "accounts"."user_id" = "users"."id"

FromJoins() makes the joined tables the FROM list, and join conditions become part of the WHERE. Other dialects return ErrUnsupported (MySQL takes Join directly).

Update or insert

ok, err := conn.UpdateOrInsert(ctx, "users",
    map[string]any{"email": "zed@example.com"},   // attributes to match
    map[string]any{"name": "Zed"},                // values to set
)

Updates the first row matching the attributes, or inserts attributes plus values.

Not atomic. It is two statements. Wrap it in a transaction and keep a unique index if concurrent writers matter, or use Upsert instead.

Increment and decrement

n, err := conn.Update("posts").Increment("views", 1).Where("id", 3).Exec(ctx)
n, err  = conn.Update("posts").Decrement("stock", 2).SetMap(map[string]any{"updated_by": 9}).Where("id", 3).Exec(ctx)
n, err  = conn.Update("posts").IncrementEach(map[string]any{"views": 1, "likes": 5}).Where("id", 3).Exec(ctx)
// UPDATE "posts" SET "views" = "views" + $1 WHERE "id" = $2

• The amount is bound, not inlined, and must be a number (otherwise db.ErrInvalidArgs).
• Chain Set or SetMap to change extra columns in the same statement.
• DecrementEach is the counterpart of IncrementEach.

Delete

n, err := conn.Delete("sessions").Where("expires_at", "<", now).Exec(ctx)

conn.Delete("posts as p").Join("users as u", "u.id", "p.user_id").Where("u.banned", true).Exec(ctx)

Joins and limits work like Update: native on MySQL (DELETE alias FROM ... JOIN ..., or ORDER BY ... LIMIT on a single table; both together are ErrUnsupported), and a ctid/rowid subquery on PostgreSQL and SQLite.

Truncate

err := conn.Truncate(ctx, "events")

Dialect	Statement
PostgreSQL	TRUNCATE "events" RESTART IDENTITY (no CASCADE: referencing tables are never emptied implicitly)
MySQL	TRUNCATE TABLE `events`
SQLite	DELETE FROM sqlite_sequence WHERE name = ?, then DELETE FROM "events"

Truncate is the explicit way to empty a table. An unbounded Delete is refused.

RETURNING

type Row struct {
    ID   int64
    Name string
}

var out []Row
err := conn.Insert("users").Values(map[string]any{"name": "Rae"}).Returning("id", "name").Get(ctx, &out)
err  = conn.Update("users").Set("name", "Rae2").Where("id", 7).Returning("id", "name").Get(ctx, &out)
err  = conn.Delete("users").Where("id", 7).Returning("*").Get(ctx, &out)

• Supported on PostgreSQL and SQLite 3.35+. MySQL and MariaDB return ErrUnsupported.
• The destination is a pointer to a slice, scanned like a select's Get.
• Get without Returning(...) is ErrInvalidArgs.
• Exec ignores returned rows and gives the affected count.

The WHERE guard

An update (including Increment*) or delete returns db.ErrNoWhere from Exec, Get and ToSQL when it has no WHERE and no LIMIT. This stops a forgotten filter from rewriting or deleting a whole table.

_, err := conn.Update("users").Set("active", false).Exec(ctx)
// db.ErrNoWhere

n, err := conn.Update("users").Set("active", false).AllRows().Exec(ctx) // explicitly confirmed

• AllRows() states that every row is meant.
• A Limit(...) also counts as a bound.
• An update with FromJoins() accepts joins in place of a WHERE.

Build-only forms

Every write builder ends in ToSQL() instead of Exec, for db.Use(...), logging or tests.

Run	Build only
Insert(...).Exec(ctx)	Insert(...).ToSQL()
Insert(...).ExecGetID(ctx, seq...)	Insert(...).IDSQL(seq...)
Update(...).Exec(ctx)	Update(...).ToSQL()
Delete(...).Exec(ctx)	Delete(...).ToSQL()
conn.Truncate(ctx, table)	conn.TruncateSQL(table) returns []db.Stmt (SQLite needs two statements)

sql, args, err := db.Use(db.Postgres).Update("users").Set("name", "Anna").Where("id", 7).ToSQL()

The same rules apply, including the WHERE guard and Returning(...).