UPSERT dan Penanganan Konflik

Aplikasi toko kita perlu pola yang sama berulang kali: tambahkan produk ke keranjang bila belum ada, atau tambah jumlahnya bila sudah ada. INSERT mentah tidak mampu memenuhi pola itu — statement langsung gagal begitu menabrak data yang sudah ada. SQLite menyediakan dua generasi jawaban: klausul INSERT OR warisan lama, dan sintaks UPSERT ON CONFLICT DO UPDATE yang jauh lebih presisi — kita bedah keduanya beserta jebakannya.

Data Contoh #

Keranjang belanja memakai produk_id sebagai kunci unik. Kolom catatan sengaja diisi nilai penting untuk mengungkap efek samping di bagian selanjutnya:

CREATE TABLE keranjang (
  produk_id INTEGER NOT NULL UNIQUE,
  nama TEXT NOT NULL,
  jumlah INTEGER NOT NULL DEFAULT 1,
  catatan TEXT
);

INSERT INTO keranjang (produk_id, nama, jumlah, catatan) VALUES
  (1, 'Kopi Gayo', 2, 'digiling halus'),
  (2, 'Teh Melati', 1, 'kemasan teh celup');

Masalah: INSERT Menabrak UNIQUE #

Menambahkan produk yang sama dua kali langsung ditolak oleh constraint UNIQUE:

sqlite3 toko.db "INSERT INTO keranjang (produk_id, nama, jumlah) VALUES (1, 'Kopi Gayo', 3);"

Output yang Dihasilkan:

Error: stepping, UNIQUE constraint failed: keranjang.produk_id (19)

Angka 19 adalah kode galat SQLITE_CONSTRAINT; statement dibatalkan dan tidak ada data yang berubah. Kondisi awal tabel tetap utuh:

SELECT rowid, * FROM keranjang ORDER BY rowid;

Output yang Dihasilkan:

1|1|Kopi Gayo|2|digiling halus
2|2|Teh Melati|1|kemasan teh celup

Cara Lama: INSERT OR #

SQLite mengizinkan kita menempelkan satu kata resolusi setelah INSERT. Ada lima pilihan:

OR IGNORE adalah obat paling ringan untuk data duplikat yang boleh dibuang:

INSERT OR IGNORE INTO keranjang (produk_id, nama, jumlah) VALUES (1, 'Kopi Gayo', 3);
SELECT changes();

Output yang Dihasilkan:

0

changes() bernilai 0 karena baris baru tidak pernah tersisip. Berbeda dengan OR ABORT, tidak ada galat yang terjadi — statement selesai diam-diam.

OR REPLACE tampak menjanjikan untuk pola “insert atau update”, tetapi mekanismya kasar: engine SQLite menjalankan DELETE pada baris lama lalu INSERT baris baru. Konsekuensinya terlihat penuh di hasil berikut:

INSERT OR REPLACE INTO keranjang (produk_id, nama, jumlah) VALUES (1, 'Kopi Gayo', 5);
SELECT rowid, * FROM keranjang ORDER BY rowid;

Output yang Dihasilkan:

2|2|Teh Melati|1|kemasan teh celup
3|1|Kopi Gayo|5|

Dua hal berubah diam-diam: rowid Kopi Gayo melompat dari 1 ke 3, dan catatan lenyap karena kolom itu tidak disebut di INSERT sehingga kembali ke nilai default. Perilaku FAIL juga patut dicermati — baris sebelum titik konflik tetap tersimpan:

sqlite3 toko.db "CREATE TABLE t (id INTEGER UNIQUE); INSERT INTO t VALUES (1), (2);"
sqlite3 toko.db "INSERT OR FAIL INTO t VALUES (3), (2), (4);"
sqlite3 toko.db "SELECT group_concat(id) FROM t;"

Output yang Dihasilkan:

Error: stepping, UNIQUE constraint failed: t.id (19)
1,2,3

Nilai 3 tetap masuk meskipun statement akhirnya gagal di nilai 2 — perilaku yang sama sekali berbeda dari ABORT yang membatalkan statement bersih.

