SQLite Upsert: Using INSERT ON CONFLICT

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!

To perform an upsert in SQLite, use the INSERT ON CONFLICT DO UPDATE syntax. This will update a row if it already exists based on a uniqueness conflict, otherwise insert a new row. Specify the conflict target columns like a primary key that should trigger an upsert. Use DO UPDATE to modify values, and excluded.* to reference the inserted columns. Wrapping in a transaction is recommended for multi-row upserts.

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

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 id, a name column, and a email column with a uniqueness constraint.

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.

Upsert Syntax

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

The 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!

Also read: SQLite Datetime: Handling Date and Time Data in SQLite

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 email column.

With 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 name and email instead.

We can update specific columns by setting their values in the DO UPDATE SET section.

Using Excluded Columns

The 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 name and email, we use excluded.name and 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 logins table.

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.

Summary

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!