Cant find user table in production db after deploying to fly.io
In dev I could easily delete users using the Prisma GUI, anyhow after deploy I can't seem to find the table containing the users.
I am accessing postgers through the fly cli with flyctl postgres connect -a myapp-db
postgres=# \dt
Did not find any relations.
postgres=# \d
Did not find any relations.
postgres=# \d "User"
I have a feeling i messed something with the db up.
During dev i didn't have a databsase_url in the env file as i was using the docker db that works out of the box.
I have deployed in that state and got the db connection string in the cli during deployment. I have then entered the string like this for production:
fly secrets set DATABASE_URL='postgres://myapp_server:[email protected]:5432/myapp_server?sslmode=disable' -a myapp-server
5 Replies
Hi @fred-123. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
----------------------
It seems like you're having trouble interacting with your production database after deploying your Wasp app to Fly.io. Here are some steps you can follow to troubleshoot this issue:
1. Check your DATABASE_URL: Make sure that the
DATABASE_URL you've set in your Fly secrets is correct. The format should be postgres://<user>:<password>@<hostname>:<port>/<database>?sslmode=disable. You can check this by running fly secrets list -a <app-name>.
2. Open a tunnel to your database: You can open a tunnel to your Fly production database using the command fly proxy 5432 -a <db-name>. This will allow you to interact with your database as if it were on your local machine. source (https://discord.com/channels/686873244791210014/1220423321422663820)
3. Run Prisma Studio: After opening the tunnel, you can run wasp db studio to interact with your database using Prisma Studio. This should allow you to see and manipulate your database tables. source (https://discord.com/channels/686873244791210014/1220423321422663820)
4. Check your .env file: If you've set the DATABASE_URL in your .env.server file, make sure to remove it to avoid accidentally interacting with your production database later. source (https://discord.com/channels/686873244791210014/1220423321422663820)
If you're still having trouble, you might want to check out the Wasp deployment documentation (https://wasp-lang.dev/docs/advanced/deployment/manually#different-providers) for more information on deploying your app to different providers.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @fred-123, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:
Perhaps try again in 15mins?@kapa.ai why do we need to set the sslmode to disable. When runing wasp deploy fly create-db <region> it returns a connectoin string without the sslmode disable paramater
Let me check the knowledge base for you. One moment! :books:
----------------------
The
sslmode=disable parameter in the DATABASE_URL is used to specify that SSL (Secure Sockets Layer) should not be used when connecting to your database. SSL is a security protocol used to establish encrypted links between a web server and a browser, ensuring that all data passed between them remains private and integral.
When you're running wasp deploy fly create-db <region>, the connection string returned doesn't include the sslmode=disable parameter because Fly.io defaults to using SSL for database connections. This is a security best practice for production databases, as it helps protect sensitive data from being intercepted during transmission.
However, there might be situations where you want to disable SSL, such as when you're connecting to your database locally for testing or development purposes. In these cases, you can add the sslmode=disable parameter to your DATABASE_URL to disable SSL.
It's important to note that you should only disable SSL if you understand the risks and have implemented other security measures to protect your data. For production databases, it's generally recommended to keep SSL enabled.
For more information on setting up your database with Wasp, you can refer to the Wasp deployment documentation (https://wasp-lang.dev/docs/advanced/deployment/cli#create-db).
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
Sorry @fred-123, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:
Perhaps try again in 15mins?
The postgres bit on the left tells you you are connected to the
postgres DB, but you should be connected to your DB e.g. my_app_server. You do that by first listing the DBs with \l and then use \c db_name to switch.
My advice would be to skip all that and use Prisma Studio in production, here are the instructions: https://github.com/wasp-lang/learning-materials/?tab=readme-ov-file#running-wasp-db-studio-on-production-dbGitHub
GitHub - wasp-lang/learning-materials: A place to collect some usef...
A place to collect some useful learning materials for Wasp - wasp-lang/learning-materials
Thank you a lot for that. Makes more sense now. I was using the prisma client before but had a weird issue when testing. I had deleted all users from the db but could still log in so I wasn't sure If I am interactinv with the correct db.