volos-mysql

volos-mysql connector

The volos-mysql connector lets you perform CRUD operations on a MySQL database using a RESTful API.

What is the volos-mysql connector?

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:

SELECT name, id, hire_date FROM employees

to a RESTful API that you could call like this:

curl http://localhost:9089/employees

and which generates a JSON response like this:

        {
            "name": "Jane Doe",
            "id": "jdoe", 
            "hire_date": "July 8, 2014"
        }, 

        ...
]

Getting started

To use this connector you need two things:

• a correctly configured MySQL database connection, and
• a SQL-to-REST mapping file.

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.

What do I need to do first?

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/.

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.

How do I install the connector?

This connector is available through npm. Note that npm was installed when you installed Node.js.

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:

• 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.

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.

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.

   ./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

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:

   vault.get('my-vault-name', function(profileString)

5. Start the HTTP server:

   node server-http

6. Test the server. In another terminal, or in a REST tool like Postman, enter this command:

   curl http://localhost:9090

7. This call returns a list of the REST commands that you can call on the database. For example:

{
    "usage": {
        "Commands": [
            "GET /employees Optional Query Parameters: "name, id, hire_date"
        ],
        "Common Optional Parameters": [
            "limit=<maxResults>",
            "expand=true",
            "orderby=<colunmNameList>"
        ]
    }
}

Note that there are some optional query parameters that you can use to filter the results: limit, expand, and orderby.

• 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.

Checkpoint

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.

Configuring the mapping file

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

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:

    'employees': {
        queryStringBasic: 'SELECT name, id, hire_date FROM employees',
        queryStringExpanded: 'SELECT * from employees',
        idName: 'employees',
        queryParameters : {
            name: 'name = \'{name}\'',
            id: 'id = \'{id}\'',
            hire_date: 'hire_date = \'{hire_date}\'',
        }
    },

Let's look at the parts one by one:

• 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.

• 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

• 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:

  curl http://localhost:9089/employees?id=jdoe

Note: You can customize the query parameter 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}\'',
        }

After the resource name is specified (for example, id), there's another mapping, like this:

``'id = \'{id}\''``

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.

Here's an example PUT configuration:

 'updateEmployee': {
        restSemantic: "PUT",
        table: 'mycompany.employees',
        path: '/employees',
        queryParameters : {
            name: 'name = \'{name}\'',
            id: 'id = \'{id}\'',
            hire_date: 'hire_date = \'{hire_date}\'',
            department: 'department' = \'{department}\''
        }
    }

• updatePerson - The name of this element.
• restSemantic - Specifies the HTTP verb for this operation.
• path - The REST API resource for this operation.
• queryParameters - Lets you specify which values to update.

For example, this call changes an employee's department:

curl 'http://locahost:9090/employees?id=jdoe&department=hr'

Here's an example POST configuration to add a new employee to the database:

'addEmployee': {
        restSemantic: "POST",
        table: 'mycompany.employees',
        path: '/employees'
    }

For example:

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

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