mysql-to-rest is a module to map a MySQL-DB to an REST API.

The foundation for this module is the express framework, as well as the mysql package for the DB connection.

$ npm install mysql-to-rest

First load express and mysql

var express = require('express');
var mysql = require('mysql');
var mysqltorest  = require('mysql-to-rest');
var app = express();

Then open your DB connection. Find more here. (You can also use mysql.createPool() to create a pool of connections and pass that to mysql-to-rest.)

When all dependencies are up and ready, init the API like this:

var api = mysqltorest(app,connection);
//Dont forget to start the server
app.listen(8000);

Once the app is up and running you have the following options:

GET /api/:table

{
    "result": "success",
    "json": [
        {
            "id": 1,
            "col1": 15,
            "col2": null,
            "col3": "String"
        }
    ],
    "table": "test",
    "length": 1
}

You can further specify your Requests with GET params. As an example:

GET /api/:table?_limit=0,10&_order[id]=DESC&id[GREAT]=4

Important: The general params are noted with the prefix you can define in the options. Default is underscore. Eg: _limit

All general params are as close to the MYSQL feeling as it would make sense in a web API. So it really helps if you understand MYSQL Syntax.

• limit= Takes either one or two comma separated integers. Acts like specified here
• order[column]= Takes either ASC or DESC. Orders the result ASC|DESC according to the column. Acts like specified here
• fields= Takes one or more comma separated columns as an argument. Filters the results to only show the specified columns. Acts like specified here

Here you can apply further conditions to your selection.

Syntax: column=value or column[operator]=value

The first option is simple and can be used to select entries where the column equals (=) the provided value.

In the second option one can specify exactly the operator which should be used. Full list:

• GREAT results in >
• SMALL results in <
• EQGREATresults in >=
• EQSMALLresults in <=
• LIKEresults in LIKE
• EQresults in =

GET /api/:table/:id

For results and params see at GET /api/:table

POST /api/:table

This will return the created row like at GET /api/:table

PUT /api/:table/:id

This will return the updated row like at GET /api/:table

DELETE /api/:table/:id

This will return the deleted id. Whereby the id is the first primary key of the table. Example:

{
    "result": "success",
    "json": {
        "deletedID": "1"
    },
    "table": "test"
}

This line inits the api. You can provide a config object to adjust the settings to your need by adding an options object:

mysqltorest(app,connection,options);

If not specified, the following options will be used:

var default_options = {
    uploadDestination:__dirname + '/uploads',
    allowOrigin:'*',
    maxFileSize:-1,
    apiURL:'/api',
    paramPrefix:'_'
};

The options consist of the following:

This specifies the multer upload destination. The default is __dirname + '/uploads'. For more read the multer documentation.

As the API sets some default headers this sets the Access-Control-Allow-Origin header. Provide the domain or url the API should be accessed by. Default is * so be careful!

This checks the filesize of the uploaded files. The value is in bytes. Default (and off) is -1.

Here the url to the api is specified. Default is /api.

This is the query prefix for not select querys like order or limit.

Currently there is only one API call:

api.setAuth(function)

Provide an express middleware to authenticate the requests to the api specifically. The following example shows the basic idea:

api.setAuth(function(req,res,next) {
    if(req.isAuthenticated && req.method === 'GET'){
        next();
    } else {
        //Handle unauthorized access
    }
});

To make the setup as easy as possible mysql-to-rest reads almost all config directly form the database. This has two "pitfalls":

• NOT NULL Columns are seen as required. Even if they have a default value.
• If you want to upload a file. You have to do the following steps:
  • Create a varchar or text column.
  • Set the default value to FILE.

A full version can be deployed using docker. (Thanks to @reduardo7) https://hub.docker.com/r/reduardo7/db-to-api/