INSERT OR REPLACE and ON CONFLICT are two ways to achieve an "upsert" (insert or update) in SQLite, but they operate quite differently under the hood, and understanding that difference is key to efficient database design.
Let’s see INSERT OR REPLACE in action. Imagine we have a simple users table:
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT
);
Now, let’s insert a user:
INSERT INTO users (user_id, username, email) VALUES (1, 'alice', 'alice@example.com');
If we try to insert another user with the same user_id (which is the primary key, so also unique):
INSERT OR REPLACE INTO users (user_id, username, email) VALUES (1, 'alice_updated', 'alice.new@example.com');
What happened? SQLite deleted the entire existing row with user_id = 1 and then inserted a brand new row with user_id = 1, username = 'alice_updated', and email = 'alice.new@example.com'. The user_id remains 1, but it’s a new row.
Now, let’s look at ON CONFLICT. This syntax is more flexible and targets specific conflict clauses. If we try to insert a user with a username that already exists, and we don’t want to replace the whole row, we can use ON CONFLICT:
INSERT INTO users (user_id, username, email) VALUES (2, 'bob', 'bob@example.com');
INSERT INTO users (user_id, username, email) VALUES (3, 'bob', 'bob.new@example.com'); -- This will conflict on username
When the second INSERT for bob fails due to the UNIQUE constraint on username, ON CONFLICT lets us define what to do. The most common action is DO NOTHING:
INSERT INTO users (user_id, username, email) VALUES (3, 'bob', 'bob.new@example.com')
ON CONFLICT(username) DO NOTHING;
In this case, the row with user_id = 3 is not inserted, and the existing row for 'bob' (which has user_id = 2) remains untouched.
The other option with ON CONFLICT is DO UPDATE. This allows you to specify which columns to update based on the conflict. To achieve an "upsert" behavior similar to INSERT OR REPLACE but with more control, you’d use DO UPDATE SET:
INSERT INTO users (user_id, username, email) VALUES (1, 'alice', 'alice.updated.again@example.com')
ON CONFLICT(user_id) DO UPDATE SET
email = excluded.email;
Here, ON CONFLICT(user_id) targets conflicts on the primary key. excluded.email refers to the email value that would have been inserted if there hadn’t been a conflict. So, if user_id = 1 already exists, its email is updated to alice.updated.again@example.com. Crucially, the row itself is not deleted and re-inserted. This means any triggers on DELETE or INSERT would behave differently, and importantly, any AUTOINCREMENT counter would not be incremented.
The most surprising thing about INSERT OR REPLACE is that it’s actually implemented as a DELETE followed by an INSERT. This means if you have BEFORE DELETE or AFTER DELETE triggers on your table, they will fire before the new row is inserted. This can lead to unexpected behavior if your triggers rely on the row’s primary key or other unique identifiers remaining consistent throughout the operation. ON CONFLICT DO UPDATE, on the other hand, performs an actual update operation, so delete triggers do not fire.
The key difference boils down to this: INSERT OR REPLACE drops the old row and creates a new one, potentially affecting row IDs, trigger execution, and auto-increment sequences. ON CONFLICT DO UPDATE modifies the existing row in place, preserving its identity and trigger behavior more predictably.
When migrating from older SQLite versions or other database systems that might have a singular "upsert" keyword, it’s easy to assume INSERT OR REPLACE is the direct equivalent of ON CONFLICT DO UPDATE. However, the underlying mechanics are fundamentally different, and this distinction becomes critical when dealing with complex table structures, triggers, or when maintaining strict row identity.