Skip to content

Node Js with MySQL Database

Jump to bottom
Aakash Goplani edited this page Mar 3, 2020 · 22 revisions

Topics Covered

Connecting with MySqL Database

  • Install MySQL dependency:

    npm install --save mysql2

  • Now we have two options to connect to database:

    1. create a connection
    2. create a pool of connections

  • Once we set up one connection and run queries, we should always close the connection once we're done with a query and the downside is that we need to re-execute the code to create the connection for every new query and there will be a lot of queries because we fetch data, we write data, we delete data, creating new connections all the time quickly becomes very inefficient both in our code and also regarding the connection to the database which is established and the performance this may cost.

  • A better way is to create a connection pool by using createPool() method and passing below meta-data

    const mysql = require('mysql2');
const pool = mysql.createPool({
   host: 'localhost',
   user: 'root',
   database: 'node_complete',
   password: 'test123'
});

  • Finally we export our pool which can then be imported in our app.js file

    module.exports = pool.promise();

/* app.js file */
const db = require('./util/sql_database');

Fetching Data

  1. In model, execute database statement and return promise

    const db = require('./util/sql_database'); 
...
static fetchAllSequelly() {
   return db.execute('SELECT * FROM products');
}

  2. In controller, execute promise and render View

    exports.getProducts = (req, res, next) => {
   Product.fetchAllSequelly()
   .then(([row, fieldData]) => {
      res.render('shop/product-list', { 
         prods: row, 
         title: 'All Products', 
         path: '/products'
      });
   })
   .catch(error => {
      console.log('Error in Shop controller while fetchung all data sequelly');
   });
};
  • Note data returned from promise is an array of length 2. Data at index 0 is actual result that we want i.e. product info and data at index 1 is meta-data of table + database. So we use ES6 destructuring syntax to fetch data and use data at index 0.

Inserting Data

  1. In model, execute database statement and return promise

    saveDataSequelly() {
   return sqlDB.execute('INSERT INTO products (title, price, description, imageUrl) VALUES (?, ?, ?, ?)',
     [this.title, this.price, this.description, this.imageUrl]);
}
    • Note: do not directly insert data into values, always use ? to insert data dynamically, this prevents SQL injection.

  2. In controller, execute promise and render View

    exports.postAddProducts = (req, res, next) => {
   const title = req.body.title;
   const imageUrl = req.body.imageUrl;
   const price = req.body.price;
   const description = req.body.description;
   const product = new Product(null, title, imageUrl, description, price);
   product.saveDataSequelly()
   .then(() => {
      res.redirect('/');
   })
   .catch(error => {
      console.log('Error in Admin controller while saving data sequelly')
   });
}

Inserting Data with Condition

  1. In model, execute database statement and return promise

    static findProductByIdSequelly(id) {
    return sqlDB.execute('SELECT * FROM products p WHERE p.id=?', [id]);
}
    • Note: do not directly insert data into values, always use ? to insert data dynamically, this prevents SQL injection.

  2. In controller, execute promise and render View

    Product.fetchAllSequelly()
.then(([row, fieldData]) => {
   res.render('shop/index', { 
       prods: row, 
       title: 'Index', 
       path: '/'
   });
})
.catch(error => {
   console.log('Error in Shop controller while fetching all data sequelly');
});
    • Note: the row here holds our data object wrapped in an array. So be sure to use row[0] as our view expects object

Sequelize

  • Sequelize is a third party package that provides an Object Relational Mapping library i.e. it does all the SQL code behind the scenes for us and maps it into javascript objects with convenience methods which we can call to execute SQL code so that we never have to write SQL code on our own.

  • It works like this,

    • we got one object let's say a user with a name, age, email and password, is mapped to a database table by sequelize automatically
    • we simply call a method on that user javascript object and sequelize executes the SQL query or the SQL command that is required.
    • so instead of writing this on our own, we simply create a javascript object and work with that e.g. to create a new user which would behind the scenes execute the SQL code we don't have to write. sql_orm_1

  • Sequelize offers us

    • the models to work with our database i.e. helps us to define which data makes up a model and therefore which data will be saved in the database.
    • We can then instantiate these models to create a new object based on that model
    • so we have a connection here and we can then run queries on that which could be for e.g. to save a new user
    • we can also associate our models to other models. sql_orm_1

