1# PostgreSQL tutorial for beginners
2
3## Create/configure database
4
5For the purpose of this tutorial let's create PostgreSQL database called `example`.
6Our user here is `postgres`, password `password`, and host is `localhost`.
7```
8psql -h localhost -U postgres -w -c "create database example;"
9```
10When using Migrate CLI we need to pass to database URL. Let's export it to a variable for convenience:
11```
12export 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
16You can find further description of database URLs [here](README.md#database-urls).
17
18## Create migrations
19Let's create table called `users`:
20```
21migrate create -ext sql -dir db/migrations -seq create_users_table
22```
23If 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
27Note the `sql` extension that we provided.
28
29In the `.up.sql` file let's create the table:
30```
31CREATE 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```
38And in the `.down.sql` let's delete it:
39```
40DROP TABLE IF EXISTS users;
41```
42By 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```
46migrate -database ${POSTGRESQL_URL} -path db/migrations up
47```
48Let's check if the table was created properly by running `psql example -c "\d users"`.
49The 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
58Indexes:
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```
63Great! Now let's check if running reverse migration also works:
64```
65migrate -database ${POSTGRESQL_URL} -path db/migrations down
66```
67Make sure to check if your database changed as expected in this case as well.
68
69## Database transactions
70
71To show database transactions usage, let's create another set of migrations by running:
72```
73migrate create -ext sql -dir db/migrations -seq add_mood_to_users
74```
75Again, 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
79In Postgres, when we want our queries to be done in a transaction, we need to wrap it with `BEGIN` and `COMMIT` commands.
80In our example, we are going to add a column to our database that can only accept enumerable values or NULL.
81Migration up:
82```
83BEGIN;
84
85CREATE TYPE enum_mood AS ENUM (
86 'happy',
87 'sad',
88 'neutral'
89);
90ALTER TABLE users ADD COLUMN mood enum_mood;
91
92COMMIT;
93```
94Migration down:
95```
96BEGIN;
97
98ALTER TABLE users DROP COLUMN mood;
99DROP TYPE enum_mood;
100
101COMMIT;
102```
103
104Now we can run our new migration and check the database:
105```
106migrate -database ${POSTGRESQL_URL} -path db/migrations up
107psql example -c "\d users"
108```
109Expected 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 |
119Indexes:
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
126Here is a very simple app running migrations for the above configuration:
127```
128import (
129 "log"
130
131 "github.com/golang-migrate/migrate/v4"
132 _ "github.com/golang-migrate/migrate/v4/database/postgres"
133 _ "github.com/golang-migrate/migrate/v4/source/file"
134)
135
136func 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```
148You can find details [here](README.md#use-in-your-go-project)
149
150## Fix issue where migrations run twice
151
152When the schema and role names are the same, you might run into issues if you create this schema using migrations.
153This is caused by the fact that the [default `search_path`](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) is `"$user", public`.
154In the first run (with an empty database) the migrate table is created in `public`.
155When the migrations create the `$user` schema, the next run will store (a new) migrate table in this schema (due to order of schemas in `search_path`) and tries to apply all migrations again (most likely failing).
156
157To solve this you need to change the default `search_path` by removing the `$user` component, so the migrate table is always stored in the (available) `public` schema.
158This can be done using the [`search_path` query parameter in the URL](https://github.com/jexia/migrate/blob/fix-postgres-version-table/database/postgres/README.md#postgres).
159
160For example to force the migrations table in the public schema you can use:
161```
162export POSTGRESQL_URL='postgres://postgres:password@localhost:5432/example?sslmode=disable&search_path=public'
163```
164
165Note that you need to explicitly add the schema names to the table names in your migrations when you to modify the tables of the non-public schema.
166
167Alternatively you can add the non-public schema manually (before applying the migrations) if that is possible in your case and let the tool store the migrations table in this schema as well.
View as plain text