Cacher is the code snippet organizer for pro developers

//npm install --save express
//or
//yarn add express

const express = require('express');

//npm install --save mysql
//or
//yarn add mysql
const mysql = require('mysql');

//listen for connections on port
const port = 3000;

//create connection
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'nodemysql'
});

//connect to DB

db.connect(err => {
    if (err) {
        throw err;
    }
    console.log('mySQL is connected');
});

//express
const app = express();

//create DB
app.get('/createdb', (req, res) => {
    let sql = 'create database nodemysql';
    db.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('database has been created');
    });
});

//create users table,
app.get('/createuserstable', (req, res) => {
    let sql =
        'CREATE TABLE users(id int auto_increment, name varchar(255), address varchar(255), PRIMARY KEY id)';
    db.query(sql, (err, result) => {
        console.log(result);
        res.send('users table created');
    });
});

//create products table,
app.get('/createproductstable', (req, res) => {
    let sql =
        'CREATE TABLE products(id int auto_increment, name varchar(255), description varchar(255), price decimal, PRIMARY KEY (ID));';
    db.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('products table created');
    });
});

//create sample product
app.get('/createsampleproduct', (req, res) => {
    let post = {
        name: 'sample product',
        description: 'Lorem ipsum dolor sit amet',
        price: '15.00'
    };

    let sql = 'Insert into products set ?';

    let query = db.query(sql, post, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('sample product added to product table');
    });
});

//select all products
app.get('/products', (req, res) => {
    let sql = 'SELECT * from products';
    let query = db.query(sql, (err, results) => {
        if (err) throw err;
        console.log('fetching products');
        res.send(results);
    });
});

//select product by id

app.get('/product/:id', (req, res) => {
    let sql = `SELECT * FROM products WHERE id = ${req.params.id}`;
    let query = db.query(sql, (err, results) => {
        if (err) throw err;
        console.log(`fetching product ${req.params.id}`);
        res.send(results);
    });
});

//update post description
app.get('/updatepost/:id', (req, res) => {
    let newDescription = 'NEW DESCRIPTION';
    let sql = `UPDATE products SET description = '${newDescription}' WHERE id = ${
        req.params.id
    }`;
    let query = db.query(sql, (err, results) => {
        if (err) throw err;
        console.log(`updating description of product ${req.params.id}`);
        res.send(`product with ID: ${req.params.id} has a new description`);
    });
});

//delete product
app.get('/deleteproduct/:id', (req, res) => {
    let sql = `Delete  FROM products WHERE id = ${req.params.id}`;
    let query = db.query(sql, (err, results) => {
        if (err) throw err;
        console.log(`deleted product ${req.params.id}`);
        res.send(results);
    });
});

app.listen(port);
console.log(
    `listening for connections on ${port} 
    go to:
    http://localhost:${port}/createproductstable to create product table,
    http://localhost:${port}/createsampleproduct to create a sample product
    http://localhost:${port}/products to see products`
);