PostgreSQL-based API Design

PostgreSQL-based API Design

The design idea of this document is mainly to simplify some repetitive verification work of the API. At the same time, the API portal is fixed, and the request is distributed in the portal to do some statistical work and limit the number of requests in the portal.
-function
api_interface (in jsonb, out jsonb); --Entry function (stored procedure)
api_interface_error (code);-unified error handling function
api_interface_lines (user_id, params, team_id);-Examples of line list

--table
api_errors-Error messages on a unified platform
api_request_log-record request log

Portal Settings

The entry is mainly to read some general information, such as access_token (certificate), interface (request interface name), version (interface version) and other information, while recording the request log, and subsequent interface request times limit, etc.
-Function: api_interface (jsonb)

-DROP FUNCTION api_interface (jsonb);

CREATE OR REPLACE FUNCTION api_interface (
    IN _input jsonb,
    OUT _result jsonb)
  RETURNS jsonb AS
$ BODY $
--Access credentials
DECLARE _access_token TEXT;
--Interface version
DECLARE _version text;
--call function
DECLARE _interface TEXT;
--parameter
DECLARE _params JSONB;
--error code
DECLARE code INTEGER;
--wrong information
DECLARE message TEXT;
--User ID
DECLARE _user_id UUID;
DECLARE v_sql TEXT;
BEGIN
    _access_token = _input :: jsonb->> ‘access_token’;
    _version = _input :: jsonb->> ‘version’;
    _interface = _input :: jsonb->> ‘interface’;
    _params = _input :: jsonb-> ‘params’;

    IF (_access_token IS NULL) THEN
        code = 1001;
        SELECT api_interface_error (code) INTO _result;
        RETURN;
    END IF;

    IF (_interface IS NULL) THEN
        code = 1002;
        SELECT api_interface_error (code) INTO _result;
        RETURN;
    END IF;

    IF (_version IS NULL) THEN
        code = 1003;
        SELECT api_interface_error (code) INTO _result;
        RETURN;
    END IF;

    IF (_params IS NULL) THEN
        code = 1004;
        SELECT api_interface_error (code) INTO _result;
        RETURN;
    END IF;

    --Check if access_token exists
    SELECT id FROM public.users WHERE account_id = (SELECT account_id FROM public.account_tokens WHERE token = _access_token LIMIT 1) LIMIT 1 INTO _user_id;

    IF (_user_id IS NULL) THEN
        code = 1005;
        SELECT api_interface_error (code) INTO _result;
        RETURN;
    END IF;

    --Insert into the request log
    INSERT INTO public.api_request_log (user_id, interface, version, params) VALUES (_user_id, _interface, _version, _params);
    -Dynamic stitching SQl
    v_sql = concat (‘SELECT api_interface_’, _interface);
    v_sql = concat (v_sql, ‘($ 1 :: uuid, $ 2 :: jsonb, $ 3 :: uuid) ′);

    EXECUTE v_sql USING _user_id, _params :: jsonb, ‘00000000-0000-0000-0000-000000000000’ INTO _result;
    RETURN;

END $ BODY $
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION api_interface (jsonb)
  OWNER TO postgres;
COMMENT ON FUNCTION api_interface (jsonb) IS ‘API interface’;

Interface examples (such as line list)

-Function: api_interface_lines (uuid, jsonb, uuid)

-DROP FUNCTION api_interface_lines (uuid, jsonb, uuid);

CREATE OR REPLACE FUNCTION api_interface_lines (
     IN _user_id uuid,
     IN _input jsonb,
     IN _team_id uuid,
     OUT _result jsonb)
   RETURNS jsonb AS
$ BODY $
DECLARE code INTEGER;
DECLARE message TEXT;
BEGIN
     code = 0;
     message = ‘‘;
     SELECT row_to_json (t) FROM (SELECT code, message, (SELECT json_agg (x) FROM (SELECT * from lines limit 2) x) lines) t INTO _result;
     RETURN;
END; $ BODY $
   LANGUAGE plpgsql VOLATILE
   COST 100;
ALTER FUNCTION api_interface_lines (uuid, jsonb, uuid)
   OWNER TO postgres;
COMMENT ON FUNCTION api_interface_lines (uuid, jsonb, uuid) IS ‘list of lines’;

Auxiliary functions and tables

Record request log
  Handle errors uniformly
  Use in nodejs

Unified error Handling

CREATE OR REPLACE FUNCTION api_interface_error (
     IN _code integer,
     OUT result jsonb)
   RETURNS jsonb AS
$ BODY $
DECLARE _message text;
BEGIN
     SELECT message FROM api_errors AS AE WHERE AE.code = _code INTO _message;
     IF (_message IS NULL) THEN
         _message = ‘‘;
     END IF;
     SELECT row_to_json (t) FROM (SELECT _code, _message) t INTO result;
     RETURN;
END $ BODY $
   LANGUAGE plpgsql VOLATILE
   COST 100;
ALTER FUNCTION api_interface_error (integer)
   OWNER TO postgres;
COMMENT ON FUNCTION api_interface_error (integer) IS ‘API interface call error’;

Logging request logs

CREATE TABLE api_request_log
(
   id uuid NOT NULL DEFAULT uuid_generate_v4 (),
   user_id uuid,
   create_at timestamp without time zone DEFAULT now (),-
   interface character varying (50),-request interface
   params jsonb,-request parameters
   version character varying (50),-API version
   CONSTRAINT api_request_log_pkey PRIMARY KEY (id)
)
WITH (
   OIDS = FALSE
);
ALTER TABLE api_request_log
   OWNER TO postgres;
COMMENT ON TABLE api_request_log
   IS ‘API request log’;
COMMENT ON COLUMN api_request_log.create_at IS ‘Creation Time’;
COMMENT ON COLUMN api_request_log.interface IS ‘request interface’;
COMMENT ON COLUMN api_request_log.params IS ‘request parameter’;
COMMENT ON COLUMN api_request_log.version IS ‘API version’;

Statistical error messages

CREATE TABLE api_errors
(
   id uuid NOT NULL DEFAULT uuid_generate_v4 (),
   code integer,
   message text,-error message
   CONSTRAINT pk_api_errors_id PRIMARY KEY (id)
)
WITH (
   OIDS = FALSE
);
ALTER TABLE api_errors
   OWNER TO postgres;
COMMENT ON TABLE api_errors
   IS ‘api error message’;
COMMENT ON COLUMN api_errors.message IS ‘error message’;

Nodejs calling stored procedures directly

var _input_json = req.body.input;
     // Judge some parameters of _input_json, such as whether the token is legal, etc.
     // omit ...
     //
     var sql = "SELECT public.api_interface ($ 1 :: jsonb) AS result";
     db.execQuery (sql, [_input_json])
         .then (function (result) {
             return result.rows [0] [‘result‘];
         })
         .then (function (result) {
             // The returned json is already in the standard format
             res.json (result);
         })
         .catch (next) .done ();

Client Request:

android or any:
    String response = ‘‘;
    request
      .send({"access_token":"TOKEN", "interface":"lines", "version":"1.0", "params":{"param1":1, "params2":2}})
      .end(response);
    System.out.println(response);

PostgreSQL-based API Design