volos-mysql - npm Package Compare versions

Comparing version 0.0.0 to 0.0.1

package.json

		{
		"name": "volos-mysql",
		"version": "0.0.0",
		"version": "0.0.1",
		"description": "Volos MySQL Database Connector",
		@@ -5,0 +5,0 @@ "main": "mysqlConnector.js",

289

README.md

		@@ -1,117 +0,181 @@
		# volos-mysql connector
		# Volos MySQL connector

		The ``volos-mysql`` connector lets you perform CRUD operations on a MySQL database using a RESTful API.
		The Volos MySQL connector is a Node.js module that lets you perform CRUD operations on a MySQL database using a RESTful API. It is one of the Volos Node.js modules from Apigee. The module is designed to work on Apigee Edge but can be run anywhere Node.js applications can run. You can use this module without any dependency on Apigee.

		## What is the volos-mysql connector?
		### Quick example

		The ``volos-mysql`` connector maps MySQL database queries to RESTful API resources and query parameters. For example, a properly configured MySQL connector could map a SQL query like this:
		This module allows you to map SQL queries to RESTful API resources and query parameters. For example, a properly configured SQL connector could map a SQL query like this:

		``SELECT name, id, hire_date FROM employees``

		```
		SELECT emp_name, emp_id FROM employees WHERE emp_id='jdoe'
		```
		to a RESTful API that you could call like this:

		``curl http://localhost:9089/employees``
		```
		$ curl http://localhost:9089/employees/jdoe
		```

		and which generates a JSON response like this:
		which generates a JSON response like this:

		```
		[
		{
		"emp_name": "Jane Doe",
		"emp_id": "jdoe"
		}
		]
		```

		```[
		{
		"name": "Jane Doe",
		"id": "jdoe",
		"hire_date": "July 8, 2014"
		},
		The SQL-to-REST mapping is enabled by simple JSON configuration. Here is a sample:

		...
		```
		'employees': {
		queryStringBasic: 'SELECT emp_name, emp_id FROM hr.employees',
		queryStringExpanded: 'SELECT * FROM hr.employees',
		idName: 'emp_id',
		queryParameters : {
		id: 'emp_id = \'{id}\'',
		name: 'emp_name = \'{name}\'',
		role: 'role = \'{role}\'',
		hire_date: 'hire_date = \'{hire_date}\''
		}
		}
		```


		To get a larger set of fields per row, use the query parameter ``expand=true``. This option uses the ``queryStringExpanded`` SQL mapping statement instead of the default ``queryStringBasic`` statement. This option gives you the flexibility to have a small message payload for a subset of fields if those are all that are required.

		```
		[
		{
		"emp_id": "jdoe",
		"emp_name": "Jane Doe",
		"role": "Manager",
		"hire_date": "06-27-1978"
		}
		]
		```

		# Installation

		# Getting started
		The ``volos-mysql`` module is designed for Node.js and is available through npm:

		To use this connector you need two things:
		```
		$ npm install volos-mysql
		```

		* a correctly configured MySQL database connection, and
		* a SQL-to-REST mapping file.
		# Usage
		There are two examples below, one basic example and one that uses the ``avault`` (Apigee Vault) Node.js module, which is a secure local storage module. Apigee Vault is used to encrypt sensitive login credentials sent to the backend database.

		Let's start by configuring a connection and testing it with the default mapping file. After that, we'll dive into the details of customizing the mapping file.
		### Simple example without Apigee Vault

		## What do I need to do first?
		The example below shows a simple usage of the ``volos-mysql`` connector using the ``http`` module to proxy requests to the connector.

		This connector is a Node.js module. So, you must download and install Node.js on your system to use the connector. Follow the instructions at http://nodejs.org/download/.
		>Note: In this example, credentials and the database endpoint are specified in plaintext. This is not a best practice.

		Most of our examples use cURL, a utility for making HTTP requests to servers. We recommend you install cURL or have a REST tool like Postman handy.
		```
		var mysqlConnector = require('volos-mysql');
		var http = require('http');
		var restMap = require('./queryToRestMap');

		## How do I install the connector?
		var profile = {
		user: 'volos',
		password: 'volos',
		host: "nsa.rds.amazon.com",
		port: "5432"
		};

		This connector is available through ``npm``. Note that ``npm`` was installed when you installed Node.js.
		var mysqlConnectorObject = new mysqlConnector.MySqlConnector({"profile": profile, "restMap": restMap});

		1. Create a folder for the connector.
		2. Go to that folder.
		3. Enter: ``npm install volos-mysql``

		## How do I configure a database connection?
		The connector needs to know a little bit about your MySQL database before it can actually connect. You'll need this information to complete the config:
		var svr = http.createServer(function (req, resp) {
		mysqlConnectorObject.dispatchRequest(req, resp);
		});

		* host - The database server's host IP address. For example: ``nsa.rds.amazonaws.com``
		* user - The username you use to log on to the database.
		* password - The password you use to log on to the database.
		* port - The port number for the database server.
		svr.listen(9089, function () {
		mysqlConnectorObject.initializePaths(restMap);
		console.log(mysqlConnectorObject.applicationName + ' node server is listening');
		});

		Let's walk through the configuration steps:
		```

		Note: We're going to run a utility called ``vaultcli.js``, which will encrypt your sensitive database login credentials. This utility is an ``npm`` module called ``avault``, which was installed along with this connector. If you're curious, you can find it in the ``node_modules`` folder.
		### Simple example using the Apigee Vault for local secure storage

		1. In a terminal, go to the folder where the ``volos-mysql`` module is installed.
		2. Enter this command to encrypt your database credentials. Fill in your database information for username, password, host, and database. Also, specify a vault name, which is the name the connector uses to load your encrypted credentials.
		This example shows the usage of the ``avault`` module to provide a secure local storage option for credentials and endpoint configuration.

		``./node_modules/avault/vaultcli.js --verbose --value='{"host":"my-database-host-ip","user":"my-username","password":"my-db-password","port":port-number}' my-vault-name``
		This example assumes you have configured a vault and loaded a configuration profile with a key 'my_profile_key'. See the section "[Database connection profile](#database-connection-profile)" below for a quick example. For a complete description of the ``avault`` module see the [Apigee Vault page on GitHub](https://github.com/apigee-127/avault).

		3. Your connector folder now has two new files: ``store.js`` and ``keys.js``. Check to make sure they're present.
		4. Open the file ``server-http.js`` and change the first parameter of the ``vault.get()`` method to the name of the vault you created previously:
		```
		var mysqlConnector = require('volos-mysql');
		var http = require('http');
		var vault = require('avault').createVault(__dirname);
		var restMap = require('./queryToRestMap');

		``vault.get('my-vault-name', function(profileString)``
		ar mysqlConnectorObject;

		5. Start the HTTP server:
		vault.get('my_profile_key', function (profileString) {
		if (!profileString) {
		console.log('Error: required vault not found.');
		} else {
		var profile = JSON.parse(profileString);

		``node server-http``
		var svr = http.createServer(function (req, resp) {
		mysqlConnectorObject.dispatchRequest(req, resp);
		});

		6. Test the server. In another terminal, or in a REST tool like Postman, enter this command:
		svr.listen(9089, function () {
		mysqlConnectorObject = new mysqlConnector.MySqlConnector({"profile": profile, "restMap": restMap});
		mysqlConnectorObject.initializePaths(restMap);
		console.log(mysqlConnectorObject.applicationName + ' node server is listening');
		});
		}
		});
		```

		``curl http://localhost:9090``
		# Getting started with your app

		7. This call returns a list of the REST commands that you can call on the database. For example:
		To use this connector you need two things:

		* A correctly configured database connection profile _and_
		* A customized SQL-to-REST mapping file

		### Database connection profile

		The database configuration profile is used by the connector to establish a connection to the backend database. The profile includes the following fields:

		* user - The username you use to log on to the database.
		* password - The password you use to log on to the database.
		* host - The database host IP address. For example: ``nsa.rds.amazonaws.com``
		* port - The port of the database. For example: ``5432``

		Example:
		```
		{
		"usage": {
		"Commands": [
		"GET /employees Optional Query Parameters: "name, id, hire_date"
		],
		"Common Optional Parameters": [
		"limit=<maxResults>",
		"expand=true",
		"orderby=<colunmNameList>"
		]
		}
		}
		var profile = {
		username: 'volos',
		password: 'volos',
		host: "nsa.rds.amazon.com",
		};
		```

		Note that there are some optional query parameters that you can use to filter the results: ``limit``, ``expand``, and ``orderby``.
		### Optional: Encrypting the connection profile with Apigee Vault

		* limit - Specifies the maximum number of records to fetch. For example: ``?limit=10``.
		* expand - Retrieves expanded records. You can customize both the basic and expanded query strings in the mapping file. For example: ``?expand=true``. By default, this parameter is ``false``.
		* orderby - Specifies a database column by which the returned records are sorted.
		The ``avault`` module provides local, double-key encrypted storage of sensitive information such as credentials and system endpoints. This provides an option to store these kinds of data in a format other than `text/plain`.

		## Checkpoint
		In order to insert a value into the vault a command-line tool is provided called `vaultcli`. This tool comes with the `avault` module. Here's an example:

		You've configured the MySQL database connector and verified that you have a valid database connection. Next, we'll explain how to customize the mapping file to map specific SQL queries directly to RESTful API resources.
		```
		$ vaultcli --verbose --value='{"username":"volos", "password": "volos", "host": "nsa.rds.amazon.com", "port":"5432", "database":"volos"}' my-vault-name
		```

		# Configuring the mapping file
		>Note:These are the same keys that are required in the plaintext version of the profile. If this command completes successfully you will find two new files: `store.js` and `keys.js`. Place them in the root directory of the ``volos-mysql`` module.

		For more detailed usage of the `avault` module refer to the [Apigee Vault page on GitHub](https://github.com/apigee-127/avault).

		# SQL to REST mapping

		The file ``queryToRestMap.js`` maps SQL query parameters to RESTful API resources. The file is JSON, and the pattern you need to follow to configure your mappings is fairly straightforward. Let's see how this works.

		## Understanding the mapping file structure
		### Understanding the mapping file structure

		The mapping file consists of a repeating pattern of JSON elements that map SQL queries to REST API resources and query parameters. A sample pattern for retrieving employee information might look like this:
		The ``queryToRestMap.js`` mapping file consists of a repeating pattern of JSON elements that map SQL queries to REST API resources and query parameters. A sample pattern for retrieving employee information (GET requests) might look like this:

		@@ -126,5 +190,15 @@ ```
		id: 'id = \'{id}\'',
		role: 'role = \'{role}\'',
		hire_date: 'hire_date = \'{hire_date}\'',
		}
		},
		'roles': {
		queryStringBasic: 'SELECT role_name, role_id FROM hr.roles',
		queryStringExpanded: 'SELECT * FROM hr.roles',
		idName: 'role_id',
		queryParameters : {
		name: 'role_name = \'{name}\'',
		pay_grade: 'grade = \'{pay_grade}\''
		}
		}
		```
		@@ -134,37 +208,50 @@

		* employees - The element name becomes the REST resource name. So, you might call this API like this: http://localhost:9089/employees.
		* queryStringBasic - A filtered query that returns a subset of information from a database column.
		* employees and roles - The element names become the REST resource names. So, you might call this API like this:

		`curl http://localhost:9089/employees` or `curl http://localhost:9089/roles`

		* queryStringBasic - A SQL query that can be used to return a subset of the information of the `queryStringExpanded`, if desired.

		* queryStringExpanded - An unfiltered (or less filtered) query. The connector uses this query string when you specify the query parameter ``?expand=true``. For example:

		``curl http://localhost:9089/employees?expanded=true``
		```
		$ curl http://localhost:9089/employees?expand=true
		```

		* idName - The name of the database column to query against.
		* queryParameters - Lets you filter the response. For example, to return information about an employee by id, you could call this API:
		* queryParameters - These let you filter the results of the SQL statement that gets executed. They are translated to WHERE clauses of the SQL statement. For example, to return a list of employees that were hired on January 1, 2014 you could make this call:

		``curl http://localhost:9089/employees?id=jdoe``
		```
		$ curl http://localhost:9089/employees?hire_date=2014-01-01
		```
		This would result in the following SQL being executed:
		```
		SELECT * FROM hr.employees WHERE hire_date='2014-01-01'
		```
		You can also use multiple query parameters as you might expect. This example would return a list of all employees with the role of "manager" hired on January 1, 2014:

		Note: You can customize the query parameter names. For example, look at this set of query parameters for our employees example:
		```
		$ curl http://localhost:9089/employees?hire_date=2014-01-01&role=manager
		```
		This would result in the following SQL being executed:
		```
		SELECT * FROM hr.employees WHERE hire_date='2014-01-01' AND role='manager'
		```

		>Note: You can customize the query parameter names and they do not need to map directly to column names. For example, look at this set of query parameters for our employees example:

		```
		queryParameters : {
		name: 'name = \'{name}\'',
		id: 'id = \'{id}\'',
		hire_date: 'hire_date = \'{hire_date}\'',
		}
		queryParameters : {
		name: 'name = \'{name}\'',
		foobar: 'id = \'{foobar}\'',
		lower_id: 'lower(id) = lower(\'{foobar}\')',
		role: 'role = \'{role}\'',
		hire_date: 'hire_date = \'{hire_date}\''
		}
		```

		After the resource name is specified (for example, ``id``), there's another mapping, like this:
		In this case the query parameter `foobar` would be mapped to the WHERE clause of the SQL statement for the `id` column. Also note that `lower_id` includes a call to the `lower(string)` function of MySQL.

		``'id = \'{id}\''``
		### Configuring POST, PUT, and DELETE operations

		This mapping lets you change the name of the query parameter, perhaps to conform to your internal standards or personal preferences. For instance, if you change the mapping like this:

		``'id = \'{employee_id\''`

		You'd call the API like this:

		``curl http://localhost:9090/employees?employee_id=jdoe``

		## Configuring POST, PUT, and DELETE operations

		You configure POST, PUT, and DELETE operations in the same ``queryToRestMap.js`` file. Again, you can follow the example pattern for each verb in the default file.
		@@ -190,2 +277,3 @@
		* restSemantic - Specifies the HTTP verb for this operation.
		* table - The database table you wish to query.
		* path - The REST API resource for this operation.
		@@ -198,3 +286,3 @@ * queryParameters - Lets you specify which values to update.

		Here's an example POST configuration to add a new employee to the database:
		Here's an example POST mapping. It adds a new employee to the database:

		@@ -211,4 +299,17 @@ ````

		``curl -X POST -Content-Type: application/json http://localhost:9090/employees -d {"id": "jdoe", "firstname": "Jane", "lastname": "Doe"} ``
		```
		curl -X POST -Content-Type: application/json http://localhost:9090/employees -d {"id": "jdoe", "firstname": "Jim", "lastname": "Doe"}
		```

		Follow the same pattern for DELETE operations. Refer to the default ``queryToRestMap.js`` file for more examples.
		Follow the same pattern for DELETE operations. Refer to the default ``queryToRestMap.js`` file for more examples.

		### Note the following with regard to query parameters
		* If you have a join query you may need to include table aliases for your query parameter statements.
		* Don't neglect the escaped quotes (`\'`) if you want the values of your query parameters to be interpreted as strings.

		# License

		MIT

volos-mysql - npm Package Compare versions

New alerts

Fixed alerts

Improved metrics