Cheat Sheet: Setting up Express with Postgres via Knex

express_postgress_knex.md

Express & Postgres via Knex

Note: <example> is meant to denote text replaced by you (including brackets).

Setup

// global dependencies
npm install -g knex

// project's dependencies
npm install knex pg --save

// project's dev dependencies
npm nodemon --save-dev

DB Setup

$ psql

CREATE DATABASE <example>;
CREATE DATABASE <example_test>;

Knex

Config

$ knex init
Created ./knexfile.js

Replace contents of ./knexfile.js with:

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

  test: {
    client: 'pg',
    connection:'postgres://localhost/<examples_test>',
    migrations: {
      directory: './db/migrations'
    },
    seeds: {
      directory: './db/seeds/test'
    },
    useNullAsDefault: true
  },

  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './db/migrations'
    },
    seeds: {
      directory: './db/seeds/production'
    },
    useNullAsDefault: true
  }
}

Migrations

Create Table

$ knex migrate:make create-<example>-table

up and down Functions

Populate those functions in your migrations/<example>.js file. For example:

exports.up = function(knex, Promise) {
  let createQuery = `CREATE TABLE <examples>(
    id SERIAL PRIMARY KEY NOT NULL,
    message TEXT,
    created_at TIMESTAMP
  )`
  return knex.raw(createQuery)
}

exports.down = function(knex, Promise) {
  let dropQuery = `DROP TABLE <examples>`
  return knex.raw(dropQuery)
}

Run Migrations

$ knex migrate:latest

Add --env=test to migrate your test database.

Seeds

Create Seeds

$ knex seed:make <examples>

Replace the function in seeds/dev/<examples>.js with your own seeds.d

Seed DB

$ knex seed:run

Requiring Necessary Modules into Express

The following lines may need their paths adjusted depending on where in the project they're used.

const environment = process.env.NODE_ENV || 'development';    // if something else isn't setting ENV, use development
const configuration = require('../knexfile')[environment];    // require environment's settings from knexfile
const database = require('knex')(configuration);              // connect to DB via knex using env's settings

You're ready to get devving!