Mastering UPSERT in SQLite: The ON CONFLICT Clause

One of the most common patterns in application development is the "Update if exists, Insert if new" logic.

Traditionally, you might write code like this in Python or JavaScript:

1. Current: SELECT * FROM user_stats WHERE user_id = 1
2. If result exists: UPDATE user_stats SET visits = visits + 1 WHERE user_id = 1
3. Else: INSERT INTO user_stats (user_id, visits) VALUES (1, 1)

This is bad for performance (2 queries) and bad for concurrency (what if another user inserts between step 1 and 3?).

SQLite (since version 3.24) solves this elegantly with the ON CONFLICT clause, commonly known as UPSERT.

The Syntax of UPSERT

The basic structure adds a clause to your INSERT statement:

INSERT INTO table_name (col1, col2) 
VALUES (val1, val2)
ON CONFLICT(target_column) 
DO UPDATE SET col2 = val2;

You can also choose to do nothing:

INSERT INTO table_name (col1, col2) 
VALUES (val1, val2)
ON CONFLICT(target_column) 
DO NOTHING;

Interactive Example: Tracking User Visits

Let's maintain a table of user statistics. We want to simply "log a visit" without worrying about whether it's the user's first visit or their 100th.

Scenario:

1. User Alice visits (First time -> Insert).
2. User Bob visits (First time -> Insert).
3. User Alice visits again (Existing -> Update/Increment).

Understanding excluded

Did you notice excluded.last_visit in the example above?

• visit_count + 1 refers to the existing row's value.
• excluded.last_visit refers to the new value you were trying to insert (the one that got "excluded" because of the conflict).

This distinction is powerful. It allows you to mix old data with new data in your update logic.

Conditional Updates

You can even add a WHERE clause to your UPSERT logic.

For example, "Only update the last login date if the new date is actually newer than the old one":

INSERT INTO sync_status (device_id, sync_time)
VALUES ('phone_1', '2025-01-20')
ON CONFLICT(device_id)
DO UPDATE SET sync_time = excluded.sync_time
WHERE excluded.sync_time > sync_status.sync_time;

OR IGNORE vs ON CONFLICT

You might have seen INSERT OR IGNORE. How is that different?

• INSERT OR IGNORE: If any constraint fails (Unique, Not Null, Check), the insert is silently skipped. You can't perform an update.
• ON CONFLICT DO NOTHING: Specifically targets the conflict you specify.
• ON CONFLICT DO UPDATE: The true UPSERT, allowing you to modify the existing row.

Best Practices

1. Require a Unique Index: UPSERT relies on a conflict. You typically need a PRIMARY KEY or UNIQUE constraint on the column(s) you are checking.
2. Atomic Counters: This is the best way to maintain counters (like "likes", "views", "inventory") because it's atomic and race-condition free within the database engine.
3. Idempotency: UPSERT allows you to make your API endpoints idempotent. A client can retry a "Create User" request safely without causing duplicate errors.

Conclusion

The ON CONFLICT clause converts complex application logic into a single, efficient, and safe SQL statement. Whether you are building counters, sync logs, or user profiles, mastering UPSERT is essential for modern SQLite development.