Guess It

Legend

Parts of this exercise are annotated with the following icons:

• A task you MUST perform to complete the exercise
• Optional step that you may perform to make sure that everything is working correctly, or to set up additional tools that are not required but can help you, or optional extra exercises to explore the topic further
• Advanced tips on how to go further (or challenges!)
• The end of the exercise
• The architecture of the software you ran or deployed during this exercise
• Troubleshooting tips: how to fix common problems you might encounter

You will need

• Git
• Your group’s fork of Guess It on GitHub, cloned on your computer
• Node.js 22, 24 or 26
• A PostgreSQL server, version 14 or newer
• A Unix CLI

Recommended reading

• Collaborating with Git

The application is Guess It, a guess-the-number game with a leaderboard. It is written in JavaScript for Node.js and stores its games in a PostgreSQL database. All its code is in one file, server.js, and three of its database queries are missing.

Each member of the group works on their own computer, at their own pace. You share your work through GitHub: whenever a push is refused, pull first.

Later in the course, each of you will deploy this application from a fork of your own, which you will make from your group’s fork, so keep it. If your group’s application does not work by then, you will be given a working version to start from instead.

Get your group’s repository

Each member of the group needs a clone of the group’s fork of Guess It, and must be allowed to push to it.

If your group has done Hello GitHub, you already have both: go on to the next step. Otherwise, do these steps of Hello GitHub first, then come back here:

1. Form your group
2. Everyone: check your SSH key on GitHub
3. Alice: fork the repository
4. Alice: invite Bob (and Chuck)
5. Bob (and Chuck): accept the invitation
6. Everyone: configure git pull
7. Everyone: clone the fork

The rest of Hello GitHub is not needed for this exercise.

Install Node.js

Check whether you have Node.js 22 or newer:

$> node --version
v26.x.y

If the command is not found, or prints a version older than 22, install Node.js 26 by following the instructions of its download page for your system. On Windows, install it in the WSL, with the Linux instructions.

Install PostgreSQL

The application needs a PostgreSQL server, version 14 or newer. It connects to it at localhost, on port 5432: that is the address in its connection URL. On Windows, the application runs in the WSL, so the server must answer in the WSL.

The simplest way is to run PostgreSQL in a container with Docker Desktop: Run PostgreSQL with Docker Desktop. It does not interfere with any PostgreSQL server you may already have.

If you would rather not install Docker Desktop, you can use a PostgreSQL server installed on your computer instead: Check what you already have.

Run PostgreSQL with Docker Desktop

Install Docker Desktop by following the instructions for macOS or Windows, and start it. On Windows, keep the default settings: Docker Desktop uses the WSL to run its containers.

Type postgres:18 in the search bar at the top of Docker Desktop, and click Run next to the postgres:18 image. Docker Desktop downloads the image the first time.

Open the Optional settings. Name the container guessit-db, and enter 5434 as its host port:

PostgreSQL listens on port 5432 inside the container. Docker Desktop forwards port 5434 of your computer to it. Using 5434 rather than 5432 avoids a conflict with a PostgreSQL server you may already have on your computer. Choose another port number if 5434 is also taken.

Below, add three environment variables, and click Run:

Choose a password made only of letters, digits and dashes: it goes into a URL later, where other characters would have to be encoded. When the container starts, PostgreSQL creates the guessit user with that password, and the guessit database.

Check that the server answers on port 5434 (or the port you chose). Run this in your terminal on macOS, or in the WSL on Windows:

