Run and return sql in supabase with plv8 - a cheesy hack

Warning: Probably a bad idea

So you want to run an arbitrary sql statement in supabase and return json? And you don't want to learn that weird plpgsql language? because chatGPT can't write decent plpgsql to save it's aritficial life?

TLDR, just put the SQL you want in this template:

CREATE OR REPLACE FUNCTION public.foo_bar()
 RETURNS json
 LANGUAGE plv8
AS $function$


  var num_affected = plv8.execute(`

-- BEGIN SQL

-- SQL GOES HERE

-- END SQL

`);

  // Convert BigInt values to integers
  num_affected.forEach(row => {
    row.user_count = parseInt(row.user_count);
  });

  return JSON.stringify(num_affected);


$function$
;

We simply have to use the plv8 extension and then we can use javascript in our stored procedures.

We can also run sql in the javascript! To do this we can use the plv8.execute method.

So let's say you have a table for storing you and your friends crossword scores. There's a table with a puzzle_id, a user_id, and a score. You whisper this request into the LLM and it spits out:

WITH ranked_solves AS (
  SELECT user_id, puzzle_id, seconds_spent_solving,
    ROW_NUMBER() OVER (PARTITION BY puzzle_id ORDER BY seconds_spent_solving ASC) AS rank
  FROM crosswordsolves
  WHERE is_solved = true
)
SELECT crosswordusers.id,
  COUNT(CASE WHEN rank = 1 THEN 1 END)::INTEGER AS first_place_finishes,
  COUNT(CASE WHEN rank = 2 THEN 1 END)::INTEGER AS second_place_finishes,
  COUNT(*)::INTEGER AS total_solves_attempted
FROM ranked_solves
JOIN crosswordusers ON ranked_solves.user_id = crosswordusers.id
GROUP BY crosswordusers.id;

Of course, we are normal humans so we can't read SQL. But you run the query and it seems to work. Now, just to convert this query to plpgsql and you're done.

Oh wait! You can't write plpgsql! And you don't want to learn it!

That's where our friend plv8 comes in. We can write a function that runs this query and returns the results as json. Then we can call that function from our sql statement.

  var num_affected = plv8.execute(`
WITH ranked_solves AS (
  SELECT user_id, puzzle_id, seconds_spent_solving,
    ROW_NUMBER() OVER (PARTITION BY puzzle_id ORDER BY seconds_spent_solving ASC) AS rank
  FROM crosswordsolves
  WHERE is_solved = true
)
SELECT crosswordusers.id,
  COUNT(CASE WHEN rank = 1 THEN 1 END)::INTEGER AS first_place_finishes,
  COUNT(CASE WHEN rank = 2 THEN 1 END)::INTEGER AS second_place_finishes,
  COUNT(*)::INTEGER AS total_solves_attempted
FROM ranked_solves
JOIN crosswordusers ON ranked_solves.user_id = crosswordusers.id
GROUP BY crosswordusers.id;
  `);

  // Convert BigInt values to integers
  num_affected.forEach(row => {
    row.id = parseInt(row.id);
    row.first_place_finishes = parseInt(row.first_place_finishes);
    row.second_place_finishes = parseInt(row.second_place_finishes);
  });

  return JSON.stringify(num_affected);

And then we just wrap this in the function definition and we're done (my syntax highlighting is broken here of course)

create extension plv8;
set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.get_stats()
 RETURNS json
 LANGUAGE plv8
AS $function$
  var num_affected = plv8.execute(`
WITH ranked_solves AS (
  SELECT user_id, puzzle_id, seconds_spent_solving,
    ROW_NUMBER() OVER (PARTITION BY puzzle_id ORDER BY seconds_spent_solving ASC) AS rank
  FROM crosswordsolves
  WHERE is_solved = true
)
SELECT crosswordusers.id,
  COUNT(CASE WHEN rank = 1 THEN 1 END)::INTEGER AS first_place_finishes,
  COUNT(CASE WHEN rank = 2 THEN 1 END)::INTEGER AS second_place_finishes,
  COUNT(*)::INTEGER AS total_solves_attempted
FROM ranked_solves
JOIN crosswordusers ON ranked_solves.user_id = crosswordusers.id
GROUP BY crosswordusers.id;
  `);

  // Convert BigInt values to integers
  num_affected.forEach(row => {
    row.id = parseInt(row.id);
    row.first_place_finishes = parseInt(row.first_place_finishes);
    row.second_place_finishes = parseInt(row.second_place_finishes);
  });

  return JSON.stringify(num_affected);
$function$
;

To call it on the client or server, you just use the rpc method on the supabase client.

const { data, error } = await supabaseAdmin.rpc("get_stats");

And that's it! You can now run arbitrary sql and return json.

If you want to add arguments, you can add them as an array as a second argument to the plv8.execute call, and just use the $1 style argument passing like so:

CREATE OR REPLACE FUNCTION public.get_leaderboard_for_month(leaderboard_id integer, day text)
 RETURNS json
 LANGUAGE plv8
AS $function$
{
  var num_affected = plv8.execute(`
    SELECT
      cu.username,
      cs.seconds_spent_solving,
      RANK() OVER (PARTITION BY cp.date ORDER BY cs.seconds_spent_solving ASC) AS rank,
      cp.date AS solve_date
    FROM
      crosswordleaderboards cl
      JOIN crosswordleaderboards_users clu ON cl.id = clu.leaderboard_id
      JOIN crosswordusers cu ON clu.user_id = cu.id
      JOIN crosswordsolves cs ON cu.id = cs.user_id
      JOIN crosswordpuzzles cp ON cs.puzzle_id = cp.id
    WHERE
      cl.id = $1
      AND cp.date BETWEEN DATE($2) - INTERVAL '30 days' AND DATE($2)
    ORDER BY
      cp.date DESC, cs.seconds_spent_solving ASC;
  `, [leaderboard_id, day]);
  
  // Serialize JSON with custom replacer function to handle BigInt values
  return JSON.stringify(num_affected, (_, value) => {
    if (typeof value === 'bigint') {
      return value.toString();
    }
    return value;
  });
}
$function$
;

Note that in this case, we opted to use the toString instead of parseInt because we want to preserve the BigInt values.