Create DB - Express with Knex

https://frontend.turing.io/lessons/module-4/knex-postgres.html

Setup

Install knex globally (will not have to do more than once)

npm i -g knex

Install knex, express, postgres, and save

npm i knex express pg --save

Open a new terminal tab -- Open postgres

psql

Create database

CREATE DATABASE <lowercaseName>;

Back to the terminal --

knex init

Created ./knexfile.js Delete all but developement environment Update to be...

module.exports = {
  development: {
    client: 'pg',
    connection: 'postgres://localhost/<databaseName>',
    useNullAsDefault: true
  }
};

Migrations

Now time to set up for migrations for version control Back in ./knexfile.js... update with migrations

module.exports = {
  development: {
    client: 'pg',
    connection: 'postgres://localhost/<databaseName>',
    migrations: {
      directory: './db/migrations'
    },
    useNullAsDefault: true
  }
};

Create a new migration In the terminal

knex migrate:make initial

This created a migrations directory at the root of the project and added a time stamped file with the name of the migration at the end.

The first one in your repo is Initial Commit, the rest of them should describe exactly what’s happening.

Edit the new migration file with the tables you would like to create, like below...

exports.up = function(knex) {
  return Promise.all([
    knex.schema.createTable('papers', function(table) {
      table.increments('id').primary();
      table.string('title');
      table.string('author');

      table.timestamps(true, true);
    }),

    knex.schema.createTable('footnotes', function(table) {
      table.increments('id').primary();
      table.string('note');
      table.integer('paper_id').unsigned()
      table.foreign('paper_id')
        .references('papers.id');

      table.timestamps(true, true);
    })
  ])
};


exports.down = function(knex) {
  return Promise.all([
    knex.schema.dropTable('footnotes'),
    knex.schema.dropTable('papers')
  ]);
};

Time to run the migration In the terminal...

knex migrate:latest

For Editing Migrations... see lesson https://frontend.turing.io/lessons/module-4/knex-postgres.html

Now you can test in Postico or terminal... Connect to DB////////////////////////////////

Seeds

Back in ./knexfile.js... update with seeds

module.exports = {
  development: {
    client: 'pg',
    connection: 'postgres://localhost/publications',
    migrations: {
      directory: './db/migrations'
    },
    seeds: {
      directory: './db/seeds/dev'
    },
    useNullAsDefault: true
  }
};

Now time to make a seed In the terminal

knex seed:make papers

Again, this creates a DEFAULT file for you that we’ll want to configure with our own tables and data... Rewrite default to be what we need...

exports.seed = function(knex) {
  // We must return a Promise from within our seed function
  // Without this initial `return` statement, the seed execution
  // will end before the asynchronous tasks have completed
  return knex('footnotes').del() // delete all footnotes first
    .then(() => knex('papers').del()) // delete all papers

    // Now that we have a clean slate, we can re-insert our paper data
    .then(() => {
      return Promise.all([
        
        // Insert a single paper, return the paper ID, insert 2 footnotes
        knex('papers').insert({
          title: 'Fooo', author: 'Bob', publisher: 'Minnesota'
        }, 'id')
        .then(paperID => {
          return knex('footnotes').insert([
            { note: 'Lorem', paper_id: paperID[0] },
            { note: 'Dolor', paper_id: paperID[0] }
          ])
        })
        .then(() => console.log('Seeding complete!'))
        .catch(error => console.log(`Error seeding data: ${error}`))
      ]) // end return Promise.all
    })
    .catch(error => console.log(`Error seeding data: ${error}`));
};

Time to run the seed Back in the terminal...

knex seed:run

For Seeding Large Datasets... see lesson https://frontend.turing.io/lessons/module-4/knex-postgres.html

Fetching from the DB

Set up a simple server.js file (https://frontend.turing.io/lessons/module-4/intro-to-express.html)

Add some configuration to work with the knex database

const express = require('express');
const app = express();

const environment = process.env.NODE_ENV || 'development';
const configuration = require('./knexfile')[environment];
const database = require('knex')(configuration);

console.log('configuration', configuration)
console.log('database', database)

app.set('port', process.env.PORT || 3000);

app.listen(app.get('port'), () => {
  console.log(`App is running on ${app.get('port')}`)
})

Write requests to retrieve or post data

GET

app.get('/api/v1/papers', (request, response) => {
  database('papers').select()
    .then((papers) => {
      response.status(200).json(papers);
    })
    .catch((error) => {
      response.status(500).json({ error });
    });
});

POST

app.post('/api/v1/papers', (request, response) => {
  const paper = request.body;

  for (let requiredParameter of ['title', 'author']) {
    if (!paper[requiredParameter]) {
      return response
        .status(422)
        .send({ error: `Expected format: { title: <String>, author: <String> }. You're missing a "${requiredParameter}" property.` });
    }
  }

  database('papers').insert(paper, 'id')
    .then(paper => {
      response.status(201).json({ id: paper[0] })
    })
    .catch(error => {
      response.status(500).json({ error });
    });
});

SQL Terminal Commands

SELECT * FROM <tablename>; - select entire table
SELECT <columnname> FROM <tablename>; - select one column from table
SELECT <first_columnname>, <second_columnname> FROM <tablename>; - select multiple columns from table
SELECT * FROM <tablename> WHERE column='value'; - select column that matches specific criteria
SELECT <first_columnname>, <second_columnname> FROM <tablename> WHERE column='value'; - select multiple columns that matches specific criteria
INSERT INTO <tablename> (title) VALUES ('Baz'); - insert
DELETE FROM <tablename> WHERE column='value'; - delete
SELECT COUNT(*) FROM <tablename>; - count entire table
SELECT <MATHFUNC>(<columnname>) FROM <tablename>; - find average of items in column from table
SELECT <columnname> FROM <tablename> WHERE <columnname> < condition ; - filter data by a condition
SELECT column_name FROM table_name WHERE column_name LIKE '%condition%' ; - filter with partial text match

Postgres Commands

\l - list all databases
CREATE DATABASE <databasename>; - create a new database
\c <databasename> - connect to a particular database
\dt - after you’re connected to a database, show the tables it contains
\d <tablename> - list all columns in a table
\q - quit

Sample Workflow Using Raw SQL

Open PostgreSQL client

psql

List all databases

\l

Create a new database

CREATE DATABASE name;

Connect to a database

\c name

Create table

CREATE TABLE tablename (
  id serial PRIMARY KEY,
  name varchar (50) NOT NULL,
  program varchar (50) NOT NULL
);

View records in a table

SELECT * FROM tablename;

Insert record

INSERT INTO tablename (name, program) VALUES ('Robbie', 'FE');

View again and only certain columns

SELECT name FROM tablename;

Delete record

DELETE FROM tablename WHERE name='Robbie';

Quit the PostgreSQL client

\q

qfarenwald/mod-4-notes.md