Clone database between apps on Heroku

When doing development on Heroku using review apps, pipelines and all the other goodness it’s sometimes required to test against a clone of a production database. Doing that is pretty easy on Heroku when you know how even without moving the data down to your own laptop and then backup to Heroku. This is done by creating a backup, generating a public, secret, URL for the backup image and then restoring from that URL.

Below are the steps in the Heroku CLI to clone a database from an app called “my-production-app” to an app called “my-development-app”. Step no. 3 is to create a new Heroku Postgres addon attached to “my-development-app” that fits the same amount of data as the one from “my-production-app”.

$ heroku pg:backups:capture --app my-production-app
Starting backup of postgresql-aerodynamic-55964… done

Use Ctrl-C at any time to stop monitoring progress; the backup will continue running.
Use heroku pg:backups:info to check progress.
Stop a running backup with heroku pg:backups:cancel.

Backing up DATABASE to b003… done

$ heroku pg:backups:url --app my-production-app b003…

$ heroku addons:create heroku-postgresql:hobby-basic --app my-development-app
Creating heroku-postgresql:hobby-basic on ⬢ my-development-app… $9/month
Database has been created and is available
! This database is empty. If upgrading, you can transfer
! data from another database with pg:copy
Created postgresql-octagonal-31270 as HEROKU_POSTGRESQL_YELLOW_URL
Use heroku addons:docs heroku-postgresql to view documentation

$ heroku pg:backups:restore --app my-development-app… HEROKU_POSTGRESQL_YELLOW

Once restored you may promote the database you restored into using the “heroku pg:promote” command and / or delete the backup you created.

$ heroku pg:backup:delete --app my-production-app b003
▸ WARNING: Destructive Action
▸ This command will affect the app my-production-app
▸ To proceed, type my-production-app or re-run this command with --confirm
▸ my-production-app

Using Heroku Postgres from local development setup (take 2)

Previously wrote about this (Using Heroku Postgres from local development setup) but starting with Postgres 8 implicit disabling of certificate verification is deprecated and will be removed. To avoid the deprecation message add “rejectUnauthorized=false” to the URL as well so a full URL in your .env file (or similar will be):


Using Heroku Postgres from local development setup

When developing locally against a Heroku Postgres database (hosted on Heroku) you quickly realize you cannot connect due to a self-signed certificate (or rather Heroku signed certificate). The solution is to ensure that you use the non-validating SSL factory for the connection pool. This is easily done by appending “ssl=true” and “sslfactory=org.postgresql.ssl.NonValidatingFactory” to the database URL i.e. something like the following with the addition in bold:


kafka-node with Heroku Kafka

The other day I was building a demo for a customer around Heroku and Heroku Kafka. My language of choice these days is node.js so I needed a Kafka library for node.js and I settled on kafka-node. Now I needed to figure how to use the library using the environment variables provided by Heroku to access my Kafka cluster.

After having bound my Kafka add-on to the app I had 5 environment variables added to my app. The variables are well explained in the Heroku DevCenter but how to use them with kafka-node had me spend some time on it. The root cause seemed to be that the name in the certificate presented by the Kafka brokers did not match the name in the certificate provided to me by Heroku. Since the library uses the node.js TLS module under the covers the solution was to implement some of the verification login myself using the checkServerIdentity method. In the method I verify the cryptographic fingerprint of the root issuer certificate as provided by Heroku (KAFKA_TRUSTED_CERT) with that of the issuing certificate of the Kafka broker. I also had to remove the “kafka+ssl://” part from the Kafka broker URL’s. I do that using regular expressions.

Below is the code. YMMW.

const kafka = require("kafka-node");
const x509 = require('x509');
const Client = kafka.KafkaClient;

