Javascript with Sequelize

Sequelize is a popular Javascript ORM that supports Postgres. It is a fully-featured ORM that supports relations, joins, eager and lazy loading, and many other features that Postgres does well.

In this tutorial we provide quickstart tips to get you going quickly as well as a full walkthrough.

Quickstart

The most common question we get with Node is related to the self-signed SSL certificates used by Crunchy Bridge. The typical error looks like this:

"name":"PostgresError","message":"no pg_hba.conf entry for host \"127.0.0.1\",
user \"application\", database \"my-db\", no encryption"
Copy

You have two options to work around this error:

Option 1: use a certificate

Crunchy Bridge provides a self-signed certificate you can use to connect securely to your clusters. These certificates are provided at the Team-level. You can download the certificate and add it to your repo, then configure your application using a block like the following:

const sequelize = new Sequelize(process.env.DATABASE_URL, {
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: true,
      ca: fs.readFileSync('crunchy-team.pem').toString(),
    }
  }
});
Copy

Option 2: disable certificate verification

Another option is to disable certificate verification, but we do not recommend doing this outside of development environments.

Disabling certificate verification is slightly messy in node-postgres. You'll want to set rejectUnauthorized: false and not add any SSL arguments to the DATABASE_URL. Here's an example:

const sequelize = new Sequelize(process.env.DATABASE_URL, {
  dialectOptions: {
    ssl: {
      require: true, 
      rejectUnauthorized: false
    }
  }
});
Copy

Full Sequelize walkthrough

Create your Crunchy Bridge Postgres cluster

If you haven't already created a Crunchy Bridge cluster, you can do so by clicking “Create Cluster” in the Dashboard. If you just provisioned your cluster, give it a few minutes to show a “ready” status on the Dashboard.

Next, click on the “Connections” tab to find the cluster's connection string.

A connection string has this general format:

postgres://user:password@host:port/database
Copy

We'll use this value to set the DATABASE_URL later. Note that when you're connecting with Sequelize, do not append any settings to the connection string when you're configuring the DATABASE_URL. Doing so will change any options passed in by the code.

Preparation

For this tutorial, you'll need to create a project directory and run the commands for the walkthrough from within that directory. You'll be creating a connection to your cluster using Sequelize, setting up a model, and inserting a small amount of sample data.

Installing sequelize and pg packages

First, you'll need to install the sequelize and pg packages by running the following command:

npm install sequelize pg
Copy

Connecting to Postgres

Next, create a file called db-initialize.js and add the following example code to it:

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize(process.env.DATABASE_URL, {
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: false
    }
  }
});

const Superhero = sequelize.define('superhero', {
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  superPowers: {
    type: DataTypes.ARRAY(DataTypes.STRING),
    allowNull: false
  }
});

async function syncSequelize() {
  await sequelize.sync();
}

syncSequelize();
Copy

Now you can run the following command in your terminal, replacing <DATABASE_URL> with the connection string you looked up earlier:

DATABASE_URL = <DATABASE_URL> node db-initialize.js
Copy

This will set your DATABASE_URL environment variable to your cluster's connection string, which allows the application code to pick up the cluster's connection string with process.env.DATABASE_URL.

Note: the command above will also synchronize the Superhero model as defined in db-initialize.js to the database via a migration. You should be aware that Sequelize migrations can alter data structures as well. Although it is outside the scope of this walkthrough, it's important to become familiar with Sequelize migrations before running them on production data as some commands can be destructive.

Inserting data

The following code will insert a small amount of test data into your database using Sequelize:

let user = await Superhero.create({
  name: "Superman",
  superPowers: ["flight", "strength", "vision"]
})
Copy

Congratulations! You've successfully connected to your cluster using Sequelize, set up a small data model, and inserted some test data.

Common errors

One common error, shown below, happens when DATABASE_URL has not been configured properly. For example, it might have been left unset and therefore is empty:

throw new Error("Dialect needs to be explicitly supplied as of v4.0.0");
Copy

If you get this error, make sure you've found the correct connection string for your cluster, and set the DATABASE_URL for your environment to that value, as shown above.