Node.js Express Knex JSON API

Technologies:

Postgres for database
knex.js for migrations, seeds and queries
express.js for our JSON routes
Mocha, Chai and SuperTest to test our routes

Prerequisites (Mac OS Commands)

Latest version of Node.js brew install node
Postgres brew install postgres brew services start postgres createdb
Express Generator npm install -g express-generator
Knex installed globally npm install -g knex

Server Check List

Create a server folder (example directory ~/dev/CRUD) mkdir server && cd server
- Generate Express App express
- Initialize Git Repo git init
  - You also need a .gitignore file gitignore node
  - In package.json edit:
```
"name": "crud-stickers",
"version: "1.0.0",
```
  - Commit to git git add . git commit -m "initial commit"
Create database createdb name-this-database
Initialize knex project (Remain in the server directory) npm install --save knex pg (This should also install a package-lock.json and also add knex and pg dependencies in your package.json file)
- Install knex and pg npm i knex pg
- Create knexfile.js (Sets up the configurations to connect knex to the database) knex init
  - In the knexfile.js delete everything except development. Should look like this:
```
  module.exports= {
           development: {
             client: 'pg',
             connection: 'postgres://localhost/named-database'
           },
         };
```

Create table migration knex migrate:make create-table-name

In the migrations folder:

exports.up = function)knex, Promise) {
  return knex.schema.createTable('table-name', (table) =>
    table.increments()
    table.text('title')
    table.text('description')
    table.float('rating')
    table.text('url')
  })
}

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('table-name')
}

Run the migration knex migrate:latest
Go into psql to make sure it worked psql named-database
- This will take you to the database. Now list it to see:
- \dt
- \d table-name

Seed table with sample data
- Best practice to use numbers to begin your seed names because they run in alphabetical order knex seed:make 01_seed_name
  - Idea behind seeding is that everytime you run seeds the database will go back to the same state
  - This created the seed folder and the seed file, in that file make the following edits:
```
exports.seed = function(knex, Promise) {
   return knex('sticker').del()
     .then(function () {
       return knex('sticker').insert(stickers)
     })
 }
```
- In your stickers.js file, you want to make sure that your data is an object that looks like the table you created. Should have module.exports = at the top.
- In your stickers file, you need to pull that array of objects in. When we run the seed, it will drop the rows and then insert the array of stickers. It should look like:
- const stickers = require('../stickers')
- Run seeds knex seed:run
- Check database for seeds
- select * from table_name
- NOTE: \x will do an expanded display if you run select again.
Convert Express Generator App to JSON API
- Remove view rendering
  - When an express app is generated by default, it comes with a "view" engine (jade / handlebars). Navigate into the app.js file and remove the view engine setup:
```
// view engine setup
app.set('views', path.join(__dirname, 'views'))
app.set('view engine', 'jade')
```
  - Delete "views" folder since it's not needed
- Remove routes folder
In app.js, remove these lines where the routes are used:
```
var index = require('./routes/index')
var users = require('./routes/users')
  
app.use('/', index)
app.use('./users', users)
```
- Remove static serve and public folder
  - In app.js, remove line 19 or the line that says: app.use(express.static(path.join(__dirname, 'public')))
  - This serves static files from the public folder
  - Delete the 'public' folder.
- Update error handler
  - At the bottom of app.js by default, it's trying to render the error page, instead we want to send back some json. Remove: `res.render('error')
  - Add:
```
res.json({
  message: err.message,
  error: req.app.get('env') === 'development' ? err : {}
})
```
  - Now you can remove the 2 lines above:
```
res.locals.message = err.message
res.locals.error = req.app.get('env') === 'development' ? err : {}
```
  - Now whenever the server has an error it will send it back as json
  - Runnpm install to make sure it has the latest dependencies.
  - If you are not using a view engine, uninstall it by npm uninstall jade. This removes it from package.json
  - Run npm start to start server
  - It should be working if you go to localhost:3000 in the browser and see the message not found object since there are no more routes.

Add api folder and create/mount router

Inside server folder, create a new folder named api
Inside of api folder, create a new file route-name.js

Inside of route-name.js file, create an express router:

 const express = require('express')
 
 const router = express.Router()
  
 // This does a simple route to make sure its working
 router.get('/', (req, res) => {
    res.json({
      message: 'working'
    })
  })
  
 module.exports = router

Mount the router at this url '/api/v1/route-name' so every route inside of this file begins with this url.

Inside app.js:

// Change all of the vars to const from var express down to var app
// Bring in router after const app = express()
const route = require('./api/route-name')

This is where we now mount the route. NOTE: It has to be exactly between:

app.use(logger('dev'))
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({ extedned: false})
app.use(cookieParser())
 
