Migration
Migration resource is a single migrate operation.
1migration "<reference>" {
2 version = "<version>"
3 transaction = true
4
5 up {
6 sql = "..."
7 }
8
9 down {
10 sql = "..."
11 }
12}
<reference>- is a migration reference name to use when connecting to other resources<version>- name that will be used to identify migration in the database, can only be digits, alphanumeric characters and underscoresup/down- migration direction, contains SQL code to be executedtransaction (optional)- specifies whether to run migration in a transaction (default:true)
DSL
Up/Down migration can use built-in DSL for Data Definition Language.
DSL order matters, code will run in that order (
sql attribute order also matters)Supported DDL:
Example
1migration "create_tenants" {
2 version = "20060102150405"
3
4 up {
5 sql = "CREATE TABLE tenants(name VARCHAR PRIMARY KEY)"
6 }
7
8 down {
9 sql = "DROP TABLE tenants"
10 }
11}
DSL example
1migration "create_categories" {
2 version = "v1"
3
4 up {
5 create_table "categories" {
6 column "id" "bigint" {}
7 column "name" "varchar" { null = false }
8
9 primary_key { columns = ["id"] }
10 }
11 }
12
13 down {
14 drop_table "categories" {}
15 }
16}
17
18migration "create_animals" {
19 version = "v2"
20
21 up {
22 create_table "animals" {
23 # "id" bigint GENERATED ALWAYS AS IDENTITY
24 column "id" "bigint" {
25 identity {}
26 }
27
28 column "name" "varchar" { null = true }
29
30 column "extinct" "boolean" {
31 null = false
32 default = "TRUE"
33 }
34
35 column "weight_kg" "int" { null = false }
36
37 # "weight_g" int GENERATED ALWAYS AS (weight_kg * 1000) STORED
38 column "weight_g" "int" {
39 generated {
40 as = "weight_kg * 1000"
41 }
42 }
43
44 column "category_id" "bigint" {
45 null = false
46 }
47
48 unique {
49 columns = ["name"]
50 include = ["weight_kg"]
51 }
52
53 primary_key {
54 columns = ["id"]
55 include = ["name"]
56 }
57
58 # CONSTRAINT "ensure_positive_weight" CHECK (weight_kg > 0)
59 check "ensure_positive_weight" {
60 expression = "weight_kg > 0"
61 }
62
63 foreign_key {
64 columns = ["category_id"]
65
66 references "categories" {
67 columns = ["id"]
68
69 on_delete = "cascade"
70 on_update = "cascade"
71 }
72 }
73 }
74 }
75
76 down {
77 drop_table "animals" {}
78 }
79}
80
81migration_set "animals" {
82 migrations = [
83 migration.create_categories,
84 migration.create_animals
85 ]
86}
DSL index example
1migration "create_animals" {
2 version = "v1"
3 transaction = false
4
5 up {
6 create_table "animals" {
7 column "id" "bigint" {}
8
9 column "name" "varchar" {}
10
11 column "extinct" "boolean" {}
12
13 column "weight_kg" "int" {}
14 }
15
16 # CREATE UNIQUE INDEX "idx_uniq_name" ON "animals" USING btree ("name") INCLUDE ("weight_kg")
17 create_index "animals" "idx_uniq_name" {
18 unique = true
19 columns = ["name"]
20 using = "btree"
21 include = ["weight_kg"]
22 }
23
24 # CREATE INDEX CONCURRENTLY "idx_heavy_animals" ON "animals" ("weight_kg") WHERE (weight_kg > 5000)
25 create_index "animals" "idx_heavy_animals" {
26 columns = ["weight_kg"]
27 where = "weight_kg > 5000"
28 concurrently = true
29 }
30 }
31
32 down {
33 drop_index "public.idx_uniq_name" {
34 cascade = true
35 }
36
37 drop_index "idx_heavy_animals" {
38 concurrently = true
39 }
40
41 drop_table "animals" {}
42 }
43}
44
45migration_set "animals" {
46 migrations = [
47 migration.create_animals
48 ]
49}