Connection with MySQL

What You'll Need

Backbench account - Sign Up, the personal account will always remain free to use.

MySQL credentials - host, user and password.

Prerequisites for the app

1. Basic knowledge of MySQL.

2. Knowledge of NodeJs.

3. MySQL credentials.

Backend

1. Select +, in the upper right corner to create a Module. For example, say "package.json" and select CREATE or hit Enter.

2. Copy and paste the code module from below.

{
    "dependencies": 
    {
        "mysql": "2.5.0"
    },
    "author": "Backbench”
}

A package.json file lists the packages that your project depends on. allows you to specify the versions of a package that your project can use using semantic versioning rules.

Creating a database

• Now create another module to establish connection with MySQL.

• Select save.

var mysql = require("mysql");
module.exports.endpoint = function(req, cb){
  var con = mysql.createConnection({
          host:"<host>",
          user: "<dbuser>",
          password: "<dbpassword>"
  });
   con.connect(function(err){
   console.log("Connected!");
   con.query("CREATE DATABASE backbench", function(err, result){
   console.log("Database created");
   cb(undefined, {msg:"Database created"})
        })
    })
    con.end();
}

Replace the name of your host , dbuser and password in the place of <host>, <dbuser> and <dbpassword> respectively.

• Now go to endpoints in backbench console.

• Click on + button.

• This will add an endpoint to the above module, let’s say “/sqldbcreate”.

• Go to the link

https://<backbench username>-<name of your bench>.beta-bench.backbench.io/sqldbcreate.

You just created a database.

Creating a table

• Now create another module to create a table in above MySQL database.

• Select save.

var mysql = require("mysql");

module.exports.endpoint = function(req, cb){
    var con = mysql.createConnection({
          host:"<host>",
          user: "<dbuser>",
          password: "<dbpassword>"
          database: "backbench"
    })

    con.connect(function(err){
        console.log("Connected");
        var sql = "CREATE TABLE employees (name VARCHAR(255), position VARCHAR(255))";
        con.query(sql, function(err, result){
            console.log("Table created");
            cb(undefined, {msg: "table created"})
        })
    })

    con.end();
}

Replace the name of your host , dbuser and password in the place of <host>, <dbuser> and <dbpassword> respectively.

• Now go to endpoints in backbench console.

• Click on + button.

• This will add an endpoint to the above module, let’s say “/sqltablecreate”.

• Go to the link

https://<backbench username>-<name of your bench>.beta-bench.backbench.io/sqltablecreate.

You just created a table.

Inserting data in a table

• Now create another module to insert data into a table in above MySQL database.

• Select save.

module.exports.endpoint = function(req, cb){
    var con = mysql.createConnection({
        host:"<host>",
        user: "<dbuser>",
        password: "<dbpassword>"
        database: "backbench"

    })

    con.connect(function(err){
        console.log("Connected");
        var sql = "INSERT INTO employees (name, position) VALUES ?";
        var values = [
            ["Hi" , "Welcome"],
            ["Good" , "Day"],
            ["Now" , "It's raining"],
        ];
        con.query(sql, [values], function(err, result){
            console.log("Values Inserted");
            cb(undefined, {msg: "values inserted"})
        })
    })

    con.end();
}

Replace the name of your host , dbuser and password in the place of <host>, <dbuser> and <dbpassword> respectively.

Now go to endpoints in backbench console.

• Click on + button.

• This will add an endpoint to the above module, let’s say “/sqlinsertdata”.

• Go to the link

https://<backbench username>-<name of your bench>.beta-bench.backbench.io/sqlinsertdata.

You just inserted values into a table.

Updating data in a table

• Now create another module to update data into a table in above MySQL database.

• Select save.

var mysql = require("mysql");

module.exports.endpoint = function(req, cb){
    var con = mysql.createConnection({
          host:"<host>",
          user: "<dbuser>",
          password: "<dbpassword>"
          database: "backbench"


    })

    con.connect(function(err){
        con.query("UPDATE employees SET position = 'Hi' WHERE name = 'Welcome to backbench'", function(err, result, fields){
            console.log(result);
            cb({msg: "Updated"})
        })
    })
}

Replace the name of your host , dbuser and password in the place of <host>, <dbuser> and <dbpassword> respectively.

• Now go to endpoints in backbench console.

• Click on + button.

• This will add an endpoint to the above module, let’s say “/sqlupdatedata”.

• Go to the link

https://<backbench username>-<name of your bench>.beta-bench.backbench.io/sqlupdatedata.

You just updated values into a table.

Deleting data in a table

• Now create another module to delete data into a table in above MySQL database.

• Select save.

var mysql = require("mysql");

module.exports.endpoint = function(req, cb){
    var con = mysql.createConnection({
          host:"<host>",
          user: "<dbuser>",
          password: "<dbpassword>"
          database: "backbench"


    })

    con.connect(function(err){
        con.query("DELETE FROM employees where position='Hi'", function(err, result){
            console.log(result);
            cb(undefined, {msg: "Deleted"})
        })
    })
}

Replace the name of your host , dbuser and password in the place of <host>, <dbuser> and <dbpassword> respectively.

• Now go to endpoints in backbench console.

• Click on + button.

• This will add an endpoint to the above module, let’s say “/sqldeletedata”.

• Go to the link

https://<backbench username>-<name of your bench>.beta-bench.backbench.io/sqldeletedata.

You just deleted values into a table.