trails-sequelize-express-SQL

trails-sequelize-express-SQL

	Instructions For Creating a Working Trails-Sequelize-Express-Mariadb Environment


These instructions are written for the complete beginner who has never used trails before.  I am starting out
with absolutely nothing, except a ten cent ($0.10) charge for a google-cloud virtual server.

With the exception of the "yo trails" command, this script could be ran as a complete script.  My suggestion is to copy/paste 
the top half, run "yo trails" manually, and run the bottom half as copy/paste.  
Optionally, it may be more educational to run each command individually.  YMMV.

A very special thank-you goes out to Scott B. Wyatt who was the sole inspiration behind this script, and whose extraordinary
patience with newbies made this script possible. Written from here:
https://github.com/scott-wyatt/trails-example-sequelize/blob/master/newbie-instructions.md


## install getpostman:
##
https://www.getpostman.com/ - install postman


## create a virtual server:
##
https://console.cloud.google.com
1) name your virtual server something like trails-express-sequelize-mariadb
2) create machine-type: micro (saves you $$)
3) choose centos7 environment  (centos7 is the best environment to use)


## connect to your new virtual server and run the following initial setup steps (5-10 minutes)
##
set -o vi ;                                             ## optional, but i love it.
sudo yum  --assumeyes  update  ;                        ## optional but always a good idea
sudo yum  --assumeyes  gcc gcc-c++  ;                   ## optional
sudo yum  --assumeyes  install  bzip2  bzip2-devel  curl  git  tar   wget   yum-utils   ;
sudo yum  --assumeyes  groupinstall "Development Tools"  ;


## we need to open the port since trails uses port 3000 by default:
##
sudo firewall-cmd --zone=dmz --add-port=3000/tcp --permanent ; sudo firewall-cmd --reload ;


## install node & npm
##
curl --silent --location https://rpm.nodesource.com/setup_8.x | sudo bash -  ;
sudo  yum --assumeyes  install nodejs;
node --version ;  ## look for v8.8.1 (at the time of this writing)


## install mariadb
##
sudo yum  -y install mariadb-server mariadb-client    ;


## start up mariadb
##
sudo systemctl start mariadb.service   ;    


## create a database, a user, and a password
##


mysql --verbose --host=localhost  --user=root   -t <<SQLROOTEND    ;

DROP DATABASE IF EXISTS  \`mariadbTrails\`   ;

GRANT ALL ON *.* TO 'mariadbUser'@'localhost'           ;
   -- make sure we have a name to delete!
DROP USER 'mariadbUser'@'localhost'           ;

CREATE USER 'mariadbUser'@'localhost' IDENTIFIED BY 'USERSPassWORD'        ;

GRANT ALL ON *.* TO 'mariadbUser'@'localhost'           \\
IDENTIFIED BY 'USERSPassWORD' WITH MAX_QUERIES_PER_HOUR 0   \\
MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0   ;

CREATE DATABASE IF NOT EXISTS \`mariadbTrails\`            ;

GRANT ALL PRIVILEGES ON \`mariadbTrails\`.* TO 'mariadbUser'@'localhost'   ;

SQLROOTEND




## log in as the newly created user and create a table, and insert one row into it
##
mysql --verbose --host=localhost  --user=mariadbUser --password=USERSPassWORD  mariadbTrails -t <<SQLUSEREND  ;   

CREATE TABLE mytable (
         username   VARCHAR(50) NOT NULL, 
         id         INT         NOT NULL AUTO_INCREMENT, 
         createdAt  DATETIME    NOT NULL, 
         updatedAt  DATETIME    NOT NULL, 
         PRIMARY KEY (id)
);
INSERT INTO mytable     (username   ,   createdAt , updatedAt )
             VALUES     ( 'big test' ,   NOW()     , NOW()     );
SELECT * FROM mytable;

SQLUSEREND


## install trails
##
sudo  npm install -g yo generator-trails ;

## create new directory and cd into it
##
mkdir  trails-test;  cd trails-test;



## finally, create our tails application
##
yo trails ;     ## notice defaults below
##  Get ready to blaze a new Trails Application!
##  
##  Checking for updates...
##  ? Choose a Web Server         express             [default WAS 'hapi']
##  ? What express version do you want to use ?  5    [default WAS 4]
##  ? Choose an ORM               sequelize           [default WAS 'waterline']
##  ? Do you want to use Footprints (automatic REST API from models) ? Yes
##  ? Module Name trails-test
##  ? Description 
##  ? Project homepage url 
##  ? Author's Name 
##  ? Author's Email                 a@a.com          [default WAS blank]
##  ? Author's Homepage 
##  ? Package keywords (comma to split) 
##  ? GitHub username or organization Guilhemehss
##  ? Which license do you want to use?    MIT        [default was Apache]
##



## install the mariadb npm and latest trailpack packages
##
npm  install   mysql  mysql2 --save;   ##  do we need mysql2 ??
npm install sequelize-cockroachdb  --save    ## in case of cockroachdb
npm  install  trailpack-sequelize@3.0.0 --save  ;   ## otherwise yo does not install the latest sequelize (thank you @jaumard)

## for fun, lets get some more debug info!
##
/usr/bin/sed -i "s/level: 'info'/level: 'debug'/;"  ./config/log.js ;   

## add a MariaDB connection to ./config/database.js stores:
##
/usr/bin/sed -i                                 \
    "s/stores: {$/& MariaDB:{host:'localhost',database:'mariadbTrails',username:'mariadbUser',password:'USERSPassWORD'}/; s/'sqlitedev'/'MariaDB'/;"   \
             ./config/database.js ; 

## add a line to the end of ./api/models/index.js
##
/usr/bin/cat <<END >> ./api/models/index.js ; 
exports.Mytable = require('./myTable')
END

## define the new testtable model in ./api/models/ directory
##
/usr/bin/cat <<END  >./api/models/myTable.js  ;
'use strict'
const Model = require('trails/model');
module.exports = class Mytable extends Model {
  static config(app, Sequelize) {
    return {
      options: {
          // underscored: true   // don't use camelcase for automatically added attributes but underscore style
                                 // so updatedAt will be updated_at
          //tableName: 'my_very_custom_table_name',    // define the table's name
      }
    }
  }
  static schema(app, Sequelize) {
    return {
      username: {
        type: Sequelize.STRING,
      },
    }
  }
}
END

## test time!!
##
npm start;

##  in postman:  get 'external IP' from console)
##      GET    http://123.123.123.123:3000/api/v1/mytable/   ( result should be "[]"  )
##      POST   http://123.123.123.123:3000/api/v1/mytable/   {  "username":"New Name Test"}
##      GET    http://123.123.123.123:3000/api/v1/mytable/

mysql  --host=localhost --user=mariadbUser  --password=USERSPassWORD    mariadbTrails    ;   
SELECT * FROM mytable;

========================================

cockroachdb create tables command:
CREATE TABLE mytable 
(  username VARCHAR(50) NOT NULL
, id SERIAL PRIMARY KEY
, "createdAt" TIMESTAMP NOT NULL
, "updatedAt" TIMESTAMP NOT NULL
);

============================
postgresql create table:
CREATE TABLE mytable 
( username varchar(50) NOT NULL
, id SERIAL
, `createdAt` TIMESTAMP NOT NULL
, `updatedAt` TIMESTAMP NOT NULL
, PRIMARY KEY (id )
);