Test Connection to MySQL failed in DataGrip

Next.js
1

Hi Forum members,

I’m at tutorial on “Creating Migrations” in Next.js part 1.

I tried Test-Connection link in DataGrip with the same steps and config as Mosh did but it failed.

image
image1175×867 84.8 KB

Also, configured MySQL in MySQL-Configurator with ver. 8.1.0

Database url:
DATABASE_URL=“mysql://root:123abc456def%23@localhost:3306/nextapp”
(%23 in the password represents # , special char)

Downloaded the drivers also. Checked drivers version: 8.0.25

Tried with this version as well as Mosh’s: 8.0.1

Failed, with both the versions.

Checked prervious questions. Found no reference.

Referred to docs also but did not find any miss.

==============
Also, run into the error below while using the command: $npx prisma migrate dev

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "nextapp" at "localhost:3306"

Error: P1001: Can't reach database server at `localhost`:`3306`

Please make sure your database server is running at `localhost`:`3306`.

================

Dug deep into debugging and ran the command CREATE DATABSE nextapp on MySQL-Command Line.

Now, at least, I can see the nextapp database, as below, which was absent earlier.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nextapp            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

BUT, still if I run npx prisma migrate dev, the same error shows up:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "nextapp" at "127.0.0.1:3306"

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

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

It’s so frustrating. Already spent half of the day to fix it.

Can anyone help ?

Sheer waste of one’s limited time.

================

Thank you
Manu

2

I had similar issues and after spending over a day on this I discovered that it may be better to put your secret DATABASE_URL string in Next.js’ .env.local file instead of .env. Next.js takes care of populating process.env object with variables defined in this file.

I then had to make Prisma use .env.local instead of looking for .env by default for the DATABASE_URL variable. You do this by installing the dotenv package with your package manager of choice such as npm install dotenv.

Next you change your migrate:env script in package.json to this:

"migrate:dev": "pnpm exec dotenv -e .env.local -- prisma migrate dev",

Or if you’re using NPM instead of my PNPM:

"migrate:dev": "npx dotenv -e .env.local -- prisma migrate dev",

This tells Prisma to look in .env.local for the DATABASE_URL string.

See here for Stack Overflow details on this too:

next.js - How to set environment variables with Prisma, Nextjs, and Vercel - Stack Overflow

All that said I’m having issues elsewhere such as DataGrip not being able to see any Prisma migrations after successfully connecting to mysql, even though mysql CLI and MySQL Workbench do see them, and other pain-points besides this. I’m also running my dev environment on Windows in a Ubuntu Linux distro using WSL. I’ve only used PostgreSQL in the past and had not issues with that in a Windows WSL dev environment so I’m switching to that instead of MySQL, which I’ve never used before.

That said, I’ve never used PostgreSQL with Prisma so I may have issues there and may need to use a different ORM than Prisma :slightly_smiling_face: