{{ message }}
Repository navigation
Expand file tree
/
Copy pathcheck-migrations-safety.test.ts
More file actions
282 lines (243 loc) · 10.7 KB
/
Copy pathcheck-migrations-safety.test.ts
File metadata and controls
282 lines (243 loc) · 10.7 KB
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
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
import { execFile } from 'node:child_process'
import { mkdtempSync, rmSync, writeFileSync } from 'node:fs'
import { tmpdir } from 'node:os'
import path from 'node:path'
import { promisify } from 'node:util'
import { afterEach, describe, expect, it } from 'vitest'
const execFileAsync = promisify(execFile)
const ROOT = path.resolve(import.meta.dirname, '..')
const SCRIPT = path.join(ROOT, 'scripts/check-migrations-safety.ts')
async function runAudit(
...args: string[]
): Promise<{ code: number; stdout: string; stderr: string }> {
try {
const { stdout, stderr } = await execFileAsync('bun', ['run', SCRIPT, ...args], { cwd: ROOT })
return { code: 0, stdout, stderr }
} catch (error) {
const failure = error as { code?: number; stdout?: string; stderr?: string }
return { code: failure.code ?? 1, stdout: failure.stdout ?? '', stderr: failure.stderr ?? '' }
}
}
const tempDirs: string[] = []
/** A scratch migrations directory holding one file. */
function migrationsDir(sql: string): string {
const dir = mkdtempSync(path.join(tmpdir(), 'migration-owner-'))
tempDirs.push(dir)
writeFileSync(path.join(dir, '0001_seq.sql'), sql)
return dir
}
afterEach(() => {
for (const dir of tempDirs.splice(0)) rmSync(dir, { recursive: true, force: true })
})
describe('migration safety audit', () => {
/**
* The regression this guards: an unresolvable base ref made `git diff` fail, the
* failure was read as an empty file list, and the audit printed
* `✓ No new migrations to check` and exited 0 — green on a branch it never read.
* CI reached that state whenever its `git fetch ... || true` swallowed a failure.
*/
it('fails loudly when the base ref cannot be diffed', async () => {
const { code, stderr } = await runAudit('origin/branch-that-does-not-exist')
expect(code).toBe(1)
expect(stderr).toContain('could not run')
expect(stderr).not.toContain('No new migrations to check')
}, 30_000)
/**
* Production keeps tables owned by an older role than the one that runs migrations, so linking a
* new sequence to an existing table passed CI and staging and failed only the production
* migration. Linking it to a table the same migration creates stays safe.
*/
it('refuses ownership-dependent statements on existing tables', async () => {
const dir = migrationsDir(
[
'CREATE SEQUENCE IF NOT EXISTS "public"."calls_seq";--> statement-breakpoint',
'ALTER SEQUENCE "public"."calls_seq" OWNED BY "calls"."position";--> statement-breakpoint',
'ALTER TABLE "calls" OWNER TO "postgres";',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain('sequence-owned-by-existing-table')
expect(`${stdout}${stderr}`).toContain('owner-to')
}, 30_000)
it.each(['"jobs"', '"public"."jobs"'])(
'allows a sequence owned by a table created in the same migration as %s',
async (table) => {
const dir = migrationsDir(
[
`CREATE TABLE ${table} ("id" text PRIMARY KEY, "position" bigint);--> statement-breakpoint`,
'CREATE SEQUENCE IF NOT EXISTS "public"."jobs_seq";--> statement-breakpoint',
'ALTER SEQUENCE "public"."jobs_seq" OWNED BY "jobs"."position";',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(`${stdout}${stderr}`).not.toContain('sequence-owned-by-existing-table')
expect(code).toBe(0)
},
30_000
)
it('matches a schema-qualified OWNED BY to an unqualified create in public', async () => {
const dir = migrationsDir(
[
'CREATE TABLE "jobs" ("id" text PRIMARY KEY, "position" bigint);--> statement-breakpoint',
'CREATE SEQUENCE IF NOT EXISTS "public"."jobs_seq";--> statement-breakpoint',
'ALTER SEQUENCE "public"."jobs_seq" OWNED BY "public"."jobs"."position";',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(`${stdout}${stderr}`).not.toContain('sequence-owned-by-existing-table')
expect(code).toBe(0)
}, 30_000)
it.each([
[
'a conditional create, which may find the table already there',
'CREATE TABLE IF NOT EXISTS "jobs" ("id" text PRIMARY KEY, "position" bigint);--> statement-breakpoint\nALTER SEQUENCE "jobs_seq" OWNED BY "jobs"."position";',
],
[
'a same-named table in another schema',
'CREATE TABLE "archive"."jobs" ("id" text PRIMARY KEY, "position" bigint);--> statement-breakpoint\nALTER SEQUENCE "jobs_seq" OWNED BY "public"."jobs"."position";',
],
[
'an ownership change wrapped in a DO block',
'DO $$ BEGIN\n ALTER SEQUENCE "jobs_seq" OWNED BY "jobs"."position";\nEXCEPTION WHEN undefined_table THEN NULL;\nEND $$;',
],
])(
'refuses OWNED BY through %s',
async (_case, sql) => {
const { code, stdout, stderr } = await runAudit('--dir', migrationsDir(sql))
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain('sequence-owned-by-existing-table')
},
30_000
)
it('reads past an escaped quote in an escape string to an ownership change after it', async () => {
const dir = migrationsDir(
"DO $$ BEGIN RAISE NOTICE E'it\\'s ready'; ALTER TABLE calls OWNER TO postgres; RAISE NOTICE 'done'; END $$;"
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain('owner-to')
}, 30_000)
it('ignores ownership words inside an escape string', async () => {
const dir = migrationsDir(
"COMMENT ON TABLE \"jobs\" IS E'Ops\\'s note: hand owner to the support team';"
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(`${stdout}${stderr}`).not.toContain('owner-to')
expect(code).toBe(0)
}, 30_000)
it('ignores ownership words inside dollar-quoted data', async () => {
const dir = migrationsDir(
'COMMENT ON TABLE "jobs" IS $note$Transfer owner to the support team, OWNED BY ops.jobs.id$note$;'
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(`${stdout}${stderr}`).not.toContain('owner-to')
expect(`${stdout}${stderr}`).not.toContain('sequence-owned-by-existing-table')
expect(code).toBe(0)
}, 30_000)
it.each([
[
'a DO block naming its language',
'DO LANGUAGE plpgsql $$ BEGIN ALTER TABLE calls OWNER TO postgres; END $$;',
],
[
'a function body',
'CREATE FUNCTION hand_over() RETURNS void AS $fn$ BEGIN ALTER TABLE calls OWNER TO postgres; END $fn$ LANGUAGE plpgsql;',
],
])(
'refuses OWNER TO inside %s',
async (_case, sql) => {
const { code, stdout, stderr } = await runAudit('--dir', migrationsDir(sql))
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain('owner-to')
},
30_000
)
it('refuses OWNED BY an existing table whose name differs only in case from a new quoted one', async () => {
const dir = migrationsDir(
[
'CREATE TABLE "Jobs" ("id" text PRIMARY KEY, "position" bigint);--> statement-breakpoint',
'ALTER SEQUENCE "jobs_seq" OWNED BY jobs.position;',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain('sequence-owned-by-existing-table')
}, 30_000)
it('does not read operations out of literals, comments, or dollar-quoted data', async () => {
const dir = migrationsDir(
[
'-- Next release: ALTER TABLE "jobs" RENAME COLUMN "a" TO "b".',
'COMMENT ON TABLE "jobs" IS $note$Then ALTER TABLE jobs DROP COLUMN old$note$;--> statement-breakpoint',
'COMMENT ON COLUMN "jobs"."id" IS \'kept until ALTER TABLE jobs DROP COLUMN id\';',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
const output = `${stdout}${stderr}`
expect(output).not.toContain('drop-column')
expect(output).not.toContain('rename')
expect(code).toBe(0)
}, 30_000)
it('still flags a real destructive operation', async () => {
const dir = migrationsDir('ALTER TABLE "jobs" DROP COLUMN "old";')
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain('drop-column')
}, 30_000)
/** Migration 0076 renames a column this way; the literal is SQL the block runs, not data. */
it.each([
['rename', "DO $$ BEGIN EXECUTE 'ALTER TABLE jobs RENAME COLUMN metadata TO data'; END $$;"],
['owner-to', "DO $$ BEGIN EXECUTE E'ALTER TABLE jobs OWNER TO postgres'; END $$;"],
])(
'reads dynamic SQL that EXECUTE runs (%s)',
async (rule, sql) => {
const { code, stdout, stderr } = await runAudit('--dir', migrationsDir(sql))
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain(rule)
},
30_000
)
/**
* A table created in another schema must not exempt the same-named existing table in `public`
* from the downtime checks: its old rows still need a default for a NOT NULL column.
*/
it('keeps checking an existing table when a same-named table is created in another schema', async () => {
const dir = migrationsDir(
[
'CREATE TABLE "archive"."jobs" ("id" text PRIMARY KEY);--> statement-breakpoint',
'ALTER TABLE "jobs" ADD COLUMN "required" text NOT NULL;',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(code).toBe(1)
expect(`${stdout}${stderr}`).toContain('add-not-null-no-default')
}, 30_000)
it('still skips the downtime checks for a table the migration creates', async () => {
const dir = migrationsDir(
[
'CREATE TABLE "public"."jobs" ("id" text PRIMARY KEY);--> statement-breakpoint',
'ALTER TABLE "jobs" ADD COLUMN "required" text NOT NULL;',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(`${stdout}${stderr}`).not.toContain('add-not-null-no-default')
expect(code).toBe(0)
}, 30_000)
it('ignores ownership words inside string literals and comments', async () => {
const dir = migrationsDir(
[
'-- Hands owner to the support team in a later release.',
'COMMENT ON TABLE "jobs" IS \'Transfer owner to the support team, OWNED BY ops.jobs.id\';',
].join('\n')
)
const { code, stdout, stderr } = await runAudit('--dir', dir)
expect(`${stdout}${stderr}`).not.toContain('owner-to')
expect(`${stdout}${stderr}`).not.toContain('sequence-owned-by-existing-table')
expect(code).toBe(0)
}, 30_000)
it('passes against a real base ref with no new migrations', async () => {
const { code, stdout } = await runAudit('HEAD')
expect(code).toBe(0)
expect(stdout).toContain('No new migrations to check')
}, 30_000)
})
You can’t perform that action at this time.