20

I've tried to connect Prisma with postgreSQL several times. prisma show this error message : "Error: undefined: invalid port number in "postgresql://postgres:password@localhost:5432/linker")".

-error enter image description here

-prisma/.env

DATABASE_URL=postgresql://postgres:password@localhost:5432/linker

-schema.prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

So, first, I checked the port number to see if it was right and 5432 is right because I use the default port number. I also checked the postgresql.conf file, which is set to "listen_address="*"" , "port=5432".

enter image description here

And I went into pgAdmin4 and saw server's properties. the port number was 5432 as shown below image, and the username was set "postgres".

enter image description here

I don't know why prisma can't connect
Did i something missed?

Improve this question
2
  • 1
    Where is "Prisma" running. On the same machine like the database server or in a Docker container ...? Commented Sep 1, 2020 at 9:03
  • @madflow same machine, i'm using windows 10, visual studio code Commented Sep 2, 2020 at 4:41

5 Answers 5

Reset to default
86

Replace special characters with percent-encodings

Make sure that in your DATABASE_URL in the .env file, the special characters in your username and password are replaced with percent-encodings.

For example, in your database URL, if your username or password contains @ character, it should be replaced with its equivalent percent-encoding, that is %40. For # it is %23 and so on. See the list below.

Percent-encodings

Following are the percent-encodings for the frequently used special characters:

Special Character Percent Encoding
! %21
# %23
$ %24
% %25
& %26
' %27
( %28
) %29
* %2A
+ %2B
, %2C
/ %2F
: %3A
; %3B
= %3D
? %3F
@ %40
[ %5B
] %5D
newline %0A or %0D or %0D%0A
space %20
" %22
% %25
- %2D
. %2E
< %3C
> %3E
\ %5C
^ %5E
_ %5F
` %60
{ %7B
| %7C
} %7D
~ %7E
£ %C2%A3
円 %E5%86%86
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks! this worked, I replaced all the special characters with the above Encodings and it worked
Any library that does this?
@Nobody, Not that I'm aware of but you can easily write your own by scanning the strings and replacing them with the corresponding characters.
To be clear, you would encode EVERY special character in this string? mysql://USER:PA$$WORD@HOST:PORT/DATABASE?connection_limit=5 - the colon and backslashes after the word "mysql?" The colons separating USER and PA$$WORD? The $ in the password? The @ separating PA$$WORD and HOST? The ? for the query parameter? The = sign? I have tried some and all and cannot get it to work
@user210757, only in your username and password. In your case only $$. That will result in mysql://USER:PA%24%24WORD@HOST:PORT/DATABASE?connection_limit=5. I hope you have also provided the actual values for HOST, PORT and DATABASE in your real url and this just an example string.
|
6

For anyone running into this, see the comments above on the answer!

Removing symbols from the db password (hosted on AWS RDS) fixed the problem for me.

Improve this answer

1 Comment

on Supabase, too.
3

You should convert your password to URL encoded format in order to use it in the database connection string.

Accepted answer is correct.

But I want to show a programmatic and easy solution since manually replacing special characters in your password is error prone.

You have NodeJS installed on your machine (obviously, since you are trying to use Prisma)

Open a terminal and start a NodeJS process (current directory does not matter):

node

Then type this, hit enter:

     new URLSearchParams({pass: "YOUR_PASSWORD_WITH_SPECIAL_CHARACTERS"}).toString().substring(5)

As a result you will get the URL encoded version of your password printed to console:

'YOUR_PASSWORD_URL_ENCODED'

Copy it (without the quotes) and use it in your database connection string as your password.

Improve this answer

Comments

2

Adding Yogesh's answer. Rather than manually convert the characters, you can use browser's console and run this command:

encodeURIComponent('your-password')

and copy-paste them.

Improve this answer

Comments

0

Yogesh's answer is very detailed. For people using VSCode and want to convert password with special characters without look up the encoding table and leaving the editor. VS Utils extension supported URL Encode feature.

  1. Install the VS Utils extension.
  2. Select the password you want to encode.
  3. Hit Ctrl + Shift + P, and select VS Utils: select a conversion to perform
  4. Select URL Encode

That's it, the password is now encoded.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.