Update the database docs on seeding the production DB on Fly

[infomiho]

We are looking to update this section of the docs to include instructions on how to seed a production db: https://wasp-lang.dev/docs/data-model/backends#seeding-the-database

This is a tested workflow as of Oct 3rd:

---

Let's say we deployed an app on Fly.

I set seeding-test as my deployment name so:

• my DB app is seeding-test-db
• my server app is seeding-test-server

Seeding a production database is a two-step process: :one: pointing your local app to use the production DB (just to seed it) :two: opening a tunnel towards the production DB so the local app can connect to it

But there are some details we need to get first: DB name and DB password

Getting the DB name:

fly postgres connect -a seeding-test-db

and then write the following:

\l

you'll find your DB name there.

By convention, it should be server_name_with_underscores, for me it was seeding_test_server.

Getting the DB password:

fly ssh console -a seeding-test-db

then

echo $OPERATOR_PASSWORD

With all this info, we can finally seed the production DB:

:one: Setting the database URL in .env.server Edit your .env.server to contain the following line (with correct values in the place of <password> and <db_name>):

DATABASE_URL=postgres://postgres:<password>@localhost:5432/<db_name>

e.g.

DATABASE_URL=postgres://postgres:myDatabasePassword@localhost:5432/seeding_test_server

:two: Opening a tunnel so the production DB is available locally First, make sure nothing else is running on the port 5432, e.g. your local dev database (which you may have started with wasp db start, or in some other way). NOTE: even if you kill the terminal that was running your wasp db start, its Docker container still might be running in the background and occupying the port 5432 -> ensure that is not happening and terminate that container if needed.

Then, run

fly proxy 5432 -a seeding-test-db

Leave this terminal tab running and open a new terminal tab for future commands.

Now you are connected to the production database, so be careful with what commands you execute!

To test the connection, you can try running wasp db studio. If it connects, the connection works (even if it throws errors upon trying to browse the tables). If it fails to connect at all, it might be that you forgot to set DATABASE_URL in the .env.server file, or that the value in DATABASE_URL is incorrect (password? database name?), or that something else is running already on the port 5432.

Finally, running the command wasp db seed should populate the production DB now :clap:

:warning: Cleanup: remove the DATABASE_URL from the local .env.server and close the tunnel.

---

Related convo on Discord, check it out for some additional directions / things to take into consideration: https://discord.com/channels/686873244791210014/1168303085442052216/1168303085442052216 .

[vaibhavpnimkar]

i would like to work on this issue,if there are any additional details or requirements I should keep in mind while making the changes, please let me know

[weedertree]

could you assign me this issue @infomiho

[Suraj-kumar00]

Hi @infomiho, how are you doing? Can I work on this issue? Can you please assign it to me?

[Martinsos]

Hey all, sure feel free to go for it -> Maybe let's see if @vaibhavpnimkar wants to take it on since he was first, and if not then somebody else can step in?

The issues is pretty well described, it will require updating docs mindfully while also testing that it all works as it should (this might be somewhat harder part as it requires running a Wasp app on Fly -> although Wasp provides easy commands for that).

[vaibhavpnimkar]

"I'll take care of it. Thanks for assigning it to me!

[CiaoRaviRaj]

@Martinsos can i contribute on this fix as my good first issue. I create PR for this issue . If you like my PR could you merge it . If not could you guide me to improve my PR.

[infomiho]

@CiaoRaviRaj you can try to tackle this as well, make sure to understand the docs that are written and that the instructions work.

[Vivek-GuptaXCode]

hello maintainers,is this issue open to work on ? really excited to contribute to such a great repository

[Martinsos]

I removed @vaibhavpnimkar since it has been two weeks. @Vivek-GuptaXCode if you want to go for it, ok, but pls check the instructions I provided above -> this is not a trivial issue. I just removed "good first issue" label as I think it is maybe not as easy as it seemed at first (due to testing).

[Vivek-GuptaXCode]

i would like to give it a try.

[Martinsos]

In that case I will assign you @Vivek-GuptaXCode !

[Vivek-GuptaXCode]

thanks a lot,i will try my best

[Vivek-GuptaXCode]

may i know why i am unassigned? @Martinsos

[Martinsos]

My bad @Vivek-GuptaXCode I was doing some clean up and forgot you have a PR for this one, I put you back now!

[Martinsos]

It is important to mention here that doing this seeding in the way explained above can be slow, since data has to travel from production DB to our local server and then to DB again and all through the tunnel. So a faster even if somewhat hacky way can be to just add an API route in your app that only you can run (protect it in whatever way makes sense for you), deploy app with that, then trigger that route, and then deploy the app without that route again. Then it all happens up there, no tunnel or connecting to the prod database from the local machine.

That said, we should investigate this a bit more, identify the best approaches for this and document them properly. Both of these approaches work, but they both feel unpolished at the moment, so either we figure these out better and document them or we even come up with something better.