|
I'm a big fan of simplicity in code. And efficiency and read-ability. With
that in mind let me give a nod of thanks to Traversy Media for the bulk of this fine, simple, functional API
tutorial in Node; it's far more straightforward and effective than many an
alternative.
const express = require('express');
const mysql = require('mysql');
// Create connection
const db = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'ah',
database : 'nodemysql'
});
// Connect
db.connect((err) => {
if(err){
throw err;
}
console.log('MySql Connected...');
});
const app = express();
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 created...');
});
});
app.get('/createpoststable', (req, res) => {
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);
res.send('Posts table created...');
});
});
// add MUST use quotes on URL line around new body
app.get('/add/:body', (req, res) => {
// let sql = `SELECT * FROM posts WHERE id = ${req.params.body}`;
let sql = `INSERT INTO posts SET body = ${req.params.body}`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post added...');
});
});
// Insert post -hard coded
app.get('/addpost1', (req, res) => {
let post = {title:'Post One', body:'This is post number one'};
let sql = 'INSERT INTO posts SET ?';
let query = db.query(sql, post, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post 1 added...');
});
});
// Select posts
app.get('/getposts', (req, res) => {
let sql = 'SELECT * FROM posts';
let query = db.query(sql, (err, results) => {
if(err) throw err;
console.log(results);
res.send('Posts fetched...');
});
});
// Select single post
app.get('/getpost/:id', (req, res) => {
let sql = `SELECT * FROM posts WHERE id = ${req.params.id}`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post fetched...');
});
});
// Update post
app.get('/updatepost/:id', (req, res) => {
let newTitle = 'Updated Title';
let sql = `UPDATE posts SET title = '${newTitle}' WHERE id = ${req.params.id}`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post updated...');
});
});
// Delete post
app.get('/deletepost/:id', (req, res) => {
let newTitle = 'Updated Title';
let sql = `DELETE FROM posts WHERE id = ${req.params.id}`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post deleted...');
});
});
app.listen('3000', () => {
console.log('Server started on port 3000');
});
Reply
|
