MySQL Node

db.js

/**
 * Created by codeforcoffee on 10/13/16.
 */
var knex = require('knex')({
    client: 'mysql',
    connection: {
        host : 'localhost',
        user : 'l33tdba',
        password : 'w0rk5pac3',
        database : 'first_run'
    }
});

modelSkeleton.js

function OneUp() {

  this.findAll = function() {

  };

  this.findById = function(id) {

  };

  this.updateById = function(id, dictionary) {

  };

  this.create = function(dictionary) {

  };

  this.findByIdAndRemove = function(dictionary) {
    
  };

};

module.exports = new OneUp();

queryPractice.js

/**
 * Created by codeforcoffee on 10/13/16.
 */
var db = require('./db');

db.raw('select * from one_ups;').then(function(response) {
    //console.log(response);  // full response from db
    console.log(response[0]);
});

// [ RowDataPacket {
//     id: 1,
//     title: 'first database entry',
//     content: 'i am amazing at everything i do',
//     occured_on: 2016-10-13T05:00:00.000Z }]

var insertQuery = "insert into one_ups (title, content, occured_on) " +
    "values ('second db entry', 'this one from javascript', current_timestamp);";

db.raw(insertQuery).then(function(response) {
   console.log(response);
});
// [ OkPacket {
//     fieldCount: 0,
//     affectedRows: 1,
//     insertId: 3,
//     serverStatus: 2,
//     warningCount: 1,
//     message: '',
//     protocol41: true,
//     changedRows: 0 },
//     undefined ]

db.raw('select * from one_ups;').then(function(response) {
    //console.log(response);  // full response from db
    console.log(response[0]);
});

// [ RowDataPacket {
//     id: 1,
//     title: 'first database entry',
//     content: 'i am amazing at everything i do',
//     occured_on: 2016-10-13T05:00:00.000Z },
// RowDataPacket {
//     id: 2,
//         title: 'second db entry',
//         content: 'this one from javascript',
//         occured_on: 2016-10-13T05:00:00.000Z } ]

shell_setup.sh

#install mysql
brew install mysql
# start server
/usr/local/Cellar/mysql/5.7.12/support-files/mysql.server start
# mysql.server start
# stop server
/usr/local/Cellar/mysql/5.7.12/support-files/mysql.server stop
# mysql.server stop
# install npm package
npm install --save mysql
# cli access
$ sudo mysql
# Password: 
# create & connect to db
create database first_run;
\u first_run
# create table
mysql> create table one_ups (id INT NOT NULL AUTO_INCREMENT,
    -> title VARCHAR(100) NOT NULL,
    -> content VARCHAR(255) NOT NULL,
    -> occured_on DATE,
    -> PRIMARY KEY (id)
    -> );
#Query OK, 0 rows affected (0.02 sec)
mysql> select * from one_ups;
#Empty set (0.01 sec)
mysql> create table users (id INT NOT NULL AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> biography VARCHAR(255),
    -> password_hash CHAR(60) BINARY,
    -> primary key (id)
    -> );
#Query OK, 0 rows affected (0.02 sec)
#create new user for security reasons
mysql> create user 'l33tdba'@'localhost' identified by 'w0rk5pac3';
#Query OK, 0 rows affected (0.00 sec)
#give user to access to database.tablename or database.* (all)
mysql> grant all privileges on first_run.* to 'l33tdba'@'localhost';
#Query OK, 0 rows affected (0.00 sec)
# ------------
# open new tab
# ------------
# express generate an app
# add modules
npm install lodash --save
npm install mysql --save
npm install knex --save
# add models/db.js
mkdir models
touch models/db.js
# add snippet below to db.js
# require in app.js ... var db = require('./models/db);
# run
npm start
# tab back to mysql
# check current logged in users
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+----+---------+-----------------+-----------+---------+------+-----------+----------------------------------------------+
| ID | USER    | HOST            | DB        | COMMAND | TIME | STATE     | INFO                                         |
+----+---------+-----------------+-----------+---------+------+-----------+----------------------------------------------+
|  8 | l33tdba | localhost:63997 | first_run | Sleep   |   33 |           | NULL                                         |
| 11 | root    | localhost       | NULL      | Query   |    0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST |
|  9 | l33tdba | localhost:63998 | first_run | Sleep   |   33 |           | NULL                                         |
+----+---------+-----------------+-----------+---------+------+-----------+----------------------------------------------+
3 rows in set (0.00 sec)
# oh snap
# let's add some content
#Database changed
mysql> insert into one_ups (title, content, occured_on) values ('first database entry', 'i am amazing at everything i do', current_timestamp); 
#Query OK, 1 row affected, 1 warning (0.01 sec)
# now view it
mysql> select * from one_ups;
+----+----------------------+---------------------------------+------------+
| id | title                | content                         | occured_on |
+----+----------------------+---------------------------------+------------+
|  1 | first database entry | i am amazing at everything i do | 2016-10-13 |
+----+----------------------+---------------------------------+------------+
1 row in set (0.00 sec)