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
|
// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
// SPDX-License-Identifier: Apache-2.0
package pgruntime_test
import (
"testing"
"go.xyrillian.de/gg/assert"
"go.xyrillian.de/gg/pgruntime"
)
func TestMigrations(t *testing.T) {
ctx := t.Context()
// reset the test DB to empty if it exists
db, _ := connector.ConnectForTest(t, defaultBehavior)
for _, tableName := range []string{"comments", "posts", "schema_migrations"} {
_, err := execQuery(ctx, db, `DROP TABLE IF EXISTS `+tableName, nil)
if err != nil {
t.Fatal(err.Error())
}
}
err := db.Close()
if err != nil {
t.Fatal(err.Error())
}
// start with an initial baseline
b := pgruntime.ConnectionBehavior{
Migrations: map[int64]string{
42: `
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
message TEXT NOT NULL
);
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT REFERENCES posts ON DELETE CASCADE,
message TEXT NOT NULL
);
`,
},
}
db, target := connector.ConnectForTest(t, b)
// check that the schema is applied by inserting some basic records
postID, err := selectOneValue[int64](ctx, db, `INSERT INTO posts (message) VALUES ($1) RETURNING id`, "Hello World!")
if err != nil {
t.Fatal(err.Error())
}
_, err = execQuery(ctx, db, `INSERT INTO comments (post_id, message) VALUES ($1, $2)`, []any{postID, "Hi there."})
if err != nil {
t.Fatal(err.Error())
}
err = db.Close()
if err != nil {
t.Fatal(err.Error())
}
// apply another migration
b.Migrations[50] = `
UPDATE comments c SET message = c.message || ' in response to: ' || p.message FROM posts p WHERE p.id = c.post_id;
`
db, err = connector.Connect(ctx, target, b)
if err != nil {
t.Fatal(err.Error())
}
// check that the data was modified appropriately
message, err := selectOneValue[string](ctx, db, `SELECT message FROM comments`)
if assert.ErrEqual(t, err, nil) {
assert.Equal(t, message, "Hi there. in response to: Hello World!")
}
// try to apply a broken migration
b.Migrations[51] = `
TRUNCATE commands; -- should be "comments"
`
_, err = connector.Connect(ctx, target, b)
assert.ErrEqual(t, err, `while migrating to schema version 51: could not execute schema migration: pq: relation "commands" does not exist (42P01)`)
// check that the migration was not applied
version, err := selectOneValue[int64](ctx, db, `SELECT version FROM schema_migrations`)
if assert.ErrEqual(t, err, nil) {
assert.Equal(t, version, 50)
}
commentCount, err := selectOneValue[int64](ctx, db, `SELECT COUNT(*) FROM comments`)
if assert.ErrEqual(t, err, nil) {
assert.Equal(t, commentCount, 1)
}
}
|
