When working on multiple features of the same application with different database schemas, there is always the burden of regenerating the schema and inserting some data each time we switch context.

To reduce this effort I have started using database dumps. This has been saving me time when working on two (or more) features, which have a different database schema and introduce some new logic using stored data.

Here are the commands I usually run:

# dump db
sudo -u postgres pg_dump database_name > branch-name.sql
# checkout other branch
git checkout other-branch
# delete db
sudo -u postgres dropdb -f database_name
# create db
sudo -u postgres createdb database_name
# load existing dump
sudo -u postgres psql database_name < other-branch.sql

This has also saved me time when testing database migrations. The workflow here would be:

1. Checkout base branch (ie main)
2. Generate some data
3. Generate dump
4. Checkout new branch
5. Run database migration
6. Check database results

In case something when wrong with the database migration, just need to load the dump, skipping the time consuming data generation step.

Update: Creating database for specific user

Depending on the configuration of the database connections you might want to create the database under certain user. The quickest method I have found to do so is with createdb:

createdb -E utf8 -O <db-owner> <db-name>