-
Notifications
You must be signed in to change notification settings - Fork 1
206 lines (191 loc) · 7.65 KB
/
migrations.yaml
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
name: migrations
on:
pull_request:
permissions:
contents: read
pull-requests: read
env:
# renovate: datasource=github-releases depName=golang-migrate/migrate
MIGRATE_VERSION: '4.17.1'
# renovate: datasource=github-releases depName=superfly/flyctl versioning=semver
FLY_CLI_VERSION: '0.2.49'
# renovate: datasource=github-releases depName=sqlc-dev/sqlc
SQLC_VERSION: '1.26.0'
jobs:
detect-changes:
runs-on: ubuntu-22.04
outputs:
# Stringified JSON Array of changed services
services: ${{ steps.changes.outputs.all_changed_files }}
steps:
- uses: actions/checkout@v4
with:
fetch-depth: 0
# Collect the services, which have changes in their migrations directory.
# Available in ${{ steps.changes.outputs.all_changed_files }}
# as a json array (it's a string)
- name: Detect changes in migrations
id: changes
uses: tj-actions/changed-files@v42
with:
json: "true"
escape_json: "false"
dir_names: "true"
dir_names_exclude_current_dir: "true"
dir_names_max_depth: 1
path: "services"
files: ./*/migrations/**
# Summary for debugging
- name: Summarize
run: |
echo "services: $services" >> $GITHUB_STEP_SUMMARY
env:
services: ${{ steps.changes.outputs.all_changed_files }}
disallow-modifications:
runs-on: ubuntu-22.04
steps:
- uses: actions/checkout@v4
with:
fetch-depth: 0
- name: Detect modifications in migrations
id: changes
uses: tj-actions/changed-files@v42
with:
json: "true"
escape_json: "false"
path: "services"
files: ./*/migrations/**
- name: Don't modify existing migrations!
if: steps.changes.outputs.modified_files != '[]'
uses: helpwave/pg-fingerprint-action@main
with:
root: "services"
github_token: ${{ secrets.GITHUB_TOKEN }}
files: ${{ steps.changes.outputs.modified_files }}
migrations:
runs-on: ubuntu-22.04
needs: detect-changes
if: needs.detect-changes.outputs.services != '[]'
strategy:
matrix:
service: ${{ fromJson(needs.detect-changes.outputs.services) }}
env:
svc: ${{ matrix.service }}
FLY_DB_APP: helpwave-staging-postgres
REMOTE_DB: helpwave_staging_${{ matrix.service }}
POSTGRES_HOST: localhost
POSTGRES_PORT: 5432
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres # will be overwritten in "Pull staging data"
services:
postgres:
image: postgres:15.6
# Set health checks to wait until postgres has started
options: >-
--health-cmd pg_isready
--health-interval 10s
--health-timeout 5s
--health-retries 5
env:
POSTGRES_HOST: localhost
POSTGRES_PORT: 5432
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres
ports:
- 5432:5432
steps:
- name: Setup flyctl
uses: superfly/flyctl-actions/setup-flyctl@master
with:
version: ${{ env.FLY_CLI_VERSION }}
# ubuntu-22.04 ships with psql 14.x,
# whose pg_dump is incompatible with our postgres server (15.x)
- name: Setup postgres client
run: |
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
sudo apt update
sudo apt remove postgresql-client
sudo apt install postgresql-client-15 -y
/usr/lib/postgresql/15/bin/pg_dump --version
- name: install migrate
run: |
TEMP_DEB="$(mktemp)"
wget -O "$TEMP_DEB" 'https://github.com/golang-migrate/migrate/releases/download/v${{ env.MIGRATE_VERSION }}/migrate.linux-amd64.deb'
sudo dpkg -i "$TEMP_DEB"
rm -f "$TEMP_DEB"
- name: install sqlc
run: wget -qO- https://downloads.sqlc.dev/sqlc_${{ env.SQLC_VERSION }}_linux_amd64.tar.gz | sudo tar xvz -C /usr/bin
- name: Clone Repo
uses: actions/checkout@v4
- name: Open tunnel to staging db
run: |
flyctl proxy 5431:5432 -a $FLY_DB_APP &
sleep 5
env:
FLY_API_TOKEN: ${{ secrets.STAGING_DB_TOKEN }}
- name: Pull staging data
run: |
# service-name -> service_name
REMOTE_DB=$(echo "$REMOTE_DB" | sed "s/-/_/g")
# Build URIs
REMOTE=postgres://$STAGING_DB_USER:$STAGING_DB_PASS@localhost:5431/$REMOTE_DB
LOCAL=postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DB
# Remote -> SQL -> Local
/usr/lib/postgresql/15/bin/pg_dump -C -c --if-exists --no-comments -O -x $REMOTE -f dump.sql
/usr/lib/postgresql/15/bin/psql -q $LOCAL -f dump.sql
# Use newly created database from now on
echo "POSTGRES_DB=$REMOTE_DB" >> "$GITHUB_ENV"
env:
STAGING_DB_USER: ${{ secrets.STAGING_DB_USER }}
STAGING_DB_PASS: ${{ secrets.STAGING_DB_PASS }}
- name: Collect current version
id: collect-version
run: |
echo -n "VERSION=" > $GITHUB_OUTPUT
# for some reason, beyond my comprehension,
# the output of migrate is sent to stderr in the CI, but stdout on local
version_output=$(./migrate.sh $svc version 2>&1 | tail -n1)
# Check if the version_output is a number
if [[ $version_output =~ ^[0-9]+$ ]]; then
echo $version_output >> $GITHUB_OUTPUT
else
echo "Error: Version is not a number: $version_output" >&2
echo "0" >> $GITHUB_OUTPUT
fi
- name: Check version
run: |
./migrate.sh $svc desired
if [ "$current" -ge "$(./migrate.sh $svc desired)" ]; then
echo "Migrations must be newer than the version of staging! You probably lack behind, merge or rebase onto main first!"
exit 1
fi
env:
current: ${{ steps.collect-version.outputs.VERSION }}
- name: Run UP migrations (1/2)
run: ./migrate.sh $svc up
- name: Generate schema.sql
run: |
/usr/lib/postgresql/15/bin/pg_dump \
postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DB \
--schema-only -O > ./services/${{ matrix.service }}/schema.sql
sed 's/\(-- Dumped from database version [0-9]\+\.[0-9]\+\).*/\1/' -i ./services/${{ matrix.service }}/schema.sql
sed 's/\(-- Dumped by pg_dump version [0-9]\+\.[0-9]\+\).*/\1/' -i ./services/${{ matrix.service }}/schema.sql
- name: Generate models
run: cd services/${{ matrix.service }} && sqlc generate
- name: Verify schema.sql and models were generated properly before
run: git diff --exit-code -- services/${{ matrix.service }} || (echo "You forgot to run ./models.sh before checking in" && exit 1)
- name: Run DOWN migrations (1/2)
run: |
[ "$VERSION" -eq 0 ] && yes | ./migrate.sh $svc down || ./migrate.sh $svc goto $VERSION
env:
VERSION: ${{ steps.collect-version.outputs.VERSION }}
- name: Run UP migrations (2/2)
run: ./migrate.sh $svc up
- name: Run DOWN migrations (2/2)
run: |
[ "$VERSION" -eq 0 ] && yes | ./migrate.sh $svc down || ./migrate.sh $svc goto $VERSION
env:
VERSION: ${{ steps.collect-version.outputs.VERSION }}