I have both the Fly App and Fly Postgres in my Personal organization. Inside the .env file, I used the DATABASE_URL given after attaching the Fly App to the Fly Postgres. However, I get a P1001 error after using npm prisma db push to upload my db schema.
npx prisma db push
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource “db”: PostgreSQL database “…”, schema “public” at “…:5432”
Error: P1001: Can’t reach database server at ...:5432
I have tried recreating the app and the Postgres app several times.
Did anyone encounter this problem or might know a solution?
For security reasons, Fly Postgres is not open to the internet by default (cf our docs), they can only be accessed on your organization’s private network on Fly.
To run DB migrations you have a couple of options:
locally, run fly proxy 5432 -a <postgres-app-name>, this will open a tunnel from your machine to your Fly private network. Then use (in your .env file) the following connection string postgres://postgres:<password>@localhost:5432 - cf our docs
or upon deployment of your app, you can use the release_command option of your fly.toml config file. (we’ve built this options with the use case of DB migrations in mind )
Thank you for your response. I have used the first approach and it works . However, I have a few questions to understand what just happened.
What I have basically done is proxy in one terminal. Then overwritten .env DATABASE_URL to the localhost format and then in the second terminal, I used npx prisma db push.
Does this mean I should overwrite the .env file back to the DATABASE_URL that was given to me after attaching the Fly App to Fly Postgres?
In the future should I update the database schema just using npx prisma db push? Will it remove the relevant data or it will just update the database structure?
If you have any further clarifications I would appreciate a short explanation to understand what is actually happening.
The DATABASE_URL that was given to you only works from your apps’ network (unless you configure it otherwise), and your .env file shouldn’t be committed/baked into your images (the DATABASE_URL secret will be instead passed by Fly as an env var to your app at runtime, since you’ve done the attaching). So I’d say you’d want to keep the localhost format in your .env file on your local machine (and do the proxying like you did).
Here is what Prisma recommends for push vs migrate. My understanding is push for dev stuff, and migrate for production (in the release_command for example)
Ok understood, so now if I run npm run dev locally, I won’t be able to communicate with the database since it can only be accessed only within the fly personal network? Or is there a workaround using the proxy?
For some reason, using the proxy and then running npm run dev, throws an error of not having a particular table in the database (which was the error from before but on the remote website). Even though on the remote website it works just fine.
Maybe check that you are connecting to the correct database (the last part of the DB URL - if not present it defaults to the user name, i.e. postgres in
)
. However, I have a few questions to understand what just happened.
Thank you.