dbrpc - Database RPC service written in go language.

JSON-RPC <-> Postgresql proxy service.

WARNING: Current version of this project is not intended for production use. This is proof-of-concept. Tests, docs and more than 1 committer is required for getting this project production-ready.

This service

• gets http request (GET or POST)

  http://hostname/api/function?arg1=val1&arg2=val2
  

• loads database function signature like

  CREATE FUNCTION function(arg1 TYPE, arg2 TYPE) RETURNS SETOF ...
  

• calls sql

  select * from function(arg1 := val1, arg2 := val2)
  

• and returns query result as json:

  curl "http://localhost:8081/api/public.echo?id=1&name=op" | jq "."
  {
    "result": [
      {
        "name": "op",
        "id": 1
      }
    ],
    "success": true
  }
  

Also, the same functionality may be used via JSON-RPC interface

Features

• configurable limit of simultaneous database connections
• caching with groupcache
• gracefull restart
• CORS support
• JSON-RPC over HTTP interface
• required args checking
• method index via /rpc/index[.json]
• named notation
• cache expiration via max_age function attribute
• JWT result encoding for configured functions
• JWT header validation & func args substitution
• Authentication
• Reset metadata cache on SIGHUP and via LISTEN
• i18n
• Access control
• RPC interface (gRPC?)
• Cache warm/bench/test with wget
• Metrics for Prometheus via expvar
• Integrated templates
• Swagger & human autodoc

ToDo

• endless uses syscall.Kill which is not portable to Windows yet.
• improve tests
• add --index arg - proc name to fetch functions list (and name -> function mapping)
• delay index load (via listen)
• light version - without index table
• ReadOnly function attr (for RO transactions & different db connection)/ Method with RW cached <1sec
• Avoid escaping (\u003cbr\u003e)
• add cron_func and cron_interval for this: for q := range "select * from cron_func(stamp)" { select * from q(stamp) }
• check if index query closed correctly
• fatal if no index data

Arrays

Declaration:

SELECT ws.register_comment(
     'echo_arr'
    ,'тест массива'
    ,'{"a_name":"массив","a_id":"число"}'
    ,'{"name":"массив","id":"число"}'
    ,''
);

CREATE OR REPLACE FUNCTION echo_arr(
  a_name   TEXT[]
,  a_id     INTEGER DEFAULT 5
) RETURNS TABLE(name TEXT[], id INTEGER) LANGUAGE 'sql' AS
$_$
    SELECT $1, $2;
$_$;

Calls:

curl -gs 'http://localhost:8081/rpc/echo_arr?a_id=107050&a_name=2&a_name=3'
{
    "success": true,
    "result": [
        {
            "id": 107050,
            "name": [
                "2",
                "3"
            ]
        }
    ]
}

curl -gs 'http://localhost:8081/rpc/echo_arr?a_id=107050&a_name=2,3'
{
    "success": true,
    "result": [
        {
            "id": 107050,
            "name": [
                "2",
                "3"
            ]
        }
    ]
}

Install

go get github.com/LeKovr/dbrpc

Download

See Latest release

Sql sample code

See pgrpc-sql-rpc

Acknowledgements

• Marcio Castilho for his blog post
• groupcache authors
• Julio Capote for his groupcache sample

License

The MIT License (MIT), see LICENSE.

Copyright (c) 2016 Alexey Kovrizhkin lekovr+pgrpc@gmail.com