Skip to content

Instantly share code, notes, and snippets.

Last active November 26, 2022 13:19
Show Gist options
  • Save laurenfazah/e0b0033cdc40a313d4710cc04e654556 to your computer and use it in GitHub Desktop.
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).


// 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_test>;



$ knex init
Created ./knexfile.js

Replace contents of ./knexfile.js with:

module.exports = {
  development: {
    client: 'pg',
    migrations: {
      directory: './db/migrations'
    seeds: {
      directory: './db/seeds/dev'
    useNullAsDefault: true

  test: {
    client: 'pg',
    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


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>(
    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.


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!

Copy link

thank you!

Copy link

Exactly what I needed, thanks.

Copy link

ghost commented Feb 4, 2020

If by chance in the connection section I want to pass a file that exports all the data. How could I do it?

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!

Copy link

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

thank you very much

Copy link

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

Copy link

Thank you so much. I wish you successful deploying)

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