Nodejs - mysql.txt

Title: Mysql with nodejs
Date: 30/10/2017
Author: Musha
Youtube: https://youtu.be/EN6Dx22cPRI
Reference: https://github.com/mysqljs/mysql


1) Install
    $ npm install mysql --save

2) use mysql and make connection
    const mysql = require('mysql');
    const db    = mysql.createConnection({
        host        : 'localhost',
        user        : 'musha',
        password    : 'password',
        database    : 'myDb'
    });

    // Connect to database
    db.connect();

3) Create database
    let sql = 'CREATE DATABASE nodemysql';
    db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
    });
    
4) Create table
    let sql = 'CREATE TABLE posts(
        id INT AUTO_INCREMENT, 
        title VARCHAR(255), 
        body VARCHAR(255), PRIMARY KEY(id)
    )';

    db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
    });

5) Fetch all data
    let sql = 'SELECT * FROM posts';
    db.query(sql, (err, results) => {
        if(err) throw err;
        console.log(results);
    });

6) Fetch single data
    let sql = `SELECT * FROM posts WHERE id = ${id}`;
    db.query(sql, (err,result) => {
        if(err) throw err;
        console.log(result);    
    });
    

7) Insert data
    let post = {title: 'Post One', body: 'This is post one'};
    let sql  = 'INSERT INTO posts SET ?';
    db.query(sql, post, (err, result) => {
        if(err) throw err;
        console.log(result);
    });

8) Update data
    let newTitle = 'Updated Title';
    let sql  = `UPDATE posts SET title = '${newTitle}' WHERE id = ${id}`;
    db.query(sql, (err, result) => {
        if(err) thow err;
        console.log(result);
    });

9) Delete data
    let sql = `DELETE FROM posts WHERE id = ${id}`;
    db.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
    });

10) Terminate connection
    db.end((err) => {
        if(err) throw err;
    });