$> nc -zv 127.0.0.1 5434
Connection to 127.0.0.1 5434 port [tcp/*] succeeded!

Then connect to the database with psql, as the guessit user. It asks for the password you chose:

$> psql -h 127.0.0.1 -p 5434 -U guessit guessit
Password for user guessit:
guessit=#

-h 127.0.0.1 makes psql connect over the network, to the port given by -p, rather than look for a server running on your computer itself. -U is the user, and the last argument the database.

If psql is not found, install it: sudo apt install postgresql-client in the WSL; brew install libpq on macOS, followed by the echo 'export PATH=...' >> ~/.zshrc command that its output gives, and a new terminal.

The database is still empty. The repository’s schema.sql file would also create the user and the database, which already exist, so only its CREATE TABLE game (...) statement is needed. Copy it from the file, from CREATE TABLE to the ; that ends it, paste it at the guessit=# prompt, and press Enter:

guessit=# CREATE TABLE game (
...
);
CREATE TABLE

Quit psql with \q. Then go on to Configure and start the application: you do not need the other PostgreSQL steps.

Tip

The container keeps its data while it exists, even when it is stopped. After you restart your computer, start Docker Desktop, and start the guessit-db container again from its Containers list. Do not delete the container: its database would be deleted with it.

More information

POSTGRES_USER makes guessit the superuser of the server in the container. That server holds the Guess It database and nothing else, so the application still cannot reach the databases of your other projects.

Check what you already have

If you do not use Docker Desktop, you may already have a PostgreSQL server, installed for another course. Check before you install anything: two PostgreSQL servers on the same computer both want port 5432, and only one of them can have it.

First, check whether a server already answers on port 5432. Run this in your terminal on macOS, or in the WSL on Windows:

$> nc -zv localhost 5432
Connection to localhost (127.0.0.1) 5432 port [tcp/postgresql] succeeded!

The message ends with succeeded! if a server answers, and with Connection refused if none does. It is slightly different on macOS, but it ends the same way.

Then check which PostgreSQL servers are installed, and follow the table for your system.

In the WSL, or on Linux:

$> pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

This lists the servers installed with apt, Ubuntu’s package manager. If the command is not found, there is none.

In the second row, the server that answers was not installed with apt: it is another server, for example one installed on Windows, which some WSL network settings make visible in the WSL.

Tip

A PostgreSQL server installed on Windows itself usually does not answer in the WSL: you are in the last row. This is expected. The WSL has its own localhost, separate from Windows’. You can install another server in the WSL: the two do not interfere with each other.

On macOS:

$> ls -d /Applications/Postgres.app   # Postgres.app
$> brew list | grep postgresql        # PostgreSQL installed with Homebrew
$> ls /Library/PostgreSQL             # the installer of postgresql.org

No such file or directory, or no output, means that it is not installed. brew: command not found means that you do not have Homebrew.

Start your server

Start the server you already have:

• Installed with apt, in the WSL or on Linux:

  $> sudo service postgresql start

  Some WSL installations do not start services by themselves. If the server is stopped again after you restart your computer, start it again the same way.

• Postgres.app: open it, and click Start.

• Homebrew: start the version that brew list printed, for example postgresql@17:

  $> brew services start postgresql@17

Run nc -zv localhost 5432 again: it must now succeed. Then connect as a superuser.

Install PostgreSQL in the WSL

Follow the Install PostgreSQL section of Microsoft’s guide to databases in the WSL, up to and including sudo service postgresql start. You do not need to give the postgres user a password, as the guide then suggests.

On Linux without the WSL, follow PostgreSQL’s instructions for Ubuntu: apt install postgresql is enough.

Run nc -zv localhost 5432 again: it must now succeed. Then connect as a superuser.

Install PostgreSQL on macOS

Check whether you have Homebrew:

$> brew --version
Homebrew 5.0.0

• If you have Homebrew, install PostgreSQL 18 with it, and start it:

  $> brew install postgresql@18
  $> brew services start postgresql@18

  At the end of its output, brew install says that postgresql@18 is keg-only, and gives an echo 'export PATH=...' >> ~/.zshrc command below. Run that command, then open a new terminal: it makes the psql command available.

• Otherwise, install Postgres.app by following the steps on its home page. Do the step that configures your $PATH, even though the page says that it is optional: you will need the psql command. Then open a new terminal.

Run nc -zv localhost 5432 again: it must now succeed. Then connect as a superuser.

Connect as a superuser

To create the application’s database in the next step, you will connect to your server as a PostgreSQL superuser. The command depends on where your server comes from:

With any other server, psql asks for the password of the postgres user, which was chosen when that server was installed. If psql is not found in the WSL, install it with sudo apt install postgresql-client.

Use your command to check the version of your server:

$> sudo -u postgres psql -c 'SHOW server_version;'
            server_version
---------------------------------------
 16.10 (Ubuntu 16.10-0ubuntu0.24.04.1)
(1 row)

It must be 14 or newer. With apt, you get the version of your Ubuntu: 14 on Ubuntu 22.04, 16 on 24.04, 18 on 26.04. If yours is older, use the PostgreSQL Apt Repository to install a newer one.

Tip

sudo -u postgres psql may also print could not change directory to "/home/jde/guessit-ex": Permission denied. It runs psql as the postgres user of your system, which is not allowed in your directory. You can ignore this warning.

Postgres.app may ask whether your terminal is allowed to connect to it the first time. Allow it.

Create the database

The repository has a schema.sql file, which creates the database user, the database and its table. Open it, and change the password it gives the user, change-me-now. Choose a password made only of letters, digits and dashes: it goes into a URL in the next step, where other characters would have to be encoded. It is simpler if everyone in the group uses the same one.

Then run it with your superuser command, giving it the file with <. For example:

$> sudo -u postgres psql < schema.sql   # installed with apt
$> psql postgres < schema.sql           # Postgres.app, or Homebrew

More information

With <, your shell reads the file and passes its content to psql. With sudo -u postgres, psql runs as another user, which is not allowed to read your files, so it could not open the file itself.

The superuser you connected as can do anything on your PostgreSQL server: create and delete users, and read, change or delete every database on it, including those of your other projects. The application needs none of that. So schema.sql creates a user of its own, guessit, which owns the guessit database and nothing else, and the application connects as that user. If the application misbehaves, because of a bug or because someone has taken control of it, it can only reach its own database.

Configure and start the application

Open your guessit-ex directory in your editor. At the top of server.js, put the password you chose into DATABASE_URL, in place of change-me-now:

const DATABASE_URL =
  'postgresql://guessit:change-me-now@localhost:5432/guessit';
//                      ^^^^^^^^^^^^^
//                       change this

If you run PostgreSQL with Docker Desktop, also replace the port 5432 with 5434, the host port of your container:

const DATABASE_URL = 'postgresql://guessit:my-password@localhost:5434/guessit';

If the application is still running from the optional step of Hello GitHub, it has restarted by itself when you saved server.js. Otherwise, install its dependencies, and start it:

$> npm ci
$> npm run dev
Guess It is listening on http://localhost:3000

Open http://localhost:3000 in your browser and you should see the application running with an empty leaderboard:

Tip

npm run dev restarts the application automatically whenever you save server.js. Stop it with Ctrl-C.

The game starts, but it does not work yet: the leaderboard stays empty, guesses are not counted, and giving up does not delete the game. The queries that do these are missing.

Troubleshooting

If the leaderboard could not be loaded from the database, then you either missed or misconfigured something from the previous steps. Look at the error below Could not load the leaderboard in the terminal where the application runs, and find it in Troubleshooting. If you are still stuck, ask for help.

More information

npm ci downloads the dependencies listed in package-lock.json into a node_modules directory. The repository’s .gitignore ignores that directory, so that you do not commit it, as you learned in Hello Git.

Implement the missing queries

Three queries in server.js are missing. Each is marked with an // IMPLEMENT ME comment, with a description of what it must do above it:

• The leaderboard, on the home page: the games that have been won, fewest attempts first, and among equals the one found first. Only the top ten.
• Recording a guess: add one to the game’s attempts, and record when the number was found if the guess is right.
• Giving up: delete the game.

Each member of the group implements at least one of them. In a group of two, one member implements two.

The queries are given below. Try to write yours first if you want to practice your SQL, and use the solution to check it. Or take it as it is, if you prefer: this exercise is about working together with Git, not about SQL. Either way, the commit is yours.

The leaderboard query

const leaderboardQuery =
  'SELECT name, attempts, found_at FROM game WHERE found_at IS NOT NULL ORDER BY attempts ASC, found_at ASC LIMIT 10';

The guess query

const updateQuery = `UPDATE game SET attempts = attempts + 1, found_at = CASE WHEN secret = ${guess} THEN NOW() ELSE found_at END WHERE id = '${game.id}'`;

The give-up query

const deleteQuery = `DELETE FROM game WHERE id = '${game.id}'`;

Check that your query works in your browser, then commit it and push it to the group’s repository. Pull the others’ work as they push theirs.

By the next session, your group’s repository on GitHub must hold a working application:

• A game can be played until the number is found, or given up.
• The leaderboard lists the games that have been won, fewest attempts first.
• Each member of the group has made at least one of these commits, on their own computer, with their own name and email address.

What have I done?

You made an application work on your computer. Its code was only one of the pieces it needed: a program to run it, the libraries it depends on, and a database server, all installed and configured before the first page could load.

Along the way, you:

• Installed Node.js, and ran a PostgreSQL server in a container, or found, started or installed one.
• Created a database, and a user for the application.
• Configured the application to connect to it, and ran it.
• Implemented a query, and shared it with your group through GitHub.

An application needs more than its code. Node.js is the runtime that runs server.js. The dependencies are listed in the repository, but not stored in it: they are downloaded into a directory that Git ignores. PostgreSQL is another program altogether, with a life of its own.

PostgreSQL is a server: a process that listens on a port, 5432, and waits for connections. The application is its client, and connects to it over the network, even when both are on the same computer.

The application is a server too, listening on port 3000 for your browser. The architecture diagram below shows the chain: the browser is a client of the application, which is a client of the database.

The connection URL gathers everything a client needs to reach a database in one address: which user connects, with which password, on which host and port, and to which database.

You used the PostgreSQL superuser once, to create the application’s own user, and never again. That user owns its database and nothing else, so if the application misbehaves, the damage stops there. Give each application the access it needs, and no more.

Each member of the group changed server.js at their own pace, and the queries merged on their own: each one is on lines of its own. Whenever a push was refused, you pulled first. Each commit carries the name its author configured, which is how your group’s history shows who implemented what.

Architecture

This is a simplified architecture of the main running processes and communication flow at the end of this exercise.

Troubleshooting

Here are a few tips about problems you may encounter during this exercise. For problems with Git and GitHub, see the troubleshooting of Hello GitHub.

libatomic.so.1: cannot open shared object file

In the WSL, node fails right after you install it, for example with nvm as its download page suggests:

$> node --version
node: error while loading shared libraries: libatomic.so.1: cannot open shared object file: No such file or directory

Node.js needs the libatomic1 library, which some WSL installations of Ubuntu do not have. Install it, then run node --version again:

$> sudo apt install -y libatomic1

password authentication failed for user "guessit"

The password in the DATABASE_URL at the top of server.js is not the one you set in schema.sql when you created the database. Put the same password in both.

If they are already the same, you may have run schema.sql more than once: see role "guessit" already exists.

role "guessit" already exists

psql prints this error, with database "guessit" already exists and relation "game" already exists, when you run schema.sql a second time. The first run created the user, the database and the table. The second run changed nothing: the user still has the password of the first run, even if you have changed it in schema.sql since.

Give the user the password of your DATABASE_URL with your superuser command, for example:

# installed with apt
$> sudo -u postgres psql -c "ALTER USER guessit WITH PASSWORD 'my-new-password';"
ALTER ROLE

# Postgres.app, or Homebrew
$> psql postgres -c "ALTER USER guessit WITH PASSWORD 'my-new-password';"
ALTER ROLE

connect ECONNREFUSED

The home page says that the leaderboard could not be loaded, and the terminal where the application runs shows ECONNREFUSED in the error below Could not load the leaderboard. The application cannot reach PostgreSQL on port 5432: your PostgreSQL server is either not running or is not reachable on that port. Check what you have again, and start your server if it is stopped.

With Docker Desktop, check that Docker Desktop and the guessit-db container are running, and that the DATABASE_URL in server.js uses port 5434.

psql: command not found

On macOS, the psql command of Postgres.app and of Homebrew’s PostgreSQL is not available until you configure your $PATH, as described in Install PostgreSQL on macOS. Open a new terminal after doing it.

Peer authentication failed for user "postgres"

Your server was installed with apt. Its postgres user can only connect from the postgres user of your system: use sudo -u postgres psql, not psql -U postgres.

role "jde" does not exist

Your server was installed with apt, and you ran psql as yourself. Use sudo -u postgres psql, as in Connect as a superuser.

Your server no longer starts after you restart your Mac

After you restart your Mac, Postgres.app says that port 5432 is already in use, or brew services list shows an error for your PostgreSQL. Or nc succeeds, but you can no longer connect as a superuser.

You have an older PostgreSQL server, from the installer of postgresql.org, which started first and took port 5432. Uninstall it with the uninstaller in its /Library/PostgreSQL/<version>/ directory, then start your server again.