Mutations

Soy provides three mutation builders: Insert(), Modify(), and Remove(). Each includes safety features to prevent common mistakes.

See API Reference for complete method signatures.

INSERT Operations

Basic Insert

user := &User{
    Email: "alice@example.com",
    Name:  "Alice",
    Age:   30,
}

created, err := users.Insert().Exec(ctx, user)
// created has the database-generated ID
fmt.Println(created.ID)

INSERT automatically includes RETURNING * to populate generated columns.

Batch Insert

newUsers := []*User{
    {Email: "bob@example.com", Name: "Bob", Age: 25},
    {Email: "carol@example.com", Name: "Carol", Age: 35},
}

count, err := users.Insert().ExecBatch(ctx, newUsers)
// count is the number of records inserted (int64)

ON CONFLICT - Do Nothing

Silently skip conflicts:

user := &User{Email: "alice@example.com", Name: "Alice"}

created, err := users.Insert().
    OnConflict("email").
    DoNothing().
    Exec(ctx, user)

// created is nil if conflict occurred

ON CONFLICT - Do Update (Upsert)

Update on conflict:

user := &User{Email: "alice@example.com", Name: "Alice Updated", Age: 31}

upserted, err := users.Insert().
    OnConflict("email").
    DoUpdate().
    Set("name", "name").
    Set("age", "age").
    Build().
    Exec(ctx, user)

INSERT INTO users (email, name, age) VALUES (:email, :name, :age)
ON CONFLICT (email) DO UPDATE SET name = :name, age = :age
RETURNING *

Multiple Conflict Columns

users.Insert().
    OnConflict("tenant_id", "email").
    DoUpdate().
    Set("name", "name").
    Build()

UPDATE Operations

Basic Update

updated, err := users.Modify().
    Set("name", "new_name").
    Set("age", "new_age").
    Where("id", "=", "user_id").
    Exec(ctx, map[string]any{
        "new_name": "Alice Smith",
        "new_age":  31,
        "user_id":  123,
    })

UPDATE returns the updated record via RETURNING *.

Required WHERE

UPDATE requires at least one WHERE condition. This prevents accidental full-table updates:

// Error: UPDATE requires WHERE clause
users.Modify().Set("status", "inactive").Exec(ctx, params)

// Correct
users.Modify().
    Set("status", "inactive").
    Where("id", "=", "user_id").
    Exec(ctx, params)

Computed Assignments

Use SetExpr for atomic increments, decrements, and other expression-based updates:

updated, err := users.Modify().
    SetExpr("login_count", "+", "increment").
    Where("id", "=", "user_id").
    Exec(ctx, map[string]any{
        "increment": 1,
        "user_id":   123,
    })

UPDATE users SET "login_count" = "login_count" + :increment WHERE "id" = :user_id RETURNING *

SetExpr can be combined with Set in the same query:

users.Modify().
    Set("name", "new_name").
    SetExpr("age", "+", "increment").
    Where("id", "=", "user_id").
    Exec(ctx, params)

Supported arithmetic operators: +, -, *, /, %.

Multiple Conditions

users.Modify().
    Set("verified", "verified").
    Where("email", "=", "email").
    WhereNull("verified_at").
    Exec(ctx, map[string]any{
        "verified": true,
        "email":    "alice@example.com",
    })

Batch Update

Update multiple records with different values:

updates := []map[string]any{
    {"user_id": 1, "new_score": 100},
    {"user_id": 2, "new_score": 200},
    {"user_id": 3, "new_score": 300},
}

rowsAffected, err := users.Modify().
    Set("score", "new_score").
    Where("id", "=", "user_id").
    ExecBatch(ctx, updates)

DELETE Operations

Basic Delete

rowsDeleted, err := users.Remove().
    Where("id", "=", "user_id").
    Exec(ctx, map[string]any{"user_id": 123})

DELETE returns the number of affected rows.

Required WHERE

DELETE requires at least one WHERE condition. This prevents accidental table truncation:

// Error: DELETE requires WHERE clause
users.Remove().Exec(ctx, nil)

// Correct
users.Remove().Where("status", "=", "status").Exec(ctx, map[string]any{"status": "deleted"})

Multiple Conditions

