Last active
March 13, 2024 02:07
-
-
Save notakaos/de6b63d504e6f3beb96d1e57cf306286 to your computer and use it in GitHub Desktop.
cuid for PostgreSQL with PL/v8
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mydb=# select cuid(); | |
cuid | |
---------------------------- | |
ck2ag1r6h00ln320f2ejrthj1p | |
(1 row) | |
mydb=# select cuid(); | |
cuid | |
---------------------------- | |
ck2ag1rkq00lo320f2g3ro0o7o | |
(1 row) | |
mydb=# select cuid(); | |
cuid | |
---------------------------- | |
ck2ag1rsg00lp320f2f32ccfi3 | |
(1 row) |
@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;
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
How do I define CUID as a column type?
@notakaos can I do something like this: