Comments (10)
It seems possible to change the decoding format according to the header. So Prefer: uuid=base58 or Prefer: uuid=base16 can be specified if needed.
Yes, modifying the output format based on a header seems feasible. You could implement a dynamic data rep function which draws configurations from the request. (This idea could theoretically extend to a data rep that garners settings from an "API configuration table," but I suspect that would be rather awful to work with in practice, akin to a function in programming that varies its behaviour contingent on a global variable. Error prone and surprising.)
In practice, while different APIs might vary on using base16 or base58, I have to believe a singular API which canβt decide one way or another on how to output ids would be a rare beast indeed.
It's sometimes desirable to be precise in what you produce but forgiving in what you accept. In this case you wouldn't need a header to do so. To accept both formats, simply count the characters β base16 UUIDs are longer than base58 ones. So with static data rep functions you could accept various input forms and maintain consistent canonical output without needing specific settings or headers at all.
from postgrest-docs.
For my use case I did something like this:
CREATE TABLE posts (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
short_id text NOT NULL GENERATED ALWAYS AS (uuid_to_base58(id)) STORED,
...
);
I could not get SQL to handle the memory of the conversion, so I used the V8 extension to directly translate my JS. Let me know if this could be simplified with pure SQL.
CREATE EXTENSION IF NOT EXISTS plv8;
CREATE OR REPLACE FUNCTION uuid_to_base58(uuid uuid)
RETURNS text AS $$
const base58Alphabet = '123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz';
let bigintValue = BigInt('0x' + uuid.toString().replace(/-/g, ''));
let base58 = '';
while (bigintValue > BigInt(0)) {
const remainder = bigintValue % BigInt(base58Alphabet.length);
bigintValue /= BigInt(base58Alphabet.length);
base58 = base58Alphabet.charAt(Number(remainder)) + base58;
}
return base58;
$$ LANGUAGE plv8 IMMUTABLE;
But obviously if I didn't need an extra column and I could just translate it directly with PostgREST, this would be ideal and save time.
This is important for me because I want my url to be simpler than my database UUID.
J
from postgrest-docs.
but I suspect that would be rather awful to work with in practice, akin to a function in programming that varies its behaviour contingent on a global variable. Error prone and surprising.
@aljungberg Agree. We can now move forward with PostgREST/postgrest#2839, let me know if you have any feedback there.
from postgrest-docs.
Ah, seems like a particular use case of PostgREST/postgrest#2523.
from postgrest-docs.
Closing as duplicate of PostgREST/postgrest#2523.
from postgrest-docs.
This is the perfect example for PostgREST/postgrest#2523. Putting it here to use it for the reference doc.
from postgrest-docs.
Default would obviously be base16... other options probably wouldn't be useful.
Prefer: uuid=base58
It seems possible to change the decoding format according to the header. So Prefer: uuid=base58
or Prefer: uuid=base16
can be specified if needed.
create domain uuid_custom_base as uuid;
CREATE CAST (uuid_custom_base AS json) WITH FUNCTION json(uuid_custom_base) AS IMPLICIT;
-- pseudocode
create or replace function json(uuid_custom_base) returns json as $$
select
case
when (current_setting('request.headers')::json)->'prefer' = 'uuid=base16' then
to_json(encode($1, 'base16'))
else
to_json(encode($1, 'base58'))
end
$$ language sql;
from postgrest-docs.
But obviously if I didn't need an extra column and I could just translate it directly with PostgREST, this would be ideal and save time.
Yep, not needing an extra column just to format a field is one of the founding reasons for the data reps feature we're working on now.
I could not get SQL to handle the memory of the conversion, so I used the V8 extension to directly translate my JS. Let me know if this could be simplified with pure SQL.
You could adapt this function we use at Screenly for formatting ULIDs (base32) stored as UUIDs behind the scenes. But it's a bit of a monster!
CREATE OR REPLACE FUNCTION public.ulid_str(input uuid) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
-- Crockford's Base32
encoding BYTEA = '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
output TEXT = '';
ulid BYTEA;
BEGIN
ulid = decode(replace(input::text, '-', ''), 'hex');
-- Encode the timestamp
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 0) & 224) >> 5));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 0) & 31)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 1) & 248) >> 3));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 1) & 7) << 2) | ((GET_BYTE(ulid, 2) & 192) >> 6)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 2) & 62) >> 1));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 2) & 1) << 4) | ((GET_BYTE(ulid, 3) & 240) >> 4)));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 3) & 15) << 1) | ((GET_BYTE(ulid, 4) & 128) >> 7)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 4) & 124) >> 2));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 4) & 3) << 3) | ((GET_BYTE(ulid, 5) & 224) >> 5)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 5) & 31)));
-- Encode the entropy
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 6) & 248) >> 3));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 6) & 7) << 2) | ((GET_BYTE(ulid, 7) & 192) >> 6)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 7) & 62) >> 1));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 7) & 1) << 4) | ((GET_BYTE(ulid, 8) & 240) >> 4)));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 8) & 15) << 1) | ((GET_BYTE(ulid, 9) & 128) >> 7)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 9) & 124) >> 2));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 9) & 3) << 3) | ((GET_BYTE(ulid, 10) & 224) >> 5)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 10) & 31)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 11) & 248) >> 3));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 11) & 7) << 2) | ((GET_BYTE(ulid, 12) & 192) >> 6)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 12) & 62) >> 1));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 12) & 1) << 4) | ((GET_BYTE(ulid, 13) & 240) >> 4)));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 13) & 15) << 1) | ((GET_BYTE(ulid, 14) & 128) >> 7)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 14) & 124) >> 2));
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 14) & 3) << 3) | ((GET_BYTE(ulid, 15) & 224) >> 5)));
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 15) & 31)));
RETURN output;
END
$$ IMMUTABLE;
from postgrest-docs.
If you got that for base58, let me know! My URLs already use that version.
Thanks!
J
from postgrest-docs.
Data Representations are documented now including examples for how to do this with base64: https://postgrest.org/en/v12/references/api/domain_representations.html#domain-response-format
Whether to do that with base58 or something else is a pure PostgreSQL question, not related to PostgREST.
from postgrest-docs.
Related Issues (20)
- How-to for dynamic schemas with `pre-config` HOT 1
- serve image with img tag HOT 1
- Readthedocs will stop working on September 25 with the current config file HOT 1
- Docker crashing on M1 HOT 3
- limiting HTTP verbs in openapi response HOT 3
- Link to Installation from tutorial
- Move binary installation from tut0.rst to install.rst and add install options to tabs
- Library not loaded on Mac HOT 4
- Rename admin page name to Observability
- Deprecated "External JWT Generation" section using Auth0 Rules
- Chocolatey doesn't add `postgrest` to the PATH
- Drop all plain HTTP snippets in favor of `curl` commands HOT 1
- Missing entries in Preferences section HOT 1
- Show a more prominent version number
- Having more than one internal schema on schema isolation is confusing HOT 4
- Use the term "secret" instead of "password" in Tutorial 1
- Avoid Globbing in Curl examples HOT 2
- Move from tailwind to PicoCSS in HTMX how-to
- Expand on Schema Isolation HOT 2
- Recommend using `row_security = off` for starting up with RLS HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
π Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. πππ
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google β€οΈ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from postgrest-docs.