Home Scripting Node.js Node.js Mysql: Express, Mysql Module Installation, Examples

Node.js Mysql: Express, Mysql Module Installation, Examples

    29 1906

    Learn in step-by-step how to

    • Install express, mysql modules for node.js
    • Interact with Mysql Database

    External modules required: Express, Mysql

    Express is a web app framework for node.js It provides tools for better and faster web development.
    Mysql module is the driver for node.js to interact with mysql database.
    Note:
    Please install mysql in your computer before going further. Once installed you can start mysql server.
    Create a database named nodejsmysql with a table name as nodejs and 2 columns fname and lname using varchar as their datatypes.

    Installing the required modules

    • Goto terminal(cmd prompt)->enter your project folder->npm install -g express. Express module will be installed globally.
    • Once the installation is done create a project folder->goto->cmd prompt(terminal)->enter your project folder->typein->express. This will create a project structure->now you have to download the dependencies which are required by our project (ex:jade module). typein->npm install. this will download the dependant modules.
    • npm install mysql. This command will install mysql module in your current project. Once installation is done, you can start coding.

    Node.js MySQL Example

    Copy the following code replace it in app.js file.

    var express = require('express')
    , http = require('http')
    , mysql = require('mysql')
    , path = require('path');
    var app = express();
    // all environments
    app.set('port', process.env.PORT || 3002);
    app.set('views', __dirname + '/views');
    app.set('view engine', 'jade');
    app.use(express.favicon());
    app.use(express.logger('dev'));
    app.use(express.bodyParser());
    app.use(express.methodOverride());
    app.use(app.router);
    app.use(express.static(path.join(__dirname, 'public')));

    app.get('/', function( req, res) {
    res.render('index');
    });
    //connect to mysql database
    var connection = mysql.createConnection({
    host : 'localhost',
    user : 'root',
    password : 'yourpasswordgoeshere',
    database : 'nodejsmysql'
    });
    connection.connect();
    app.get('/users', function (req, res) {
    connection.query('select * from nodejs', function(err, docs) {
    res.render('users', {users: docs});
    });
    });
    // Add a new User
    app.get("/users/new", function (req, res) {
    res.render("new");
    });
    // Save the Newly created User
    app.post("/users", function (req, res) {
    var fname=req.body.fname;
    var lname=req.body.lname;
    connection.query('INSERT INTO nodejs (fname, lname) VALUES (? , ?);' , [fname, lname], function(err, docs) {
    if (err) res.json(err);
    res.redirect('users');
    });
    });
    http.createServer(app).listen(app.get('port'), function(){
    console.log('Express server listening on port ' + app.get('port'));
    });

    *goto->terminal(cmd prompt)->your project folder->run the app->node app.js (when needed to terminate use ctrl+c command).
    *you can check the output in the browser at->localhost:3002

    Explanation of node.js mysql example

    • lines 1 to 4->here we have included all the required modules for our application.
    • lines 5 15->All these are the default setters and getters provided by the express framework.
    • line 16->app.get(‘/’, function(req, res) { res.render(‘index’);}); ->when you open the browser and type localhost:3002/ this default path url(/) is taken by the framework and index.jade file is rendered. this index file is inside the views folder in your project. It will render the static content inside the index.jade file.
      We can also pass data into the index file using optional parameters, thats what happened at lines 29 and 35->whenever someone called localhost:3001/articles or localhost:3001/articles/new different pages are rendered. Now going back to
    • line 20->Send the required parameters to the database server using mysql.createConnection() method, there are many other parameters you can pass. refer https://npmjs.org/package/mysql for more information.
      Here nodejsmysql is the name of the database.
    • line 26->connection.create() and connection.end() functions are used to connect and disconnect to the mysql server.
    • line 30->We can interact with mysql using a simple connection.query() method. We can perform all the CRUD operations here.

    Create the required views to render the output

    By default Express framework uses jade templating system.

    Inside the views folder edit index.jade file and replace the existing code with the following code


    extends layout

    block content
    p Welcome to nodejs mysql tutorial
    p
    a(href='/users') Show All users

    Create a users.jade file and paste the following code in it


    h1 Showing All Users

    ul
    - each user in users
    li User FirstName: #{user.fname}
    li User LastName: #{user.lname}
    hr

    a(href="users/new") Add New User
    br
    a(href="/") Home

    Note->To pass dynamic values use #{nameofthevariable}

    Create a new.jade file and paste the following code


    h1 Add new User

    form(method="POST" , action="/users")
    p FirstName:
    input#title(type="text" , name="fname")
    p LastName:
    input#body(type="text" , name="lname")

    p: button(type="submit") Add New User

    Thats it. You can go to home page at localhost:3002, add new users at localhost:3002/users/new and see the existing users localhost:3002/users

    Note:
    1. Make Sure that mysql server is running while performing the above CRUD database operations.
    2. It is quite easy to learn jade templating system, but be careful while using node.js functions inside the .jade files. you need to use ‘-’ like in articles.jade file.
    3. Whenever you modify any file restart the webserver. Use ctrl+c to exit and then run again.

    Here is the Source Code for the Tutorial.

    • Pratip Ghosh

      Hello,nnIn index.js file I have , nnapp.get(‘/’, function (req, res) {nconnection.query(‘select message from messages’, function(err, docs) {nres.render(‘page’, {messages: docs});n});n});nn… and in my page.jade I havenn#content(style=’width: 500px; height: 300px; margin: 0 0 20px 0; border: solid 1px #999; overflow-y: scroll;’):#{messages.message}nnbut it says “messages is not defined”. What am I doing wrong?

      • Acharya Vaddey

        Hi Pratip,nnTry removing unnecessary CSS from your .jade file, The indentation is very important when you are using Jade templating system, and also be aware of these: make sure that your mysql server is running and you have a database named messages with a table named message, add some data in it. When ever you modify the nodejs code, you have to restart the web server. Also, I have updated the tutorial and added the complete code separately. Give it a try.

    • Sonus

      Really great post matennNote for those who are trying this: In Jade template its has this crazy rule where tab and spaces will be used to map the data so take them seriously, I had to spend almost half an hour to understand it :)

      • Acharya Vaddey

        Hi Sonus,nnThanks for your comment. You are absolutely right about jade, Jade is probably pulling the beginners away from learning node. There are other template systems available, But a beginner to node want to focus on learning node rather than trying to fix problems/learn jade.

    • Mark Arjohn

      Hi, I’ve tried your post and I had fun while understanding it but I got stuck at the users.jade part.nThis is the error that I’ve encountered:nexpected “indent”, but got “newline”

      • Mark Arjohn

        Oh it’s fixed, I’ve went to the link that you gave and copy pasted the users.jade source code there.nI’m used to always using tab to indent my codes but when it comes to jade, does tab and space matters?

        • Acharya Vaddey

          Hey Mark,nnThanks for commenting, Yes indentation matters when it comes to jade template system, once you try 3 or 4 examples you will understand how jade works. You can try online demo @ http://jade-lang.com/demo/ , Have fun coding.

          • sri risha

            hi can you please tell me how to connect phpmyadmin

            • Acharya Vaddey

              May be you should try Express Admin: Its a MySQL Admin for Node.jsnhttps://github.com/simov/express-adminnnnYou can watch the introduction here: http://www.youtube.com/watch?v=1CdoCB96QNk

            • sri risha

              Is it not possible using phpmyadmin.

            • sri risha

              Do you have any idea how to connect phpmyadmin.because iam using phpmyadmin.

            • Acharya Vaddey

              Hey Risha,nnThanks for commenting.nPhpmyadmin is used for MySQL Administration. If you have executed the above example successfully, then the user names will be shown in the table with the name nodejs. But If you want to install Phpmyadmin, you can simply install xampp: http://sourceforge.net/projects/xampp/

            • sri risha

              hi nnnnThanks for reply.nnnyesterday i started node.js.i dont know how to connect phpmyadmin in app.js. without downloading mysql, is it possible to use phpmyadmin.

            • Acharya Vaddey

              Hi Sri Risha,nI have a couple of questions, n1. Why do you want to connect phpmyadmin with app.js?n2. Have you installed MySQL on your system?

            • sri risha

              Thank you very much! Your post is extremely helpful.

            • Acharya Vaddey

              You are Welcome.

    • Andy Heaton

      This has been quite helpful. If I could make a suggestion, make the code for users.jade that’s in the tutorial have the formatting that the code on github does, cause I think most people who aren’t familiar with jade will scratch their heads about that. nnnAlso, do you have any thoughts on why my button to add new users doesn’t work? I can’t figure it out.

      • Acharya Vaddey

        Hey Andy, Thanks for commenting. There is a problem with my theme. Its not supporting the indentation.. that’s why I have added the github link. Coming to your button problem- try copying the content as it is from the new.jade file as provided on github. Its probably an indentation problem. thanks.

        • Andy Heaton

          Yeah, this is my first time doing anything with Jade so I was pretty unfamiliar with the spacing. Thanks so much, this has been a great start to learning Node!

      • Andy Heaton

        Nevermind, I figured it out. The code on this tutorial page again didn’t have the same spacing in the jade files as the code in the repo. nnnThis was a very helpful tutorial, thank you!

    • Andy Heaton

      For anybody who was not only new to Node, Jade, and MySQL, here are some things that got me caught up or took me a while to figure out:nn 1. On all the Jade files, go to the github repo he posted and get the code from there. The spacing is important and didn’t make it onto the code here in the tutorial.nn 2. MySQL has a great getting started tutorial here:n http://dev.mysql.com/doc/workbench/en/wb-getting-started-tutorial.html

    • Matt

      Hi, I have got one question. The database connection is opened and is possible to send some query to the DB ,but what about conn.end()? Is it alright have opened connection all the time without any release…?

      • Acharya Vaddey

        Hi Matt,nnThanks for commenting.nnCase1: If the program is long lived and only needs a single connection but uses that connection continuously then you can just leave that one connection open, but you should be prepared to reopen the connection if required – e.g. in case if the server gets restarted.nnCase2: If the program is short lived, i.e. it makes a connection, does some stuff, and then exits you can get away without closing the connection because it’ll get closed automatically when your program exits.nnIdeally it is better to close the connection.

    • Acharya Vaddey

      Hi Matt,nnThanks for commenting.nnCase1: If the program is long lived and only needs a single connection but uses that connection continuously then you can just leave that one connection open, but you should be prepared to reopen the connection if required – e.g. in case if the server gets restarted.nnCase2: If the program is short lived, i.e. it makes a connection, does some stuff, and then exits you can get away without closing the connection because it’ll get closed automatically when your program exits.nIdeally it is better to close the connection.

    • Guest

      Hi, I have a problem when I type node app.js, after I type npm install MySQL. I installet phpMyAdmin in my System and the MySql is running. How I must do? Thank’s

    • Kholid Fadlli

      Hi, I have a problem after I type node app.js. I installed “npm install myslq”. I also installed phpMyAdmin on my system and the MySQLserver is running. How to fix it?

      • Acharya Vaddey

        This is the problem with your mysql database access credentials, looks like you have fixed it..

    • Pingback: 15 Node.js Modules for Beginners | Tutorialindustry.com