Upserts

Recipe: Implement insert-or-update with PostgreSQL ON CONFLICT.

ASTQL supports PostgreSQL's ON CONFLICT clause for upsert operations.

Basic Upsert

Insert a row or update if it already exists:

func UpsertUser(instance *astql.ASTQL) (*astql.QueryResult, error) {
    values := instance.ValueMap()
    values[instance.F("email")] = instance.P("email")
    values[instance.F("username")] = instance.P("username")
    values[instance.F("updated_at")] = instance.P("now")

    return astql.Insert(instance.T("users")).
        Values(values).
        OnConflict(instance.F("email")).
        DoUpdate().
        Set(instance.F("username"), instance.P("username")).
        Set(instance.F("updated_at"), instance.P("now")).
        Build().
        Render(postgres.New())
}

// INSERT INTO "users" ("email", "updated_at", "username")
// VALUES (:email, :now, :username)
// ON CONFLICT ("email") DO UPDATE SET "updated_at" = :now, "username" = :username

DO NOTHING

Skip conflicting rows without updating:

func InsertIgnoreDuplicates(instance *astql.ASTQL) (*astql.QueryResult, error) {
    values := instance.ValueMap()
    values[instance.F("email")] = instance.P("email")
    values[instance.F("username")] = instance.P("username")

    return astql.Insert(instance.T("users")).
        Values(values).
        OnConflict(instance.F("email")).
        DoNothing().
        Render(postgres.New())
}

// INSERT INTO "users" ("email", "username")
// VALUES (:email, :username)
// ON CONFLICT ("email") DO NOTHING

Multi-Column Conflict

Handle conflicts on composite unique constraints:

func UpsertUserRole(instance *astql.ASTQL) (*astql.QueryResult, error) {
    values := instance.ValueMap()
    values[instance.F("user_id")] = instance.P("user_id")
    values[instance.F("role_id")] = instance.P("role_id")
    values[instance.F("granted_at")] = instance.P("now")

    return astql.Insert(instance.T("user_roles")).
        Values(values).
        OnConflict(instance.F("user_id"), instance.F("role_id")).
        DoUpdate().
        Set(instance.F("granted_at"), instance.P("now")).
        Build().
        Render(postgres.New())
}

// INSERT INTO "user_roles" ("granted_at", "role_id", "user_id")
// VALUES (:now, :role_id, :user_id)
// ON CONFLICT ("user_id", "role_id") DO UPDATE SET "granted_at" = :now

RETURNING with Upsert

Get the inserted or updated row:

func UpsertUserReturning(instance *astql.ASTQL) (*astql.QueryResult, error) {
    values := instance.ValueMap()
    values[instance.F("email")] = instance.P("email")
    values[instance.F("username")] = instance.P("username")

    return astql.Insert(instance.T("users")).
        Values(values).
        OnConflict(instance.F("email")).
        DoUpdate().
        Set(instance.F("username"), instance.P("username")).
        Build().
        Returning(instance.F("id"), instance.F("created_at"), instance.F("updated_at")).
        Render(postgres.New())
}

// INSERT INTO "users" ("email", "username")
// VALUES (:email, :username)
// ON CONFLICT ("email") DO UPDATE SET "username" = :username
// RETURNING "id", "created_at", "updated_at"

Batch Upsert

Insert multiple rows with conflict handling:

func BatchUpsertProducts(instance *astql.ASTQL, count int) (*astql.QueryResult, error) {
    query := astql.Insert(instance.T("products"))

    // Add multiple value sets
    for i := 0; i < count; i++ {
        values := instance.ValueMap()
        values[instance.F("sku")] = instance.P(fmt.Sprintf("sku_%d", i))
        values[instance.F("name")] = instance.P(fmt.Sprintf("name_%d", i))
        values[instance.F("price")] = instance.P(fmt.Sprintf("price_%d", i))
        query = query.Values(values)
    }

    return query.
        OnConflict(instance.F("sku")).
        DoUpdate().
        Set(instance.F("name"), instance.P("name_0")).  // Uses first row params
        Set(instance.F("price"), instance.P("price_0")).
        Build().
        Render(postgres.New())
}

