Thursday 14 August 2014

connect mysql database with node.js with express

Standard

you have developed a app but you have not database or you dont know how to connect with database like you create a icecream but its not cool

we have lots of database engine but today i will tell you how you connect with mysql database

so today i will describe how you connect database or install mysql.



first you need mysql so open terminal and type bellow syntax

sudo apt-get install mysql-server

ok now you need to install mysql module in node so first open terminal and goto that folder where you want to create you app and next follow below command

sudo npm install mysql

now you need to connect to express so you need to connection module again follow below command in terminal

sudo npm install express-myconnection

now you have all resource for database connection
now you need to connect with mysql from command prompt follow below command

mysql -uroot -p

now need password to connect to mysql after login open a mysql console now create and use a database follow below command

create database test;
use test;

now you need to create a user table for test

next you need to edit your app.js file and paste below command

var mysql = require('mysql');
var connection  = require('express-myconnection');
/*****************mysql connection*******************/
app.use(
    connection(mysql,{
        host: 'localhost',
        user: 'mysql username',
        password : 'mysql password',
        port : 3306,
        database:'test'
    },'request')
);
/*****************************************/


now you need to fetch data from database and use it , ok now send a sql query to mysql server and show all data to console see below

app.get('/', function(req, res) {
        req.getConnection(function(err,connection){
        var query=connection.query("select * from user",function(err,rows){
                if(err){

//fire when send a error from mysql server
                    res.redirect('/');
                }
                else{
                    console.log(rows);
                    res.redirect('/');       
                }
            });
    });
    res.render('index', {title:'test title'});
});


ok you have created a app that connect with mysql server

now enjoy and fun with your code.
if you got any problem or anything you can contact with me through comment.

happy to help u.