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 )
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.