Connect to Azure Database for PostgreSQL server with Prisma

Question

I am looking for a way to connect my Next.js application with Azure Database for PostgreSQL server. Prisma seems to work well with Next.js but I can't figure connection string or if it is even supported database.

Example: DATABASE_URL = 'postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public'

The host I am trying to connect to: posgreservertest.postgres.database.azure.com:5432

So it should be something like "[email protected]:5432/mydb?".

I am trying to npx prisma introspect and this is how my cmd looks like:

PS C:\Web stuff\nextjs\test> npx prisma introspect
Environment variables loaded from prisma\.env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "posgreservertest.postgres.database.azure.com:5432"

Introspecting based on datasource defined in prisma\schema.prisma …
Error: P1001

Can't reach database server at posgreservertest.postgres.database.azure.com:`5432`

Please make sure your database server is running at posgreservertest.postgres.database.azure.com:`5432`.

Is it even possible to connect to Connect to Azure Database for PostgreSQL server with Prisma as it doesn't feature this option in their supported databases?

Answer 1

Make sure you've set your database as publicly available.

Add a firewall rule to allow connections from your IP. If you aren't concerned much about security you can allow connections from any IP. Just set 0.0.0.0-255.255.255.255 rule.

Ensure that you are using the full username id. For Azure PostgreSQL server id consists of two parts <username>@<servername>. In your following example full connection string will look like this

postgresql://username@servername:[email protected]:5432/dbname?your=options

If you've set SSL enforcing on your server make sure to add sslmode=require option

Answer 2

Your database does need not be exposed to all IP addresses. Just ensure your current public IP address has access to the azure PostgreSQL server.

To know what your own public IP address is use this command in your terminal.

• for Powershell

(Invoke-WebRequest 'ifconfig.me').Content

• for POSIX

curl ifconfig.me

OR

wget ifonfig.me

Should yield your PUBLIC IP address.

Go to the networks tab in the azure console for the PostgreSQL server, select the networks tab and whitelist your public ip address.

Alternatively just click where the red arrow is pointing at. If it happens to be gray as in this image it means your PUBLIC IP is already whitelisted.

If the steps above have been followed use the connection URL format below:

postgresql://<dbuser>:<dbpassword>@<dbservername>.postgres.database.azure.com:<port>/<bdname>?schema=public&sslmode=require

It is important to note that the database requires at least one table to be created or the prisma db pull command will return an error.