It has been almost 4 months since our last release, and for a good reason. Version 0.9.0 which was released today is packed with some highly-anticipated features. Perhaps at the top of the list, is a feature that has been in discussion for more than a year in a half and was one of the most commonly requested features in the Ent User Survey: the Upsert API!
Version 0.9.0 adds support for "Upsert" style statements using a new feature flag:
Ent has a collection of feature flags that can be switched on to add more features
to the code generated by Ent. This is used as both a mechanism to allow opt-in to some features that are not necessarily
desired in every project and as a way to run experiments of features that may one day become part of Ent's core.
In this post, we will introduce the new feature, the places where it is useful, and demonstrate how to use it.
"Upsert" is a commonly-used term in data systems that is a portmanteau of "update" and "insert" which usually refers to
a statement that attempts to insert a record to a table, and if a uniqueness constraint is violated (e.g. a record by
that ID already exists) that record is updated instead. While none of the popular relational databases have a specific
UPSERT statement, most of them support ways of achieving this type of behavior.
For example, assume we have a table with this definition in an SQLite database:
If we try to execute the same insert twice:
We get this error:
In many cases, it is useful to have write operations be idempotent, meaning we can run them many times in a row while leaving the system in the same state.
In other cases, it is not desirable to query if a record exists before trying to create it. For these kinds of situations,
SQLite supports the
ON CONFLICT clause in
statements. To instruct SQLite to override an existing value with the new one we can execute:
If we prefer to keep the existing values, we can use the
DO NOTHING conflict action:
Sometimes we want to merge the two versions in some way, we can use the
DO UPDATE action a little differently to
achieve do something like:
In this case, after our second
INSERT the value for the
name column would be:
Tamir, Rotem (formerly: Rotem Tamir).
Not very useful, but hopefully you can see that you can do cool things this way.
Assume we have an existing Ent project with an entity similar to the
users table described above:
As the Upsert API is a newly released feature, make sure to update your
ent version using:
Next, add the
sql/upsert feature flag to your code-generation flags, in
Next, re-run code generation for your project:
Observe that a new method named
OnConflict was added to the
This (along with more new generated code) will serve us in achieving upsert behavior for our
To explore this, let's first start by writing a test to reproduce the uniqueness constraint error:
The test passes:
Next, let's see how to instruct Ent to override the existing values with the new in case a conflict occurs:
Running our test:
Alternatively, we can use the
Ignore modifier to instruct Ent to keep the old version when resolving the conflict.
Let's write a test that shows this:
In this post, we presented the Upsert API, a long-anticipated capability, that is available by feature-flag in Ent v0.9.0. We discussed where upserts are commonly used in applications and the way they are implemented using common relational databases. Finally, we showed a simple example of how to get started with the Upsert API using Ent.
Have questions? Need help with getting started? Feel free to join our Slack channel.