1# CockroachDB tutorial for beginners (insecure cluster)
2
3## Create/configure database
4
5First, 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
7Once you have it, create a database. Here I am going to create a database called `example`.
8Our user here is `cockroach`. We are not going to use a password, since it's not supported for insecure cluster.
9```
10cockroach sql --insecure --host=localhost:26257
11```
12```
13CREATE DATABASE example;
14CREATE USER IF NOT EXISTS cockroach;
15GRANT ALL ON DATABASE example TO cockroach;
16```
17
18When using Migrate CLI we need to pass to database URL. Let's export it to a variable for convienience:
19```
20export 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
26You can find further description of database URLs [here](README.md#database-urls).
27
28## Create migrations
29Let's create a table called `users`:
30```
31migrate create -ext sql -dir db/migrations -seq create_users_table
32```
33If 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
37Note the `sql` extension that we provided.
38
39In the `.up.sql` file let's create the table:
40```
41CREATE 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```
49And in the `.down.sql` let's delete it:
50```
51DROP TABLE IF EXISTS example.users;
52```
53By 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```
57migrate -database ${COCKROACHDB_URL} -path db/migrations up
58```
59Let's check if the table was created properly by running `cockroach sql --insecure --host=localhost:26257 -e "show columns from example.users;"`.
60The 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```
70Now let's check if running reverse migration also works:
71```
72migrate -database ${COCKROACHDB_URL} -path db/migrations down
73```
74Make sure to check if your database changed as expected in this case as well.
75
76## Database transactions
77
78To show database transactions usage, let's create another set of migrations by running:
79```
80migrate create -ext sql -dir db/migrations -seq add_mood_to_users
81```
82Again, 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
86In 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.
87In our example, we are going to add a column to our database that can only accept enumerable values or NULL.
88Migration up:
89```
90BEGIN;
91
92ALTER TABLE example.users ADD COLUMN mood STRING;
93ALTER TABLE example.users ADD CONSTRAINT check_mood CHECK (mood IN ('happy', 'sad', 'neutral'));
94
95COMMIT;
96```
97Migration down:
98```
99ALTER TABLE example.users DROP COLUMN mood;
100```
101
102Now we can run our new migration and check the database:
103```
104migrate -database ${COCKROACHDB_URL} -path db/migrations up
105cockroach sql --insecure --host=localhost:26257 -e "show columns from example.users;"
106```
107Expected 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
120Here is a very simple app running migrations for the above configuration:
121```
122import (
123 "log"
124
125 "github.com/golang-migrate/migrate/v4"
126 _ "github.com/golang-migrate/migrate/v4/database/cockroachdb"
127 _ "github.com/golang-migrate/migrate/v4/source/file"
128)
129
130func 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```
142You can find details [here](README.md#use-in-your-go-project)View as plain text