github.com/solvedata/migrate/v4@v4.8.7-0.20201127053940-c9fba4ce569f/database/postgres/TUTORIAL.md (about)

     1  # PostgreSQL tutorial for beginners
     2  
     3  ## Create/configure database
     4  
     5  For the purpose of this tutorial let's create PostgreSQL database called `example`.
     6  Our user here is `postgres`, password `password`, and host is `localhost`.
     7  ```
     8  psql -h localhost -U postgres -w -c "create database example;"
     9  ```
    10  When using Migrate CLI we need to pass to database URL. Let's export it to a variable for convienience:
    11  ```
    12  export POSTGRESQL_URL=postgres://postgres:password@localhost:5432/example?sslmode=disable
    13  ```
    14  `sslmode=disable` means that the connection with our database will not be encrypted. Enabling it is left as an exercise.
    15  
    16  You can find further description of database URLs [here](README.md#database-urls).
    17  
    18  ## Create migrations
    19  Let's create table called `users`:
    20  ```
    21  migrate create -ext sql -dir db/migrations -seq create_users_table
    22  ```
    23  If there were no errors, we should have two files available under `db/migrations` folder:
    24  - 000001_create_users_table.down.sql
    25  - 000001_create_users_table.up.sql
    26  
    27  Note the `sql` extension that we provided.
    28  
    29  In the `.up.sql` file let's create the table:
    30  ```
    31  CREATE TABLE IF NOT EXISTS users(
    32     user_id serial PRIMARY KEY,
    33     username VARCHAR (50) UNIQUE NOT NULL,
    34     password VARCHAR (50) NOT NULL,
    35     email VARCHAR (300) UNIQUE NOT NULL
    36  );
    37  ```
    38  And in the `.down.sql` let's delete it:
    39  ```
    40  DROP TABLE IF EXISTS users;
    41  ```
    42  By adding `IF EXISTS/IF NOT EXISTS` we are making migrations idempotent - you can read more about idempotency in [getting started](GETTING_STARTED.md#create-migrations)
    43  
    44  ## Run migrations
    45  ```
    46  migrate -database ${POSTGRESQL_URL} -path db/migrations up
    47  ```
    48  Let's check if the table was created properly by running `psql example -c "\d users"`.
    49  The output you are supposed to see:
    50  ```
    51                                      Table "public.users"
    52    Column  |          Type          |                        Modifiers                        
    53  ----------+------------------------+---------------------------------------------------------
    54   user_id  | integer                | not null default nextval('users_user_id_seq'::regclass)
    55   username | character varying(50)  | not null
    56   password | character varying(50)  | not null
    57   email    | character varying(300) | not null
    58  Indexes:
    59      "users_pkey" PRIMARY KEY, btree (user_id)
    60      "users_email_key" UNIQUE CONSTRAINT, btree (email)
    61      "users_username_key" UNIQUE CONSTRAINT, btree (username)
    62  ```
    63  Great! Now let's check if running reverse migration also works:
    64  ```
    65  migrate -database ${POSTGRESQL_URL} -path db/migrations down
    66  ```
    67  Make sure to check if your database changed as expected in this case as well.
    68  
    69  ## Database transactions
    70  
    71  To show database transactions usage, let's create another set of migrations by running:
    72  ```
    73  migrate create -ext sql -dir db/migrations -seq add_mood_to_users
    74  ```
    75  Again, it should create for us two migrations files:
    76  - 000002_add_mood_to_users.down.sql
    77  - 000002_add_mood_to_users.up.sql
    78  
    79  In Postgres, when we want our queries to be done in a transaction, we need to wrap it with `BEGIN` and `COMMIT` commands.
    80  In our example, we are going to add a column to our database that can only accept enumerable values or NULL.
    81  Migration up:
    82  ```
    83  BEGIN;
    84  
    85  CREATE TYPE enum_mood AS ENUM (
    86  	'happy',
    87  	'sad',
    88  	'neutral'
    89  );
    90  ALTER TABLE users ADD COLUMN mood enum_mood;
    91  
    92  COMMIT;
    93  ```
    94  Migration down:
    95  ```
    96  BEGIN;
    97  
    98  ALTER TABLE users DROP COLUMN mood;
    99  DROP TYPE enum_mood;
   100  
   101  COMMIT;
   102  ```
   103  
   104  Now we can run our new migration and check the database:
   105  ```
   106  migrate -database ${POSTGRESQL_URL} -path db/migrations up
   107  psql example -c "\d users"
   108  ```
   109  Expected output:
   110  ```
   111                                      Table "public.users"
   112    Column  |          Type          |                        Modifiers                        
   113  ----------+------------------------+---------------------------------------------------------
   114   user_id  | integer                | not null default nextval('users_user_id_seq'::regclass)
   115   username | character varying(50)  | not null
   116   password | character varying(50)  | not null
   117   email    | character varying(300) | not null
   118   mood     | enum_mood              | 
   119  Indexes:
   120      "users_pkey" PRIMARY KEY, btree (user_id)
   121      "users_email_key" UNIQUE CONSTRAINT, btree (email)
   122      "users_username_key" UNIQUE CONSTRAINT, btree (username)
   123  ```
   124  
   125  ## Optional: Run migrations within your Go app
   126  Here is a very simple app running migrations for the above configuration:
   127  ```
   128  import (
   129  	"log"
   130  
   131  	"github.com/solvedata/migrate/v4"
   132  	_ "github.com/solvedata/migrate/v4/database/postgres"
   133  	_ "github.com/solvedata/migrate/v4/source/file"
   134  )
   135  
   136  func main() {
   137  	m, err := migrate.New(
   138  		"file://db/migrations",
   139  		"postgres://postgres:postgres@localhost:5432/example?sslmode=disable")
   140  	if err != nil {
   141  		log.Fatal(err)
   142  	}
   143  	if err := m.Up(); err != nil {
   144  		log.Fatal(err)
   145  	}
   146  }
   147  ```
   148  You can find details [here](README.md#use-in-your-go-project)