Error: P1001: Can't reach database server at 127.0.0.1:3309 when connecting locally with prisma

[m4rvr]

Hey!

I want to use a dev database locally but when I run e.g. the prisma migrate dev command, I get the error Error: P1001: Can't reach database server at 127.0.0.1:3309.

The dev & shadow branches exist and running pscale connect XXX dev --port 3309& pscale connect XXX shadow --port 3310 works since I get the message Secure connection to database XXX and branch dev is established! for both.

I basically followed this tutorial (without the Next.js & Netlify part.

I've read something about sslcert errors and the very generic error message but I'm not sure if I'm missing something here? Do I need to set up some additional things like ssl configurations or do I need to install mysql (running on Mac)?

Prisma schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

datasource db {
  provider             = "mysql"
  url                  = env("DATABASE_URL")
  shadowDatabaseUrl    = env("SHADOW_DATABASE_URL")
  referentialIntegrity = "prisma"
}

model User {
  id    Int    @id @default(autoincrement())
  email String
}

.env file:

DATABASE_URL="mysql://root@127.0.0.1:3309/XXX"
SHADOW_DATABASE_URL="mysql://root@127.0.0.1:3310/XXX"

[m4rvr]

Okay, after a restart it works somehow. Maybe it needed that after installing the cli and the mysql client.

[jgreet]

I tried restarting the mysql homebrew service but this also didn't work.

That really shouldn't affect the proxy connection, except for perhaps occupying port 3306 which is the default port pscale connect would grab if you weren't specifying 3309 and 3310.

You're on a Mac, so I assume you installed using Homebrew and you're using an up-to-date version of the CLI?

The next time you see Prisma unable to connect through the proxy, could you check if the mysql command line client fails to connect as well?

mysql -h 127.0.0.1 -P 3309

Thanks!

[jgreet]

Also just thought I should mention that we use pscale connect in the Prisma documentation but there's no reason you couldn't use connection strings for DATABASE_URL and SHADOW_DATABASE_URL instead if that works better.

https://docs.planetscale.com/concepts/connection-strings

You'd need to create a password for each branch but the Prisma connection string is available right there in the web app for copy/pasting.

[m4rvr]

Thanks for your feedback! After upgrading from 0.85.0 to 0.86.0 it works again, but I'm not sure if it was the outdated version (I should have checked that anyway, sorry).

Do you mean that I could connect directly to the db with the connection string and not the local proxy because I already use the connection string mysql://root@127.0.0.1:3309/XXX in the .env file? Not sure, but if I connect to the branches and do dev migrations & local testing, does this count as read/write because with the proxy it seems it doesn't count?
If not, what's the advantages/disadvantage of having a local proxy instead the "real" connection?

I will also keep in mind to check the connection with the mysql command if any errors occurs again. :)

Thanks!

[jgreet]

You're right, I mean connect directly with a connection string like

mysql://<USERNAME>:<PASSWORD>@<HOST>.psdb.cloud/<DATABASE_NAME>?sslaccept=strict

Rows read and written will be the same.

I wouldn't say either way is inherently better for local development but if you keep having trouble with the proxy it might be useful to try the other connection method.

The advantage of the proxy connection is that you don't have to keep track of credentials or worry about SSL. It's also great for connecting GUI SQL clients that don't support secure connections properly. The disadvantage is that it can make your dev environment differ from production where you're more likely to authenticate with a username and password.

[m4rvr]

Sorry for the late reply, and many thanks for your great feedback! 🙂

[dlinah]

hi , i have the same problem , using mac and running pscale connect XXX dev --port 3309 & pscale connect XXX shadow --port 3310
i can connect to it with mysql command line client, but with prisma it never worked even after upgrading psacle to 0.87.0
do you have an idea why this would happen?

[pauxiel]

worked for me too

[Emiltayeb]

Worked for me as well, timeout at 30 seconds

[ael-bagh]

connect_timeout=60 WORKED FOR ME
DATABASE_URL="postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}?schema=public&connect_timeout=60"

[felixhaeberle]

@jgreet This still is a bug and appears in my case:

felixhaberle@MacBook-Pro-von-Felix ~/D/g/backstage (main) [SIGINT]> npx prisma db push
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "stage" at "127.0.0.1:3309"

Error: P1001: Can't reach database server at `127.0.0.1`:`3309`

Please make sure your database server is running at `127.0.0.1`:`3309`.

with the following proxy running:

felixhaberle@MacBook-Pro-von-Felix ~/D/g/backstage (main)> pscale connect stage main --port 3309
Secure connection to database stage and branch main is established!.

Local address to connect your application: 127.0.0.1:3309 (press ctrl-c to quit)

[alamindevms]

Thanks.
Worked for me.

[honeybread]

In my case, on the terminal session, logging into planetscale using pscale auth login before attempting to prisma db push solved the issue.

[AlexandraCernat29]

In my case I missed a step: In a separate terminal, run the following command:
pscale connect <db_name> <db_branch_name> --port 3309

After that it works