Connecting With DB

  1. Install sequelize dependency. Note this dependency needs mysql2, so be sure to install that first

    npm install --save sequelize

  2. Create a Sequelize constructor and export it.

    const Sequelize = require('sequelize');
const sequelize = new Sequelize('node_complete', 'root', 'admin', { dialect: 'mysql', host: 'localhost' });
module.exports = sequelize;
    • Important: Sequelize automaticaly creates a pool of connection for us behind the scenes (one which have to configure manually in case of mysql2)

Creating Model

  • First step in Sequelize is to create model. So in your model file import installed Sequelize package and sequelize database connection created earlier:

    const Sequelize = require('sequelize');
const sequelize = require('../util/sql_database');

  • Defining model is simply stating/writing down the schema of table. We do this by calling define() method on sequelize.

    • The first argument is the model name.
    • Second argument is the structure of table in form of JavaScript object where each key corresponds to column name and each value corresponds to a object that holds column meta-data 
      const ProductSequel = sequelize.define('product_sequelize', {
   id: {
     type: Sequelize.INTEGER,
     autoIncrement: true,
     allowNull: false,
     primaryKey: true
   },
   title: {
     type: Sequelize.STRING,
     allowNull: false
   },
   description: Sequelize.STRING, 
   imageUrl: {
     type: Sequelize.STRING,
     allowNull: false
   }
});

  • Finally we export our model

    module.exports = ProductSequel;

Syncing JavaScript with Database

  • In app.js just before we start our server we call sync() method on sequelize

    sequelize.sync()
.then((result) => {
   console.log('TABLE CREATED', result);
})
.catch(error  => {
   console.log('Error in creating sequelize table in app.js', error);
});

  • This method looks/scans for modules available in the app and create table product_sequelize (name which we assign while creating model)

Inserting Data

  • Use create() method for inserting data 
    Product.create({
   title,
   price,
   description,
   imageUrl
})
.then(results => {
   res.redirect('/admin/products');
})
.catch(error => {
   console.log('Error in Admin controller while saving data sequelly using sequelize', error);
});

Fetching Data

  • Fetch ALL data using findAll() method

    Product.findAll()
.then((product) => {
   res.render('admin/products', { 
      prods: product, 
      title: 'Admin Products', 
      path: '/admin/products'
   });
})
catch(errors => {
   console.log('Error in Admin controller while saving data sequelly using sequelize', errors)
});

  • Fetch PARTICULAR data using findByPk(id) method

    const productId = req.params.productId;
Product.findByPk(productId)
.then(product => {
   res.render('admin/edit-product', { 
      title: 'Edit Product', 
      path: '/admin/edit-product',
      editing: editMode,
      product: product
   });
})
.catch(error => {
   console.log('Error in Shop controller while fetchung product with id sequelly');
});

  • You can also use findAll(_params_) method to find a particular product

    const productId = req.params.productId;
Product.findAll({ where: {id: productId} })
.then(products => {
   res.render('shop/product-detail', {
      product: products[0], 
      title: products[0].title, 
      path: '/products'
   });
})
.catch(error => {
   console.log('Error in Shop controller while fetchung product with id sequelly');
});

Updating Data

  • Update data with save() method 
    const productId = req.params.productId;
// find the product
Product.findByPk(id)
.then(product => {
   product.title = title;
   product.price = price;
   product.description = description;
   product.imageUrl = imageUrl;
   // update the product
   return product.save();
})
.then(results => {
   res.redirect('/admin/products');
})
.catch(error => {
   console.log('Error in Admin controller while saving edited data sequelly using sequelize', error);
});

