Go(lang) Todo App (Part 1) — Database setup

Let’s work on setting up a TODO app with Go. We’re going to do it with a PostgreSQL database on a Debian 12 Linux machine. Let’s start by setting up PostgreSQL.

To install PostgreSQL we need to run the following instructions on a terminal:

sudo apt update
sudo apt upgrade
sudo apt install postgresql

The first instruction is to sync the list of packages we have on the local computer to the ones available in the internet. Second one is to set our local computer up to date and the third is to actually install PostgreSQL.

We’re going to use a database user to access the data. On a terminal we need to run the following commands:

sudo su - postgres
createdb todos
createuser todos
psql

The first command is to switch the user we’re currently using the terminal with to postgres. This user has database super user privileges, and we need for our next steps. The next command creates the todos database. The third command creates the todos user. This user does not have privileges on our todos database yet, so we need to grant them. And that’s what our last command is for: psql. This command allows us to connect to the database and run commands inside of it. Both the createdb and createuser commands run commands inside the database to complete this common tasks, and because they’re so common, PostgreSQL’s team decided to create this shortcut commands. So now we’re inside the database and we need to run the following SQL Commands to grant our todos user privileges over our newly created todos database and set the user password.

GRANT ALL PRIVILEGES ON DATABASE todos TO todos;
ALTER DATABASE todos OWNER TO todos;
SET password_encryption = 'scram-sha-256';
ALTER USER todos with password 'T0d05!';

Ok, we’ve created our database and a user to manage it. Now we need to make sure that we PostgreSQL allows connections to our database. PostgreSQL configures this in it’s pg_hba.conf file. To find this file we execute this command after the grant all privileges command:

SHOW hba_file;

This will give us the full path to this file. To open the file as root in an editor of your choice on the same terminal please enter the exit command 2 times (please note that this file can be different on your system):

sudo vim /var/lib/postgresql/15/main/pg_hba.conf

And make sure you have this line:

host    all             all             127.0.0.1/32            scram-sha-256

Restart PostgreSQL (or the machine)

sudo service postgresql restart

Let’s create the go(lang) project folder by opening a terminal and running the following commands (if you haven’t installed Go on your system here’s a nice tutorial):

cd Documents
mkdir todos
cd todos
go mod init github.com/jdmr/todos
code .

With these instructions we’re initializing our go project to use modules. Notice the url as part of of the go mod init command. This is the public url to get this project’s code. You should switch the jdmr part to whatever your user in GitHub is. The last instruction is to open VSCode with that folder as its home.

Once your VSCode is open, make sure you have the SQL Tools extension by Matheus Teixeira. And add add a PostgreSQL connection

Add connection name, server address, username and change the use password dropdown to save as plaintext in settings and then in password type your plain password (in my case is T0d05!). Then at the bottom click the TEST CONNECTION button, and a Successfully connected! message should show up (if it doesn’t, did you remember to save the pg_hba.conf file? did you restart PostgreSQL database service?). Then just click the SAVE CONNECTION button.

Let’s create the table by making a new file called todos.sql with the following:

drop table if exists todos;
create table todos (
    id serial primary key,
    title varchar(255) not null,
    completed boolean default false,
    created_at timestamp not null,
    updated_at timestamp not null default now()
);

And click the Run on active connection link that shows up above the SQL script.

Our database is ready!