// INSERT INTO "products" ("name", "price", "sku")
// VALUES (:name_0, :price_0, :sku_0), (:name_1, :price_1, :sku_1), ...
// ON CONFLICT ("sku") DO UPDATE SET "name" = :name_0, "price" = :price_0

Conditional Upsert

Update only certain fields based on values:

func UpsertWithCondition(instance *astql.ASTQL) (*astql.QueryResult, error) {
    values := instance.ValueMap()
    values[instance.F("product_id")] = instance.P("product_id")
    values[instance.F("quantity")] = instance.P("quantity")
    values[instance.F("updated_at")] = instance.P("now")

    // Only update if new quantity is greater
    // Note: This requires raw SQL for the WHERE clause on conflict
    return astql.Insert(instance.T("inventory")).
        Values(values).
        OnConflict(instance.F("product_id")).
        DoUpdate().
        Set(instance.F("quantity"), instance.P("quantity")).
        Set(instance.F("updated_at"), instance.P("now")).
        Build().
        Render(postgres.New())
}

Sync Pattern

Replace all rows for an entity (delete-then-insert pattern):

func SyncUserPreferences(instance *astql.ASTQL, prefCount int) ([]astql.QueryResult, error) {
    results := make([]astql.QueryResult, 0, 2)

    // 1. Delete existing preferences
    deleteResult, err := astql.Delete(instance.T("preferences")).
        Where(instance.C(instance.F("user_id"), astql.EQ, instance.P("user_id"))).
        Render(postgres.New())
    if err != nil {
        return nil, err
    }
    results = append(results, *deleteResult)

    // 2. Insert new preferences
    query := astql.Insert(instance.T("preferences"))
    for i := 0; i < prefCount; i++ {
        values := instance.ValueMap()
        values[instance.F("user_id")] = instance.P("user_id")
        values[instance.F("key")] = instance.P(fmt.Sprintf("key_%d", i))
        values[instance.F("value")] = instance.P(fmt.Sprintf("value_%d", i))
        query = query.Values(values)
    }

    insertResult, err := query.Render(postgres.New())
    if err != nil {
        return nil, err
    }
    results = append(results, *insertResult)

    return results, nil
}

Upsert with Timestamps

Common pattern: set created_at on insert, updated_at always:

func UpsertWithTimestamps(instance *astql.ASTQL) (*astql.QueryResult, error) {
    values := instance.ValueMap()
    values[instance.F("email")] = instance.P("email")
    values[instance.F("username")] = instance.P("username")
    values[instance.F("created_at")] = instance.P("now")  // Set on insert
    values[instance.F("updated_at")] = instance.P("now")  // Set always

    return astql.Insert(instance.T("users")).
        Values(values).
        OnConflict(instance.F("email")).
        DoUpdate().
        Set(instance.F("username"), instance.P("username")).
        Set(instance.F("updated_at"), instance.P("now")).  // Only update updated_at
        Build().
        Render(postgres.New())
}

Best Practices

1. Always Use Unique Constraints

ON CONFLICT requires a unique constraint or index:

-- Unique constraint
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Or unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

2. Be Explicit About Updated Fields

Only update fields that should change on conflict:

// Good: explicit updates
.DoUpdate().
    Set(instance.F("username"), instance.P("username")).
    Set(instance.F("updated_at"), instance.P("now"))

// Bad: might overwrite fields you want to preserve

3. Use RETURNING to Avoid Extra Queries

// Good: single query
.Returning(instance.F("id"))

// Less efficient: separate SELECT after INSERT

4. Consider DO NOTHING for Idempotency

When you only care about ensuring a row exists:

// Idempotent: insert if missing, ignore if exists
.OnConflict(instance.F("id")).DoNothing()