cuid for PostgreSQL with PL/v8

create_function_plv8_cuid.sql

-- original code: https://github.com/ericelliott/cuid

-- Add the "plv8" extension
create extension if not exists "plv8";

-- Add the "pgcrypto" extension
create extension if not exists "pgcrypto";
\dx

-- Connect a database
\c mydb

-- initialize
CREATE OR REPLACE FUNCTION plv8_init() RETURNS void AS $$

  const blockSize = 4;
  const base = 36;
  const discreteValues = Math.pow(base, blockSize);
  let c = 0;

  function pad(num, size) {
    const s = '000000000' + num;
    return s.substring(s.length - size);
  }

  function fingerprint() {
    const padding = 2;
    const pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    const pidWithPadding = pad(pid.toString(36, padding));
    const hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    const length = hostname.length;
    const hostId =
      pad(
        hostname
          .split('')
          .reduce((prev, char) => +prev + char.charCodeAt(0), +length + 36)
          .toString(36)
        , padding);
    return pid + hostId;
  }

  function getRandomValue() {
    const lim = Math.pow(2, 32) - 1;
    const len = 4;
    const num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    return Math.abs(num / lim);
  }

  function randomBlock() {
    return pad((getRandomValue() * discreteValues << 0).toString(base), blockSize);
  }

  function safeCounter() {
    c = c < discreteValues ? c : 0;
    c++;
    return c - 1;
  }

  plv8.global = {
    c,
    blockSize,
    base,
    discreteValues,

    pad,
    fingerprint,
    getRandomValue,
    randomBlock,
    safeCounter
  };

$$ LANGUAGE plv8 STRICT;
-- SET plv8.start_proc = 'plv8_init';
-- RESET plv8.start_proc
alter database mydb set plv8.start_proc to plv8_init;

CREATE OR REPLACE FUNCTION cuid() RETURNS text AS $$
  const { base, blockSize, safeCounter, fingerprint, randomBlock, pad } = plv8.global;

  const letter = 'c';
  const timestamp = (new Date().getTime()).toString(base);
  const counter = pad(safeCounter().toString(base), blockSize);
  const print = fingerprint();
  const random = randomBlock() + randomBlock();

  // return `letter: ${letter}, timestamp: ${timestamp}, counter: ${counter}, print: ${print}, random: ${random}`;
  return letter + timestamp + counter + print + random;
$$ LANGUAGE plv8 STRICT;

log.sql

mydb=# select cuid();
         cuid          
----------------------------
 ck2ag1r6h00ln320f2ejrthj1p
(1 row)

mydb=# select cuid();
         cuid          
----------------------------
 ck2ag1rkq00lo320f2g3ro0o7o
(1 row)

mydb=# select cuid();
         cuid          
----------------------------
 ck2ag1rsg00lp320f2f32ccfi3
(1 row)

How do I define CUID as a column type?

@notakaos can I do something like this:

CREATE EXTENSION cuid;


CREATE TABLE foo
(
    id uuid default cuid() primary key,
    value bigint not null
);

@harshmandan, just treat CUIDs as text in the DB, eg..

CREATE EXTENSION cuid;
CREATE TABLE foo (
    id text default cuid() primary key,
    value bigint not null
);

I'm getting this error.
function public.gen_random_bytes(unknown) does not exist

Yeah, same error as @akashlama1998-icloud :(

@akashlama1998-icloud Okay, on something like supabase, where pgcrypto is already installed, make sure to make the extension available in your main schema (public).

ALTER EXTENSION pgcrypto SET SCHEMA public;

Thanks for this.

I've ported/updated this to cuidv2 (by taking the nodejs impl and adjusting it to pgcrypto) here https://gist.github.com/ashb/d0d95cfe372e1fdf8ad4e52c4016d76f