Setup dev and prod databases with Prisma & Railway

[aryanprince]

Thing is, now we only have 1 database that's used in production and during local development.

Ideally we would have a setup like this:

• 2 databases (on Railway or anywhere else)
• Our local .env file only contains connection URL to the dev database
• Meanwhile Vercel and GitHub will store a connection URL to our prod database
• Let's say I'm working on a new feature and I wanna add email column to Student table - I would type prisma migrate dev -name "Added email column" and this would create an email column only on dev database. The prod database would remain unchanged.
• Once I'm happy with my new changes locally, I make a push to main branch on GitHub (directly or PR)
• Now a new GitHub Action will run (similar to the Nodejs Build one I showed you)
• This GitHub Action will run the command prisma migrate deploy and that's it - email field will show up on prod database too...ONLY after we've actually made a push to GitHub. Until then, email column only exists on dev database.

PS: Although we can run prisma migrate deploy locally, it's best to use prisma migrate dev on our local machine and instead use prisma migrate deploy only on our CI/CD pipeline.

You can read more about this on: https://www.prisma.io/docs/guides/deployment/deploy-database-changes-with-prisma-migrate

What I Want Us To Do

• Look into creating a new 'environment' (better) or a new database altogether on Railway (or any other platform too if we wanna try)
• Read those Prisma docs
• Update our GitHub/Vercel .env to prod and update our local .env to our new dev database
• Set up GitHub Action to run the prisma migrate deploy command on new changes to main branch

[aryanprince]

Made some progress with this issue, I set up a Docker Compose script so we can just run that to spin up a quick Postgres database for local development. This means we won't have to mess with our production data during development.

What do you think?

Script rn:

This creates a Postgres container we can connect to in our ENV file: