Now let’s put all that theory into practice. We will start by setting up the Postify database using PostgreSQL.
<aside> 💡
Why a new (to us) database like PostgreSQL, instead of using SQLite again?
<aside> 💭
If you don’t have Docker installed yet, please revisit Docker setup from the core program.
</aside>
First, install PostgresSQL on your machine. We recommend using Docker for this:
docker run --name my-postgres \\
-e POSTGRES_USER=hyfuser \\
-e POSTGRES_PASSWORD=hyfpassword \\
-e POSTGRES_DB=postgres \\
-p 5432:5432 \\
-d postgres:latest
Verify that the container is running with docker ps -a :
If you followed the steps correctly, your local PostgreSQL server should be running with the following settings:
my-postgreslocalhost:5432hyfuserhyfpasswordIf any of these failed, please try to troubleshoot the faults. The elaborate Postgres Docker or the official Postgresql documentation may help.
psql<aside> 💭
psql is the PostgreSQL version of the sqlite3 command we learned in Week 12 - Databases.
</aside>
Connect to your database using the powerful psql command-line tool:
docker exec -it my-postgres psql -U hyfuser -d postgres
You should see the psql prompt:
psql (17.6 (Debian 17.6-1.pgdg13+1))
Type "help" for help.
postgres=#
<aside> ⚠️
Because we run our Postgres server inside a Docker container, the psql command is not installed on our host machine, this is why we use docker exec to run psql from within the container.
</aside>
Next, type the command \\list or \\l to show all databases:
You'll see a few system databases that are managed by the PostgreSQL server - you can ignore these.
Next, we are going to create your first database. In your terminal, the psql interface should now be opened by the previous command, showing something like postgres=# . Creating a new database is simple. Let’s create one and immediately connect to it:
postgres=# CREATE DATABASE "POSTIFY";
CREATE DATABASE
postgres=# \\connect "POSTIFY";
You are now connected to database "POSTIFY" as user "postgres".
POSTIFY=#
In Week 12 of the core program you first learned about Data Definition Language (DDL), and how to create tables using SQL. Next we will write and run a SQL script in Postgres to set up some tables for the Postify app.
In your favorite editor, create a file, and name it e.g. setup-postify.sql. Put all DDL necessary to create the 3NF-normalized tables in there: include foreign keys, primary keys, as well as tables and columns. Make sure to use the same spelling as in the examples above! Specifically pay attention to the casing.
Note that Postgres has different data types than sqlite3 that you’ve used before. Postgres has more detailed definitions, for instance to choose how many bytes you would like to store an integer in.
When you’ve finished the script, you can run the scripts in Postgres using the \\i command like this:
postgres=# \\i /path/to/your/file/setup-postify.sql
If you get file permission issues (meaning Postgres cannot open your file), or if you’re running from docker, it might be easier to just run the statements directly. Once you’ve set up some tables, you can verify it worked with some of the following commands:
| Goal | psql shorthand | SQL alternative |
|---|---|---|
| List databases | \\l |
SELECT datname FROM pg_database; |
| Switch database | \\c dbname |
— |
| List schemas | \\dn |
SELECT schema_name FROM information_schema.schemata; |
| List tables | \\dt |
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; |
| Inspect a table | \\d tablename |
Query information_schema.columns |
| Inspect + indexes | \\d+ tablename |
Query pg_indexes |
| Exit | \\q |
— |
Next, let’s insert some dummy data. You can use the following script (which assumes you spelled the tables and columns in the same way):
Now you should be able to see some data in your tables, for instance:
postgres=# select * from albums;
--- Result;
album_id | artist_id | label_id | album_title | album_year
----------+-----------+----------+-------------------------------+------------
1 | 1 | 1 | Mama I Made It | 2025
2 | 2 | 2 | Rémi | 2017
(...)
(33 rows)
Go ahead and look around your database a little bit! Can you show all tables in the database? Query the other tables? Insert or update data?
Instead of using psql and command line, there are many GUI tools to connect and control your Postgres server. Feel free to choose the one that you like the most.
If you used DBeaver in the core program, it also supports PostgreSQL servers with the already familiar interface.
pgAdmin is a free and open-source web-based administration tool for PostgreSQL servers. It provides a comprehensive interface for database management.
Database Client is a free VS Code extension that can connect to many different database providers, including PostgreSQL. It allows you to manage databases directly from your code editor.
DataGrip is a powerful database management IDE developed by JetBrains. It supports many database providers including PostgreSQL. It is free for non commercial use.
The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*
Built with ❤️ by the HackYourFuture community · Thank you, contributors
Found a mistake or have a suggestion? Let us know in the feedback form.