Ninety Percent Muffin

    monetdb-rest

    0.1.1 • Public • Published

    MonetDB RESTful Proxy

    Build Status npm version Dependency Status

    A proxy for MonetDB that provides a RESTful interface and extended documents support. It currently intended for as an experiment, rather than a complete replacement of the MonetDB SQL interface and MAPI protocol. Having said that, the proxy is meant to be extensible (see below).

    Installation and start

    Run npm install [-g] monetdb-rest to install any dependencies

    Run npm start to start the MonetDB RESTful Proxy. The Node.js process will start on port 8888.

    API

    Database management

    HTTP method URI path Body Description
    GET /database/_all List all attached databases
    PUT /database/<database-name> Content-Type: application/json
    {
    "host" : "<hostname>",
    "port" : <port>,
    "user" : "<username>",
    "password" : "<password>"
    }
    Attach a database with the specified name and connection
    DELETE /database/<database-name> Detach the database with the specified name
    GET /database/<database-name>/_connection Show the database connection info
    GET /database/<database-name>/_api List all available endpoints at database level

    Data management

    HTTP method URI path Body Description
    GET /database/<database-name>/schema/_all List all schemas
    GET /database/<database-name>/schema/<schema-name>/_api List all available endpoints at schema level
    GET /database/<database-name>/schema/<schema-name>/table/_all List all available tables for the schema
    POST /database/<database-name>/schema/<schema-name>/table/ Content-Type: application/json
    {
    "table" : "<table-name>",
    "columns" : {
    "<column-name-1>": "<data-type-1>",
    "<column-name-2>": "<data-type-2>"
    }
    }
    Create a new table with the specified name and columns
    GET /database/<database-name>/schema/<schema-name>/table/<table-name>/_info Show table info
    GET /database/<database-name>/schema/<schema-name>/table/<table-name>/_all Get all rows and columns
    POST /database/<database-name>/schema/<schema-name>/table/<table-name> Content-Type: application/json
    {
    "values": [<value-1>, <value-2>]
    }
    Insert values into table
    POST /database/<database-name>/schema/<schema-name>/table/<table-name> Content-Type: application/json
    {
    "values" : {
    "<column-name-1>": <value-1>,
    "<column-name-2>": <value-2>
    }
    }
    Insert values into the specified columns on a table
    GET /database/<database-name>/schema/<schema-name>/table/<table-name>?columns=<column-name-1>,<column-name-2> Get all values for the specified columns
    GET /database/<database-name>/schema/<schema-name>/table/<table-name>?filter_<column-name>=<value> Get all matching the filter
    GET /database/<database-name>/schema/<schema-name>/table/<table-name>?orderBy=<column-name> Order the results but a column-name
    GET /database/<database-name>/schema/<schema-name>/table/<table-name>?limit=<value> Limit the results to the specified number of first values
    PUT /database/<database-name>/schema/<schema-name>/table/<table-name>?filter_<column-name>=<value> Content-Type: application/json
    {
    "<column-name-1>": <updated-value-1>,
    "<column-name-2>": <updated-value-2>
    }
    Update the records matching the filter with the specified values
    DELETE /database/<database-name>/schema/<schema-name>/table/<table-name> Delete the records matching the filter
    GET /database/<database-name>/schema/<schema-name>/view/_all Shows all views for the specified schema
    POST /database/<database-name>/schema/<schema-name>/view/ Content-Type: application/json
    {
    "view": "<view-name>",
    "query": "<SQL-query>"
    }
    Create a view with the specified name and SQL query
    GET /database/<database-name>/schema/<schema-name>/view/<view-name>/_info Get the view info
    GET /database/<database-name>/schema/<schema-name>/view/<view-name> Get the view records
    DELETE /database/<database-name>/schema/<schema-name>/view/<view-name> Delete the view
    GET /database/<database-name>/query?q=<query> Executes the URI-encoded SQL query
    POST /database/<database-name>/query Content-Type: text/plain
    SQL Query
    Executes the raw SQL query in the body
    GET /database/<database-name>/function/<function-name>?a=<value-1>&b=<value-2> Calls the SQL function with the provided parameters

    Strings, spaces and nulls

    • String values with white space in a path parameters need to be URL encoded, e.g. /database/demo/schema/sys/table/cities?filter_city=Amsterdam%20Zuid
    • JSON null values are passed to the database as null values.

    Document management

    To start working with document one must first create a schema documents and a table documents with the following columns: _id int, body json. If the schema and table are no found in the attached database, the proxy can create them with a POST to /database/<database-name>/document/_init.

    HTTP method URI path Body Description
    POST /database/<database-name>/document/_init Initialise the document storage schema and table
    GET /database/<database-name>/document/_all List all documents
    POST /database/<database-name>/document/ Content-Type: application/json
    Document body
    Store a document with a auto-generated ID
    PUT /database/<database-name>/document/<document-id> Content-Type: application/json
    Document body
    Store or update a document with the specified document ID. If the document exits, replace it
    GET /database/<database-name>/document/<document-id> Get the document with the specified document ID
    DELETE /database/<database-name>/document/<document-id> Delete the document with the specified document ID
    GET /database/<database-name>/document/<document-id>/_find?<key>=<value> Get all documents matching the specified filter

    Extensions

    The MonetDB RESTful Proxy is designed to be extensible, allowing developers to write their own endpoints and interfaces and dropping in place.

    Routes are handed by the Express Node.js framework router. The routes directory contains all the URL path routes and endpoints in hierarchical order. Each directory contains an index.js file which scans the directory contents and registers all files as routes, ignoring only the entries prefixed with _. Endpoints or sub-routes are defined in each of the files or sub-directories. If you want to add another level to the route, use the addRoutes function from lib/router-loader.js, to add a new set of endpoints at the desired level.

    For example, the routes directory contains an index.js that scans the directory and a database.js file (added by the index) for database level operations - like creating a new database connection. In database.js a new rotue is added using addRoutes, referring to routes/_database directory and its contents. This adds the document, function, query and schema routes, which all appear as URL paths for specific database, as seen in the API documentation above.

    If you want to add a new route, e.g. join, you need to write a join.js, following the example of another 'query.js', and drop the file in routes/_database. After restarting the database you will be able to execute queries to the defined endpoints at /database/<database-name>/join.

    Alternatively, if you want to extend the search operations that can be performed on all documents, you need only work on the routes/_database/_document/find.js file.

    Keywords

    Install

    npm i monetdb-rest

    DownloadsWeekly Downloads

    4

    Version

    0.1.1

    License

    MPL-2.0

    Last publish

    Collaborators

    • dnedev