Node.js Database Connection and Select Query

Posted at: March 18, 2019 4:37 PM

How to connect MySQL database in Node.js

In this lesson we will learn, how to connect MySQL database in the Node.js and will fetch records from database using select query using express framework.

For creating a nodejs application, you can see my previous lesson How to handle GET and POST requests in Node js

Before use mysql in Node.js must be ensured MySQL Database should be installed in your machine. You can see Installing MySQL documentation.

Install mysql node modules in node.js

Install mysql node modules in your application.


npm install mysql
Copy

Create a Custom Module for Database Connection

Create a db.js file in the root folder for database connection and use this connection in your entire project.

Connect your database using your MySQL database credential host, database user, database password and database name.


var mysql = require('mysql');

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'nodeapp'
});

connection.connect(function(err) {
  if (err) throw err;
  console.log('connected!');
});

module.exports = connection;
Copy

MySQL SELECT query

Create a file routes/products.js and import var db = require('../db'); into this for database connection.

Use MySQL select query for fetching all the records.


var express = require('express');
var router = express.Router();
var db = require('../db');

router.get('/', function(req, res, next) {

  var query = 'select * from products';
  db.query(query, function(err, rows, fields) {
    if (err) throw err;
    
    /*If you are creating api then get response in json format*/
    //res.json(rows);

    /*If you want response as json then comment below line*/
    res.render('products', { title: 'Products', products: rows});
  })
});

module.exports = router;
Copy

Fetch Single Record

For fetch single record using rows[0].


var express = require('express');
var router = express.Router();
var db = require('../db');

router.get('/', function(req, res, next) {

  var query = 'select * from products WHERE id=5';
  db.query(query, function(err, rows, fields) {
    if (err) throw err;
    
    /*If you are creating api then get response in json format*/
    //res.json(rows[0]);

    /*If you want response as json then comment below line*/
    res.render('products', { title: 'Products', products: rows[0]});
  })
});

module.exports = router;
Copy

View File

Create a file views/products.hbs


<div class="container">
  <h1>MySQL Database Integration and Select Data</h1>
  <h2>{{title}}</h2>

  <table class="table">
    <tr>
      <th>#ID</th>
      <th>Product Name</th>
      <th>Price</th>
      <th>SKU</th>
    </tr>
    {{#each products}}
      <tr>
        <td>{{this.id}}</td>
        <td>{{this.product_name}}</td>
        <td>{{this.price}}</td>
        <td>{{this.sku}}</td>
      </tr>
    {{/each}}
  </table>
</div>
Copy

Edit app.js

Edit app.js file and import var productsRouter = require('./routes/products'); and set products route app.use('/products', productsRouter);


.....
var productsRouter = require('./routes/products');
.....
.....
app.use('/products', productsRouter);
Copy

Complete code of app.js


var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var productsRouter = require('./routes/products');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'hbs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/products', productsRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;
Copy

Integrate Bootstrap

If you want to use bootstrap in your application, then copy Bootstrap CDN link in the views/layout.hbs


<!DOCTYPE html>
<html>
  <head>
    <title>{{title}}</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel='stylesheet' href='/stylesheets/style.css' />
  </head>
  <body>
    {{{body}}}
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>
Copy

MySQL Database Tables

Table products


CREATE TABLE `products` (
  `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) DEFAULT NULL,
  `sku` varchar(50) DEFAULT NULL,
  `price` decimal(8,2) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `products` (`id`, `product_name`, `sku`, `price`, `is_active`, `created_at`) VALUES
(1, 'Mobile', 'mobile', '6000.00', 1, '2018-09-15 00:00:00'),
(2, 'Laptop', 'laptop', '36000.00', 1, '2018-09-15 00:00:00'),
(3, 'LED Bulb', 'led-blub', '150.00', 1, '2018-09-15 00:00:00'),
(4, 'Television', 'Tele', '21150.00', 1, '2018-09-15 00:00:00'),
(5, 'Mouse', 'Mouse', '150.00', 1, '2018-09-15 00:00:00');
Copy

Conclusion

In this lesson we have learned integration mysql database in the Node.js and use a MySQL select query for fetching the records. And also integrate bootstrap.

This lesson also available on YouTube
mysqlselect querybootstrapdatabase connection

Please leave comments

3 Comments


ss 4 years ago
ss
Reply
kasid 5 years ago
incomplete
Reply
Jabir khan 5 years ago
nice
Reply