Insert, upsert, update and delete

Write Plexivo Postgres rows from your app: one or many inserts, upserts on any unique key, updates, counters, deletes and soft deletes.

Writes run as the signed-in user, and your policies’ with check decides what they may write. Each write returns the rows it wrote, as the database stored them, with their ids and defaults.

The table on this page#

sql

create table products (
  id bigint generated always as identity primary key,
  owner uuid not null default auth.uid(),
  sku text not null,
  name text not null,
  price numeric(10, 2) not null,
  stock int not null default 0,
  deleted_at timestamptz,
  unique (owner, sku)
);
alter table products enable row level security;
create policy products_own on products for all to authenticated
  using (owner = auth.uid()) with check (owner = auth.uid());

import { createClient } from '@plexivo/client';

const db = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY);
await db.auth.signUp({ email, password });

dart

import 'package:plexivo_client/plexivo_client.dart';

final db = PlexivoClient(dataUrl, publishableKey);
await db.signUp(email: email, password: password);

Insert#

const tea = await db.from('products').create({ sku: 'TEA-1', name: 'Assam tea', price: 4.5, stock: 40 });
console.log(tea.id);

// Several rows in one request: up to 1,000
const added = await db.from('products').create([
  { sku: 'MUG-1', name: 'Mug', price: 8, stock: 10 },
  { sku: 'POT-1', name: 'Teapot', price: 25 }
]);

// Only the number of rows, not the rows
const n = await db.from('products').create([{ sku: 'CUP-1', name: 'Cup', price: 3 }], { returning: 'minimal' });

dart

final tea = await db.from('products').create({'sku': 'TEA-1', 'name': 'Assam tea', 'price': 4.5, 'stock': 40});

final added = await db.from('products').createMany([
  {'sku': 'MUG-1', 'name': 'Mug', 'price': 8, 'stock': 10},
  {'sku': 'POT-1', 'name': 'Teapot', 'price': 25},
]);

A column you leave out takes its default, never null.

For more than 1,000 rows, createInBatches(rows, 500) sends them 500 at a time. Each batch commits on its own; to have all or none, write them in a transaction of up to 50 batches.

const many = Array.from({ length: 1200 }, (_, i) => ({ sku: `BULK-${i}`, name: `Item ${i}`, price: 1 }));
const inserted = await db.from('products').createInBatches(many, 500);
console.log(inserted.length);

Upsert#

Insert, or update the row that has the same unique key. The key can be one column or several:

// Merge: update every column you send
await db.from('products').upsert({ sku: 'TEA-1', name: 'Assam tea', price: 5 }, { onConflict: 'owner,sku' });

// Update only some columns
await db.from('products').upsert({ sku: 'TEA-1', name: 'Ignored', price: 5.5 }, { onConflict: ['owner', 'sku'], update: ['price'] });

// Ignore: keep the row that is there
await db.from('products').upsert({ sku: 'TEA-1', name: 'Ignored', price: 0 }, { onConflict: 'owner,sku', ignore: true });

dart

await db.from('products').upsert({'sku': 'TEA-1', 'name': 'Assam tea', 'price': 5}, onConflict: 'owner,sku');

await db.from('products').upsert({'sku': 'TEA-1', 'name': 'Ignored', 'price': 5.5}, onConflict: ['owner', 'sku'], update: ['price']);

await db.from('products').upsert({'sku': 'TEA-1', 'name': 'Ignored', 'price': 0}, onConflict: 'owner,sku', ignore: true);

Without onConflict, the primary key decides. Supabase’s ignoreDuplicates: true works too. save(row) inserts a row without an id and updates one with an id, as GORM’s Save does.

Update#

await db.from('products').where('sku', 'MUG-1').update({ price: 9 });

// Counters, done in the database, so two buyers never lose an update
await db.from('products').where('sku', 'MUG-1').decrement('stock');
await db.from('products').where('sku', 'TEA-1').increment('stock', 10);

dart

await db.from('products').where('sku', 'MUG-1').update({'price': 9});

await db.from('products').where('sku', 'MUG-1').decrement('stock');
await db.from('products').where('sku', 'TEA-1').increment('stock', by: 10);

Delete#

await db.from('products').where('sku', 'CUP-1').delete();

await db.from('products').whereLike('sku', 'BULK-%').delete({ returning: 'minimal' });

dart

await db.from('products').where('sku', 'CUP-1').delete();

An update or delete must say which rows it touches. The client libraries stop one without a filter before it is sent, with missing_where, and the Data API refuses it too. Add allowGlobalUpdate() when you do mean every row you can reach.

await db.from('products').update({ price: 0 });

Soft delete#

Give a model its soft-delete column, as GORM’s gorm.DeletedAt. Then delete() sets it, reads skip those rows, and you can bring them back:

const Product = db.model('products', { softDelete: 'deleted_at' });

await Product.query().where('sku', 'POT-1').delete();
const visible = await Product.query().count();                     // without the teapot
const all = await Product.query().unscoped().count();              // with it
await Product.query().onlyTrashed().where('sku', 'POT-1').restore();

dart

final products = db.model('products', softDelete: 'deleted_at');

await products.query().where('sku', 'POT-1').delete();
final visible = await products.query().count();
await products.query().onlyTrashed().where('sku', 'POT-1').restore();

Over HTTPS#

bash

curl "$PLEXIVO_DATA_URL/rest/v1/products?on_conflict=owner,sku&upsert=merge" \
  -H "apikey: $PLEXIVO_PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "content-type: application/json" \
  -d '[{"sku": "TEA-9", "name": "Darjeeling", "price": 7}]'

POST inserts one object or a list of up to 1,000; PATCH and DELETE take filters in the query string, or work on /rest/v1/products/17. Add returning=minimal to get only the count back.

Next steps#

Transactions and functionsSeveral writes that commit together. Row-level securityWho may write what.

Updated 28 October 2026