UPSERT Modern: ON CONFLICT DO UPDATE #

Sejak SQLite 3.24, SQLite menyediakan mekanisme yang tepat untuk pola “insert atau update”: klausa ON CONFLICT yang menjalankan UPDATE pada baris lama di tempatnya, tanpa menghapus apa pun:

INSERT INTO keranjang (produk_id, nama, jumlah) VALUES (2, 'Teh Melati', 3)
ON CONFLICT(produk_id) DO UPDATE SET jumlah = jumlah + excluded.jumlah;
SELECT produk_id, nama, jumlah FROM keranjang ORDER BY produk_id;

Output yang Dihasilkan:

1|Kopi Gayo|5
2|Teh Melati|4

Mari kita bedah statement tersebut baris per baris:

1. Baris 1 (INSERT INTO keranjang ... VALUES (2, ...)): Statement tetap dibuka seperti INSERT biasa; nilai-nilai ini adalah kandidat baris baru.
2. Baris 2 (ON CONFLICT(produk_id)): Klausa ini menargetkan constraint UNIQUE pada kolom produk_id — konflik pada constraint lain tetap memunculkan galat.
3. Baris 2 (DO UPDATE SET jumlah = jumlah + excluded.jumlah): Saat konflik terjadi, engine SQLite mengubah statement menjadi UPDATE. Nama jumlah tanpa prefiks merujuk kolom baris lama.
4. Baris 2 (excluded.jumlah): Tabel semu excluded memuat nilai dari baris yang gagal disisipkan — di sini angka 3 — sehingga hasilnya 1 + 3 = 4.

Sejak SQLite 3.35, statement INSERT juga menerima klausa RETURNING yang mengembalikan baris hasil, termasuk hasil UPDATE dari UPSERT:

INSERT INTO keranjang (produk_id, nama, jumlah) VALUES (2, 'Teh Melati', 5)
ON CONFLICT(produk_id) DO UPDATE SET jumlah = keranjang.jumlah + excluded.jumlah
RETURNING produk_id, nama, jumlah;

Output yang Dihasilkan:

2|Teh Melati|9

Prefiks keranjang.jumlah memperjelas bahwa nilai diambil dari baris lama (4), bukan dari excluded. Variasi kedua adalah DO NOTHING — setara OR IGNORE tetapi lebih presisi karena menargetkan constraint tertentu:

INSERT INTO keranjang (produk_id, nama, jumlah) VALUES (2, 'Teh Melati', 99)
ON CONFLICT(produk_id) DO NOTHING;
SELECT changes();

Output yang Dihasilkan:

0

Klausa DO UPDATE juga menerima WHERE sebagai syarat: bila syarat salah, tidak ada yang terjadi dan statement berlaku seperti DO NOTHING. Pola ini mencegah penimpaan tanpa sengaja:

INSERT INTO keranjang (produk_id, nama, jumlah) VALUES (1, 'Kopi Gayo', 99)
ON CONFLICT(produk_id) DO UPDATE SET jumlah = excluded.jumlah
WHERE jumlah <> excluded.jumlah;
SELECT produk_id, jumlah FROM keranjang ORDER BY produk_id;

Output yang Dihasilkan:

1|99
2|9

Terakhir, bila tidak ada konflik sama sekali, klausa ON CONFLICT tidak berbuat apa-apa dan baris baru disisipkan normal — satu statement ini menangani kedua cabang:

INSERT INTO keranjang (produk_id, nama, jumlah) VALUES (3, 'Gula Aren', 4)
ON CONFLICT(produk_id) DO NOTHING
RETURNING produk_id, nama, jumlah;

Output yang Dihasilkan:

3|Gula Aren|4

OR REPLACE atau UPSERT #

Kesimpulannya lugas: untuk logika “buat baru atau perbarui”, pakai UPSERT; sisakan OR REPLACE untuk kasus penggantian baris penuh yang memang disengaja. Bila yang kita inginkan adalah reaksi otomatis setiap kali data berubah — itulah pekerjaan trigger, dibahas pada Chapter Trigger.