Search code examples
typescriptherokuheroku-postgressequelize-typescript

Unable to connect to Heroku's database using sequelize-typescript

My Express API using sequelize-typescript cannot connect to Heroku's PostgreSQL database.

The Heroku app was initialized by running the following commands:

heroku create <my-app-name>
heroku addons:create heroku-postgresql:hobby-dev
git push heroku master

This application runs without problems in my local environment.

I can confirm that the DATABASE_URL environment variable is correct on Heroku.

My package.json scripts also seem to be correct:

"scripts": {
  "build": "tsc",
  "dev": "node_modules/.bin/nodemon",
  "postinstall": "npm run build",
  "start": "node dist/server/server.js"
}

What I've tried to solve the problem from most important to least:

  1. Setting NPM_CONFIG_PRODUCTION=false by doing heroku config:set. I did this after reading through this SO thread, which pointed to this Heroku document. I think the idea is that this tells Heroku to skip the step where it prunes the devDependencies so Heroku can compile the TypeScript files into Javascript.

  2. Setting dialectOptions: { ssl: true } in my Sequelize connection when process.env.NODE_ENV === 'production'. I did this after reading a couple of SO documents on this topic.

  3. Providing the Sequelize connection with the username and password fields. I did this by doing:

// In the terminal
heroku config:set DB_USER=<database user from Heroku database credentials>
heroku config:set DB_PASSWORD=<database password from Heroku database credentials>

// In Sequelize connection
export const connection = new Sequelize({
    dialect: 'postgres',
    protocol: 'postgres',
    username: process.env.DB_USER || '',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DATABASE_URL || 'fitnius-db',
    logging: false,
    dialectOptions: { ssl },
    models: [__dirname + '/models', __dirname + '/models' + '/joins']
})

  1. Adding @types/pg as a dependency

  2. Deleting my Heroku app and instantiating a new one

  3. Removing "node_modules" from "exclude": [] in tsconfig.json

  4. Removing all environment variables from nodemon.json

  5. Creating a Procfile and adding web: node dist/server/server.js inside of it

  6. Moving, @types/express and @types/sequelize around between dependencies and devDependencies. I was desperate and read through this document from Sequelize.

    • Also, installing @types/node and @types/validator as dependencies

  7. Setting build script to "build": "tsc -p ." (Not exactly sure what the -p flag does... but I did it)

  8. Setting a relative path to the compiled server file in my start script:

    • "start": "node ./dist/server/server.js"

Question: How can I get Sequelize to establish a connection to Heroku's PostgreSQL database and get my application running?

Below is relevant code pertaining to the issue.

Error log from heroku logs --tail

{ SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432
at connection.connect.err (/app/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:170:24)
at Connection.connectingErrorHandler (/app/node_modules/pg/lib/client.js:191:14)
at Connection.emit (events.js:198:13)
at Socket.reportStreamError (/app/node_modules/pg/lib/connection.js:72:10)
at Socket.emit (events.js:198:13)
at emitErrorNT (internal/streams/destroy.js:91:8)
at emitErrorAndCloseNT (internal/streams/destroy.js:59:3)
at process._tickCallback (internal/process/next_tick.js:63:19)
name: 'SequelizeConnectionRefusedError',
parent:
{ Error: connect ECONNREFUSED 127.0.0.1:5432
at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1106:14)
errno: 'ECONNREFUSED',
code: 'ECONNREFUSED',
syscall: 'connect',
address: '127.0.0.1',
port: 5432 },

at=error code=H10 desc="App crashed" method=GET path="/" 
host=fitnius-web-api.herokuapp.com 
request_id=c8c76ce3-8201-4733-99fb-bee4121b09d1 fwd="100.1.44.6" 
dyno= connect= service= status=503 bytes= protocol=https

Sequelize connection

import { Sequelize } from 'sequelize-typescript'

const ssl = process.env.NODE_ENV === 'production'

export const connection = new Sequelize({
    dialect: 'postgres',
    protocol: 'postgres',
    database: process.env.DATABASE_URL || 'fitnius-db',
    logging: false,
    dialectOptions: { ssl },
    models: [__dirname + '/models', __dirname + '/models' + '/joins']
})

Express server

import app from './app'
import { connection } from '../database/connection'

const PORT = process.env.PORT || 3000
connection
    .sync()
    .then(() => {
        app.listen(PORT, () => console.log(`Server is running on port ${PORT}`))
    })
    .catch(err => console.log(err))

tsconfig.json

{
  "compilerOptions": {
    "target": "es6",
    "module": "commonjs",
    "outDir": "dist",
    "strict": true,
    "declaration": true,
    "esModuleInterop": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "sourceMap": true,
    "lib": ["es6", "dom"]
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", ".vscode"]
}

package.json

{
  "name": "fitnius-server",
  "version": "1.0.0",
  "description": "",
  "main": "dist/server/server.js",
  "scripts": {
    "build": "tsc",
    "dev": "node_modules/.bin/nodemon",
    "postinstall": "npm run build",
    "start": "node dist/server/server.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "@types/express": "^4.17.0",
    "@types/graphql": "^14.2.3",
    "@types/pg": "^7.4.14",
    "@types/sequelize": "^4.28.4",
    "express": "^4.17.1",
    "express-graphql": "^0.9.0",
    "graphql": "^14.4.2",
    "pg": "^7.12.0",
    "reflect-metadata": "^0.1.13",
    "sequelize": "^5.12.3",
    "sequelize-typescript": "^1.0.0-beta.3"
  },
  "devDependencies": {
    "nodemon": "^1.19.1",
    "ts-node": "^8.3.0",
    "typescript": "^3.5.3"
  }
}

Directory tree

root
  dist (compiled by tsc)
  node_modules
  src
    database
      models
      connection.ts (Sequelize connection)
    server
      app.ts
      server.ts (Express server)
  nodemon.json
  tsconfig.json
  package.json
Solution

  • After digging through sequelize-typescript's npm package, I found out the database parameter does not refer to the entire database's url.

    I was able to solve the problem by parsing the following from Heroku's database url. I used this database url parser as my solution.

    interface ParsedDatabaseOptions {
    protocol: string | undefined
    host: string | undefined
    username: string | undefined
    password: string | undefined
    database: string | undefined
}

    After converting the parser into a rough TypeScript version, which can be found here, I then created a conditional by checking if NODE_ENV === 'production' and set my database options to either:

    import { Sequelize } from 'sequelize-typescript'
// parseDatabaseUrl is currently a wonky piece of code
import parseDatabaseUrl from '../utils/parse-database-url'

const optionsProduction = parseDatabaseUrl(process.env.DATABASE_URL)
const optionsDevelopment = { database: <your local database> }

// Create conditional Sequelize database options here
const sequelizeOptions = process.env.NODE_ENV === 'production'
    ? optionsProduction
    : optionsDevelopment

const connection = new Sequelize({
    dialect: 'postgres',
    logging: false,
    ...sequelizeOptions, // Spread options here
    models: ...
})