pgmold 0.33.5

PostgreSQL schema-as-code management tool
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
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

mod common;
use common::*;
use pgmold::apply::{apply_migration, ApplyOptions};

#[tokio::test]
async fn apply_succeeds_with_valid_schema() {
    let (_container, url) = setup_postgres().await;
    let connection = PgConnection::new(&url).await.unwrap();

    let schema_file = write_sql_temp_file(
        r#"
        CREATE TABLE users (
            id BIGINT NOT NULL PRIMARY KEY,
            email TEXT NOT NULL
        );
        "#,
    );

    let schema_source = schema_file.path().to_str().unwrap().to_string();
    let result = apply_migration(
        &[schema_source],
        &connection,
        ApplyOptions {
            dry_run: false,
            allow_destructive: false,
        },
    )
    .await
    .unwrap();

    assert!(result.applied);
    assert!(!result.operations.is_empty());

    let schema = introspect_schema(&connection, &["public".to_string()], false)
        .await
        .unwrap();
    assert!(schema.tables.contains_key("public.users"));
}

#[tokio::test]
async fn apply_returns_error_on_invalid_sql() {
    let (_container, url) = setup_postgres().await;
    let connection = PgConnection::new(&url).await.unwrap();

    // Create table with a row that has no name value
    sqlx::query("CREATE TABLE users (id INT PRIMARY KEY)")
        .execute(connection.pool())
        .await
        .unwrap();
    sqlx::query("INSERT INTO users (id) VALUES (1)")
        .execute(connection.pool())
        .await
        .unwrap();

    // Target schema adds a NOT NULL column without a DEFAULT — existing row will cause failure
    let schema_file = write_sql_temp_file(
        r#"
        CREATE TABLE users (
            id INT NOT NULL PRIMARY KEY,
            name TEXT NOT NULL
        );
        "#,
    );

    let schema_source = schema_file.path().to_str().unwrap().to_string();
    let result = apply_migration(
        &[schema_source],
        &connection,
        ApplyOptions {
            dry_run: false,
            allow_destructive: false,
        },
    )
    .await;

    assert!(result.is_err(), "Expected Err but got Ok");
}

#[tokio::test]
async fn apply_rolls_back_on_failure() {
    let (_container, url) = setup_postgres().await;
    let connection = PgConnection::new(&url).await.unwrap();

    let setup_file = write_sql_temp_file(
        r#"
        CREATE TABLE table_a (
            id INT NOT NULL PRIMARY KEY,
            value TEXT
        );
        "#,
    );

    let setup_source = setup_file.path().to_str().unwrap().to_string();
    apply_migration(
        &[setup_source],
        &connection,
        ApplyOptions {
            dry_run: false,
            allow_destructive: false,
        },
    )
    .await
    .unwrap();

    // Insert a row so the subsequent NOT NULL column addition fails
    sqlx::query("INSERT INTO table_a (id, value) VALUES (1, 'row1')")
        .execute(connection.pool())
        .await
        .unwrap();

    // Target adds NOT NULL column (will fail due to existing row) AND a new table_b.
    // Because the transaction rolls back, table_b should not persist.
    let target_file = write_sql_temp_file(
        r#"
        CREATE TABLE table_a (
            id INT NOT NULL PRIMARY KEY,
            value TEXT,
            required_field TEXT NOT NULL
        );
        CREATE TABLE table_b (
            id INT NOT NULL PRIMARY KEY
        );
        "#,
    );

    let target_source = target_file.path().to_str().unwrap().to_string();
    let apply_result = apply_migration(
        &[target_source],
        &connection,
        ApplyOptions {
            dry_run: false,
            allow_destructive: false,
        },
    )
    .await;

    assert!(apply_result.is_err(), "Expected Err but got Ok");

    // table_b must not exist — the transaction was rolled back
    let schema = introspect_schema(&connection, &["public".to_string()], false)
        .await
        .unwrap();
    assert!(
        !schema.tables.contains_key("public.table_b"),
        "table_b should not exist after rollback"
    );
}