Upsert vs. Separate POST & PATCH: Choosing the Right API Design

In the world of API design, developers often face the decision between using a single Upsert endpoint or separate POST and PATCH endpoints for creating and updating resources. While Upsert can seem like a convenient solution, it comes with its own set of challenges. In this edition explores the disadvantages of Upsert and the advantages of using separate POST and PATCH endpoints, with a focus on Go backends and MS SQL databases.

Understanding Upsert

Upsert is a combination of “update” and “insert.” It’s an operation that either inserts a new record or updates an existing one if it already exists. While this sounds convenient, it can lead to several issues in API design and implementation.

Disadvantages of Upsert

Lack of Clear Intent

Upsert operations blur the line between creation and update. This can make it difficult for API consumers to understand the exact outcome of their request.

Potential for Unintended Updates If not implemented carefully, Upsert can lead to accidental updates of existing records when the intent was to create a new one.
Complexity in Error Handling Error scenarios become more complex with Upsert. Was the error due to a failed insert or a failed update?
Reduced Granularity in Permissions With Upsert, it’s harder to implement fine-grained permissions. You might want to allow certain users to update but not create new resources, or vice versa.
Challenges in Auditing Tracking whether a record was created or updated becomes more difficult, which can be crucial for audit trails.
Performance Considerations Upsert operations can be less performant, especially in distributed systems, as they require additional checks and potential locking mechanisms.

Code Example: Upsert in Go with MS SQL

Here’s an example of an Upsert operation in Go using MS SQL:

func UpsertUser(db *sql.DB, user User) error {
    query := `
    MERGE INTO Users AS target
    USING (VALUES (@ID, @Name, @Email)) AS source (ID, Name, Email)
    ON target.ID = source.ID
    WHEN MATCHED THEN
        UPDATE SET Name = source.Name, Email = source.Email
    WHEN NOT MATCHED THEN
        INSERT (ID, Name, Email) VALUES (source.ID, source.Name, source.Email);
    `
    _, err := db.Exec(query, sql.Named("ID", user.ID), sql.Named("Name", user.Name), sql.Named("Email", user.Email))
    return err
}

While this looks concise, it hides complexity and potential issues.

Advantages of Separate POST and PATCH

Now, let’s explore the benefits of using separate POST and PATCH endpoints.

Clear Intent Separate endpoints make the intent of the operation clear. POST is for creation, PATCH for updates.
Simplified Error Handling With separate endpoints, error scenarios are more straightforward to handle and communicate.
Fine-grained Permissions You can easily implement different permission levels for creation and updates.
Better Auditing It’s easier to track and log creation and update operations separately.
Optimized Performance Separate operations can be optimized individually, potentially leading to better performance.
Partial Updates PATCH endpoints are designed for partial updates, allowing clients to send only the fields that need to be changed.

Code Examples: POST and PATCH in Go with MS SQL Here are examples of separate POST and PATCH implementations:

func CreateUser(db *sql.DB, user User) error {
    query := `INSERT INTO Users (Name, Email) VALUES (@Name, @Email)`
    _, err := db.Exec(query, sql.Named("Name", user.Name), sql.Named("Email", user.Email))
    return err
}

func UpdateUser(db *sql.DB, userID int, updates map[string]interface{}) error {
    query := `UPDATE Users SET `
    var params []interface{}
    i := 1
    for field, value := range updates {
        if i > 1 {
            query += ", "
        }
        query += fmt.Sprintf("%s = @p%d", field, i)
        params = append(params, sql.Named(fmt.Sprintf("p%d", i), value))
        i++
    }
    query += ` WHERE ID = @ID`
    params = append(params, sql.Named("ID", userID))
    
    _, err := db.Exec(query, params...)
    return err
}

Performance Considerations

When it comes to performance, separate POST and PATCH operations often have an edge over Upsert:

Reduced Complexity Separate operations are simpler, which can lead to faster execution times.
Optimized Indexing You can optimize indexes separately for inserts and updates.
Reduced Locking Separate operations may require less locking, especially in high-concurrency scenarios.
Caching Opportunities Separate endpoints make it easier to implement and manage caching strategies.

Benchmarking Example

Here’s a simple benchmarking example to compare Upsert with separate POST and PATCH operations:

func BenchmarkUpsert(b *testing.B) {
    // Setup db connection
    for i := 0; i < b.N; i++ {
        UpsertUser(db, User{ID: i, Name: "Test", Email: "[email protected]"})
    }
}

func BenchmarkPostPatch(b *testing.B) {
    // Setup db connection
    for i := 0; i < b.N; i++ {
        if i%2 == 0 {
            CreateUser(db, User{Name: "Test", Email: "[email protected]"})
        } else {
            UpdateUser(db, i, map[string]interface{}{"Name": "Updated"})
        }
    }
}

In many cases, you’ll find that the separate POST and PATCH operations perform better, especially as the complexity of your data model increases.

Conclusion

While Upsert can seem like a convenient shortcut, separate POST and PATCH endpoints offer clearer intent, better error handling, finer-grained control, and often better performance. They align more closely with RESTful principles and provide a more maintainable and scalable API design. When working with Go and MS SQL, leveraging separate endpoints allows you to optimize each operation independently, potentially leading to better overall system performance and a clearer, more intuitive API for your consumers. Remember, the best choice depends on your specific use case, but in most scenarios, separate POST and PATCH endpoints will serve you better in the long run. 💡