How to utilize Heroku's 'postdeploy' script to create table within a provisioned Heroku PostgreSQL database?

Question

I am working on a web app that is set up on Heroku. I'd like others to be able to use it themselves, so I'm trying to create a 'Deploy to Heroku' button to include in my repository's README.

Following along with Heroku's documentation^1,2, I created an app.json file which outlines everything Heroku needs to provision the app. Here is what my app.json file looks like:

{
  "name": "[title]",
  "author": "[author]",
  "description": "[desc]",
  "repository": "[https://github.com/[user]/[repo]",
  "logo": "[url]",
  "addons": [
    "heroku-postgresql:hobby-dev",
    "wwwhisper:solo"
  ],
  "scripts": {
    "postdeploy": "node server/models/database.js"
  },
  "env": {
    "TZ": "America/Los_Angeles"
  }
}

As you can see, the postdeploy script is supposed to call the database.js script, which looks like so:

const pg = require('pg');
const connectionString = process.env.DATABASE_URL;

const client = new pg.Client(connectionString);

client.connect();
client.query('CREATE TABLE IF NOT EXISTS table_name (id uuid, url VARCHAR(2000), \
  title TEXT, description TEXT, been_visited BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT NOW())', (err, res) => {
    if (err) {
      client.end();
      return console.error('error with PostgreSQL database', err);
    }
});

client.end();

I know the query works as I tested it locally, but when I test the button with the above app.json, I still get the error error: relation "tanabata_tree" does not exist - meaning the table was never created.

Where / what am I doing wrong?

---

¹: https://devcenter.heroku.com/articles/heroku-button https://devcenter.heroku.com/articles/heroku-button

²: https://devcenter.heroku.com/articles/app-json-schema

Answer 1

You have your client.end(); outside your database query callback function. Your database connection is ended before your create table query is finished because JavaScript is Asynchronous.

The solution is to put your client.end(); inside your callback function so it gets called after your database query finishes.

Here is the working code:

const connectionString = process.env.DATABASE_URL;

const client = new pg.Client(connectionString);

client.connect();
client.query('CREATE TABLE IF NOT EXISTS table_name (id uuid, url VARCHAR(2000), \
  title TEXT, description TEXT, been_visited BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT NOW())', (err, res) => {
    if (err) {
      return console.error('error with PostgreSQL database', err);
    }
    client.end();
});