Expose Postgresql Stored Functions as Hasura Mutations

Date: 25 Feb 2020

EDIT: 2021-11-17

Hasura now supports volatile functions as mutations. So this article is no longer relevant.

See: https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/custom-functions.html

Hasura does not provide a direct mechanism to call postgresql stored functions as mutations at the current version (1.2-beta), but you can combine some easy tricks to archive that.

Requirements

Expose arbitrary user defined functions as mutations
Accept arbitrary parameters
Return arbitrary results
Control which functions are exposed by graphql

Proposed Solution

Define a table to list exposed functions
Define a table to enqueue function calls
Define a trigger to call actual functions
Configure hasura with proper presets

fig1

Implementation

1. Create a table for the names of the functions that will be exposed by GraphQL

--    This table saves actions types.
--    Any action inserted in action_journal must be registerded here.
CREATE TABLE action (
    id_ text not null primary key
);

2. Create a table to insert function calls (mutation)

--    This table saves actions. A trigger on insert will
--    dispatch logic associated with the action.
--    Action Functions Contract:
--       Signatuire: 
--         FUNCTION action_{one of action.id_}(journalId bigint, userId text, request jsonb) RETURNS jsonb
--       Returns:
--         { _status: success | error, _message?, ...rest }
--
CREATE TABLE action_journal(
    id_ bigserial primary key,
    ts_ timestamp not null default now(),
    user_id_ text not null,
    action_id_ text not null references action(id_),
    request_ jsonb not null,
    response_ jsonb
);

3. Define a trigger to call actual functions on insert on action_journal

--    Dispatch actions to the actual function based on action_journal.action_id_.
--    Important: Due to postgresql limitations about transaction handling in triggers,
--               any exception raised from the function will propagate to the caller
--               and the transaction will be rolled back.
CREATE OR REPLACE FUNCTION action_dispatcher_trigger() 
RETURNS trigger AS $BODY$
DECLARE
    response jsonb;
BEGIN
    EXECUTE 'SELECT action_' || NEW.action_id_ || '($1, $2, $3)' 
        INTO response
        USING NEW.id_, NEW.user_id_, NEW.request_;
    NEW.response_ = response;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER dispatcher
    BEFORE INSERT
    ON action_journal
    FOR EACH ROW
    EXECUTE PROCEDURE action_dispatcher_trigger();

4. Configure hasura

Expose table action_journal
Set insert permissions as needed
Configure insert presets to match user_id_ with session var x-hasura-user-id

Test

Ok, now we can define our own function, register it in actions and call it from GraphQL

1. Define our function

-- Simple function to do something stupid.
-- request: { a, b }
CREATE OR REPLACE FUNCTION action_sum(action_id bigint, user_id text, request jsonb)
RETURNS jsonb AS $BODY$
DECLARE
    result numeric;
BEGIN
    result = (request->>'a')::numeric + (request->>'b')::numeric;
    RETURN jsonb_build_object(
        '_status', 'success',
        'result', result
    );
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

2. Register our function as public

Expose our function

INSERT INTO action VALUES ('sum');

The trigger will prepend ‘action_’ and call action_sum

3. Call our function as a graphql mutation

mutation POST_ACTION($object : action_journal_insert_input!) {
    action: insert_action_journal(objects: [$object]) {
      returning {
        response_
      }
    }
}

variables {
    object: {
        action_id_: "sum",
        request_: { a: 10, b: 20 }
    }
}

Result:

{
    data: {
        action: {
            returning: [
                {
                    response_: {
                        _status: "success",
                        result: 30
                    }
                }
            ]
        }
    }
}

To add more functions you just need to insert the name in table action and create the function in postgresql with ‘action_’ prefix.

Github example code

See example implementation code: Link to source code

hasura plpgsql postgresql