RETURNING SQLite: Ambil Hasil INSERT, UPDATE, DELETE

Cara Melihat Apa yang Baru Saja Terjadi

Saat kamu menjalankan INSERT, UPDATE, atau DELETE, SQLite memberitahumu berapa banyak baris yang tersentuh, tapi tidak memberitahu baris yang mana, atau seperti apa nilai akhirnya. Solusi klasiknya adalah SELECT lanjutan. Itu berarti dua kali perjalanan ke database, dua pernyataan, dan celah waktu kecil di mana orang lain bisa mengubah baris itu di antara keduanya.

RETURNING mengatasinya. Kamu menambahkannya ke pernyataan tulis, menyebutkan kolom yang ingin dikembalikan, dan SQLite memberimu baris yang terpengaruh seolah-olah kamu baru saja menjalankan SELECT atas baris-baris itu:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO notes (title) VALUES ('First note')
RETURNING id, title, created_at;

Satu pernyataan, satu perjalanan, dan kamu mendapatkan kembali id yang dihasilkan serta nilai default created_at yang diisi database untukmu.

RETURNING ditambahkan di SQLite 3.35.0 (Maret 2021). Jika pernyataanmu ditolak sebagai error sintaks, cek SELECT sqlite_version();, karena build yang lebih lama tidak mengenal kata kunci ini.

Mendapatkan Kembali ID yang Dihasilkan

Alasan paling umum memakai RETURNING adalah untuk mengambil primary key yang dibuat otomatis tepat setelah insert:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (email) VALUES ('rosa@example.com')
RETURNING id;

Sebelum ada RETURNING, kamu melakukan insert lalu memanggil last_insert_rowid() (atau padanannya di driver-mu) pada koneksi yang sama. Cara itu masih berfungsi, tapi bergantung pada keadaan koneksi yang tersembunyi, sehingga mudah keliru jika memakai connection pool atau thread. RETURNING id itu eksplisit, hanya berlaku untuk pernyataan itu, dan bekerja dengan cara yang sama apa pun yang menjalankan koneksinya.

Jika tabelmu tidak mendeklarasikan INTEGER PRIMARY KEY secara eksplisit, kamu tetap bisa mendapatkan pengenal baris implisitnya:

CREATE TABLE logs (event TEXT);

INSERT INTO logs (event) VALUES ('startup')
RETURNING rowid, event;

Setiap tabel SQLite biasa punya rowid, dan RETURNING akan memberikannya.

Beberapa Kolom dan Ekspresi

RETURNING menerima bentuk yang sama dengan daftar kolom pada SELECT. Tuliskan kolom, pakai *, buat ekspresi, beri alias:

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL
);

INSERT INTO line_items (product, price, quantity)
VALUES ('Notebook', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

RETURNING * praktis jika kamu ingin semuanya, termasuk nilai default yang diisi database, tanpa menyebut setiap kolom:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (name) VALUES ('login')
RETURNING *;

Kamu melihat id yang baru, name yang kamu berikan, dan timestamp yang dihitung SQLite.

RETURNING dengan UPDATE

Pada UPDATE, RETURNING memberimu nilai setelah update, yaitu baris sebagaimana terlihat setelah perubahanmu diterapkan:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance INTEGER NOT NULL
);
INSERT INTO accounts (name, balance) VALUES ('Ada', 100), ('Boris', 50);

UPDATE accounts
SET balance = balance + 25
WHERE name = 'Ada'
RETURNING id, name, balance;

Kamu mendapatkan saldo baru Ada sebesar 125, bukan yang lama 100. Itu membuat RETURNING sangat cocok untuk penghitung atomik dan operasi kredit/debit: kamu tidak perlu membaca, menghitung, menulis, lalu membaca ulang.

Jika WHERE cocok dengan banyak baris, kamu mendapat satu baris kembali untuk setiap baris yang terpengaruh:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (name, price) VALUES ('A', 10), ('B', 20), ('C', 30);

UPDATE products
SET price = price * 1.1
RETURNING id, name, price;

Tiga baris masuk, tiga baris keluar. Urutannya tidak dijamin. Jika kamu butuh urutan tertentu, urutkan hasilnya di sisi client.

RETURNING dengan DELETE

Pada DELETE, RETURNING memberimu baris sebagaimana adanya tepat sebelum dihapus. Berguna untuk pengarsipan, jejak audit, atau sekadar memastikan apa yang terhapus:

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    expires_at TEXT NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES
    (1, '2024-01-01'), (2, '2024-01-02'), (3, '2026-01-01');

DELETE FROM sessions
WHERE expires_at < '2025-01-01'
RETURNING id, user_id, expires_at;

Kamu mendapatkan kembali dua sesi yang sudah kedaluwarsa, lengkap dengan semua field-nya, meskipun keduanya sudah tidak ada di tabel. Jika kamu ingin memindahkannya ke tempat lain, ini persiapan yang pas untuk tabel arsip: baca hasilnya lalu masukkan ke tempat lain dalam transaksi yang sama.

RETURNING dengan UPSERT

RETURNING juga bekerja dengan INSERT ... ON CONFLICT ... DO UPDATE. Baris yang dikembalikan mencerminkan cabang mana pun yang dijalankan, entah insert baru atau update karena konflik:

CREATE TABLE counters (
    name TEXT PRIMARY KEY,
    value INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, value) VALUES ('visits', 1)
ON CONFLICT(name) DO UPDATE SET value = value + 1
RETURNING name, value;

Jalankan pernyataan itu dua kali. Pertama kali, pernyataan itu melakukan insert dan mengembalikan ('visits', 1). Kedua kalinya, konfliknya terjadi, nilainya dinaikkan, dan kamu mendapatkan kembali ('visits', 2). Bagaimanapun, satu pernyataan dan satu baris keluar, tanpa perlu bertanya "tadi insert atau update?" sebelum melanjutkan.

Inilah pola paling rapi di SQLite untuk "berikan nilai saat ini, dan buat jika belum ada" tanpa bolak-balik ke database.

Beberapa Hal yang Perlu Diketahui

Beberapa detail yang sering mengejutkan orang:

• RETURNING selalu melihat baris setelah perubahan untuk INSERT dan UPDATE, dan sebelum perubahan untuk DELETE. Tidak ada sintaks untuk meminta sisi yang lain.
• Urutan baris yang dikembalikan tidak dijamin. Tambahkan ORDER BY di sisi client jika urutannya penting.
• Kamu tidak bisa menaruh RETURNING di dalam subquery. Klausa ini adalah klausa tingkat atas pada pernyataan tulis, bukan sebuah ekspresi.
• RETURNING tidak mengembalikan data hasil modifikasi trigger BEFORE, melainkan nilai yang benar-benar ditulis. Trigger AFTER berjalan di antara penulisan dan pengembalian barisnya.
• Generated column dan nilai DEFAULT terlihat di hasilnya. Justru karena itulah RETURNING * menjadi cara cepat untuk memeriksa apa yang diisi database untukmu.

Selanjutnya: Mengimpor Data CSV

RETURNING sangat berguna saat kamu menulis satu atau beberapa baris sekaligus dan ingin langsung melihat hasilnya. Saat kamu memuat ribuan baris dari sebuah file, kamu akan memakai tool impor CSV milik SQLite. Itulah topik halaman berikutnya.