// PUT THE MOUNT HERE:
app.use('/api/v1/route-name', route-name)
 
app.use(function(req, res, next) {
  const err -new Error('Not Found')
  err.status = 404
  next(err)
})

Now when you make a request from the mount, goes into router file and runs request handler
Install nodemon as a development dependency npm install nodemon --save-dev
In package.json, add nodemon in the "scripts" object under "start": "dev": "nodemon"
Start up nodemon npm run dev
In the browser, test url: localhost:3000/api/v1/route-name, you should see starter message

Connect to database
- This will require any module in our app and make queries against the database
- Create database connection file
  - Create db folder in server folder
  - Create a file inside of db named knex.js
  - In knex.js, get the environment
    - This code, wherever it's running, will be connecting to a different database so when it connects on your machine, it will use the development connection. When you're on production, there will be a different way to connect. const environment = process.env.NODE_ENV || 'development'
  - Add the knex config:
```
const config = (require('../knexfile')
const environmentConfig = config[environment]
const knex = require('knex')
const connection = knex(environmentConfig)
module.exports = connection
```
- Create a queries file
  - In db folder, create a new file named queries.js
  - Inside queries.js, require connection to db:
```
const knex = require('./knex') // the connection

module.exports = {
  getAll(){
    // this is a function that will get all the seeds from the table
  }
}
```
List all records with GET /api/v1/route-name
Create query

Create route

Now we will create a get route to list all of our stickers in our database
Go into the api folder and go into stickers.js:

const express = require('express')
 
const router = express.Router()
 
// require our queries
const queries = require('../db/queries')
 
// This is the route where we want to list all of the stickers. The actual query to do that will be in another file so we need to bring that into here
 router.get('/', (req, res) => {
   queries.getAll().then(stickers => {
     res.json(stickers)
   })
 })
 
 module.exports = router

In our queries.js file, we need to write out the getAll function

const knex = require('./knex) //the connection!

module.exports = {
  getAll() {
    return knex('sticker') // this selects all the rows in the sticker table
  }
}

If we now enter localhost:3000/api/1/stickers We should get our data from our database and sending it back as json.
Recap: queries.js creates the getAll method, the router file calls that method, we take the data through the promise and send it back as json

Setup tests

Install mocha, chai and supertest as dev dependencies npm install --save-dev mocha chai supertest

Add a test database connection

In knex.js

module.exports= {
   development: {
     client: 'pg',
     connection: 'postgres://localhost/named-database'
   },
   development: {
     client: 'pg',
     connection: 'postgres://localhost/test- named-database'
   }
 };

When we run these tests, they will run against a different database

Add npm test script
- Create test directory and test file
- In server folder mkdir test cd test touch app.test.js
- In app.test.js:

describe('CRUD Stickers', () => {

})
```js
* In the package.json add `"test": "NODE_ENV=test mocha"` to the "scripts" object.
* If you think back to the db connection we created in knex.js, we're using the test object in the knexfile.js file instead of the development connection because we utilized NODE_ENV in the knex.js file.
* [ ] Drop/Create database
* Before mocha runs, however, we want to drop the database and create it from scratch
* So continue to add to this test value:
* `"test": "(dropdb --if-exists test-named-database && createdb test-named-database) && NODE_ENV=test mocha"`
* If we run `dropdb --help` we can use the option `--if-exists` because the first time you run it, it won't exist, so it won't try dropping something that doesn't exist.
 
* [ ] Create before
* In app.test.js:
```js
// bring in our database connection
const knex = require('')

describe('CRUD Stickers', (../db/knex) => {
  // this function will run before any of the test below it are defined.
  before(() => {
    // run migrations
    // run seeds
  })
})

Run migrations/seeds on test db
In app.test.js:

// bring in our database connection
const knex = require('')

describe('CRUD Stickers', (../db/knex) => {
  // this function will run before any of the test below it are defined.
  before(() => {
    // run migrations
    knex.migrate.latest()
      .then(() => {
       // run seeds
       return knex.seed.run()
      })
    
  })
})

Make sure the tests are working!
We need to tell mocha when the migration and seeds are done running using the built in done function we can call.

  // bring in our database connection
  const knex = require('')
  
  describe('CRUD Stickers', (../db/knex) => {
    // this function will run before any of the test below it are defined.
    // add built in mocha done function
    before((done) => {
      // run migrations
      knex.migrate.latest()
        .then(() => {
         // run seeds
         return knex.seed.run()
         //invoke done function
        }).then(() => done())
    })
    // have basic test to check if the before is creating the tables and seeding the databse
    
  })

ebectar/knex-checklist-with-notes.md

Node.js Express Knex JSON API

Technologies:

Prerequisites (Mac OS Commands)

Server Check List