Skip to content

Instantly share code, notes, and snippets.

@laurenfazah
Last active November 26, 2022 13:19
Star You must be signed in to star a gist
Save laurenfazah/e0b0033cdc40a313d4710cc04e654556 to your computer and use it in GitHub Desktop.
Cheat Sheet: Setting up Express with Postgres via Knex

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!

@Matt-GitHub
Copy link

Hey Lauren, just wanted to say thank you for posting this. Helped me solve my issue and create my first database with Postgres!

@akiosTerr
Copy link

I'm finally able to deploy my API because of this

thank you very much

@githubmave
Copy link

Thanks! That is exactly what I need! Connecting to postgresql would be a great addition to my project!

@rocket111185
Copy link

Thank you so much. I wish you successful deploying)

@Wijayaac
Copy link

Wijayaac commented Jun 7, 2021

Hi Lauren thanks for your gist , this is what i exactly need !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment