Introduction to Upserts
Learn how to insert and update in Ecto using upserts.
We'll cover the following
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.
Get hands-on with 1200+ tech skills courses.