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)