DrizzleORM - ClientAuthentication Error using `heroku-postgres` database (aws) connection url

I am trying to connect to a heroku-postgres database with drizzleORM, the db is actually an aws db.

This is the error I get, using the db url I got from the heroku dashboard:

xxx\node_modules\drizzle-kit\bin.cjs:23472
        const message = name === "notice" ? new messages_1.NoticeMessage(length, messageValue) : new messages_1.DatabaseError(messageValue, length, name);

error: no pg_hba.conf entry for host "xxx", user "xxx", database "xxx", no encryption
    at Parser.parseErrorMessage (xxx\node_modules\drizzle-kit\bin.cjs:23472:98)
    at Parser.handlePacket (xxx\node_modules\drizzle-kit\bin.cjs:23313:25)
    at Parser.parse (xxx\node_modules\drizzle-kit\bin.cjs:23237:34)
    at Socket.<anonymous> (xxx\node_modules\drizzle-kit\bin.cjs:23513:44)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  length: 175,
  severity: 'FATAL',
  code: '28000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'auth.c',
  line: '536',
  routine: 'ClientAuthentication'
}

Node.js v18.19.0

I added ?sslmode=require to the end of the url to fix this but now I get this one:

DEPTH_ZERO_SELF_SIGNED_CERT

What do I have to do? I basically only have the connection url and that is it. I can not configure the database on heroku.

This is my drizzle config:

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./schema.ts",
    out: "./drizzle",
    driver: "pg",
    dbCredentials: {
        connectionString: process.env.DATABASE_HOST!
    },
    verbose: true
})

I've just faced the same issue as you, I was working on a project that was using postgres.js as the driver. I tried everything:

• SSL set to no-verify
• Run heroku config:set PGSSLMODE=no-verify
• Set the ssl key to { rejectUnauthorized: false}
• Set the sslmode=no-verify in the connection url
• Disable SSL and set it to every option it could have

In the end, unfortunately, the fix for me was to switch the driver to node-postgres aka pg. The reason I thought I needed to switch is that whenever I switched the SSL option on postgres.js and I logged the connection it would show the change there but Heroku would throw the "No Encryption" message. That didn't make sense to me if I was forcing SSL or trying something else. At least I would have expected some change in error message from Heroku's Postgres when I'm tweaking those values. So I figured that my SSL changes weren't properly pulling through to the connection.

Anyways, I highly recommend using pg instead of postgres.js in Drizzle when developing for Heroku.