const kafkaHosts = process.env.KAFKA_URL.replace(/kafka\+ssl:\/\//gi, "");
const kafkaCert = x509.parseCert(process.env.KAFKA_TRUSTED_CERT);

const options = {
  "key": process.env.KAFKA_CLIENT_CERT_KEY,
  "cert": process.env.KAFKA_CLIENT_CERT,
  "ca": [process.env.KAFKA_TRUSTED_CERT],
  "checkServerIdentity": (host, cert) => {
    if (kafkaCert.fingerPrint === cert.issuerCertificate.fingerprint) return undefined;
    return Error('Not authentic')

module.exports = {
    "client": () => {
        return new Client({
            "kafkaHost": kafkaHosts,
            "sslOptions": options
    "topic": `${process.env.KAFKA_PREFIX}safe-habour`

Yet another reason for using Platform as a Service (PaaS)

I’m using a Platform as a Service (PaaS) for all my application development — because why wouldn’t I?! Heroku is the platform of choice with the full disclaimer that Heroku is a Salesforce company and I work for Salesforce.

However the driving reason of my usage of Heroku is that being a PaaS Heroku provides me with a high level of abstraction of the underlying compute infrastructure. On Heroku I just worry about the app and not anything else. No load balancing. No compute instances. No gluing and regluing stuff together. Just the app.

As part of the applications I have on Heroku I have a wide variety of databases including Heroku Postgres (a fully managed and elastic Postgres instance). A nice aspect of this is that for me Postgres is just another data store but I do not have to worry about the operations of it. This is great as I’m much better at writing apps than managing an always-on, fully load balanced and highly available database cluster.

Yesterday I received a notice about upcoming maintenance and one section in the email made me take notice so I thought I would share:

Your database must undergo maintenance.

At that time, we will create a hidden follower, wait for it to catch up, push the hidden follower’s creds to your app, unfollow it from your former leader and repoint any followers you may have to properly follow your new leader.

Let’s break that out really quick… When they — Heroku — do the maintenance for me and I won’t have to lift a finger they will:

  • Create a hidden follower (database)
  • Wait for it to catch up (to allow uninterrupted operations)
  • Push the hidden follower’s creds to your app (so the app uses the new follower database)
  • Unfollow it from your former leader (to ensure smooth operations)
  • Repoint any followers you may have to properly follow your new leader (again to ensure smooth operations)

All this without me doing anything. Again I just worry about my app that will continue running without any change by the way. How cool is that?! I just worry about the app.

Just another reason for using a PaaS…

Simultaneous INSERTs with Heroku Connect in the same transaction

So had some fun working with Heroku Connect the other day writing some data from Heroku to Salesforce using Postgres. On Heroku that is easily done using Heroku Connect which is a turn-key solution we have for that. Easy to use and easy to setup. The way it works is that you configure a connection from Heroku to Salesforce, map it to a Postgres database on Heroku and selects the objects and fields from Salesforce to synchronize and Heroku Connect does the rest. Powerful stuff!

So what did I do and what did I learn…

In Salesforce I have two custom objects – a parent custom object (Parent__c) and a child custom object (Child__c) with a master-detail relation to the parent (Parent__r / Parent__c). Both the Parent and Child custom objects have an external ID (External_ID__c) to make writing back from Heroku easy.

Once the objects are mapped using Heroku Connect I get 2 new tables in the “salesforce”-schema in Postgres (“salesforce.parent__c” and “salesforce.child__c”) with the selected fields.

What I wanted to do was – in a transaction – write a new parent record and the associated child records. I’m using node.js but since I’m using raw SQL that shouldn’t be too different to any other language. What I did do however is use promises which is important for this post.

Below is some code to illustrate what I did. Basically the code does the following:

  1. Starts a transaction (BEGIN)
  2. Inserts the parent record (INSERT INTO salesforce.parent__c…)
  3. Creates a promise to insert the 3 child records (INSERT INTO salesforce.child__c…) and resolves them all at once (i.e. the inserts will happen simultaneously)
  4. Commits the transaction (COMMIT) if all is good or rolls it back (ROLLBACK)
// data to work with
const objData = {
   "id": "ffe16a92-0f22-4a16-947a-461d307b4905", 
   "text": "Some Parent Text...", 
   "children": [
      {"id": "ffe16a92-0f22-4a16-947a-461d307b5906", "text": "Child1"},
      {"id": "ffe16a92-0f22-4a16-947a-461d307c4907", "text": "Child2"},
      {"id": "ffe16a92-0f22-4a16-947a-461d307b3904", "text": "Child3"}

// start a new tx
pool.query("BEGIN").then(rs => {
   // insert parent record
   return pool.query(`INSERT INTO salesforce.parent__c 
      (External_ID__c, Text__c) 
      ('${}', '${objData.text}');`);
}).then(rs => {
   // map each child to a promise to insert child record
   const promises = => {
      return pool.query(`INSERT INTO salesforce.Child__c 
         (parent__r__external_id__c, "External_ID__c", "Text__c") 
         ('${}', '${}', '${child.text}')`);
   return Promise.all(promises);
}).then(rs => {
   // commit tx
   console.log("Commiting tx");
   return pool.query("COMMIT");
}).catch(err => {
   // roll tx back
   console.log(`Rolling back tx: ${err.message}`);
   return pool.query("ROLLBACK");

The issue was that I kept seeing 2 errors in the log when the code ran as shown below. Funny thing is that I didn’t see any information about the transaction being rolled back.

2019-03-16T14:32:20-07:00 event="record 8 ERROR ↑SALESFORCE [SOAP] Foreign key external ID: ffe16a92-0f22-4a16-947a-461d307b4905 not found for field External_ID__c in entity Parent__c" addon_id=ba3d4995-092d-4095-aa5e-7ac41981bd93 object_type=sync object_id=ba3d4995-092d-4095-aa5e-7ac41981bd93 state=POLLING_DB_CHANGES level=debug  
22019-03-16T14:32:20-07:00 event="record 9 ERROR ↑SALESFORCE [SOAP] Foreign key external ID: ffe16a92-0f22-4a16-947a-461d307b4905 not found for field External_ID__c in entity Parent__c" addon_id=ba3d4995-092d-4095-aa5e-7ac41981bd93 object_type=sync object_id=ba3d4995-092d-4095-aa5e-7ac41981bd93 state=POLLING_DB_CHANGES level=debug  

Okay so clearly the insert fails because the foreign key of the child records cannot be resolved i.e. the ID pointing from salesforce.child__c to salesforce.parent__c. But why only 2 errors and not 3 errors? (I was attempting to insert 3 child records) I added an intermediate COMMIT before inserting the child records and started a new transaction and then all was well. So it had to do with the transaction… In Postgres the solution would be to defer the referential constraint check but I couldn’t do it. Why? Because Heroku Connect doesn’t use constraints as can be seen if inspecting the child table (psql: “\d salesforce.child__c”) but seems to do the referential integrity check using a trigger on the tables created by Heroku Connect. I’m guessing this is part of the magic that it Heroku Connect.

I played a bit more around with the code and finally solved the issue by doing the inserts in turn by resolving the promises sequentially. So instead of using Promise.all (that resolves the promises simultaneously) I resolved each promise in turn and then it worked.

My understanding of this is must be as follows. The triggers created by Heroku Connect that handles the underlying integration to Salesforce uses some log-tables (as described in the documentation) but the triggers must also employ some locking of the salesforce.parent__c table and hence the other inserts to salesforce.child__c running at the same time fails. Also this doesn’t cause my transaction to roll back which from my point of view is unfortunate as I really want the inserts to be atomic hence the use of a transaction. In this case there was a solution in doing the inserts sequentially. I’ll follow up internally in Salesforce and see if I can provide a better answer that the above.

Salesforce Canvas Apps

A Salesforce Canvas app is an often overlooked easy way to integrate existing apps into Salesforce. A Canvas app is inlined into the Salesforce user interface and it requires only a very small change to your app to have it play nice with Salesfore. In theory you could get away without any change but usually you’d like to know who the calling user is. What’s really great about a Canvas App is that this information is POST’ed to the application at invocation together with an OAuth access_token to allow authenticated callbacks to Salesforce. To implement this you need to:

  1. Support POST at a URL you specify
  2. Render the application from here or redirect the user after the POST has been received
  3. Receive and handle the signed request

The signed request is a base64 encoded blob in two parts separated by a period. It looks very much like a JSON Web Token (jwt). To verify it you compute keyed hash (hmac) using the sha-256 algorithm with the client secret of the Connected App from Salesforce being the secret. Doing this in node.js is done like so:

const ourSignature = Buffer.from(crypto.createHmac(algorithm, clientSecret).update(objPart).digest()).toString('base64')
The algorithm is “sha-256”, the client secret is a string and objPart of the object part of the signed request.
To make it even easier I’ve created a repo showing how it’s done in node.js in an Express app. The source including an example app is available at The repo also contains a test app (canvas-test-app) that is easily deployable to Heroku.

Fixing Heroku and SFDX CLI after upgrading to macOS High Sierra

macOS was just recently approved by Salesforce IT so I upgraded but only to find that my heroku CLI and SalesforceDX CLI tools had stopped working. I seem to remember that SalesforceDX is basically the same as the Heroku CLI so them failing together made sense. Running the tools only gave strange errors…

$ heroku
    stat /Users/mheisterberg/.local/share/heroku/client/bin/heroku: not a directory
    fork/exec /Users/mheisterberg/.local/share/heroku/client/bin/heroku: not a directory
$ sfdx
    stat /Users/mheisterberg/.local/share/sfdx/client/bin/sfdx: not a directory
panic: fork/exec /Users/mheisterberg/.local/share/sfdx/client/bin/sfdx: not a directory

goroutine 1 [running]:
panic(0x259de0, 0xc420017350)
	/usr/local/go/src/runtime/panic.go:500 +0x1a1
main.must(0x3c03c0, 0xc420017350)
	/home/ubuntu/.go_workspace/src/ +0x5c
main.getExitCode(0x3c03c0, 0xc420017350, 0xc420017350)
	/home/ubuntu/.go_workspace/src/ +0x12b
	/home/ubuntu/.go_workspace/src/ +0x14c

I started thinking it was an issue with node but it turned out to be caused by the command line tools for macOS having been uninstalled or needing to be updated. Below are the steps I used to install the command line tools, upgrade node (I’m using Homebrew) and fix the Heroku CLI and SalesforceDX CLI.

// update / install command line tools for macOS
$ xcode-select --install

// update homebrew
$ brew update

// verify my node version
$ node --version

// upgrade node
$ brew upgrade node
==> Upgrading 1 outdated package, with result:
node 9.3.0_1

// now to the real magic - fix heroku cli
$ rm -rf ~/.local/share/heroku/client
$ heroku update
heroku-cli: Updating to 6.14.43-73d5876... 12.7 MB/12.7 MB
heroku-cli: Updating CLI... already on latest version: 6.14.43-73d5876
heroku-cli: Updating plugins... done
$ heroku --version
heroku-cli/6.14.43-73d5876 (darwin-x64) node-v9.2.0

// rinse and repeat for SalesforceDX cli
$ rm -rf ~/.local/share/sfdx/client
$ sfdx update
sfdx-cli: Updating to 6.0.26-3d23012... 19.3 MB/19.3 MB
Installing dependencies for /Users/mheisterberg/Programming/repos/sfdx-l18n-plugin... done
sfdx-cli: Updating CLI... already on latest version: 6.0.26-3d23012
sfdx-cli: Updating plugins... done
$ sfdx --version
sfdx-cli/6.0.26-3d23012 (darwin-x64) node-v8.6.0

Websockets in an Express node.js app on Heroku

Last night I was having an issue with websockets and TLS in an Express.js node.js app. My websocket was working just fine when developing locally over plain HTTP but when I deployed the app to Heroku I received an error as that app runs over HTTPS but the websocket was still plain HTTP (using ws:// instead of wss://). Hmmm…. I started digging into websockets over TLS and how that would work without any luck. So I asked around but then it dawned on me and I answered my own question… Sometimes finding the answer is all about the question you ask 🙂

So the main realisation is that TLS connections are terminated by the Heroku router and forwarded to a web dyno hence there was no need to listen for TLS based websocket connections in my app. Also remembering how websocket connections are created is important. A websocket connection is a normal HTTP connection which is then upgraded to a websocket connection. So the real solution was to understand how a web dyno in node.js using Express could share it’s port with websockets using a HTTP server and that the same HTTP server would be used for both HTTP transport and websocket connections.

The solution was as follows:

const express = require('express')
const http = require('http')
const WebSocket = require('ws')

const port = process.env.PORT || 8080
const app = express()
const httpServer = http.createServer(app)
const wss = new WebSocket.Server({
    'server': httpServer

So in essence:

  1. Create the Express.js app (but do not set it up to listen in a port)
  2. Create HTTP server in node.js passing in the Express.js app
  3. Create websocket server agin using the HTTP server as the server
  4. Make the HTTP server listen on the port provided through environment variable