Setting up the database

We need to prepare and create the database using the psql client tool. The SQL database script can be found inside schema.sql under the db folder in the GitHub repository, and we are going to use this to create all the relevant tables inside Postgres.

Change the directory to chapter1 and run the Postgres database using the following Docker command:

docker run --name test-postgres -e POSTGRES_PASSWORD=mysecretpassword -v $(pwd):/usr/share/chapter1 -p 5432:5432 postgres

Once postgres is running, use the following command to enter into psql:

docker exec -it test-postgres psql -h localhost -p 5432 -U postgres -d postgres

Once inside the psql command, run the following:

\i /usr/share/chapter1/db/schema.sql

This will instruct psql to execute the commands inside schema.sql, and on completion, you will see the following output:

postgres=# \i /usr/share/chapter1/db/schema.sql
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

To reconfirm that everything is set up correctly, use the following command (do not forget to include the dot after gowebapp):

\dt gowebapp.* 

You should see the following output:

postgres=# \dt gowebapp.*
            List of relations
  Schema  |   Name    | Type  |  Owner
----------+-----------+-------+----------
 gowebapp | exercises | table | postgres
 gowebapp | images    | table | postgres
 gowebapp | sets      | table | postgres
 gowebapp | users     | table | postgres
 gowebapp | workouts  | table | postgres
(5 rows)

Now that we have completed setting up our database, we are ready to move to the next section, where we will be setting up sqlc to generate the Go files.