Devbox, is an interesting, nix-based tool for setting up reproducible development environments.
I recently needed to quickly setup a postgres database and load the Chinook dataset to play around with some queries.
I could have used Docker, but I am not a fan of its UI or how heavyweight it has become (looking into podman is also on my todo list) and I’ve been using nix a lot lately, which is what led me to the devbox project.
After installing devbox, I setup a project
mkdir test-db
cd test-db
devbox init
devbox add postgresql
Next, I ran
devbox services up
but was getting the following error
pg_ctl: directory "/Users/.../.devbox/virenv/postgresql/data" is not a database cluster directory
It turns out data was an empty directory.
I initialized a database in this spot
rm -rf .devbox/virtenv/postgresql/data
initdb -D .devbox/virtenv/postgresql/data
then ran the command again and that seemed to solve the issue.
Finally, I created the database and added the data
createdb chinook
psql chinook -f out.sql
Note: since I never specified -U, I create all of these things as my system user, not as the typical postgres user.
This seems to work fine for devbox but keep this in mind if you are running the database for an app that uses a consistent user name.
Lastly, we can check to see all the data is loaded as we expect
❯ psql -h localhost -d chinook
psql (15.6)
Type "help" for help.
chinook=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+-------------
public | actor | table | danielcorin
public | album | table | danielcorin
public | artist | table | danielcorin
public | category | table | danielcorin
public | customer | table | danielcorin
public | employee | table | danielcorin
public | film | table | danielcorin
public | film_actor | table | danielcorin
public | film_category | table | danielcorin
public | genre | table | danielcorin
public | invoice | table | danielcorin
public | invoice_line | table | danielcorin
public | media_type | table | danielcorin
public | playlist | table | danielcorin
public | playlist_track | table | danielcorin
public | track | table | danielcorin
(16 rows)