Connection Postgres to Express with Knex

connect-postgres-to-express-with-knex.txt

Install pg, knex, postgrator-cli
npm i pg knex
npm i postgrator-cli -D

Create Dev and Test DB for project
createdb -U <username> <project-name-dev>
createdb -U <username> <project-name-test>

Create Postgrator-cli config file
require('dotenv')

module.exports = {
  'migrationDirectory': 'migrations',
  'driver': 'pg',
  'host': process.env.MIGRATION_DB_HOST,
  'port': process.env.MIGRATION_DB_PORT,
  'database': process.env.MIGRATION_DB_NAME,
  'username': process.env.MIGRATION_DB_USER
};

Add migrate script to package.json
"migrate": "postgrator --config postgrator-config.js"

Create migration folder in root of dir
 -001.do.some-sql-action.sql
 -001.undo.some-sql-action.sql
- execute by running npm run migrate -- <num>
- to rollback, subtract 1 from the migration number
- running just npm run migrate will run all migrations

Add necessary ENV vars to .env file
  -MIGRATION_DB_HOST=localhost
  -MIGRATION_DB_PORT=5432
  -MIGRATION_DB_NAME=<project_name>
  -MIGRATION_DB_USER=<username> // i like using postgres here
  -TEST_<PROJECT_NAME>_DB_URL=postgres://<username>@<host>/<test-db-name>
  -DEV_<PROJECT_NAME>_DB_URL=postgres://<username>@<host>/<dev-db-name>

Run migrations
  For Dev DB
    -npm run migrate
  For Test DB
    MIGRATION_DB_NAME=<test-db-name> npm run migrate

Setup Knex Inside of server.js
const db = knex({
  client: 'pg',
  connection: <dev-db-url>
});

Make db a global var on app
app.set('db', db);

Inside of Route file - Get access to db in routes
const db = req.app.get('db');

Setup testing structure
In file that needs access to db

let db;
before('establish db', () => {
  db = knex({
    client: 'pg',
    connection: process.env.<test-db-url>
  });
});

Setup before and after hooks to seed and wipe data
  -before('wipe all table data', () => db('<table-name>').truncate());
  -after('close db connection', () => db.destroy());

  -beforeEach('make db available to request', () => app.set('db', db));
  -afterEach('wipe all data from table', () => db('<table-name>').truncate());