🥋 Postgres Dojo

---

About Postgres Dojo

Technologies used in this Dojo:

• 🧑‍🍳 mise https://mise.jdx.dev/
• 🐘 postgres https://www.postgresql.org/
• 🦀 uv https://docs.astral.sh/uv/
• 🐍 pgcli https://www.pgcli.com/
• 🐚 bash scripting https://www.gnu.org/software/bash/manual/bash.html

---

How to install Postgres Dojo

---

Step 1 - Be pre-emptive and add the mise functionality to your shell

Before installing mise, you can take the pre-emptive approach of adding its functionality to your shell with this if statement.

If you are using zsh, add this to the end of your ~/.zshrc file

if command -v mise 1>/dev/null 2>/dev/null; then
	eval "$(mise activate zsh)"
fi

If you are using bash, add this to the end of your ~/.bashrc file

if command -v mise 1>/dev/null 2>/dev/null; then
	eval "$(mise activate bash)"
fi

---

Step 2 - Be pre-emptive and add cargo to you Path variable

This is where executable binaries from Rust packages on https://crates.io will be placed.

If you your shell is zsh then add this command to your ~/.zprofile file

export PATH="$HOME/.cargo/bin:$PATH"

If you your shell is bash then add this command to your ~/.bash_profile file

export PATH="$HOME/.cargo/bin:$PATH"

---

Step 2 - Install mise on your system

mise is incredibly easy to install on any system.

You can view all the available methods here, and pick the one that is easiest for you:

mise-en-place installation guide

---

My prefered installation method: Using the Rust toolchain

NOTE: You don't have to setup Rust to use mise!

I just prefer to install mise using the Rust toolchain because mise is a Rust-based tool and so installing it via the Rust toolchain guarantees that I will always have the latest version of mise installed.

I'm on Arch Linux so I'll use my package manager to install rustup, the Rust Rust toolchain manager.

sudo pacman -S --needed rustup

The equivalent on Debian-based distros is:

sudo apt install rustup

Then install the latest stable version of Rust

rustup default stable

Install cargo-binstall, this is a tool that makes it easier and faster to install Rust-based tools that have a pre-compiled binary executable like mise.

This will build cargo-binstall tool from source and add an executable binary to $HOME/.cargo/bin/

This will take about 6 minutes to build from source. This is the only time you have to build it from source.

cargo install cargo-binstall

The next time you want to update it, just run:

cargo binstall --no-confirm cargo-binstall

Then you can install / update mise using this command:

cargo binstall --no-confirm mise

---

Step 3 - Setup Postgres Dojo

git clone --depth 1 https://github.com/dezlymacauley/postgres-dojo.git

Enter the directory

cd postgres-dojo

Note: For the rest of this guide, all of the instructions listed will assume that you are at the root of this directory.

Give mise permission to apply the settings listed in mise.toml

mise trust

Make the bash scripts in the .mise-tasks directory executable, so that they can be used by mise to simplify your Postgres workflow.

chmod +x .mise-tasks/*.sh

---

How to check that your Python virtual environment is setup correctly.

At this point your Python virtual environment should have already been setup for you. You can confirm this by running the following commands

which python

python --version

• You should see a path that ends with: postgres-dojo/.venv/bin/python

• And you should see the version of Python that is in your .python-version file, and your pyproject.toml file

You do not need to activate or deactivate your Python virtual environment.

This is automatically done for you, whenever you enter and exit the postgres-dojo directory.

---

How to easily fix Python version issues.

If you see an incorrect version or a file path that looks like this when you are in the postgres-dojo directory:

/usr/bin/python

Then that means that mise is not using your Python virtual environment.

The solution to this a very simple, once-off fix:

Run this command:

uv sync

Go back one directory:

cd ..

Re-enter the postgres-dojo directory

cd postgres-dojo

---

How to use Postgres Dojo

Simply run the command mise-tasks to see the list of available commands.

You should see a table like this

Name        Description
admin       Perform admin tasks with psql
clean       Delete the Postgres cluster and Python environment
connect     Connect to a database with pgcli
setup-dojo  Setup the Postgres Dojo
start       Start the Postgres server
status      Status check of the Postgres Dojo
stop        Stop the Postgres server

---

Note: The quickest way to get started is to simply run mise connect

• I have designed each of these commands to be idempotent, to ensure gracefully shutdowns, ensure clean builds and command executions, and most importantly to support each other regardless of the order you choose to run them.

• For example, if you run mise connect without running the setup-dojo command and you do not have a Postgres cluster or pgcli installed...

• mise connect will simply run the setup setup-dojo command, and connect to the database.

---

How to change the Postgres and uv (Python package manager) versions

mise use postgres@version-number-you-want

mise use uv@version-number-you-want

---

How to change the Python and pgcli versions

• Simply change the version numbers in .python-version, and pyproject.toml

then run this command:

uv sync

---

How to use custom connection details

E.g. I want to use the following connection details:

• login user name: auth_service

and I want to connect to:

• Database name: user_accounts
• Host name: 127.0.0.8
• Database Port: 6145

Step 1: Update the mise.toml file:

Change this

[env]
LOGIN_USER_NAME = "{{ env.USER }}"
DB_NAME = "postgres_dojo"
DB_HOST = "127.0.0.1"
DB_PORT = "5432"

To this:

[env]
LOGIN_USER_NAME = "auth_service"
DB_NAME = "user_accounts"
DB_HOST = "127.0.0.8"
DB_PORT = "6145"

Step 2: Run mise admin command

Note: The mise admin command will always connect with these settings:

DEFAULT_LOGIN_USER_NAME = "{{ env.USER }}"
DEFAULT_DB_NAME = "postgres_dojo"
DEFAULT_DB_HOST = "127.0.0.1"
DEFAULT_DB_PORT = "5432"

Once you are connected your prompt should look like this:

postgres_dojo=#

Create the user

CREATE USER auth_service WITH PASSWORD 'a-secure-password';

Create the Database:

CREATE DATABASE user_accounts OWNER auth_service;

Explicitly allow connections

GRANT CONNECT ON DATABASE user_accounts TO auth_service;

Type \q and press enter to exit the psql prompt

---

Run the mise connect command

You should see your connection details from the mise.toml file

___________________________________________

🗃️ Connecting to database: user_accounts
  - Host: 127.0.0.8
  - Port: 6145
👤 via the user: auth_service
___________________________________________

And your prompt should be the pgcli prompt which looks like this:

user_accounts 🥋

To confirm that you are actually connected with what your specified in the mise.toml file, you can use this SQL query:

SELECT
     current_user AS user_name,
     current_database() AS database_name,
     inet_server_addr() AS host,
     inet_server_port() AS port;

You should get this output back:

+--------------+---------------+-----------+------+
| user_name    | database_name | host      | port |
|--------------+---------------+-----------+------|
| auth_service | user_accounts | 127.0.0.8 | 6145 |
+--------------+---------------+-----------+------+

Type \q and press enter to exit pgcli.

---