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
You might be interested in a fork of the MEAN.JS project called PEAN (Postgres, Express, Angular, Node). The PEAN project also uses Sequelize.
https://github.com/StetSolutions/pean
Error: Cannot find module ‘sequelize’
at Function.Module._resolveFilename (module.js:469:15)
Works fine
sequelize has serious issues with postgres transactions. be advised.
Hi Ali,
I am getting the following error
db[model.name] = model;
^
TypeError: Cannot read property ‘name’ of undefined
Any idea what I could be doing wrong?
The problem is that it cannot find the path or files in models folder. Make sure models folder exist and the index.js file is inside it and any other model js file you are defining are inside that folder
Hi,
Does the use of sequlize mean that one cannot use stored procedures (with joins between tables) to retrieve data?
Thanks
You can use store procedures. Have a look at their documentation about raw SQL function calls
when you check a todo as done, it is not reflected in the database.
Hi Ali, it looks like you are denying my question.
Do you get any type of error? Make sure you’re connection config file is correct and you have connected to the database
Hi,
Where is the database and table structure ?
Sorry for late reply Majid, the table structures are defined in models folder using .js files. As long as you have correct connection defined in the config file the table will be created when running the server for the first time. Each .js file in models folder can define a table