sqldiff 0.5.0

primitives to auto-generate SQL schema 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
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

use anyhow::{Error, Result};
use itertools::Itertools;
use sqlx::PgConnection;
use sqlx::postgres::PgPoolOptions;

use crate::{Schema, schema};
use crate::schema::{Column, Table};

const QUERY_COLUMNS: &str = "SELECT
	table_name, column_name, ordinal_position, is_nullable, data_type
FROM information_schema.columns
WHERE
	table_schema=$1
ORDER BY table_name, ordinal_position";

const QUERY_TABLES: &str = "SELECT
    table_schema
    , table_name
FROM information_schema.tables
WHERE
    table_schema = $1 ";

#[derive(sqlx::FromRow)]
pub struct SchemaColumn {
    pub table_name: String,
    pub column_name: String,
    pub ordinal_position: i32,
    pub is_nullable: String,
    pub data_type: String,
}

pub async fn query_schema_columns(mut conn: &mut PgConnection, schema_name: &str) -> Result<Vec<SchemaColumn>> {
    let result = sqlx::query_as::<_, SchemaColumn>(QUERY_COLUMNS)
        .bind(schema_name)
        .fetch_all(conn)
        .await?;
    Ok(result)
}

#[derive(sqlx::FromRow)]
pub struct TableSchema {
    pub table_schema: String,
    pub table_name: String,
}

pub async fn query_table_names(mut conn: &mut PgConnection, schema_name: &str) -> Result<Vec<String>> {
    let result = sqlx::query_as::<_, TableSchema>(QUERY_TABLES)
        .bind(schema_name)
        .fetch_all(conn)
        .await?;
    Ok(result.into_iter().map(|t| t.table_name).collect())
}


impl Schema {
    pub async fn try_from_database(conn: &mut PgConnection, schema_name: &str) -> Result<Schema> {
        let column_schemas = query_schema_columns(conn, schema_name).await?;
        let mut tables = column_schemas.into_iter()
            .group_by(|c| c.table_name.clone())
            .into_iter()
            .map(|(table_name, group)| {
                let columns = group.map(|c: SchemaColumn| {
                    let nullable = c.is_nullable == "YES";
                    let typ = schema::Type::from_str(&c.data_type)?;
                    Ok(Column {
                        name: c.column_name.clone(),
                        typ,
                        nullable,
                        primary_key: false,
                        default: None,
                    })
                }).collect::<Result<Vec<_>, Error>>()?;
                Ok(Table { name: table_name, columns, indexes: vec![] })
            })
            .collect::<Result<Vec<_>, Error>>()?;

        // Degenerate case but you can have tables with no columns...
        let table_names = query_table_names(conn, schema_name).await?;
        for name in table_names {
            if tables.iter().any(|t| t.name == name) {
                continue;
            }
            tables.push(Table {
                name,
                columns: vec![],
                indexes: vec![],
            })
        }
        Ok(Schema { tables })
    }
}