Drizzle Team•3mo ago

Error when attempting to migrate on Windows

Hey, I am working on a project which uses Next.js, TypeScript and Drizzle, I'm running a local, freshly installed Postgres server on my machine, in which I have created a database and granted all permissions for it to user postgres. I am getting a connection URL from the environment variables (specified in a .env.local file) in the following format: postgres://postgres:[email protected]:5432/<db_name>. The error I'm getting is this:

Migration failed: Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string
    at <path_to_project>\node_modules\pg-pool\index.js:45:11
    at processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PgDialect.migrate (<path_to_project>\node_modules\src\pg-core\dialect.ts:72:3)
    at async migrate (<path_to_project>\node_modules\src\node-postgres\migrator.ts:10:2)
    at async runMigration (<path_to_project>\src\migration\migrate.ts:9:5)
Database connection closed

Migration failed: Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string
    at <path_to_project>\node_modules\pg-pool\index.js:45:11
    at processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PgDialect.migrate (<path_to_project>\node_modules\src\pg-core\dialect.ts:72:3)
    at async migrate (<path_to_project>\node_modules\src\node-postgres\migrator.ts:10:2)
    at async runMigration (<path_to_project>\src\migration\migrate.ts:9:5)
Database connection closed

Postgres logs show me the following:

<date> <time> <timezone> [14860] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

<date> <time> <timezone> [14860] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

My drizzle.config.ts looks like this:

import { defineConfig } from 'drizzle-kit'
import { config } from 'dotenv'
import { resolve } from 'path'

const loadEnv = () => {
  config()
  config({ path: resolve(process.cwd(), '.env.local'), override: true })
}
loadEnv()
const { DATABASE_URL } = process.env

if (!DATABASE_URL) {
  throw new Error('DATABASE_URL is not set in the environment variables')
}
export default defineConfig({
  schema: './src/dao/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
})

import { defineConfig } from 'drizzle-kit'
import { config } from 'dotenv'
import { resolve } from 'path'

const loadEnv = () => {
  config()
  config({ path: resolve(process.cwd(), '.env.local'), override: true })
}
loadEnv()
const { DATABASE_URL } = process.env

if (!DATABASE_URL) {
  throw new Error('DATABASE_URL is not set in the environment variables')
}
export default defineConfig({
  schema: './src/dao/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
})

I have verified that the environment variable for the URL is being pulled correctly. The weird thing is, I get the exact same setup working without issues on my macOS machine.

11 Replies

Angelelz•3mo ago

Did you forget to put the user and password in your connection string?

KarlOP•3mo ago

No, as can be seen in quite the beginning of my original post, the connection URL stored as an environment variable is formatted as postgres://postgres:[email protected]:5432/<db_name>, meaning the user and password both are postgres

Angelelz•3mo ago

I see. The issue might be that you shouln'd provide a connection string, but credentials in form of an object with user, password properties

KarlOP•3mo ago

That is not the case. I have tested that exact potential solution, but to no avail.

Angelelz•3mo ago

There are different answers there, hopefully one can help you https://stackoverflow.com/questions/69878173/scram-server-first-message-client-password-must-be-a-string

Stack Overflow

SCRAM-SERVER-FIRST-MESSAGE: client password must be a string

Ive read documentation from several pages on SO of this issue, but i havent been able to fix my issue with this particular error. throw new Error('SASL: SCRAM-SERVER-FIRST-MESSAGE: client passw...

KarlOP•3mo ago

I have previously checked all of the fixes suggested in that article, none of them have fixed my issue. When I change the authentication method for the Postgres server, I am also getting an wrror which tells me "Authentication failed for user '<my local system username>". This makes me wonder if something is wrong with my server config. Why does it try to connect using my local account username, when I have specifically provided a connection string which uses 'postgres' as the username? I have no idea and none of the online resources seem to answer this question either. The behaviour is the same on Linux. I have tried a clean install on macOS as well, and there the behaviour is different. Everything works as expected out of the box. I have also tried copying over the server config files (specifically pg_hba.conf and pg_ident.conf), but this does not change anything.

Angelelz•3mo ago

I’ve never had this problem, so I honestly don’t know. Then again I’ve never use connection strings. I use objects

KarlOP•3mo ago

Problem was elsewhere - Next.js environment variable handling intricacies. Thanks for help anyways, @Angelelz!

Angelelz•3mo ago

Please share

Darren•3mo ago

guess trying to use DATABASE_URL in client side so its undefined since its not NEXT_PUBLIC or const { DATABASE_URL } = process.env this is being run at run time, in which case you cant destructure process.env in nextjs

Angelelz•3mo ago

That’s probably why I haven’t seen those problems, because I’ve never done those things lol

Gaming

Programming

Error when attempting to migrate on Windows