db.js

import postgres from "postgres";

const connectionString = "[YOUR CONNECTION STRING]";
const sql = postgres(connectionString);

export default sql;

setup.js

import sql from "./db.js";

async function setup() {
  await sql`CREATE EXTENSION IF NOT EXISTS pgsodium`;

  await sql`
  CREATE TABLE IF NOT EXISTS test (
      id bigserial primary key,
      name text,
      secret text
      );
  `;

  const keyResult =
    await sql`SELECT format('ENCRYPT WITH KEY ID %s', (pgsodium.create_key('aead-det')).id) AS seclabel`;
  const { seclabel } = keyResult[0];

  await sql.unsafe(
    `SECURITY LABEL FOR pgsodium ON COLUMN test.secret IS '${seclabel}'`
  );

  await sql`
  CREATE OR REPLACE FUNCTION upsert_test(p_id bigint, p_name text DEFAULT NULL, p_secret text DEFAULT NULL)
      RETURNS test LANGUAGE sql AS
      $$
      INSERT INTO test (id, name, secret) VALUES (p_id, p_name, p_secret)
      ON CONFLICT (id) DO UPDATE
          SET name   = coalesce(p_name, (SELECT name FROM test WHERE id = p_id)),
              secret = coalesce(p_secret, (SELECT decrypted_secret FROM decrypted_test WHERE id = p_id))
      RETURNING *
      $$;
  `;

  console.log("Setup complete");
}

setup();

test.js

import { createClient } from "@supabase/supabase-js";
import dotenv from "dotenv";

dotenv.config();

const supabaseUrl = process.env.SUPABASE_URL;
const supabaseServiceKey = process.env.SUPABASE_SERVICE_KEY;
const options = {
  auth: {
    persistSession: false,
  },
};

const supabase = createClient(supabaseUrl, supabaseServiceKey, options);

async function upsertTestData(p_id, p_name, p_secret) {
  const { data, error } = await supabase.rpc("upsert_test", {
    p_id: p_id,
    p_name: p_name,
    p_secret: p_secret,
  });

  if (error) {
    console.error("Error executing function:", error);
  } else {
    console.log("Returned data:", data);
  }
}

// Example usage
upsertTestData(1, "John Doe", "secretValue");