dbschema 0.1.3

Define database schema's as HCL files, and generate idempotent SQL migrations
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

variable "tables" {
  default = ["users", "orders"]
}

table "users" {
  schema = "public"
  column "updated_at" {
    type = "timestamptz"
    nullable = true
  }
}

table "orders" {
  schema = "public"
  column "updated_at" {
    type = "timestamptz"
    nullable = true
  }
}

function "set_updated_at" {
  schema   = "public"
  language = "plpgsql"
  returns  = "trigger"
  replace  = true
  body = <<-SQL
    BEGIN
      NEW.updated_at = now();
      RETURN NEW;
    END;
  SQL
}

trigger "upd" {
  for_each = var.tables
  name     = "set_updated_at_${each.value}"
  schema   = "public"
  table    = each.value
  timing   = "BEFORE"
  events   = ["UPDATE"]
  level    = "ROW"
  function = "set_updated_at"
}

test "for_each_triggers" {
  assert = [
    "SELECT COUNT(*) = 2 FROM pg_trigger WHERE tgname IN ('set_updated_at_users','set_updated_at_orders')"
  ]
}

test "for_each_behavior" {
  setup = [
    "INSERT INTO public.users DEFAULT VALUES",
    "UPDATE public.users SET updated_at = updated_at",
    "INSERT INTO public.orders DEFAULT VALUES",
    "UPDATE public.orders SET updated_at = updated_at"
  ]
  assert = [
    "SELECT COUNT(*) = 1 FROM public.users WHERE updated_at IS NOT NULL",
    "SELECT COUNT(*) = 1 FROM public.orders WHERE updated_at IS NOT NULL"
  ]
}