Building an API REST: Node.js + Express 4 + MySQL

Creating a good API today is easier than ever. We have a lot of tools and frameworks which help us to do a good job. For example, these days we can heard a lot about the MEAN stack (MongoDB, Express, AngularJS, Node.js).

MEAN define an architecture from back-end to front-end. The front-end is built using AngularJS, a great way to enhance in a simple way the HTML of your site to achieve complex behaviors. On the back-end side we use Node.js to create a server written in Javascript. Combining Node.js with Express will give us the possibility to create easily routes for our API using HTTP verbs. For the database MEAN uses MongoDB, however in this tutorial we are going to create an API using MySQL. How can we access to a database in MySQL using Node.js? There are several modules for Node.js to achieve this, but we are going to use Sequelize.

In this tutorial I will talk only about the back-end side. Eventually I’ll write a new independent tutorial for the front-end side. So this is what we are going to learn with this tutorial. I’m going to use a new system with a newly installed Debian 7. All the contents of this tutorial can be downloaded from my GitHub site.

  1. At first, we need to install Apache + Node.js. Follow this tutorial to do it: How to install Node.js on Debian 7
  2. Once installed Node.js, we will install the rest of the LAMP stack (Linux, Apache, MySql, PHP). Follow the next link (to an external website) and install everything (even Apache): Installing Apache2 With PHP5 And MySQL Support On Debian Wheezy

    Important note: if the last step (phpMyAdmin) doesn’t work for you, and you cannot access to http://<your local IP>/phpmyadmin/ try this to fix it:

    sudo nano /etc/apache2/apache2.conf

    At the end of the file, add the following line:

    Include /etc/phpmyadmin/apache.conf

    Finally, restart the apache2 service with this command:

    sudo service apache2 restart
  3. Now we are going to start with the main files of the project. Choose a folder to create those files. In this tutorial I’m going to use just one file to store the main Node.js code, routes and models. In other tutorial I’ll write about how to distribute these files for a large project. At first, we create the package.json file:
    {
    	"name": "node-api",
    	"main": "server.js",
    	"dependencies": {
    		"express": "~4.0.0",
    		"body-parser": "~1.0.1",
    		"mysql": "~2.5.0",
    		"sequelize": "~1.7.0",
    		"crypto": "~0.0.3"
    	}
    }

    This file allows Node.js to know the project dependencies. Once created, in the same folder, with a simple line of code Node.js will fetch and install the modules:

    sudo npm install
  4. I’ve prepared a simple test database for this tutorial. We are going to manage a users database with just two fields: username and password. Both are VARCHAR fields but password will be stored encrypted. To create the database, use phpMyAdmin (or whatever you want) to import these SQL queries:
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";
    
    CREATE DATABASE `tutorial` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    USE `tutorial`;
    
    CREATE TABLE IF NOT EXISTS `users` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
      `password` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
  5. We need a way to specify our database configuration to Node.js. There are several modules to manage configuration but we are going to use a very simple method. Create a file called database.json with this information:
    {
      "dev": {
        "driver": "mysql",
        "user": "root",
        "database": "tutorial",
        "password": "root"
      },
     
      "production": {
        "driver": "mysql",
        "user": "root",
        "database": "myapp"
      }
    }

    As you can see, we are defining to different databases: development and production. This way we can switch easily between different databases.

  6. Now is the turn for the main file of Node.js. Create a new file called server.js. We start requiring some modules, initializing variables and defining the environment:
    // BASE SETUP
    // =============================================================================
    
    var express = require('express'),
    	bodyParser = require('body-parser');
    
    var app = express();
    app.use(bodyParser());
    
    var env = app.get('env') == 'development' ? 'dev' : app.get('env');
    var port = process.env.PORT || 8080;

    We will use the bodyParser module to help us to parser the form data of PUT and DELETE requests. Now, in the same file, we are going to configure the module called Sequelize:

    // IMPORT MODELS
    // =============================================================================
    var Sequelize = require('sequelize');
    
    // db config
    var env = "dev";
    var config = require('./database.json')[env];
    var password = config.password ? config.password : null;
    
    // initialize database connection
    var sequelize = new Sequelize(
    	config.database,
    	config.user,
    	config.password,
    	{
    		logging: console.log,
    		define: {
    			timestamps: false
    		}
    	}
    );
    
    var crypto = require('crypto');
    var DataTypes = require("sequelize");

    Remember that Sequelize is the module who access to the database. The previous piece of code reads from the database.json which we created previously and it opens the connection. At next, we will define our model (User) and a few methods for CRUD operations:

    var User = sequelize.define('users', {
        username: DataTypes.STRING,
        password: DataTypes.STRING
      }, {
        instanceMethods: {
          retrieveAll: function(onSuccess, onError) {
    		User.findAll({}, {raw: true})
    			.success(onSuccess).error(onError);	
    	  },
          retrieveById: function(user_id, onSuccess, onError) {
    		User.find({where: {id: user_id}}, {raw: true})
    			.success(onSuccess).error(onError);	
    	  },
          add: function(onSuccess, onError) {
    		var username = this.username;
    		var password = this.password;
    		
    		var shasum = crypto.createHash('sha1');
    		shasum.update(password);
    		password = shasum.digest('hex');
    		
    		User.build({ username: username, password: password })
    			.save().success(onSuccess).error(onError);
    	   },
    	  updateById: function(user_id, onSuccess, onError) {
    		var id = user_id;
    		var username = this.username;
    		var password = this.password;
    		
    		var shasum = crypto.createHash('sha1');
    		shasum.update(password);
    		password = shasum.digest('hex');
    					
    		User.update({ username: username,password: password},{where: {id: id} })
    			.success(onSuccess).error(onError);
    	   },
          removeById: function(user_id, onSuccess, onError) {
    		User.destroy({where: {id: user_id}}).success(onSuccess).error(onError);	
    	  }
        }
      });  

    Once we have the methods to access to the information, we need to route the different entry points to its Sequelize functions. We will use Express to detect the GET, PUT, DELETE requests. First, without parameters:

    // IMPORT ROUTES
    // =============================================================================
    var router = express.Router();
    
    // on routes that end in /users
    // ----------------------------------------------------
    router.route('/users')
    
    // create a user (accessed at POST http://localhost:8080/api/users)
    .post(function(req, res) {
    
    	var username = req.body.username; //bodyParser does the magic
    	var password = req.body.password;
    	
    	var user = User.build({ username: username, password: password });
    
    	user.add(function(success){
    		res.json({ message: 'User created!' });
    	},
    	function(err) {
    		res.send(err);
    	});
    })
    
    // get all the users (accessed at GET http://localhost:8080/api/users)
    .get(function(req, res) {
    	var user = User.build();
    	
    	user.retrieveAll(function(users) {
    		if (users) {				
    		  res.json(users);
    		} else {
    		  res.send(401, "User not found");
    		}
    	  }, function(error) {
    		res.send("User not found");
    	  });
    });

    So just two methods here, “Give me all users” and “Insert one user”. On each method we use the model functions that we defined previously. Now we have to route the requests with a user_id in the URL:

    // on routes that end in /users/:user_id
    // ----------------------------------------------------
    router.route('/users/:user_id')
    
    // update a user (accessed at PUT http://localhost:8080/api/users/:user_id)
    .put(function(req, res) {
    	var user = User.build();	
    	  
    	user.username = req.body.username;
    	user.password = req.body.password;
    	
    	user.updateById(req.params.user_id, function(success) {
    		console.log(success);
    		if (success) {	
    			res.json({ message: 'User updated!' });
    		} else {
    		  res.send(401, "User not found");
    		}
    	  }, function(error) {
    		res.send("User not found");
    	  });
    })
    
    // get a user by id(accessed at GET http://localhost:8080/api/users/:user_id)
    .get(function(req, res) {
    	var user = User.build();
    	
    	user.retrieveById(req.params.user_id, function(users) {
    		if (users) {				
    		  res.json(users);
    		} else {
    		  res.send(401, "User not found");
    		}
    	  }, function(error) {
    		res.send("User not found");
    	  });
    })
    
    // delete a user by id (accessed at DELETE http://localhost:8080/api/users/:user_id)
    .delete(function(req, res) {
    	var user = User.build();
    	
    	user.removeById(req.params.user_id, function(users) {
    		if (users) {				
    		  res.json({ message: 'User removed!' });
    		} else {
    		  res.send(401, "User not found");
    		}
    	  }, function(error) {
    		res.send("User not found");
    	  });
    });	

    Finally! We have all the models defined and all the routers specified. What else? Just a few details. Now we are going to define a little middleware to print something on screen to show that something is happening:

    // Middleware to use for all requests
    router.use(function(req, res, next) {
    	// do logging
    	console.log('Something is happening.');
    	next();
    });

    Then we have to register on the application all the routes created with Express:

    // REGISTER OUR ROUTES
    // =============================================================================
    app.use('/api', router);

    And finally the line that starts our new server:

    // START THE SERVER
    // =============================================================================
    app.listen(port);
    console.log('Magic happens on port ' + port);
  7. Everything is done now! Just execute the server.js with Node.js and check if your server works:
    sudo node server.js