Deleting Data

  • Delete data using destroy() method 
    const productId = req.params.productId;
// find the product
Product.findByPk(productId)
.then(product => {
   // delete product
   return product.destroy();
})
.then(results => {
   res.redirect('/admin/products');
})
.catch(error => {
   console.log('Error in Admin controller while deleting data sequelly using sequelize', error);
});

Defining Associations between Models

  • We have two models Product and User. One User can have many Product (1:n) whereas one Product has one User (1:1).

  • We need to define associations just before we sync() method

    const sequelize = require('./util/sql_database');
const Product = require('./models/product');
const User = require('./models/user');
...
Product.belongsTo(User, { constraints: true, onDelete: 'CASCADE' });
User.hasMany(Product);
...
sequelize.sync({force: true})
.then(result => { // console.log('TABLE CREATED', result); })
.catch(error => { // console.log('Error in creating sequelize table in app.js', error); });
    • Option force:true within sync() method results in DROPing existing table and recreating table again. We should omit this in PROD because we don't want to create table again and again.

Important Note

  • Important concept: We are storing user in a request object. Here are fetching user first and then storing in request object. We are also repeating this process in within sequelize() method.

  • Now you might be wondering if this can ever return a user if we only create it down there. Now keep in mind, app.use() here only registers a middleware so for an incoming request, we will then execute this function.

  • npm start runs this code for the first time and npm start is what runs sequelize() here not incoming requests, incoming requests are only funneled through our middleware.

  • So npm start runs this code which sets up our database but never this middleware, it just registers it as middleware for incoming requests. This code will only run for incoming requests which on the other hand can only reach this if we did successfully start our server here with app listen and that in turn is only true if we are done with our initialization code here, so we are guaranteed to find a user here.

  • Also keep in mind the user we're retrieving from the database here is not just a javascript object with the values stored in a database, it's a sequelize object with the value stored in the database and with all these utility methods sequelize added, like destroy.

app.use((req, res, next) => {
    User.findByPk(1)
    .then(user => {
        req.user = user;
        next();
    })
    .catch(error => {
        console.log('unable to find user in middleware: ', error)
    });
});
...
sequelize.sync()
.then(result => {
    return User.findByPk(1);
})
.then(user => {
    if (!user) {
        User.create({ name: 'test', email: '[email protected]' });
    }
    // else return user, but not an object, return user as a promise so that we can use .then() on it
    return Promise.resolve(user);
})
.then(user => {
    app.listen(4200, 'localhost');
})
.catch(error => {
    // console.log('Error in creating sequelize table in app.js', error);
});

Magic Methods

  • When an association is defined between two models, the instances of those models gain special methods to interact with their associated counterparts. For example, if we have two models, Foo and Bar, and they are associated, their instances will have the following methods/mixins available, depending on the association type

  • Foo.hasOne(Bar) OR Foo.belongsTo(Bar)

    • fooInstance.getBar()
    • fooInstance.setBar()
    • fooInstance.createBar()

  • Foo.hasMany(Bar) OR Foo.belongsToMany(Bar, { through: Baz })

    • fooInstance.getBars()
    • fooInstance.countBars()
    • fooInstance.hasBar()
    • fooInstance.hasBars()
    • fooInstance.setBars()
    • fooInstance.addBar()
    • fooInstance.addBars()
    • fooInstance.removeBar()
    • fooInstance.removeBars()
    • fooInstance.createBar()

  • Note: Method names As shown in the examples above, the names Sequelize gives to these special methods are formed by a prefix (e.g. get, add, set) concatenated with the model name (with the first letter in uppercase). When necessary, the plural is used, such as in fooInstance.setBars(). Again, irregular plurals are also handled automatically by Sequelize. For example, Person becomes People and Hypothesis becomes Hypotheses.

  • More Reading

Reference

Official Sequelize Docs

Home

HTML 5

GitHub

Angular

PWA

Node JS

JavaScript

Clone this wiki locally