mysql-lazyRest 🛌

LazyREST lets you automagically create REST routes for all the tables and views of a specified schema of your MySQL db, by only configuring the database connection string.

Disclaimer: I recommend this library only for development environments.

Installation

This is a Node.js module available through the npm registry.

npm install mysql-lazyrest --save

Quick Start

After installing mysql-lazyrest you can follow the next example an have a REST service running in 1-2-3.

The MySQL connection string adepts to the connection string used by mysql.createConnection(config);

You can read more about this in the Mysql npm repository.

1-2-3 Standalone Example

// 1 - require the library
const lr = require('mysql-lazyrest');
 
// 2 - create a new lazyrest object and configure the database connection string
let rest = new lr.lazyRest({
    host: "XXX.XXX.XXX.XXX",
    user: "username",
    password: "PA$$",
    database:"schema_name",
    insecureAuth: true //optional
});
    
// 3 - Run it ! It runs by default on port 8080
rest.run();

Examples:

You can see below some other examples of how to implement mysql-lazyrest.

Ex 1 - Standalone

// we call the library
const lr = require('mysql-lazyrest');
 
// we create a new lazyRest and we send the conneciton string data
let rest = new lr.lazyRest({
    host: "XXX.XXX.XXX.XXX",
    user: "username",
    password: "PA$$",
    database:"schema_name",
    insecureAuth: true //optional
});
 
// we run the standalone example
rest.run();

Ex 2 - Standalone w/ configurations

// we call the library
const lr = require('mysql-lazyrest');
 
// we create a new lazyRest and we send the conneciton string data
let rest = new lr.lazyRest({
    host: "XXX.XXX.XXX.XXX",
    user: "username",
    password: "PA$$",
    database:"schema_name",
    insecureAuth: true //optional
});
 
// hide tables to be populated
rest.hide(["A_OLD_lkp_city", "A_OLD_lkp_states"]);
 
// set models for specific tables, this sets the alias, the access of a table
rest.models([{ table_name: "t_city", alias: "city", access: [lr.READ] }]);
 
// we run the stand alone example with some configurations
rest.run({ port: "", app: "", prefix: "/api/v1", lifetime: 0 });

Ex 3 - With existing Express project + configurations

// Ex3 - Integrated with an existing project
 
const express = require("express");
const app = express();
const port = 8080;
// we call the library
const lr = require('mysql-lazyrest');
 
// we create a new lazyRest and we send the conneciton string data
let rest = new lr.lazyRest({
    host: "XXX.XXX.XXX.XXX",
    user: "username",
    password: "PA$$",
    database:"schema_name",
    insecureAuth: true //optional
});
 
app.get("/", (req, res) => res.send("Hello World!"));
app.listen(port, () => console.log(`Example app listening on port ${port}!`));
 
// we run the example with some configurations
let config = { port: port, app: app, prefix: "/api/v1", lifetime: 24 };
rest.run(config);

Configurations

There are some existing enhancements that can be applied to the general behavior or per table/view

Database connection string *required

When instantiating lazyRest we need to send a connectionString as a parameter to create a MySQL connection.

Ex.

let connectionString = {
    host: "XXX.XXX.XXX.XXX",
    user: "username",
    password: "PA$$",
    database:"schema_name",
}
let rest = new lr.lazyRest(connectionString);

---

Hide *optional

If we want to hide tables form the public we are able to do so by including an array of the tables names in the method hide

Ex.

rest.hide([
    "table_with_ssn", 
    "table_with_customer_data", 
    "table_with_more_sensitive_data",
    ...
]);

---

Models *optional

We can use this method to change some behavior for all the tables we need to.

Ex.

rest.models([
    { table_name: "tbl_contracts", alias: "contracts", access: [lr.READ,lr.CREATE] },
    { table_name: "tbl_users", alias: "users", access: [lr.CREATE] },
    { table_name: "tbl_very_long_name_of_table_from_2018", alias: "long_name"},
    ...
]);

Options availabe:

{ 
     // name of the table in the db
    table_name: "table_name", 
    // set the name route for this table
    alias: "table",  
    // set the access level for this route, available: READ,CREATE,UPDATE,DELETE,ALL or empty for ALL
    access: [ 
        lr.READ,
        lr.CREATE
    ] 
}

---

Run *required

We need this method to start all the automated routing. This configurations are global and will affect the operation of all the routes.

Ex.

rest.run({ port: "", app: "", prefix: "/api/v1", lifetime: 0 , access: '*'});

Options available:

{ 
    // set a port for express routing, by default 8080
    port: "", 
    // override the express app if you already have one in your project
    app: "",
    // prefix for the routes ex. localhost:8080/api/v1/...
    prefix: "",
    // set the max-age of Cache-Control, the expected param is in hours and set by default to 24. If set to 0 then it will set no-cache, no-store, must-revalidate.
    lifetime: 0,
    // set the Access-Control-Allow-Origin for CORS support , ex. * or a specific domain
    access: ""
}

Routes

List of routes that will be created automagically per table:

Method	Url	HTTP Status	Result
GET	tables/	200	Returns a table object with information of the available tables.
GET	routes/	200	Returns a JSON with all the routes created
GET	verbose/<true_false>	200	If set to true it will display the detail of the error.
GET	<table_name>/describe	200	Returns the db description of the requested table per column
GET	<table_name>/	200	Returns all the data of the requested table
GET	<table_name>/order/<col_name>/<asc_desc>	200	Returns all the data of the requested table ordered by a requested column and in asc or desc order
GET	<table_name>/limit/<num>	200	Returns the first number of rows of the request table
GET	<table_name>/order/<col_name>/<asc_desc>/limit/<num>	200	Returns the first number of rows of a requested table ordered by a requested column and in asc or desc order
GET	<table_name>/<prim_key_col>/<primkey_val>	200	Returns a specific row from the requested table based on the primary key and its value. (Faster that search)
GET	<table_name>/search/<col>/<val>	200	Returns the rows from the requested table based on the searched column key and value. NOTE: In this route you are also able to apply the order or limit filters or even both at the same time
GET	<table_name>/status	200	Returns the timestamp if table is alive or returns last time the table was updated, this last ONLY works in MyISAM tables
PATCH	<table_name>/<prim_key_col>/<primkey_val>	201	Updates data in the requested table based on the primary key and its value
POST	<table_name>/	201	Inserts data to the requested table
DELETE	<table_name>/<prim_key_col>/<primkey_val>	200	Deletes a specific row from the requested table based on the primary key and its value

Error codes

List of possible error codes.

HTTP Code	Meaning
400	Bad request - There was an error at the time of building the query string.
404	Not found - The requested route does not exist.
502	Bad Gateway - Could not resolve the query or the route due to bad input.

License

MIT