Using devbox for a local PostgreSQL database

devbox
devbox postgres chinook nix

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

Terminal window
mkdir test-db
cd test-db
devbox init
devbox add postgresql

Next, I ran

Terminal window
devbox services up

but was getting the following error

Terminal window
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

Terminal window
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

Terminal window
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

Terminal window
❯ 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)
raw edit

Recommended

Using Nix to Install `llm` with Plugins

I use Simon's llm to quickly run LLM prompts. This package is easily installed with brew or pip, so if you want to use it, I recommend those...

Installing git-lfs with Nix

I was pulling the openai/evals repo and trying to running some of the examples. The repo uses git-lfs, so I installed that to my system using...