Have you ever needed to update a row in a SQLite table that doesn’t exist, or insert a row when it already does? This is a common requirement in many applications, and SQLite provides a great way to do it with the SQLite UPSERT feature.
In this complete guide, you’ll learn all about upserts in SQLite. We’ll start with the basics, see some examples, learn the syntax details, and finish off with a handy comparison table plus some best practices. Let’s get started!
What's in this article
What is SQLite Upsert?
The term “upsert” is a combination of “update” and “insert”.
As the name implies, upsert allows us to update a row if it already exists in a table, or insert a new row if it doesn’t exist.
The key thing that triggers an upsert is a uniqueness conflict on a specified column or set of columns. For example, if we try to insert a new row, and it has the same primary key value as an existing row, SQLite will realize there is a conflict due to the primary key uniqueness constraint.
At that point, instead of throwing an error and rejecting the insert, SQLite will update the existing row. This avoids duplicate key errors and having to manually check if a row exists before doing an insert or update.
Upserts are very useful in situations where:
- We fetch data from an external source and want to sync it to our SQLite database
- We allow users to update their profile information which may or may not already exist
- We collect analytics data and append new events, creating profiles if needed
Essentially, anywhere we want seamless insert or update semantics on a table.
Also read: SQLite Boolean Data Type
SQLite Upsert Quick Reference
|INSERT ON CONFLICT DO NOTHING||Ignores insert and does nothing on conflict||INSERT … ON CONFLICT(id) DO NOTHING|
|INSERT ON CONFLICT DO UPDATE||Updates row on conflict||INSERT … ON CONFLICT(email) DO UPDATE SET name=‘Bob’|
|No conflict target||Uses primary key as default||INSERT … ON CONFLICT DO UPDATE SET …|
|excluded.* columns||Reference inserted values||DO UPDATE SET name=excluded.name|
|COALESCE()||Handle NULL values||DO UPDATE SET value=COALESCE(excluded.value, value)|
Creating a Sample SQLite Table
Before looking at upsert examples, let’s create a table to test with:
CREATE TABLE user ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE );
This creates a
user table with a primary key
name column, and a
Let’s also insert a couple of rows:
INSERT INTO user (id, name, email) VALUES (1, 'Alice', '[email protected]'), (2, 'Bob', '[email protected]');
With this table created, we have the necessary pieces to demonstrate upserts.
The upsert syntax in SQLite is implemented through a special
ON CONFLICT clause added to a normal
INSERT statement. Here is the syntax:
INSERT INTO table (columns) VALUES (values) ON CONFLICT (conflict_target) DO UPDATE|NOTHING;
It looks just like a regular INSERT statement, with the addition of
ON CONFLICT and
DO UPDATE clauses.
Let’s break it down:
INSERT INTO table– Standard insert into a table
(columns)– Specify which columns to insert into
VALUES (values)– Values to insert
ON CONFLICT (conflict_target)– This specifies the uniqueness constraint that will trigger the upsert when violated
DO UPDATE– If there is a conflict, perform an update instead of insert
DO NOTHING– Alternative to perform no operation on conflict
conflict_target can be either a column name or list of column names that have a uniqueness constraint, such as a primary key or unique index.
If we don’t specify a
conflict_target, it will use the primary key of the table by default.
Now let’s look at some examples of upserts in action!
SQLite INSERT On Conflict DO NOTHING Example
The simplest upsert is
ON CONFLICT DO NOTHING, which will silently ignore any insert that would result in a conflict:
-- Insert a new row INSERT INTO user (id, name, email) VALUES (3, 'Sam', '[email protected]') ON CONFLICT DO NOTHING; -- Try to insert row with existing email INSERT INTO user (id, name, email) VALUES (4, 'John', '[email protected]') ON CONFLICT DO NOTHING; -- Rows after upsert SELECT * FROM user; id name email ---------- ---------- ---------------------- 1 Alice [email protected] 2 Bob [email protected] 3 Sam [email protected]
For the first insert, there is no existing row with
id = 3, so it inserts successfully.
On the second insert, the specified email
[email protected] already exists in row id 2. This violates the unique constraint on the
ON CONFLICT DO NOTHING specified, SQLite simply does nothing and moves on when it hits the conflict. No error is thrown, and no data is modified.
This is useful for cases where you want to silently ignore constraint violations on inserts.
SQLite INSERT On Conflict DO UPDATE Example
To perform an update on conflicts, we use
ON CONFLICT DO UPDATE:
-- Insert a new row INSERT INTO user (id, name, email) VALUES (4, 'John', '[email protected]') ON CONFLICT DO NOTHING; -- Try to insert row with existing id INSERT INTO user (id, name, email) VALUES (2, 'Robert', '[email protected]') ON CONFLICT(id) DO UPDATE SET name='Robert', email='[email protected]'; -- Rows after upsert SELECT * FROM user; id name email ---------- ---------- ---------------------- 1 Alice [email protected] 2 Robert [email protected] 3 Sam [email protected] 4 John [email protected]
Here we explicitly specify the
id column as the
conflict_target. This means if there is an
id conflict, it will execute the
DO UPDATE clause.
On insert of id
2, it finds the existing row with that primary key value, and executes an update to change the
We can update specific columns by setting their values in the
DO UPDATE SET section.
Using Excluded Columns
excluded keyword available in the
DO UPDATE clause lets us reference values from the INSERT for the conflicting row:
INSERT INTO user (id, name, email) VALUES (2, 'Robert', '[email protected]') ON CONFLICT(id) DO UPDATE SET name = excluded.name, email = excluded.email; SELECT * FROM user; id name email ---------- ---------- ---------------------- 1 Alice [email protected] 2 Robert [email protected] 3 Sam [email protected] 4 John [email protected]
Instead of hardcoding the new
excluded.email to reference the values from the INSERT statement.
This is really handy for cases where we want to use the provided values on update, but don’t want to repeat them in the
DO UPDATE section.
Upserting with an SQLite SELECT Statement
For real use cases, we often want to upsert values selected from another table or query result.
Here is an example of doing an upsert with a SQLite SELECT:
CREATE TABLE logins ( user_id INTEGER NOT NULL, ts DATETIME NOT NULL ); INSERT INTO logins (user_id, ts) SELECT user.id, datetime('now') FROM user WHERE user.name = 'Alice' ON CONFLICT(user_id) DO UPDATE SET ts = excluded.ts; SELECT * FROM logins; user_id ts ---------- -------------------- 1 2022-10-15 15:23:45
This simulates logging a new login event for a user. It selects the
id and current timestamp value, and upserts into the
If there is an existing row with that
user_id, the timestamp
ts column is updated. Otherwise, a new row is inserted.
SQLite Upsert Best Practices
Now that you’ve seen examples of how upserts work in SQLite, let’s go over some best practices:
- Use SQLite upserts for sync or merge scenarios – Upserts shine for cases where you want to sync/merge external data into your database. For example, a daily sync from a SaaS product to update your user profiles.
- Prefer DO UPDATE over DO NOTHING – In most cases, DO UPDATE is the better option compared to DO NOTHING. DO NOTHING can lead to missed updates if you have a logic error.
- Include all required columns – Make sure to specify all NOT NULL columns in your INSERT and any default values in DO UPDATE. Otherwise, you may hit constraint errors.
- Use transactions for multi-row upserts – If you are doing batch upserts from selects/queries, wrap them in a transaction to ensure atomicity.
- Mind the unique constraints – Only uniqueness constraints trigger upserts, not other constraints like CHECK or NOT NULL.
- Test handling of NULLs – Use COALESCE() if you want to retain NULL values on update.
That covers everything you need to know to use upserts like a pro in SQLite. We looked at the syntax, saw examples with DO NOTHING and DO UPDATE, how to reference excluded columns, do upserts with SELECT, and some best practices.
Upserts are a very handy SQLite feature, and can help reduce complexity and errors when doing inserts and updates. Give them a try in your projects!