Domain Representations

Domain Representations separates “how the data is presented” from “how the data is stored”. It works by creating domains and casts, the latter act on the former to present and receive the data in different formats.

Custom Domain

Suppose you want to use a uuid type for a primary key and want to present it shortened to web users.

For this, let’s create a domain based on uuid.

create domain app_uuid as uuid;

-- and use it as our table PK.
create table profiles(
  id   app_uuid
, name text
);

-- some data for the example
insert into profiles values ('846c4ffd-92ce-4de7-8d11-8e29929f4ec4', 'John Doe');

Domain Response Format

We can shorten the uuid with base64 encoding. Let’s use JSON as our response format for this example.

To change the domain format for JSON, create a function that converts app_uuid to json.

-- the name of the function is arbitrary
CREATE OR REPLACE FUNCTION json(app_uuid) RETURNS json AS $$
  select to_json(encode(uuid_send($1),'base64'));
$$ LANGUAGE SQL IMMUTABLE;

-- check it works
select json('846c4ffd-92ce-4de7-8d11-8e29929f4ec4'::app_uuid);
            json
----------------------------
 "hGxP/ZLOTeeNEY4pkp9OxA=="

Then create a CAST to tell PostgREST to convert it automatically whenever a JSON response is requested.

CREATE CAST (app_uuid AS json) WITH FUNCTION json(app_uuid) AS IMPLICIT;

With this you can obtain the data in the shortened format.

HTTPCurl

GET /profiles HTTP/1.1
Accept: application/json

[{"id":"hGxP/ZLOTeeNEY4pkp9OxA==","name":"John Doe"}]

Note

• Casts on domains are ignored by PostgreSQL, their interpretation is left to the application. We’re discussing the possibility of including the Domain Representations behavior on pgsql-hackers.

• It would make more sense to use base58 encoding as it’s URL friendly but for simplicity we use base64 (supported natively in PostgreSQL).

Important

After creating a cast over a domain, you must refresh PostgREST schema cache. See Schema Cache Reloading.

Domain Filter Format

For Horizontal Filtering to work with the shortened format, you need a different conversion.

PostgREST considers the URL query string to be, in the most generic sense, text. So let’s create a function that converts text to app_uuid.

-- the name of the function is arbitrary
CREATE OR REPLACE FUNCTION app_uuid(text) RETURNS app_uuid AS $$
  select substring(decode($1,'base64')::text from 3)::uuid;
$$ LANGUAGE SQL IMMUTABLE;

-- plus a CAST to tell PostgREST to use this function
CREATE CAST (text AS app_uuid) WITH FUNCTION app_uuid(text) AS IMPLICIT;

Now you can filter as usual.

HTTPCurl

GET /profiles?id=eq.hGxP/ZLOTeeNEY4pkp9OxA== HTTP/1.1
Accept: application/json

[{"id":"hGxP/ZLOTeeNEY4pkp9OxA==","name":"John Doe"}]

Note

If there’s no CAST from text to app_uuid defined, the filter will still work with the native uuid format (846c4ffd-92ce-4de7-8d11-8e29929f4ec4).

Domain Request Body Format

To accept the shortened format in a JSON request body, for example when creating a new record, define a json to app_uuid conversion.

-- the name of the function is arbitrary
CREATE OR REPLACE FUNCTION app_uuid(json) RETURNS public.app_uuid AS $$
  -- here we reuse the previous app_uuid(text) function
  select app_uuid($1 #>> '{}');
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (json AS public.app_uuid) WITH FUNCTION app_uuid(json) AS IMPLICIT;

Now we can Insert (or Update) as usual.

HTTPCurl

POST /profiles HTTP/1.1
Content-Type: application/json
Prefer: return=representation

{"id":"zH7HbFJUTfy/GZpwuirpuQ==","name":"Jane Doe"}

The response:

[{"id":"zH7HbFJUTfy/GZpwuirpuQ==","name":"Jane Doe"}]

Note that on the database side we have our regular uuid format.

select * from profiles;

                  id                  |   name
--------------------------------------+----------
 846c4ffd-92ce-4de7-8d11-8e29929f4ec4 | John Doe
 cc7ec76c-5254-4dfc-bf19-9a70ba2ae9b9 | Jane Doe
(2 rows)

Note

If there’s no CAST from json to app_uuid defined, the request body will still work with the native uuid format (cc7ec76c-5254-4dfc-bf19-9a70ba2ae9b9).

Advantages over Views

Views also allow us to change the format of the underlying type. However they come with drawbacks that increase complexity.

1. Formatting the column in the view makes it non-updatable since Postgres doesn’t know how to reverse the transform. This can be worked around using INSTEAD OF triggers.

2. When filtering by this column, we get full table scans for the same reason (also applies to Computed Fields) . The performance loss here can be avoided with a computed index, or using a materialized generated column.

3. If the formatted column is used as a foreign key, PostgREST can no longer detect that relationship and Resource Embedding breaks. This can be worked around with Computed Relationships.

Domain Representations avoid all the above drawbacks. Their only drawback is that for existing tables, you have to change the column types. But this should be a fast operation since domains are binary coercible with their underlying types. A table rewrite won’t be required.

Note

Why not create a base type instead? CREATE TYPE app_uuid (INTERNALLENGTH = 22, INPUT = app_uuid_parser, OUTPUT = app_uuid_formatter).

Creating base types need superuser, which is restricted on cloud hosted databases. Additionally this way lets “how the data is presented” dictate “how the data is stored” which would be backwards.