Skip to content

schluete/node-postgres-proxy

Repository files navigation

node-postgres-proxy - a simple HTTP proxy for PostgreSQL in node.js

Installation and configuration

  • the proxy was developed with node v0.2.5, download it from http://nodejs.org/
  • to use the proxy you have to install all required dependencies via make dependencies
  • copy settings.json.sample to settings.json, then edit the configuration of the proxy. You should at least configure one database, because without a database configuration the proxy isn't exactly useful :)
  • you can preconfigure headers to be included in the HTTP response to a query, e.g. a Server-header or Cache-Control to modify the caching behaviour of proxy clients. Currently it is not possible to configure dynamic headers, e.g. a timestamp or a value based on the incoming request.

Authentication

  • configure some usernames and passwords to allow access to the server. The credentials must be send by the caller via HTTP BASIC AUTH.
  • you could also provide some HMAC authentication secrets in the settings.json so that no plaintext passwords have to be send via BASIC AUTH. To generate the HMAC key for a request the caller has to compute the HMAC SHA1 digest from the request URL and one of the preconfigured secrets. The digest must then be send in the 'x-sig' HTTP request header.

Querying a database

  • each configured database is available under its own endpoint URL, i.e. the database foobar will be accessible under http://localhost:7070/[action]/foobar. The action part of the path can either be sql or json, depending on the query format.
  • to execute a sql action query send SQL command via a POST request to the proxy:
    curl -u "top:secret" -X POST --data "select * from persons" http://localhost:7070/sql/node
    { 'success': true,
    'rows': [ { 'id': 1, 'name': 'Pierre Niemans' },
    { 'id': 2, 'name': 'Max Kerkerian' },
    { 'id': 3, 'name': 'Fanny Ferreira' }
    ]}
    
  • you can also send a JSON formatted query to the json action. The JSON format supports multiple queries in a single request. Each JSON request is a combined insert/update SQL request (something like http://en.wikipedia.org/wiki/Upsert). The proxy will check if a row with the given conditions exists and update it. If it doesn't exist an update SQL query will be generated:
    curl -u "top:secret"
    -X POST --data '{"table": "persons",
    "data": [{"conditions": {"id": 6, "age": 16},
    "values": {"name": "Judith Hérault"}},
    {"conditions": {"id": 5, "age": 7},
    "values": {"name": "Rémy Caillois", "age": 20}}]}'
    http://localhost:7070/json/node
    { 'success': true }
    
    • the request returns the result as a JSON-formatted message. If the query was successful the field success will contain the boolean value true. In case of an error success will be false and the field error contains a textual error message.
  • the return field rows contains the queried rows, if any.

Unit testing

  • there a few unit tests via the http://vowsjs.org unittesting framework. To execute the tests make target you have to install vows:

    $ curl http://npmjs.org/install.sh | sh
    $ npm install vows 
    

About

simple HTTP proxy for PostgreSQL in node.js

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published