Welcome back! At this point we have a our website talking to Rust and printing stuff to the console. We have 4 major pages now, we have the index page, the signup page, the login page and the submission page. Now we will shift gears and go to the bottom of our application stack and begin working our database and it's connection.

We will be using PostgreSQL with the diesel crate as our ORM. An ORM is an object relational mapper that will make database records look and act like objects so we can stay away from writing raw SQL.

Installing Postgres

Find your version of postgres and install it.

https://www.postgresql.org/download/

I am using Ubuntu on Windows Subsystem for Linux, so to install postgresql I used apt.

> sudo apt-get install postgresql-12

Once I had this installed I also needed the dev library for postgres.

> sudo apt-get install libpq

And finally because Windows Subsystem for Linux doesn't have a working init we'll need to manually start the postgres server each time or we'll to add it to something like our bashrc.

> sudo service postgresql start

At this point, we should have a working postgres installation. We can test our installation by checking the version. You may need to do a locate to find the actual executable.

> /usr/lib/postgresql/12/bin/postgres -V
postgres (PostgreSQL) 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)

Now that we have postgres installed, we need to do one more thing. We need to set up a username and password for postgres.

> sudo su postgres 
> psql postgres
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \password postgres
Enter new password: 

We switch to the postgres user. We then run psql using the username postgres. We then change our password for a specific user with the command \password.

We now have a postgres user set up with a password and can move to the next step.

Installing Diesel CLI

We will now install the diesel_cli crate. This will let us work with postgres from the commandline using diesel. This is a standalone binary built in rust, diesel proper will be a package we add later to our project so we, ourselves, can use the ORM.

cargo install diesel_cli --no-default-features --features postgres

We set the no-default-features flags and then we selectively install postgres. We can also set up MySQL and SQLite for diesel_cli but for this tutorial we'll stick with postgres.

If you need any help, please take a look at the diesel getting started page, it is very helpful.

https://diesel.rs/guides/getting-started/

Now we have Postgres and diesel_cli installed, but before we jump into programming directly in rust we will design our database and get it setup through the diesel cli. We're almost there!

Designing our Database

We are building a Hacker News clone so the first thing we need is ability to make posts and make comments. Comments can be replies so that will also need to be encoded somehow. Users will also need to be able to login so we need to save that information as well.

We could use the structs we created in the previous chapters as the bases for our database tables.

Let's start with the easier one, the User.

User {
    id,
    username,
    email,
    password
}

This is good enough for now, we could add some more fields to track things like when this user joined or if they want to have an about section.

Now the next table will be the Post.

Post {
    id,
    title,
    link,
    author,
    created_at
}

This is also a straightforward design. We could add more fields like number of likes and how many times someone has favorited this article.

The last table we'll build is for the Comments.

Comment {
    id
    comment,
    Post.id
    User.id,
    created_at,
    parentComment.id,
}

The comment table is a little different from the first 2 table we created. This is because the comments belong to a post and they also belong to a specific user. This is why we need to reference both of the other tables. Comments can also be replies to other comments, so we also need a way to keep track of what are replies and to who, this we can do by adding information about the parent the comment is replying to.

Comments are children of the User, the Post and can be children of the Comment table itself.

Now we have a design, lets implement it!

Setting Up Our Database

The first step will be quick, what we need to do is create a database in postgres and have it available to diesel. Diesel checks the environment variables for a postgres url or it will check the current folder for a .env file.

We will create a .env which we also be using later.

./.env

DATABASE_URL=postgres://postgres:password@localhost/hackerclone

This sets up an environment variable, DATABASE_URL, with the postgres url. "postgres:password" is our username and password that we setup earlier. "hackerclone" is the name of the database that we want to create. This database is what our User, Post and Comment tables will live in.

We can now use the diesel cli to create our database.

> diesel setup
Creating migrations directory at: hacker-clone/migrations
Creating database: hackerclone

diesel setup has created our database and it has created a migrations folder.

The migrations folder will contain the raw SQL we will write to create and modify tables. Each time we want to modify our table we will run a generate option in diesel to create a checkpoint.

Our first checkpoint will be the creation of the 3 tables we need.

hacker-clone> diesel migration generate hackerclone

We've now generated one checkpoint in our migrations folder. You should see a folder with a date and inside it will be 2 files, an up.sql and a down.sql. The up.sql is where we will write our modifications to our database and our down will be the SQL that will reverse what we did. This way we can move forwards and backwards, and by using the datestamped folders we have a history that we can go through at will!

Writing SQL

We can now get to writing some SQL!

./migrations/2020-10-18-061233_hackerclone/up.sql

-- Your SQL goes here
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    password VARCHAR NOT NULL,

    UNIQUE(username),
    UNIQUE(email)
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR NOT NULL,
    link VARCHAR,
    author INT NOT NULL,
    created_at TIMESTAMP NOT NULL,

    CONSTRAINT fk_author
        FOREIGN KEY(author)
            REFERENCES users(id)
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    comment VARCHAR NOT NULL,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    parent_comment_id INT,
    created_at TIMESTAMP NOT NULL,

    CONSTRAINT fk_post
        FOREIGN KEY(post_id)
            REFERENCES posts(id),

    CONSTRAINT fk_user
        FOREIGN KEY(user_id)
            REFERENCES users(id),

    CONSTRAINT fk_parent_comment
        FOREIGN KEY(parent_comment_id)
            REFERENCES comments(id)
);

Here we create our 3 tables and set up the fields we need and their types. The other thing to pay attention to is that we use the idea of a foreign key in our post and comments. This way we can in the future implement things like a "my comments" page or a "my posts" page easily. The comments table also has a foreign key to itself, this is because comments can be both comments to the post and replies to other comments.

To undo our 3 create commands, we will need to do some DROPs.

./migrations/2020-10-18-061233_hackerclone/down.sql

-- This file should undo anything in `up.sql`
DROP TABLE comments;
DROP TABLE posts;
DROP TABLE users;

With that, we now have a complete checkpoint for diesel. We can now do "diesel migration run" to run the SQL in our up.sql file which will create our tables with all the fields we need. We can also do "diesel migration redo" which will run the down.sql file first and then the up.sql file. This way we can modify and change things quickly.

A note here is that, on production systems we rarely remove columns. In development this is a handy feature but it is something to be careful of.

Whew! We're done! We finally have our database set up and we have our tables created. We can now move on to actually working in rust and start doing some programming.

Feel free to take a breather, I know I need one!