Let'sGoFurther - Chapter 12: User Model Setup and Registration

 

zzh@ZZHPC:/zdata/Github/greenlight$ migrate create -seq -ext=.sql -dir=./migrations create_user_table
/zdata/Github/greenlight/migrations/000004_create_user_table.up.sql
/zdata/Github/greenlight/migrations/000004_create_user_table.down.sql

 

CREATE TABLE IF NOT EXISTS user (
    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
);

 

zzh@ZZHPC:/zdata/Github/greenlight$ make migrate_up
migrate -path ./migrations -database "postgres://greenlight:greenlight@localhost/greenlight?sslmode=disable" up
error: migration failed: syntax error at or near "user" (column 28) in line 1: CREATE TABLE IF NOT EXISTS user (
    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
); (details: pq: syntax error at or near "user")
make: *** [Makefile:37: migrate_up] Error 1

CANNOT use keyword 'user' as a table name.

 

zzh@ZZHPC:/zdata/Github/greenlight$ migrate -database $GREENLIGHT_DB_DSN -path ./migrations version
4 (dirty)

 

zzh@ZZHPC:/zdata/Github/greenlight$ migrate -path ./migrations -database $GREENLIGHT_DB_DSN force 3
zzh@ZZHPC:/zdata/Github/greenlight$ migrate -path ./migrations -database $GREENLIGHT_DB_DSN version
3

 

Changed the table name from 'user' to 'users' and re-applied the migration.

 

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)

 

 

greenlight=> SELECT id, password_hash FROM users;
 id |                                                       password_hash                                                        
----+----------------------------------------------------------------------------------------------------------------------------
  1 | \x243261243132245a4d783436775466774e41346e764b344a2f794f302e695a72616f794a4b6a787a444a7a4d33475043645358546678743774724132
(1 row)

 

 

greenlight=> SELECT id, encode(password_hash, 'escape') FROM users;
 id |                            encode                            
----+--------------------------------------------------------------
  1 | $2a$12$ZMx46wTfwNA4nvK4J/yO0.iZraoyJKjxzDJzM3GPCdSXTfxt7trA2
(1 row)