DEV Community: voboda

Upserts with PostgREST or Supabase

voboda — Sun, 11 Apr 2021 19:06:13 +0000

I have a table with a UNIQUE INDEX on multiple columns, and I'd like to upsert new values to that table based on that unique constraint. Can I do this without major contortions?

Game on.

Users can have one pick per event, so the picks table has 4 columns pickid, userid, eventid, pick. Primary key is unique, and there's also a unique index on userid and eventid.

Now if a user wants to change their pick, I need an upsert (insert if that userid/eventid pair doesn't exist, otherwise update the pick column of the current record.)

Upserts in PostgreSQL are handled with ON CONFLICT, as follows:

INSERT INTO picks (userid, eventid, pick)
VALUES(20, 30,'My pick') 
ON CONFLICT (userid, eventid) 
DO 
   UPDATE SET pick = 'My pick'

PostgREST knows about upserts too, and does the ON CONFLICT part automagically for you. You can set the Prefer: resolution=merge-duplicates or Prefer: resolution=ignore-duplicates headers.

Things get a bit tricky when you want to upsert based on that UNIQUE INDEX based on multiple columns. The automagical upsert works only with primary keys. If you want it to use another column or multiple columns, pass the on_conflict get variable to the PostgREST api, like on_conflict=userid,eventid

One more thing though - I'm use the postgrestjs/supabasejs client, so how do I pass on_conflict? To find out, I heroicly ventured into the postgrest-js source code and found this wonderful PR to support on_conflict. So I just need to add an onConflict option like so:

const { data, error } = await supabase
  .from('picks')
  .insert([{ userid, eventid, pick}], { upsert: true, onConflict: "userid,eventid"})

Notice the camelCase instead of the under_line. (Mental note: it seems camelCase is kind of the preferred naming convention for Javascript libraries.)

That's it! It works nicely, encapsulating somewhat complex logic with clear, understandable and short code.

Easy functions and transactions using Postgres + PostgREST or Supabase

voboda — Thu, 08 Apr 2021 20:49:00 +0000

These days, I'm taking advantage of PostgreSQL's ability to handle logic, saving me all the work of an entire middleware layer. (Instead, using generalized middlewares like Graphile, Supabase, or Postgrest.)

A little gotcha in the Postgres learning curve has been working out the difference between stored procedures and functions, and realizing that the simpler table triggers and constraints (using with check) actually does a lot of what I need.

One place I got stuck is sql transactions, or grouping several sql statements into a group so that if one fails, they all "roll back" as if they didn't happen. PostgreSQL has some specific rules about using transactions in or out of functions and stored procedures -- but I found a simple way out.

It turns out that PostgREST, the middleware Supabase is based on, surrounds rpc() calls in a transaction already. So I could just define a function in sql with:

CREATE OR REPLACE FUNCTION public.topup(user text, amount integer)
    RETURNS integer
    LANGUAGE 'plpgsql'

AS $$
begin
  update users set balance = balance + amount;
  insert into "transactions" (user, amount, note) values (user, amount, 'top up');
  return 1;
end
$$;

In Supabase and PostgREST, this automatically exposes topup in the API. When called over the API, it's automatically wrapped in a transaction. (I tested this by placing a contraint on user.balance to not let it go above 100, and when the user's balance was higher than the amount in the rpc call, neither user.balance was updated, nor was a new record added to transactions.)

Now, I can write a lot of logic into the PostgreSQL database itself. This simplifies my middleware a lot, and often enables me to make apps that call Supabase/PostgREST/Graphile calls directly from my front-end components. An entire layer of devops and code removed.

Multiple Github accounts conveniently

voboda — Fri, 26 Mar 2021 04:09:05 +0000

I use several github accounts, and wanted a way to use them without constantly tweaking remote urls and stuff for each repo.

The good news is at some point, it seems git started to traverse directories upwards, looking for .gitconfig files. So it makes it easy to setup several directories, each of which uses different credentials automatically.

So, for my voboda account on github:

mkdir ~/repos/voboda
cd ~/repos/voboda

And there I added a .gitconfig file containing:

[core]
    sshCommand = ssh -i ~/.ssh/id_rsa_voboda
[user]
    name = Voboda
    email = voboda@mygithubemail.com

Now, anything I need to work on in my voboda account, I just clone that under the ~/repos/voboda directory. For example ~/repos/voboda/onelineforyourmind and ~/repos/voboda/underlinery. Both will use the above user information automatically. That specific ssh command references the ssh key I setup for my voboda github account.

Sidenote: separate ssh keys

On Linux, I setup that ssh key using ssh-keygen -f id_rsa_voboda and then copying the 2 outputted keyfiles to my ~.ssh directory.

supabase-js with sapper

voboda — Mon, 22 Mar 2021 18:11:56 +0000

A few gotchas I found when setting up, so saving my notes here. Hopefully they save you a bit of time.

1) Set the client context to `window`

In rollup.config.js:

export default {
    client: {
        context: 'window', // for supabase compatibility
        input: config.client.input(),

2) Install `@rollup/plugin-json`

Also in rollup.config.js:

import json from '@rollup/plugin-json';

...

export default {
    client: {
        context: 'window', // for supabase compatibility
        input: config.client.input(),
        output: config.client.output(),
        plugins: [
            json(),  //For supabase compatibility 

...

    server: {
        input: config.server.input(),
        output: config.server.output(),
        plugins: [
            json(),  //For supabase compatibility