users.Remove().
    Where("status", "=", "status").
    Where("created_at", "<", "cutoff").
    Exec(ctx, map[string]any{
        "status": "inactive",
        "cutoff": time.Now().AddDate(0, -6, 0),
    })

Batch Delete

Delete with different conditions per batch:

deletions := []map[string]any{
    {"user_id": 1},
    {"user_id": 2},
    {"user_id": 3},
}

totalDeleted, err := users.Remove().
    Where("id", "=", "user_id").
    ExecBatch(ctx, deletions)

Lifecycle Callbacks

Register callbacks to intercept records before writes or after scans. See the Lifecycle Guide for full details.

// Validate before insert
users.OnRecord(func(ctx context.Context, record *User) error {
    if record.Email == "" {
        return errors.New("email is required")
    }
    return nil
})

// Normalize after scan (fires on INSERT RETURNING, UPDATE RETURNING)
users.OnScan(func(ctx context.Context, result *User) error {
    result.Email = strings.ToLower(result.Email)
    return nil
})

Transactions

Execute mutations within transactions:

tx, err := db.BeginTxx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

// Insert order
order, err := orders.Insert().Build().ExecTx(ctx, tx, newOrder)
if err != nil {
    return err
}

// Insert order items
for _, item := range items {
    item.OrderID = order.ID
    _, err := orderItems.Insert().Build().ExecTx(ctx, tx, item)
    if err != nil {
        return err
    }
}

// Update inventory
_, err = inventory.Modify().
    Set("quantity", "new_qty").
    Where("product_id", "=", "product_id").
    ExecTx(ctx, tx, params)
if err != nil {
    return err
}

return tx.Commit()

Error Handling

Mutations capture builder errors and report them at execution:

result, err := users.Modify().
    Set("invalid_field", "value").  // Error captured
    Where("id", "=", "id").
    Exec(ctx, params)

// err: field "invalid_field" not in schema

Inspecting SQL

Use Render() to see generated SQL:

result, err := users.Modify().
    Set("name", "new_name").
    Where("id", "=", "user_id").
    Render()

fmt.Println(result.SQL)
// UPDATE users SET name = :new_name WHERE id = :user_id RETURNING *

Complete Example

// Upsert with transaction
func UpsertUser(ctx context.Context, db *sqlx.DB, user *User) (*User, error) {
    tx, err := db.BeginTxx(ctx, nil)
    if err != nil {
        return nil, err
    }
    defer tx.Rollback()

    // Try insert with conflict handling
    result, err := users.Insert().
        OnConflict("email").
        DoUpdate().
        Set("name", "name").
        Set("age", "age").
        Set("updated_at", "now").
        Build().
        ExecTx(ctx, tx, map[string]any{
            "email": user.Email,
            "name":  user.Name,
            "age":   user.Age,
            "now":   time.Now(),
        })
    if err != nil {
        return nil, err
    }

    // Log the change
    _, err = auditLogs.Insert().Build().ExecTx(ctx, tx, &AuditLog{
        UserID:    result.ID,
        Action:    "upsert",
        Timestamp: time.Now(),
    })
    if err != nil {
        return nil, err
    }

    if err := tx.Commit(); err != nil {
        return nil, err
    }

    return result, nil
}

Dialect-Specific Behaviour

Different databases have varying levels of support for mutation features. Soy automatically adapts to each dialect's capabilities.

Feature Support Matrix

* MSSQL uses UPDATE-then-INSERT fallback for upsert operations. ** MariaDB tracks MDEV-5092 for future support.

MSSQL Upsert Fallback

MSSQL doesn't support ON CONFLICT syntax. When you use OnConflict() with MSSQL, Soy automatically:

1. Attempts an UPDATE using the conflict columns as WHERE conditions
2. If no rows are affected, performs an INSERT
3. Returns the resulting record via a SELECT

// This works transparently across all dialects
result, err := users.Insert().
    OnConflict("email").
    DoUpdate().
    Set("name", "name").
    Build().
    Exec(ctx, user)

MariaDB UPDATE Behaviour

MariaDB doesn't support RETURNING on UPDATE statements. When executing Modify():

1. The UPDATE is executed without RETURNING
2. A follow-up SELECT retrieves the updated record using the WHERE conditions

This is handled automatically - your code remains the same across dialects.