datahub-restful-core

export mysql as restful service on koa v1

中文版

Usage

const koa = require('koa');

const {DatahubRestfulCore} = require('../index');
let port = process.env.PORT || 3003;
let host = process.env.IP || '127.0.0.1';

const core = new DatahubRestfulCore({
    port, host,
    name: 'demo',
    endpoint: "/",
    options:
        {
            client: 'mysql',
            connection: {
                host: '127.0.0.1',
                user: 'root',
                password: '',
                database: 'datahub',
                supportBigNumbers: true,
                bigNumberStrings: true,
                multipleStatements: true
            },
            acquireConnectionTimeout: 60000,
            pool: {min: 0, max: 2}
        }
});

app = core.mount(koa());

app.listen(port, host, function () {
    console.log('Datahub started http://%s:%s', host, port);
});

//curl -v http://127.0.0.1:3003/demo/

features:-)

• router rules
• restful interfaces
• POST/GET/PUT/DELETE
• pagination
• order by
• composite primary keys
• custom SQL
• search
• or condition
• hooks
• not
• in,not in
• between,not between
• null，empty string
• charset

misc

• execute vm
• export to xls file
• import

URL schema

API

• all databases: [your ip:port/api/v1/databases](your ip:port/api/v1/databases)
• all schemas of some database: [your ip:port/api/v1/{database}](your ip:port/api/v1/{db})
• declare charset: ?_charset=utf8
• all tables of some schema : [your ip:port/api/v1/{db}/{schema}](your ip:port/api/v1/{db}/{schema})
• table of schema : [your ip:port/api/v1/{db}/{schema}/{table}](your ip:port/api/v1/{db}/{schema}/{table}) RESTFUL support on above url
• query: GET to [your ip:port/api/v1/{db}/{schema}/{table}?{k}={v}](your ip:port/api/v1/{db}/{schema}/{table}?{k}={v})
  • support operators e.g. contains: [your ip:port/api/v1/{db}/{schema}/{table}?{k}:contains={v}](your ip:port/api/v1/{db}/{schema}/{table}?{k}:contains={v}),list all records contains keyword v
  • contains : like %keyword% contains keyword
  • startswith: like keyword% starts with keyword
  • endswith : like %keyword endswithkeyword
  • gt or >: greater than
  • lt or < : less than
  • gte : greater than or equals
  • lte :less than or equals
  • pagination: page=0 （0 represents first page）pageSize=3（records per page）
  • order by: _sort=-policy_name,id order by policy_name desc and order by id asc,
  • Or: [your ip:port/api/v1/{db}/{schema}/{table}?k=v&{or:k2}={v2}](your ip:port/api/v1/{db}/{schema}/{table}?k=v&{or:k2}={v2})
  • not:
  • isnull: IS NULL
  • isnotnull: IS NOT NULL
  • isempty: empty string
  • isnotempty: not empoty string
  • in / notin: parameters are array somecolumn:in=a,b,c orsomecolumn:in=a&somecolumn:in=b&somecolumn:in=c
  • between / notbetween: parameters are array somecolumn:between=1,100
  • nested or more complex query，use the complex query
  • return result example

  {"content":[{"id":83,"wifi_id":85,"policy_name":"wifi83_-78486031","status":-1,
  "create_time":"2016-05-12T12:43:19.000Z"},{"id":70,"wifi_id":82,"policy_name":"wifi",
  "status":-1,"create_time":"2016-05-11T02:57:22.000Z"},
  {"id":75,"wifi_id":82,"policy_name":"wifi",
  "status":1,"create_time":"2016-05-11T03:56:19.000Z"}],
  "page":{"size":30,"totalElements":3,"totalPages":1,"number":0}}

• complex query：
• get: GET [your ip:port/api/v1/{db}/{schema}/{table}/{id}](your ip:port/api/v1/{db}/{schema}/{table}/{id})
• get(composite primary keys): GET [your ip:port/api/v1/{db}/{schema}/{table}/{key1}={a};{key2}={b}](your ip:port/api/v1/{db}/{schema}/{table}/{key1}={a};{key2}={b})
• create: JSON POST to [your ip:port/api/v1/{db}/{schema}/{table}](your ip:port/api/v1/{db}/{schema}/{table})
• update: JSON PUT to [your ip:port/api/v1/{db}/{schema}/{table}/{id}](your ip:port/api/v1/{db}/{schema}/{table}/{id})
• delete: DELETE [your ip:port/api/v1/{db}/{schema}/{table}/{id}](your ip:port/api/v1/{db}/{schema}/{table}/{id})
• patch(Partial update): PATCH [your ip:port/api/v1/{db}/{schema}/{table}/{id}](your ip:port/api/v1/{db}/{schema}/{table}/{id})
• custom SQL: GET [your ip:port/api/v1/{db}/{schema}/_q/{key}](your ip:port/api/v1/{db}/{schema}/_q/{key})
• custom SQL with pagination: GET [your ip:port/api/v1/{db}/{schema}/_q/{key}/pagination](your ip:port/api/v1/{db}/{schema}/_q/{key}/pagination)
• hooks
  • pre hook * [your ip:port/api/v1/?_hook:before=someHook1](your ip:port/api/v1/?_hook:before=someHook1)
  • post hook * [your ip:port/api/v1/?_hook:after=someHook1](your ip:port/api/v1/?_hook:after=someHook1)
• execute SQL: GET/POST sql to [your ip:port/api/v1/{db}/sqlExecutor](your ip:port/api/v1/{db}/sqlExecutor)
• execute SQL with pagination: GET/POST sql to [your ip:port/api/v1/{db}/sqlExecutor/pagination](your ip:port/api/v1/{db}/sqlExecutor/pagination)
• support queryString as ?sql=select * from t_x_table where f_some_field = :value&value=value

KnexHelper object

- getInternalApiUrl() get internal api url`http://${host}:${port}${endpoint}`
- * findConnectionByDbkey(dbkey) get the db id as `cdb_x`
- getDatabaseConnection(cdbId) get datbase raw connection object

Test

tnpm i -g mocha
tnpm run test