Roadmap: nodeId support in custom return types

[marshall007]

In addition to the roadmap items outlined in the README, we've identified another use case.

In the following example we have a user_history table in a private schema with custom queries and mutations exposed in the public schema. We create a custom type public.user_history_item which matches the underlying table definition.

In this scenario it would be nice if we could expose a nodeId computed property on the custom type so that we can interact with it more like a default CRUD operation from the client's perspective.

create table private.user_history (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id             text NOT NULL REFERENCES public.users(auid),
  data                jsonb NOT NULL,
  visited_date        timestamptz NOT NULL DEFAULT now()
);


create type public.user_history_item as (
  id                  uuid,
  user_id             text,
  data                jsonb,
  visited_date        timestamptz
);


create function public.get_history(
  "between" tstzrange default tstzrange(now() - interval '1 DAY', now())
) returns setof public.user_history_item
as $$
  select *
  from private.user_history
  where
    "between" @> visited_date AND
    user_id = current_setting('user.auid')::text
  order by visited_date desc;
$$ language sql stable strict security definer;


create function public.set_history(
  data json
) returns public.user_history_item
as $$
  insert into private.user_history (user_id, data, visited_date)
  values (
    current_setting('user.auid')::text,
    data,
    now()
  )
  returning *;
$$ language sql volatile strict security definer;

[marshall007]

Note: we're aware of various ways we could work around this (namely by moving the user_history table to the public schema and using @omit smart comments), but you can imagine a broad variety of custom queries returning partial or otherwise transformed table types that you still want to attach consistent identifiers to.

The tricky case will be supporting custom types that could have multiple nodeId references to different (or even the same) tables. I suspect the only way to really achieve this will be @foreignKey smart comments on custom types (which I think is currently only supported on materialized views?).

[benjie]

The problem with this is that elements with node IDs are supposed to be fetchable - i.e. there'd have to be an interface for fetching them. This also prevents us treating them in the same way we do everything else - e.g. we cannot fetch the record and pass it to functions - we'd have to pass the raw identifiers instead which is not how everything works currently.

It might be that a view is a better approach for this than a custom type?