MonetDB RESTful Proxy

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.