Remember to check out my GitHub site and download the source code of this tutorial. On the next tutorial, we will learn how to split all the code in files for large APIs with a lot of models.

Good coding!

26 Comments


  1. Awesome!

    I’m migrating from Express 3.x / MongoDB / Mongoose, to Express 4.x / PostgreSQL / Sequelize. Your article was timely and helpful.

    Also, I created a minor pull request to fix a confusing typo in the server.js comments.

    Thanks for sharing Pablo!

    Reply


  2. Thanks for this! Helped me get up and running – also checked out your sequelize-restful follow up, simple but informative.

    FYI there’s actually a bug in your user model here – where you do this:


    updateById: function(user_id, onSuccess, onError) {
    var id = user_id;

    followed by this:


    removeById: function(user_id, onSuccess, onError) {
    User.destroy({id: user_id}).success(onSuccess).error(onError);
    }

    The result of executing a removeById is that the User.destroy where clause is compromised, since id has been set = user_id. The effect is essentially to do a delete where user_id = user_id which is always true meaning that all user records will be deleted regardless of what ID you specify… maybe worth a quick edit!

    Reply

    1. Ah actually I was wrong about the cause of the issue – the problem is in fact that the ‘where’ keyword is missing from the destroy call – my bad.

      The removeById function should look like this:


      removeById: function(user_id, onSuccess, onError) {
      User.destroy({ where: { id: user_id }}).success(onSuccess).error(onError);
      }

      Reply

      1. Thank you very much Nick! I’m so sorry for the bug. I’ve edited my model here and in GitHub. I expect this didn’t cause you too much troubles.

        Thanks for your feedback!

        Reply

  3. Hi pjanaya,
    I have download and installed Node.js – node.v0.10 in windows
    while executing node.js in cmd prompt > npm install I am getting below error and unable to proceed further.Kindly help if you can

    100 http GET https://registry.npmjs.org/mysql
    101 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    102 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    103 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    104 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    105 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    106 info trying registry request attempt 2 at 03:06:47
    107 http GET https://registry.npmjs.org/express
    108 info trying registry request attempt 2 at 03:06:47
    109 http GET https://registry.npmjs.org/mysql
    110 info trying registry request attempt 2 at 03:06:47
    111 http GET https://registry.npmjs.org/sequelize
    112 info trying registry request attempt 2 at 03:06:47
    113 http GET https://registry.npmjs.org/body-parser
    114 info trying registry request attempt 2 at 03:06:47
    115 http GET https://registry.npmjs.org/crypto
    116 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    117 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    118 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    119 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    120 info retry will retry, error on last attempt: Error: CERT_UNTRUSTED
    121 info trying registry request attempt 3 at 03:07:48
    122 http GET https://registry.npmjs.org/mysql
    123 info trying registry request attempt 3 at 03:07:48
    124 http GET https://registry.npmjs.org/sequelize
    125 info trying registry request attempt 3 at 03:07:48
    126 http GET https://registry.npmjs.org/crypto
    127 info trying registry request attempt 3 at 03:07:48
    128 http GET https://registry.npmjs.org/express
    129 info trying registry request attempt 3 at 03:07:48
    130 http GET https://registry.npmjs.org/body-parser
    131 silly lockFile 19633609-sequelize sequelize@*
    132 silly lockFile 19633609-sequelize sequelize@*
    133 silly lockFile 63b9c09b-crypto crypto@*
    134 silly lockFile 63b9c09b-crypto crypto@*
    135 silly lockFile 68913812-mysql mysql@*
    136 silly lockFile 68913812-mysql mysql@*
    137 silly lockFile 9dcfb469-express-4-0-0 express@~4.0.0
    138 silly lockFile 9dcfb469-express-4-0-0 express@~4.0.0
    139 silly lockFile ed7f1d4f-body-parser-1-0-1 body-parser@~1.0.1
    140 silly lockFile ed7f1d4f-body-parser-1-0-1 body-parser@~1.0.1
    141 error Error: CERT_UNTRUSTED
    141 error at SecurePair. (tls.js:1380:32)
    141 error at SecurePair.emit (events.js:92:17)
    141 error at SecurePair.maybeInitFinished (tls.js:979:10)
    141 error at CleartextStream.read [as _read] (tls.js:471:13)
    141 error at CleartextStream.Readable.read (_stream_readable.js:340:10)
    141 error at EncryptedStream.write [as _write] (tls.js:368:25)
    141 error at doWrite (_stream_writable.js:225:10)
    141 error at writeOrBuffer (_stream_writable.js:215:5)
    141 error at EncryptedStream.Writable.write (_stream_writable.js:182:11)
    141 error at write (_stream_readable.js:601:24)
    141 error at flow (_stream_readable.js:610:7)
    141 error at Socket.pipeOnReadable (_stream_readable.js:642:5)
    142 error If you need help, you may report this *entire* log,
    142 error including the npm and node versions, at:
    142 error
    143 error System Windows_NT 6.1.7601
    144 error command “C:\\Program Files\\nodejs\\\\node.exe” “C:\\Program Files\\nodejs\\node_modules\\npm\\bin\\npm-cli.js” “install”
    145 error cwd C:\nodejs\nodejs-express4-mysql-master
    146 error node -v v0.10.33
    147 error npm -v 1.4.28
    148 verbose exit [ 1, true ]

    Regards,
    Raghv

    Reply

  4. I am getting this error on localhost , whenever I am trying to connect to the phpmyadmin .

    { [SequelizeConnectionRefusedError: connect ECONNREFUSED]
    name: ‘SequelizeConnectionRefusedError’,
    message: ‘connect ECONNREFUSED’,
    parent:
    { [Error: connect ECONNREFUSED]
    code: ‘ECONNREFUSED’,
    errno: ‘ECONNREFUSED’,
    syscall: ‘connect’,
    fatal: true },
    original:
    { [Error: connect ECONNREFUSED]
    code: ‘ECONNREFUSED’,
    errno: ‘ECONNREFUSED’,
    syscall: ‘connect’,
    fatal: true } }

    Reply

  5. Great article (Y) . Very helpful.

    I was also looking for how to clearly separate different parts of application and I was stuck at how to separate different sequelize models in different files and have a model service that loads only once that is singleton model service, to define and export models.

    Then I found this github article that shows how to create singleton module to define and use sequelize models.

    Reply

  6. Very nice article, but I have landed in some trouble, I get undefined is not a function when adding the user, the data is recorded in the database, but I get this error, so I won’t get the expected json response.

    Please help me out in this.

    Reply

  7. Dear Pablo,
    i have this error on calling the service i have tried to solve it but it’s not work probably .

    Error: Please note that find* was refactored and uses only one options object from now on.

    thanks in advance

    Reply

    1. Hi Asem! Thanks for reading me.

      I’ve updated the project on GitHub to specify versions on package.json.

      Download it again, execute “npm install” and that should be enough.

      Let me know if you still have problems 🙂

      Reply

  8. Hi Pablo, i got an error when i upgrade the version of my dependency in sequelize, the problem is rror: Please note that find* was refactored and uses only one options object from now on. how will i resolve this, do i have to revert to the original version? please help.

    Reply

    1. Hi Alain,

      The versions of the different packages in NPM changes a lot. Because of this usually the scripts stop working when a major version of some package is release. Sequelize releases a lot of breaking versions so it’s not safe to update the dependency. I’m sorry but I’m not maintaining the code, but feel free of making a “Pull Request” on GitHub if you update the script to the latest version of Sequelize.

      Thanks for reading me.

      Good luck!

      Reply

  9. Hi, pablo
    I have got error, when run this script. can you help me..

    toor@mint ~/Workspace/nodejs-express4-mysql-master $ node server.js
    The program ‘node’ can be found in the following packages:
    * node
    * nodejs-legacy
    Try: sudo apt-get install

    thanks

    Reply

      1. [SOLVED] After run script sudo apt-get install nodejs-legacy.

        in other case I create modular squelize from your patern and I get error

        model.js

        var DataTypes = require(“sequelize”),
        sequelize = app.get(‘sequelize’);

        var Session = sequelize.define(‘sessions’, {
        session_id: DataTypes.STRING,
        apikey: DataTypes.STRING,
        email: DataTypes.STRING,
        session: DataTypes.STRING,
        starttime: DataTypes.INTEGER,
        endtime: DataTypes.INTEGER
        }, {
        instanceMethods: {
        retrieveAll: function(onSuccess, onError) {
        Session.findAll({}, {
        raw: true
        }).success(onSuccess).error(onError);
        }
        });

        exports = module.exports.session = Session;

        controller.js

        var model = require(‘../model/model.js’);
        exports = module.exports = {
        get: {
        sess: sess
        }
        };
        function sess(req, res) {
        var session = model.session.build();
        session.retrieveAll(function(sessions) {
        if (sessions) {
        res.json(sessions);
        } else {
        res.send(401, “Sessions not found”);
        }
        }, function(error) {
        res.send(“Sessions not found”);
        });
        }

        router.js
        var session = require(‘./controller/controller.js’);
        app.get(‘/sessions/’, session.get.sess);

        when run [GET] http://localhost:3001/sessions

        I got error

        Error: Please note that find* was refactored and uses only one options object from now on.
        at Model.findAll (/home/rahadyan/Workspace/backend/node_modules/sequelize/lib/model.js:1296:11)
        at sequelize.define.instanceMethods.retrieveAll (/home/rahadyan/Workspace/backend/modules/session/model/model.js:15:15)
        at sess (/home/rahadyan/Workspace/backend/modules/session/controller/controller.js:43:13)
        at Layer.handle [as handle_request] (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/layer.js:95:5)
        at next (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/route.js:131:13)
        at Route.dispatch (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/route.js:112:3)
        at Layer.handle [as handle_request] (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/layer.js:95:5)
        at /home/rahadyan/Workspace/backend/node_modules/express/lib/router/index.js:277:22
        at Function.process_params (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/index.js:330:12)
        at next (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/index.js:271:10)
        at /home/rahadyan/Workspace/backend/common/express.js:24:3
        at Layer.handle [as handle_request] (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/layer.js:95:5)
        at trim_prefix (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/index.js:312:13)
        at /home/rahadyan/Workspace/backend/node_modules/express/lib/router/index.js:280:7
        at Function.process_params (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/index.js:330:12)
        at next (/home/rahadyan/Workspace/backend/node_modules/express/lib/router/index.js:271:10)
        Please note that find* was refactored and uses only one options object from now on.

        Reply

  10. Hi,

    Thanks for such an awesome blog .
    I have one doubt as what is the input param to the web service because i am getting this error

    username: NaN
    password: NaN

    TypeError: Not a string or buffer
    at TypeError (native)
    at Hash.update (crypto.js:70:16)
    at sequelize.define.instanceMethods.add (/home/ankit/Desktop/nodeapidev/server.js:56:11)
    at Object.handle (/home/ankit/Desktop/nodeapidev/server.js:98:7)
    at next_layer (/home/ankit/Desktop/nodeapidev/node_modules/express/lib/router/route.js:103:13)
    at Route.dispatch (/home/ankit/Desktop/nodeapidev/node_modules/express/lib/router/route.js:107:5)
    at /home/ankit/Desktop/nodeapidev/node_modules/express/lib/router/index.js:195:24
    at Function.proto.process_params (/home/ankit/Desktop/nodeapidev/node_modules/express/lib/router/index.js:251:12)
    at next (/home/ankit/Desktop/nodeapidev/node_modules/express/lib/router/index.js:189:19)
    at Function.proto.handle (/home/ankit/Desktop/nodeapidev/node_modules/express/lib/router/index.js:234:5

    Reply

  11. Gracias por compartir tus conocimientos.
    Me funcionó de una y con Postgres.
    Soy novato en Nodejs.
    Por favor solicito tu ayuda, como puedo estructurar un menú (las rutas desde un Sidebar) para un proyecto con (n) mòdulos: User (ya está), Home, Blog, +, por ejemplo. Me explico un tanto mejor: La idea es sacar a rutas y sequelize del archivo server.js. Este archivo va a crecer mucho con un proyecto mediano.

    No tienes en mente un post que le adicione a este gran Tuto AngularJs? Sería bien agradecido por la comunidad.

    Nuevamente Gracias.

    Miguel León
    Quito.-Ecuador
    “La Mitad del Mundo”

    Reply

    1. Buenas Miguel,

      Te he enviado un email con un proyecto de prueba que hice hace tiempo. Muchas gracias por leerme y espero que tengas suerte.

      Un saludo.

      Reply

        1. Hi Axel, thanks for reading me 🙂

          Don’t worry, you have to understand the difference between different HTTP verbs. If you use the link “http://localhost:8080/api/users?username=axel&password=asdf”, you are actually using the default HTTP verb: GET. However, to add a new user, you need to use POST. You can try these verbs and learn the different using the tool POSTman, which is really useful. Let me know if you need more help 🙂

          Reply

  12. Hi Pablo,

    Thanks for great article.
    It helped me a lot to kick start my nodeJS learning with MVC structure.

    One question : You mentioned that you will write another article to separate things in different files (for large projects).
    I am going start a project where I will have to deal with 10-12 models (MYSQL DB) and AngularJS as front-end. I was wondering if you can give me little guidance. If you have written other article for separating things, Please give me link.

    Thanks in advance 🙂

    Harpreet Gill

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *