Missing Use Case: Upsert

[pikaju]

Overview

Upsert — inserting a row or updating it if it already exists — is one of the most common write patterns in application development. PostgreSQL supports this via INSERT ... ON CONFLICT DO UPDATE, MySQL via INSERT ... ON DUPLICATE KEY UPDATE, and SQLite via INSERT OR REPLACE / ON CONFLICT. Toasty has no upsert support; inserts that hit a unique constraint simply fail.

Use Cases

1. Idempotent Event Ingestion

Processing webhooks, message queues, or API retries where the same event may arrive more than once.

INSERT INTO events (external_id, payload, received_at)
VALUES ('evt_abc123', '{"type":"payment"}', NOW())
ON CONFLICT (external_id) DO UPDATE
SET payload = EXCLUDED.payload, received_at = EXCLUDED.received_at;

Without upsert, the application must catch the unique-constraint error and issue a separate UPDATE, introducing a race condition window.

2. Counters and Accumulators

Incrementing a counter or appending to a value, creating the row on first encounter.

INSERT INTO page_views (url, view_count)
VALUES ('/home', 1)
ON CONFLICT (url) DO UPDATE
SET view_count = page_views.view_count + 1;

3. Last-Seen / Heartbeat Tracking

Updating a timestamp each time a device or user checks in, creating the record if it's the first time.

INSERT INTO device_heartbeats (device_id, last_seen_at, firmware_version)
VALUES ('d-42', NOW(), '3.1.0')
ON CONFLICT (device_id) DO UPDATE
SET last_seen_at = EXCLUDED.last_seen_at,
    firmware_version = EXCLUDED.firmware_version;

4. User Settings / Preferences

Saving a user preference that may or may not exist yet — a natural fit for ON CONFLICT.

INSERT INTO user_settings (user_id, key, value)
VALUES (7, 'theme', 'dark')
ON CONFLICT (user_id, key) DO UPDATE
SET value = EXCLUDED.value;

Current Limitation in Toasty

• No ON CONFLICT or DO UPDATE representation in the statement AST.
• No .upsert() or .on_conflict() method on create builders.
• Inserts that violate a unique constraint return an error with no recovery path within the ORM.

Possible Directions

• Add an .on_conflict(field) builder method that accepts a conflict target (unique column or constraint) and a closure specifying which fields to update.
• For DynamoDB, map to conditional PutItem with attribute updates on condition-check failure, or use UpdateItem with an attribute_not_exists condition for insert-or-update semantics.