Upsert generator

Upsert into Supabase: INSERT … ON CONFLICT DO UPDATE

Name your table, key and columns and get the statement Supabase actually accepts, with a guard so an older row never overwrites a newer one. Below it: how the mechanic works, what breaks, and how Datrise loads Supabase incrementally.

Generate the statement

Statement · INSERT … ON CONFLICT DO UPDATE

INSERT INTO "deals" ("id", "name", "stage", "amount", "owner_id", "updated_at")
SELECT "id", "name", "stage", "amount", "owner_id", "updated_at"
FROM "deals_staging"
ON CONFLICT ("id") DO UPDATE SET
  "name" = EXCLUDED."name",
  "stage" = EXCLUDED."stage",
  "amount" = EXCLUDED."amount",
  "owner_id" = EXCLUDED."owner_id",
  "updated_at" = EXCLUDED."updated_at"
WHERE "deals"."updated_at" IS NULL
   OR "deals"."updated_at" < EXCLUDED."updated_at";

How the upsert works in Supabase

Supabase is Postgres, so the upsert is INSERT … ON CONFLICT DO UPDATE, and the guarantees are the same: a unique index on the key and an atomic per-row write. What differs is the path the data takes. supabase-js exposes .upsert(rows, { onConflict: 'id' }) over PostgREST, which is convenient for a few hundred rows but subject to request size limits and to row-level security on the calling key.

For a sync job, connect directly to the database (the pooled connection string) with the service role, COPY into a staging table in a dedicated schema, and run the generated statement. Keep synced tables out of the public schema exposed by the API unless you have written RLS policies for them; a synced CRM table with no policy is readable by anyone holding the anon key.

Before you run it

The conflict target needs a UNIQUE index or constraint on exactly those columns; without one Postgres raises "there is no unique or exclusion constraint matching the ON CONFLICT specification".
EXCLUDED is the row that would have been inserted. The WHERE after DO UPDATE runs per conflicting row, so a stale row (older updated-at) is skipped rather than overwriting fresher data.
PostgreSQL 15 added MERGE for multi-branch logic (WHEN NOT MATCHED BY SOURCE … DELETE), but MERGE is not concurrency-safe under two writers; ON CONFLICT is the atomic upsert.

Questions people ask

supabase-js .upsert() or SQL?

.upsert() is fine for small interactive writes. For batches of thousands of rows use SQL over a direct connection: fewer round trips, no PostgREST payload limits, and the watermark guard.

Does row-level security apply to upserts?

Yes, when the write comes through the API with the anon or authenticated key. The service role and direct database connections bypass RLS, which is what a server-side sync should use.

What does ignoreDuplicates do?

It maps to ON CONFLICT DO NOTHING: existing rows are left untouched instead of updated. Use it for append-only facts, not for CRM records that change.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Supabase with this exact mechanic, a watermark on updated-at, and typed columns, so the statement above is what runs on your behalf. Join the waitlist to get early access.

Browse the integration catalog