Connecting your Angular JS app with PostgreSQL or MySQL

Following previous tutorial on developing a simple AngularJS “ToDo” app now we want to connect our app to a database such as PostgreSQL or MySQL.

Prerequisites

If you don’t have any Angular JS app ready, simply follow this post to set up the basic structure of an Angular JS app.

Before starting this tutorial please make sure you have your favorite database like PostgreSQL or MySQL setup and running on your PC or on a remote server to connect to.

For this tutorial I am assuming you are familiar with node js and express js applications, if now read my previous posts to get started.

Step 1: Installing Sequelize js

Sequlize js is a ORM (Object-Relational-Mapper). The library is written entirely in JavaScript and can be easily used in the Node.JS environment.

If you wondering why we are using Sequelize js, it is because it is one the best ORMs out there and if you’re developing very complex Node js app it will make your life much easier. 🙂

To install Sequelize js open any console application and navigate to your AngularJS application folder and type the following:

For PostgreSQL database install:

$ npm install sequelize
$ npm install pg

If you want to use MySQL install following:

$ npm install sequelize
$ npm install mysql

Step 2: Creating Sequelize config file

Inside your root project folder create a folder and name it “config”, then inside the config folder create a JSON file and name it config.json

Open config.json file and add the following into it:


{
"development": {
"username": "DATABASE_USERNAME",
"password": "DATABASE_PASSWORD",
"database": "DATABASE_NAME",
"host": "127.0.0.1",
"dialect": "postgres",
"port": "DATABASE_PORT"
}
}

The first line of the above json file is you environment name. You can have different database for different environments such as development, test and production databases that you can add to same file similar like below, but make sure there is no empty lines in your file which might cause problem:

{
"development": {
"username": "DATABASE_USERNAME",
"password": "DATABASE_PASSWORD",
"database": "DATABASE_NAME",
"host": "127.0.0.1",
"dialect": "postgres",
"port": "DATABASE_PORT"
},
"production": {
"username": "DATABASE_USERNAME",
"password": "DATABASE_PASSWORD",
"database": "DATABASE_NAME",
"host": "PRODUCTION_DATABASE_URL",
"dialect": "postgres",
"port": "DATABASE_PORT"
}
}

 Step 3: Creating Data Models

Inside your root project folder create a new folder and name “models” then inside the models folder create file as “index.js” and add the following into it:

"use strict";

var fs = require("fs");
var path = require("path");
var Sequelize = require("sequelize");
var env = process.env.NODE_ENV || "development";
var config = require(__dirname + '/../config/config.json')[env];
var sequelize = new Sequelize(config.database, config.username, config.password, config);
var db = {};

fs.readdirSync(__dirname).filter(function(file) {
 return (file.indexOf(".") !== 0) && (file !== "index.js");
}).forEach(function(file) {
 var model = sequelize["import"](path.join(__dirname, file));
 db[model.name] = model;
});

Object.keys(db).forEach(function(modelName) {
 if ("associate" in db[modelName]) {
 db[modelName].associate(db);
 }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

This code at the start of your node js app will look into the models folder and create a table and association with the database in order to save and retrieve data using model objects we will create later on.

Let’s make our todo model now. Create a file inside models folder and name “todo.js”. This will be our todo model object.

now let’s define some data fields with datatypes for our todo model. In our todo project we have three simple variables that we want to save to database such as:

  • task name
  • is it marked as done
  • is it archived

so add the following into the todo.js file:

"use strict";
module.exports = function(sequelize, DataTypes) {
 var Todo = sequelize.define("Todo", {
 text : {
 type : DataTypes.STRING,
 allowNull : false,
 },
 done : {
 type : DataTypes.BOOLEAN,
 }
 });
 return Todo;
};

Sequelize js supports many datatypes which can be found here.

 Step 4: Include Sequelize in app.js

Now let’s tell our node js app to use Sequelize on boot up. Inside your app.js file or whatever name your main app file has, add following:

For express js version less than 4 use:

var models = require("./models"); //place on top of the file
models.sequelize.sync().then(function() {
 http.createServer(app).listen(app.get('port'), function() {
 console.log('Express server listening on port ' + app.get('port'));
 });
});

For express js version 4 or later use:

var models = require("./models"); //place on top of the file
models.sequelize.sync().then(function() {
 var server = app.listen(app.get('port'), function() {
 console.log('Express server listening on port ' + server.address().port);
 });
});

 

 Step 5: Define new route for todo HTTP calls

Now we need to create a post url route inside the app.js file in order to make HTTP post call from Angular js in client side.

Add the following into app.js file:

var routes = require('./routes'); //place on top of the file</pre>
app.get('/todo', routes.gettodos);
app.post('/todo', routes.savetodos);

open index.js file inside routes folder and add the following to any existing code:

var models = require("../models"); //place on top of the file</pre>
exports.gettodos = function(req, res) {
    models.Todo.findAll().then(function(todos){
        res.json(todos);
    });
};

exports.savetodos = function(req, res) {
    models.Todo.create({
        text: req.body.text,
        done: req.body.done
    }).then(function(todos){
        res.json(todos.dataValues);
    }).catch(function(error){
        console.log("ops: " + error);
        res.status(500).json({ error: 'error' });
    });
};

ok this will take care of getting and saving new data.

 Step 6: Using Todo model

In order to use our model we can simply call the HTTP url, “/todo”, we just created inside the Angular js controller function that we have from existing application.

Simply under the public, then js folder open todo.js file which contains our Angular controller function for the todo app and replace everything with the following code:

function TodoCtrl($scope, $http) {
    $scope.todos = [];
    $http.get('/todo').success(function(data, status, headers, config) {
        $scope.todos = data;
        if (data == "") {
            $scope.todos = [];
        }
    }).error(function(data, status, headers, config) {
        console.log("Ops: could not get any data");
    });

    $scope.addTodo = function() {
        $http.post('/todo', {
            text : $scope.todoText,
            done : false,
        }).success(function(data, status, headers, config) {
            $scope.todos.push({
                text : $scope.todoText,
                done : false
            });
            $scope.todoText = '';
        }).error(function(data, status, headers, config) {
            console.log("Ops: " + data);
        });
    };

    $scope.remaining = function() {
        var count = 0;
        angular.forEach($scope.todos, function(todo) {
            count += todo.done ? 0 : 1;
        });
        return count;
    };

    $scope.archive = function() {
        var oldTodos = $scope.todos;
        $scope.todos = [];
        angular.forEach(oldTodos, function(todo) {
            if (!todo.done)
                $scope.todos.push(todo);
        });
    };
}

As you can see we just simply using Angular $http AJAX service to call our backend data model to retrieve and save data.

For practice you can modify the code slightly to make sure the data is also saved when the user click on done checkboxs.

for updating an existing data with Sequelize create and “update” route function and then use following code to update the data:


	models.Todo.find({
		where: {
		   id: req.body.id
		}
		}).then(function (todo) {
		todo.updateAttributes({
			text: req.body.text,
			done: req.body.done,
		}).then(function (todo) {
			res.json(todo);
		}).catch(function (err) {
			console.log(err);
		});
		}).catch(function (err) {
			console.log(err);
	});

I hope this is useful for your projects. Leave comment if you need help or more clarifications. 🙂

Find the source code on GitHub: https://github.com/molasaheb/nodejs-angularjs-sequelizejs