18

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?

sdy
  • 335
  • 1
  • 3
  • 10

5 Answers5

57

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
Yogesh Umesh Vaity
  • 41,009
  • 21
  • 145
  • 105
  • 1
    Thanks! this worked, I replaced all the special characters with the above Encodings and it worked – TRomesh Jul 03 '21 at 08:50
  • Any library that does this? – Nobody Feb 26 '22 at 16:01
  • 1
    @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. – Yogesh Umesh Vaity Feb 27 '22 at 06:32
  • Yeah, that's what I ended up doing, thanks! – Nobody Feb 27 '22 at 13:10
  • 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 Oct 27 '22 at 21:31
  • 2
    @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. – Yogesh Umesh Vaity Oct 28 '22 at 05:04
  • Wow, this helped me. So bizarre. I needed to replace characters like you instructed but only in my `SHADOW_DATABASE_URL` https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database and not in my `DATABASE_URL`. Weird. – Ryan Feb 19 '23 at 14:42
5

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.

user3777933
  • 414
  • 1
  • 5
  • 17
2

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.

Alper Güven
  • 329
  • 4
  • 15
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.

Herosimo Sribiko
  • 61
  • 3
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.

Tan Nguyen
  • 1,636
  • 1
  • 12
  • 9