TL;DR: Automatically create new records or update existing records in your database with the upsert API.

Starting today, all Gadget model APIs will include an <inline-code>upsert<inline-code> meta API to call a model’s <inline-code>create<inline-code> or <inline-code>update<inline-code> action, depending on if a record already exists in the database. 

The <inline-code>upsert<inline-code> API is the fastest and easiest way to manage and store data coming from external services. Does the data already exist in the database? <inline-code>upsert<inline-code> will update it! Does a new record need to be created? Leave it to <inline-code>upsert<inline-code>! Are created and updated webhooks with identical payloads being fired concurrently, and need to be handled appropriately (thanks Shopify)? Just <inline-code>upsert<inline-code> the payloads; your record will be saved properly, and you’ll maintain data consistency!

When building integrations with third-party systems, you’ll no longer need to worry about dealing with race conditions. The <inline-code>upsert<inline-code> executes automatically and ensures concurrent commands don't interfere with each other, making data management with Gadget seamless. You don’t need to worry about transactions or data consistency in order to reflect changes from other systems in your database.

<inline-code>upsert<inline-code> can be called like any other model action in Gadget:

What is an `upsert`?

In literal terms, an upsert is a combination of the words update and insert, and that’s exactly what it does: updates and/or creates data. It uses a common validation pattern for storing data coming from any kind of <inline-code>update<inline-code> webhook or third-party service where you:

• Check to see if the record already exists in the database
• If it does, update the existing record
• If it doesn’t, create a new record

This is similar to using an ON_CONFLICT cause in Postgres to update a record if there is a uniqueness violation.

When you receive data from an external service, you may not know whether a resource is already stored in your database. With the new <inline-code>upsert<inline-code> API, Gadget will automatically check your data model for a record, and subsequently run the <inline-code>create<inline-code> or <inline-code>update<inline-code> action once it has determined whether or not that record already exists.

Without an upsert command, you would need to manually check to see if the record is already stored in your database before deciding whether to create a new record or update an existing one, and spend valuable compute time to repeat that process for any model in your application that needs updating. With Gadget, all of that is handled for you.

Upsert allows you to run the appropriate action with a single API call:

By default, the upsert API uses the <inline-code>id<inline-code> field to determine if a record exists in your database. This is the record ID field assigned automatically by Gadget when invoking the <inline-code>create<inline-code> model actions from your app’s public API. 

If you have a custom field you want to use instead, you can define what makes a record unique by using an <inline-code>on<inline-code> parameter to specify your preferred identifier.

Note: This means you can use <inline-code>upsert<inline-code> to manage your record ID when creating new records in Gadget! Previously, this was not possible with your Gadget app’s public API. Now, you have much more flexibility.

Using <inline-code>upsert<inline-code> eliminates the repetitive code required to keep your model data up-to-date, reducing the chance of introducing bugs or data errors to your projects.

Because this is such a useful API, a <inline-code>bulkUpsert<inline-code> API is also available on all models:

But what if there isn’t one field to rule them all?

If you have a multi-tenant application where IDs are not unique across tenants, then just passing a single field into the upsert to determine uniqueness won’t work!

That’s why the upsert API allows you to use multiple fields in the <inline-code>on<inline-code> parameter. For a multi-tenant <inline-code>gizmo.upsert<inline-code>, we can also pass in the user ID so that <inline-code>gizmoId<inline-code> records are upserted per user:

I don’t want to run actions, just get the data in my database!

An <inline-code>upsert<inline-code> command will call your public API <inline-code>create<inline-code> or <inline-code>update<inline-code> actions. This means any action code will also be run! 

If you just want to upsert a value in the database, you can also call <inline-code>upsert<inline-code> as part of your app’s internal API. Calling the internal API skips any code in the <inline-code>create<inline-code> and <inline-code>update<inline-code> actions and just updates the values in the database.

Be aware that the internal API should be used with caution, as it provides direct access to the database that could potentially disrupt data integrity.

The public upsert API is available on Gadget framework +v1.1 for all models with <inline-code>create<inline-code> and <inline-code>update<inline-code> actions. All apps and models have access to the internal upsert API, regardless of framework version.

Want more upsert examples? Check out the Gadget documentation or your application’s API docs.