For my hobby projects I like to download live, production db and load it on my localhost environment so that I can quickly explore data or test potentially dangerous migrations.

Assumptions

This post assumes your hosting is onRailway, and you usePostgresql as a database.

Optional, but I'd also assume that you usedotenv for yourRuby on Rails backend and your app is running locally onDocker.

Credentials

In my app I have two env files./backend/.env.development:

# localhostPGDATABASE=some-local-databasePGUSER=postgresPGPASSWORD=somepasswordPGHOST=dbPGPORT=5432# docker:db container defaultsPOSTGRES_PASSWORD=${PGPASSWORD}

Thanks to this config, my application will be able to connect to local database using the same set ofenvironment variables as Railway.

My Rails db config then looks likebackend/config/database.yml:

default:&defaultadapter:postgresqlpool:<%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>timeout:5000encoding:unicodedatabase:<%= ENV["PGDATABASE"] %>username:<%= ENV["PGUSER"] %>password:<%= ENV["PGPASSWORD"] %>host:<%= ENV["PGHOST"] %>production:<<:*defaultdevelopment:<<:*defaulttest:<<:*defaultdatabase:some_app_test

The aliasing ofPOSTGRESQL_PASSWORD then takes advantage ofdefault Postgresql Docker setup which will then set up respective user on container creation.

One thing to note is thatRailway uses TimescaleDB so yourdocker-compose.yml will need to:

services:  db:    env_file:       - ./backend/.env.development    image: timescale/timescaledb-postgis:latest-pg13

Second file looks very similar./backend/.env.production:

# railway.appPGDATABASE=some-railway-databasePGUSER=postgresPGHOST=some-railway-url.railway.appPGPORT=1234

You need to fill this one with credentials found in yourRailway -> PostgreSQL -> Variables.

The script

Now for the actual dump/download/load I'm using following script./bin/database_pull.sh:

#!/bin/bashDUMP_FILENAME='./some-app.com_dump.sql'source ./backend/.env.production||exit$?echo'💾 Creating production dump'pg_dump-U$PGUSER-h$PGHOST-p$PGPORT-W-F t$PGDATABASE>$DUMP_FILENAMEsource ./backend/.env.development||exit$?echo'🎡 Loading dump on localhost'dropdb$PGDATABASE-p$PGPORT-U$PGUSER-h$PGHOSTcreatedb$PGDATABASE-p$PGPORT-U$PGUSER-h$PGHOSTpg_restore-U$PGUSER-h$PGHOST-p$PGPORT-W-F t-d$PGDATABASE$DUMP_FILENAMEecho'✨ Success!'

This scriptneeds to be provided the values of${PGPASSWORD} manually. You have to provideproduction first andlocal second:

./bin/database_pull.sh💾 Creating production dumpPassword:🎡 Loading dump on localhostPassword:✨ Success!

Conclusion

Developer Experience is important part of every developer life. Being able to use standard configuration and run one command to get my hands on the data I need in the format I prefer is IMO a huge DX improvement.

Image generated by Midjourney prompt:railway hosting; blog post; downloading database; coding; ruby on rails --ar 16:9