As a Django newbie, I’ve found it easy to consider the database stuff “magic” and just leave it alone.
I’m starting to realize, however reluctantly, that it’s super important to know how databases work!
After seeing a software engineer YouTuber talk through a few of his database tables with some simple commands, I decided that would be a helpful skill.
So I ran psql and then… obviously it didn’t work because that would be too easy.
In this post, I’ll talk about how to use psql to view your PostgreSQL database from your Django project.
The Docker Problem
Docker has been great to me, but it’s also been a huge headache. There’s just so much going on behind the scenes; it makes reality difficult to comprehend. Layer that on top of learning Django and you’ve got a clear path to overwhelm.
For context, I can attribute my knowledge of Docker and Docker Compose to Will Vincent from his book “Django for Professionals”. That book really helped getting going, but deviating from that path is definitely challenging.
How to Run the Django Server Locally
Here’s a quick review of my local setup.
# pull base image FROM python:3.8 # set environment variables ENV PYTHONDONTWRITEBYTECODE 1 ENV PYTHONUNBUFFERED 1 # set work directory WORKDIR /code # install dependenceies COPY Pipfile Pipfile.lock /code/ RUN pip install pipenv && pipenv install --system # copy project COPY . /code/
version: "3.7" services: web: build: . command: python /code/manage.py runserver 0.0.0.0:8000 env_file: - .env volumes: - .:/code ports: - 8000:8000 depends_on: - db db: image: postgres:12 volumes: - postgres_data:/var/lib/postgresql/data environment: - "POSTGRES_HOST_AUTH_METHOD=trust" env_file: - .env ports: - 5432:5432 volumes: postgres_data:
Then to run the local Django server and local PostgreSQL database:
sudo docker-compose up -d --build
Accessing the Python Container
When running management commands with Docker Compose, we have to specify which service we want to run the command.
I have two services: web and db.
When I need to use Django’s manage.py, I have to prepend this command:
sudo docker-compose exec web...
So, for example, when I want to migrate my users app migrations, it looks like this:
sudo docker-compose exec web python manage.py migrate users
Python Container or Database Container?
So there are two ways (that I know) to access the psql command.
- python manage.py dbshell
python manage.py dbshell
As specified in the Django docs:
- For PostgreSQL, this runs the psql command-line client.
- For MySQL, this runs the mysql command-line client.
- For SQLite, this runs the sqlite3 command-line client.
- For Oracle, this runs the sqlplus command-line client.
This sounds like the way to go, so let’s run it.
sudo docker-compose exec web python manage.py dbshell
And then we get an error:
CommandError: You appear not to have the 'psql' program installed or on your path.
WHAT!? How is that possible?
Our PostgreSQL service “db” surely has psql installed, but that’s a different container. Instead of trying to install psql inside our “web” container, let’s try running psql directly from the “db” service.
To run, we’ll have to specify the “db” service:
sudo docker-compose exec db psql
Annnnnnd we get another error:
psql: error: could not connect to server: FATAL: role "root" does not exist
This error is telling us that psql is, in fact, available. Confirm by checking the version.
sudo docker-compose exec db psql --version
Now the error told us that “role ‘root’ does not exist”. The default command with no arguments is trying to log in as root. But if we check out the .env file, we can see we specified different values for the PostgreSQL database name, username, and password:
POSTGRES_DB=postgres POSTGRES_USER=postgres POSTGRES_PASSWORD=supersecretpassword POSTGRES_HOST=db POSTGRES_PORT=5432
Well hey then, we don’t NEED to connect from “root”. What if we specify the username with the -U flag?
sudo docker-compose exec db psql -U postgres
Then we get a new prompt:
This is the psql prompt.
If you’re still getting an error:
psql: error: FATAL: database "postgres" does not exist
It’s because psql is trying to login to the database “postgres” as the user “postgres”, but your database and user are different. You can specify them both with the -d and -U flags:
sudo docker-compose exec db psql -d postgres_db -U postgres_username
Hey congrats! Now you can use psql!
Or should I say, “Now you have to learn psql commands!”
Here’s a few to get your started:
- List all databases: \l
- View all tables in the current database: \d
- Describe a table: \d table_name
- List all available users: \du
- Quit psql: \q