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

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

    29 3529
    nodejs mysql tutorial

    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.

    SIMILAR ARTICLES

    best node.js cms and blogging platforms

    1 816
    docpad cms (nodejs) tutorial

    1 655