Introduction to Upserts

Why do we need upserts?

Let’s say we wanted to front-load our music database’s genres table with every musical genre we could think of. We might choose to create a Comma-separated values (CSV) file with all the data we want to load, then write some code to parse the file and insert the data into the database. At first glance, this seems pretty straightforward. We’d need to load each row of the CSV and call Repo.insert for each new record. What if the script crashed halfway through and we wanted to rerun it though? Or maybe it succeeded but we later realized that our CSV has missing or incorrect data.

To handle those scenarios, we have to rewrite our code. Instead of calling insert for every row in the CSV, we have to check the database first to see if the data is already there. If it isn’t, we call an insert as we did before. If it is there, we want to update it with the newest possible data from the CSV.

We also have to wrap all that logic in a transaction to avoid possible race conditions. Suddenly, this is not nearly as straightforward as it first seemed. Fortunately, Ecto can do all of this heavy lifting through “upsert” operations.

What are upserts?

The term upsert is a mash-up of “update” and “insert” and refers to a single operation that either updates an existing record with new data or inserts a new record if it doesn’t already exist, as shown in the figure below. To do this, upserts rely on a column with a unique index, either the primary key or some other value guaranteed to be unique.