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

29 3225

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

29 COMMENTS

  1. 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?

    • 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.

  2. 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 :)

    • 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.

  3. 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”

  4. 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.

    • 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.

      • 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!

    • 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!

  5. 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…?

    • 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.

  6. 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.

  7. 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

  8. 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?

Leave a Reply