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