github.com/Elate-DevOps/migrate/v4@v4.0.12/database/cockroachdb/TUTORIAL.md (about) 1 # CockroachDB tutorial for beginners (insecure cluster) 2 3 ## Create/configure database 4 5 First, let's start a local cluster - follow step 1. and 2. from [the docs](https://www.cockroachlabs.com/docs/stable/start-a-local-cluster.html#step-1-start-the-first-node). 6 7 Once you have it, create a database. Here I am going to create a database called `example`. 8 Our user here is `cockroach`. We are not going to use a password, since it's not supported for insecure cluster. 9 ``` 10 cockroach sql --insecure --host=localhost:26257 11 ``` 12 ``` 13 CREATE DATABASE example; 14 CREATE USER IF NOT EXISTS cockroach; 15 GRANT ALL ON DATABASE example TO cockroach; 16 ``` 17 18 When using Migrate CLI we need to pass to database URL. Let's export it to a variable for convienience: 19 ``` 20 export COCKROACHDB_URL='cockroachdb://cockroach:@localhost:26257/example?sslmode=disable' 21 ``` 22 `sslmode=disable` means that the connection with our database will not be encrypted. This is needed to connect to an insecure node. 23 24 **NOTE:** Do not use COCKROACH_URL as a variable name here, it's already in use for discrete parameters and you may run into connection problems. For more info check out [docs](https://www.cockroachlabs.com/docs/stable/connection-parameters.html#connect-using-discrete-parameters). 25 26 You can find further description of database URLs [here](README.md#database-urls). 27 28 ## Create migrations 29 Let's create a table called `users`: 30 ``` 31 migrate create -ext sql -dir db/migrations -seq create_users_table 32 ``` 33 If there were no errors, we should have two files available under `db/migrations` folder: 34 - 000001_create_users_table.down.sql 35 - 000001_create_users_table.up.sql 36 37 Note the `sql` extension that we provided. 38 39 In the `.up.sql` file let's create the table: 40 ``` 41 CREATE TABLE IF NOT EXISTS example.users 42 ( 43 user_id INT PRIMARY KEY, 44 username VARCHAR (50) UNIQUE NOT NULL, 45 password VARCHAR (50) NOT NULL, 46 email VARCHAR (300) UNIQUE NOT NULL 47 ); 48 ``` 49 And in the `.down.sql` let's delete it: 50 ``` 51 DROP TABLE IF EXISTS example.users; 52 ``` 53 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) 54 55 ## Run migrations 56 ``` 57 migrate -database ${COCKROACHDB_URL} -path db/migrations up 58 ``` 59 Let's check if the table was created properly by running `cockroach sql --insecure --host=localhost:26257 -e "show columns from example.users;"`. 60 The output you are supposed to see: 61 ``` 62 column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden 63 +-------------+--------------+-------------+----------------+-----------------------+----------------------------------------------+-----------+ 64 user_id | INT8 | false | NULL | | {primary,users_username_key,users_email_key} | false 65 username | VARCHAR(50) | false | NULL | | {users_username_key} | false 66 password | VARCHAR(50) | false | NULL | | {} | false 67 email | VARCHAR(300) | false | NULL | | {users_email_key} | false 68 (4 rows) 69 ``` 70 Now let's check if running reverse migration also works: 71 ``` 72 migrate -database ${COCKROACHDB_URL} -path db/migrations down 73 ``` 74 Make sure to check if your database changed as expected in this case as well. 75 76 ## Database transactions 77 78 To show database transactions usage, let's create another set of migrations by running: 79 ``` 80 migrate create -ext sql -dir db/migrations -seq add_mood_to_users 81 ``` 82 Again, it should create for us two migrations files: 83 - 000002_add_mood_to_users.down.sql 84 - 000002_add_mood_to_users.up.sql 85 86 In Cockroach, when we want our queries to be done in a transaction, we need to wrap it with `BEGIN` and `COMMIT` commands, similar to PostgreSQL. 87 In our example, we are going to add a column to our database that can only accept enumerable values or NULL. 88 Migration up: 89 ``` 90 BEGIN; 91 92 ALTER TABLE example.users ADD COLUMN mood STRING; 93 ALTER TABLE example.users ADD CONSTRAINT check_mood CHECK (mood IN ('happy', 'sad', 'neutral')); 94 95 COMMIT; 96 ``` 97 Migration down: 98 ``` 99 ALTER TABLE example.users DROP COLUMN mood; 100 ``` 101 102 Now we can run our new migration and check the database: 103 ``` 104 migrate -database ${COCKROACHDB_URL} -path db/migrations up 105 cockroach sql --insecure --host=localhost:26257 -e "show columns from example.users;" 106 ``` 107 Expected output: 108 ``` 109 column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden 110 +-------------+--------------+-------------+----------------+-----------------------+----------------------------------------------+-----------+ 111 user_id | INT8 | false | NULL | | {primary,users_username_key,users_email_key} | false 112 username | VARCHAR(50) | false | NULL | | {users_username_key} | false 113 password | VARCHAR(50) | false | NULL | | {} | false 114 email | VARCHAR(300) | false | NULL | | {users_email_key} | false 115 mood | STRING | true | NULL | | {} | false 116 (5 rows) 117 ``` 118 119 ## Optional: Run migrations within your Go app 120 Here is a very simple app running migrations for the above configuration: 121 ``` 122 import ( 123 "log" 124 125 "github.com/Elate-DevOps/migrate/v4" 126 _ "github.com/Elate-DevOps/migrate/v4/database/cockroachdb" 127 _ "github.com/Elate-DevOps/migrate/v4/source/file" 128 ) 129 130 func main() { 131 m, err := migrate.New( 132 "file://db/migrations", 133 "cockroachdb://cockroach:@localhost:26257/example?sslmode=disable") 134 if err != nil { 135 log.Fatal(err) 136 } 137 if err := m.Up(); err != nil { 138 log.Fatal(err) 139 } 140 } 141 ``` 142 You can find details [here](README.md#use-in-your-go-project)