Setting up the users database table

Let’s begin by creating a new users table in our database. If you’re following along, use the migrate tool to generate a new pair of SQL migration files:

$ migrate create -seq -ext=.sql -dir=./migrations create_users_table
/home/alex/Projects/greenlight/migrations/000004_create_users_table.up.sql
/home/alex/Projects/greenlight/migrations/000004_create_users_table.down.sql

And then add the following SQL statements to the ‘up’ and ‘down’ files respectively:

CREATE TABLE IF NOT EXISTS users (
    id bigserial PRIMARY KEY,
    created_at timestamp(0) with time zone NOT NULL DEFAULT NOW(),
    name text NOT NULL,
    email citext UNIQUE NOT NULL,
    password_hash bytea NOT NULL,
    activated bool NOT NULL,
    version integer NOT NULL DEFAULT 1
);

DROP TABLE IF EXISTS users;

There are a few interesting things about this CREATE TABLE statement that I’d like to quickly explain:

1. The email column has the type citext (case-insensitive text). This type stores text data exactly as it is entered — without changing the case in any way — but comparisons against the data are always case-insensitive… including lookups on associated indexes.

2. We’ve also got a UNIQUE constraint on the email column. Combined with the citext type, this means that no two rows in the database can have the same email value — even if they have different cases. This essentially enforces a database-level business rule that no two users should exist with the same email address.

3. The password_hash column has the type bytea (binary string). In this column we’ll store a one-way hash of the user’s password generated using bcrypt — not the password itself in plaintext form.

4. The activated column stores a boolean value to denote whether a user account is ‘active’ or not. We will set this to false by default when creating a new user, and require the user to confirm their email address before we set it to true.

5. We’ve also included a version number column, which we will increment each time a user record is updated. This will allow us to use optimistic locking to prevent race conditions when updating user records, in the same way we did with movies earlier in the book.

OK, let’s execute the ‘up’ migration:

$ migrate -path=./migrations -database=$GREENLIGHT_DB_DSN up
4/u create_users_table (62.43511ms)

And then you should be able to connect to your database and verify that the new users table has been created, as expected:

$ psql $GREENLIGHT_DB_DSN 
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

greenlight=> \d users
                                          Table "public.users"
    Column     |            Type             | Collation | Nullable |              Default              
---------------+-----------------------------+-----------+----------+-----------------------------------
 id            | bigint                      |           | not null | nextval('users_id_seq'::regclass)
 created_at    | timestamp(0) with time zone |           | not null | now()
 name          | text                        |           | not null | 
 email         | citext                      |           | not null | 
 password_hash | bytea                       |           | not null | 
 activated     | boolean                     |           | not null | 
 version       | integer                     |           | not null | 1
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

One important thing to point out here: the UNIQUE constraint on our email column has automatically been assigned the name users_email_key. This will become relevant in the next chapter, when we need to handle any errors caused by a user registering twice with the same email address.