cklanac/challenge-07-single-table.md

## challenge-07-single-table.md

      
    Raw
  

              challenge-07-single-table.md
            
          
    Noteful Challenge - Postgres/Knex with Single-Table

For this challenge you will clone the Noteful V2 starter and update the app to use knex. The starter is configured to connect to the noteful-app database you created in the previous challenge.
Create a database and table

Before getting started you should have completed the previous challenge where you created a noteful-app database and populated the notes table with sample data.
Requirements


Clone starter repo
Install and knex and pg
Create queries in scratch file
Connect the Notes router file to the database
Update the Notes router queries

Get All Notes (with a searchTerm)
Get Note By Id
Update Note By Id
Create a Note
Delete Note By Id


Verify the changes using Postman, and query the DB using psql or your favorite GUI and test the front-end client

Set up Project repo

To get started, clone the starter repo.

Find the noteful-app-v2 pinned to your Cohort's GitHub Organziation
Clone the noteful-app-v2 repo to your local development environment
Rename the origin to upsteam using git remote rename origin upstream
On GitHub create a new repo in your Cohort org on Github.

Name the new repo "[YOUR-NAME]-noteful-v2".
Do not initialize the repository with a README.


On the next page find the section titled …or push an existing repository from the command line and run the two commands
Verify your remotes are configured correctly. Enter git remote -v, you should see two remotes: one called origin pointing to your repo on GitHub and one named upstream pointing Thinkful's repo.

NPM Install

As usual, one of the first things you do after cloning a repo is run npm install. Notice the starter repo includes references to express and morgan as well as knex and pg.
Connect to the database

The project starter repo is preconfigured two files, knexfile.js and knex.js,  which load the knex package and configure it to connect to your database.
The knexfile.js is a knex specific configuration file, you can learn more here. Currently, it contains two environments: "development" and "production". Each environment property has a connection object appropriate to the environment.
The knex.js file loads knexfile.js and connects to the database using the appropriate configuration object for the environment. It then exports the connection.
Create CRUD Queries

The overall goal for this challenge is to update Noteful app endpoints and connect them to Postgres using Knex. But this involves many updating several moving parts. It is a good practice, especially when learning a new library, to simplify the problem. So you will first create the queries in a clean, isolated environment and then, once they are working, transfer them to the Express router. We'll help you get started.
Create a scratch file /scratch/queries.js and add following code, then run it using nodemon.
const knex = require('../knex');

let searchTerm = 'gaga';
knex
  .select('notes.id', 'title', 'content')
  .from('notes')
  .modify(function (queryBuilder) {
    if (searchTerm) {
      queryBuilder.where('title', 'like', `%${searchTerm}%`);
    }
  })
  .orderBy('notes.id')
  .then(results => {
    console.log(JSON.stringify(results, null, 2));
  })
  .catch(err => {
    console.error(err);
  });
The require('../knex') statement loads the knex.js file which loads the knexfile.js and returns a connection to the database which is assigned to knex.
The .select() is similar to the exercises in the curriculum, with one exception, the .modify(). The Modify method allows us to conditionally add a .where() clause depending on the state of searchterm. If searchTerm exists then find notes where the title is LIKE the searchTerm. Learn more about LIKE here
Your Turn!
We've provided the query which will be used in the GET /notes endpoint. Your challenge is to create the other 4 queries:

(done) Get All Notes accepts a searchTerm and finds notes with titles which contain the term. It returns an array of objects.
Get Note By Id accepts an ID. It returns the note as an object not an array
Update Note By Id accepts an ID and an object with the desired updates. It returns the updated note as an object
Create a Note accepts an object with the note properties and inserts it in the DB. It returns the new note (including the new id) as an object.
Delete Note By Id accepts an ID and deletes the note from the DB.

When you are confident that you have all the queries ready then move on to the next section.
Update the routers

Great! Now that you created queries for each CRUD operation, it is time to integrate them into the application.
To get started, you need to remove simDB references and add knex and PostgreSQL. At the top of your routes/notes.js file, comment out the simDB and insert const knex = require('../knex');
// const data = require('../db/notes');
// const simDB = require('../db/simDB');
// const notes = simDB.initialize(data);

const knex = require('../knex');
Next, integrate the queries from the scratch file (above) into the endpoints. The queries in the scratch file use console.log(), but the endpoints should to return JSON (e.g. res.json(results), and instead of writing errors to console.error() is should call next(err).
For example:
router.get('/', (req, res, next) => {
  const searchTerm = req.query.searchTerm;

  knex.select('id', 'title', 'content')
    .from('notes')
    .modify(function (queryBuilder) {
      if (searchTerm) {
        queryBuilder.where('title', 'like', `%${searchTerm}%`);
      }
    })
    .orderBy('notes.id')
    .then(results => {
      res.json(results);
    })
    .catch(err => {
      next(err);
    });
});
We've provided you with a solution for the first endpoint, your challenge is to create the rest of them.

(done) Get All Notes accepts a searchTerm and finds notes with titles which contain the term. It responds with an array of objects and a 200 status.
Get Note By Id accepts an ID. It responds with the note as an object, not an array  with status 200. If the ID is not found then it responds with a 404 status.
Update Note By Id accepts an ID and an object with the desired updates. It responds with the updated note as an object and a 200 status
Create a Note accepts an object with the note properties and inserts it in the DB. It responds with the new note (including the new id) as an object along with a location header and a 201 status.
Delete Note By Id accepts an ID and deletes the note from the DB and responds with a 204 status.

As you update the endpoints, check them using Postman and cross-check the results in the database using psql or your favorite GUI.
Finally, check the front-end Noteful App client
Good luck!