Migration with Sequelize CLI to DigitalOcean Postgres Database Throwing SSL Error

Question

Connecting to my my DigitalOcean database with Sequelize works fine when I'm not migrating. For example, attempting to create a new table works just fine; the code below successfully connects and creates a new table.

sequelize = new Sequelize(config.use_env_variable, config);
sequelize.authenticate().then(console.log('success')).catch((error) => console.log(error));
sequelize.define('test-table', {
   test_id: {
       type: Sequelize.INTEGER,
         },
});

sequelize.sync();

I have a CA certificate .crt file I downloaded from DigitalOcean that I'm passing in with the Sequelize options. My config.js looks like

development: {
    use_env_variable: 'postgresql://[digitalocean_host_url]?sslmode=require',
    ssl: true,
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false,
        ca: fs.readFileSync(`${__dirname}/../.postgresql/root.crt`),
      },
    },
  },

However when I try to create tables using migrations with

npx sequelize-cli db:migrate

I receive the following output and error:

Parsed url postgresql://[digitalocean_host_url]?sslmode=require

ERROR: no pg_hba.conf entry for host [host], user [user], database [database], SSL off

Which is very strange, because SSL is working when I create a table using just Sequelize sync. I have a .sequelizerc file for the sequelize-cli configurations, which looks like this:

const path = require('path');
const env = process.env.NODE_ENV || 'development'
const config = require('./config/config')[env];

module.exports = {
  'config': path.resolve('config', 'config.js'),
  'url': config.use_env_variable,
  'options-path': path.resolve('config', 'sql-options.json')
}

inside my sql-options.json I have the following

{
    "use_env_variable": "postgresql://[digitalocean_host_url]?sslmode=require",
    "dialect":"postgres",
    "ssl": true,
    "dialectOptions": {
        "ssl": {
            "required": true,
            "rejectUnauthorized": true,
            "ca": "/../.postgresql/root.crt"
        }
    }
}

I've tried a lot of the advice from various resources, including the sequelize/cli repo. But none of it seems to work. Any advice would be helpful.

score 2 · Answer 1 · answered Apr 09 '22 at 21:38

I had the same issue and the fix was to add the code below in the migrations config file even though you already have it in the database connection file. The following code is in the config/config.js file for migrations.

production: {
    username: ****,
    password: ****,
    database: ****,
    host: ****,
    dialect: ****,
    port: ****,
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false,
      },
    },
  },

This is how my DB connection looks like that was working normally.

const sequelize = new Sequelize({
  host: ****,
  database: ****,
  username: ****,
  password: ****,
  dialect: ****,
  port: ****,
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: false,
    },
  },
});

Migration with Sequelize CLI to DigitalOcean Postgres Database Throwing SSL Error

1 Answers1