Retrieving data from MySQL database using NodeJS and passing it to AngularJS page

So you want to learn how to use AngularJS, NodeJS and MySQL. Very nice. AngularJS and NodeJS both use JavaScript. AngularJS is 100% JavaScript. There are just few nuts and bolts that must fit together. AngularJS focuses on the frontend side, while NodeJS focuses on the backend side. MySQL is used for database management. There are just few tricks that you must use like MVC to make you code work and be robust. There are many ways you can implement your project. One of them is the following:

1. Start Node.js command prompt.
2. Create the new project directory. For example “myjspro”
3. cd pathto/myjspro or cd pathto\myjspro or cd pathto\myjspro
4. npm init
5. npm install mysql --save
6. npm install express --save
7. npm install body-parser --save
8. etc.

Once you have done the above steps, we can start coding. In your backend code you need to set up the listening port. You also need to configure the default directory. In your frontend you can use data binding to wire up your data model to your views. e.g. Scope is the glue between app controller and the view. Structure your app in a modular way. There are many building blocks we can use for our apps. The list is endless, so let's start... Double curly expressions {{ }} are used for observing, registers listeners methods and update views.

Front End:

• app/view/index.html
• app/js/app.js
• app/js/test.js
• app/lib/angular/angular.js
• app/lib/jquery/jquery.js

Back End:

• db/mydb2.sql

• node_modules

• index.js

• package.json

Database:

• Create the database e.g. “mydb2”
• Create database user e.g. “jspro2”
• Create the database user's password.
• Create database tables for the project e.g. “clientes2”

To start your project you can use: node index.js or npm start with the command prompt. The app will be running at localhost. Use the browser to view the project. i.e. http://localhost:8000/

Happy coding...

index.js

//////////////////////
//
// index.js
//
///////////////////////

console.log("Start...");
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var mysql = require('mysql');
var now = new Date();

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static(__dirname+'/app'));

var conexao = mysql.createConnection({
    host: "localhost",
    user: "jspro2",
    password: "jspro32",
    database: "mydb2"
});

conexao.connect(function(err){
    if(err){
        console.log(info()+" "+err);
    }
    else
    {
        console.log(info()+" connected...");
    }
});

function info()
{
    now = new Date();
    return now.getTime();
}

app.set('port', (process.env.PORT || 8000));
app.get('/', function(req, res){
    console.log(info()+" page request.... ");
    res.sendFile(__dirname +'/'+'app/view/index.html');
});

app.get('/clientes', function(req, res){
    console.log(info()+" clientes request.... ");
    var sql = "SELECT * FROM CLIENTES2";
    conexao.query(sql, function(err, result, fields){
        if(err){
            console.log(info()+" "+err);
            res.send(info()+": dbErr...");
        }
        else
        {
            console.log(info()+" "+result);
            res.send(result);
        }
    });
});

app.post('/clientPost', function(req, res){
    var data = req.body;
    var dnome = data.nome;
    var dmorada = data.morada;
    var sql = "INSERT INTO CLIENTES2 (nome,morada) VALUES(?, ?)";
    conexao.query(sql, [dnome, dmorada], function(err, result){
        if(err){
            console.log(info()+":02 "+err);
            res.send(info()+": dbErr02...");
        }
        else
        {
            console.log(info()+" "+ result);
            res.send(result);
        }
    });
});

var dport = app.get('port');
app.listen(dport, function(){
    console.log(info()+" "+" app is running at http://localhost:"+dport+"/");
    console.log("   Hit CRTL-C to stop the node server.  ");
});
//   
//

app.js

/////////////////////////
//
// app.js
//
/////////////////////////////////

//alert("start...");
var now = new Date();

//Define the clientesApp module.
var clientesApp = angular.module('clientesApp', []);

//Define the clientesCtrl controller.
clientesApp.controller('clientesCtrl', function clientsList($scope, $http){
    $scope.clientes = [
        {
            nome: 'Marc',
            morada: '123 West Parade, Nome'
        },
        {
            nome: 'Jean',
            morada: '432 East Cresent, Lisboa'
        }
    ];

    $scope.feedback = now;

    $scope.listView = function(){
        //alert("View");
        $http.get('/clientes').then(function(data){
            $scope.clientes = data.data;
            $scope.feedback = data;
        }); 
    };

    $scope.listSubmit = function(){
        //alert("Submit..");
        var listData = {
            nome: $scope.nome,
            morada: $scope.morada
        };
        //alert(listData.nome);
        $http.post('/clientPost', listData).then(function(data){
            $scope.feedback = data;
        }); 
    };

});

//alert(now);
//
//

index.html

<!DOCTYPE html>
<!--
index.html
-->
<html lang="en">
    <head>
        <title>DemoJSPro</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body ng-app="clientesApp">
        <h1>AngularJS Demo using NodeJS and MySQL.</h1>

        <div ng-controller="clientesCtrl">
            <hr>
            <div>{{ feedback }}</div>
            <hr>
            <div>
                <br>
                Nome:
                <input type="text" ng-model="nome">
                <br>
                Morada:
                <input type="text" ng-model="morada">
                <br>
                <input type="button" value="OK" ng-click="listSubmit()">
                <br>
            </div>
            <div>
                <p>Selecionar um registo:</p>
                <select ng-model="clienteSelecionado" ng-options="x.nome for x in clientes"> 
                </select>
                <table>
                    <tr>
                        <td>{{ clienteSelecionado.nome }}</td>
                        <td>{{ clienteSelecionado.morada }}</td>
                    </tr>
                </table>
            </div>
            <hr>
            <div>
                <input type="button" value="View" ng-click="listView()">
                <hr>
                <table>
                    <tr>
                        <th>###</th>
                        <th>Nome</th>
                        <th>Morada</th>
                    </tr>
                    <tr ng-repeat=" y in clientes">
                        <td>{{ $index + 1 }}</td>
                        <td>{{ y.nome }}</td>
                        <td>{{ y.morada }}</td>
                    </tr>
                </table>
            </div>
            <br>
            <hr>
            <br><br>
        </div>

        <script src="../lib/angular/angular.js"></script>
        <script src="../lib/jquery/jquery.js"></script>
        <script src="../js/app.js"></script>
        <script src="../js/test.js"></script>
    </body>
</html>

mydb2.sql

#//////////////////////////
#//
#// mydb2.sql
#//
#///////////////////////////

CREATE DATABASE MYDB2;

USE MYDB2;

CREATE TABLE CLIENTES2 (
id int NOT NULL auto_increment,
nome varchar (30) NOT NULL,
morada varchar (99) NOT NULL,
PRIMARY KEY (id)
);

GRANT ALL ON MYDB2.* to jspro2@localhost identified by 'jspro32';

Enjoy.