sqltrail-cli 0.1.0

Reusable PostgreSQL schema migration CLI and library: diff declared schema against a live database, generate versioned SQL, apply with a checksum-protected history
Documentation
use std::path::PathBuf;

use sqltrail_cli::history::{MigrationFile, compute_checksum};

fn test_database_url() -> Option<String> {
    std::env::var("SQLTRAIL_TEST_DATABASE_URL").ok()
}

#[tokio::test]
async fn introspection_reads_comments_indexes_and_functions() -> anyhow::Result<()> {
    let Some(url) = test_database_url() else {
        return Ok(());
    };
    let client = sqltrail_cli::database::connect(&url).await?;
    // 清理由此前异常中断的同类测试留下的专用 schema。
    for row in client
        .query(
            "SELECT nspname FROM pg_namespace WHERE nspname LIKE 'sqltrail_it_%'",
            &[],
        )
        .await?
    {
        let stale: String = row.get(0);
        if stale
            .strip_prefix("sqltrail_it_")
            .is_some_and(|suffix| suffix.chars().all(|ch| ch.is_ascii_digit()))
        {
            client
                .batch_execute(&format!("DROP SCHEMA \"{stale}\" CASCADE"))
                .await?;
        }
    }
    let schema = format!("sqltrail_it_{}", std::process::id());
    let setup = format!(
        "CREATE SCHEMA \"{schema}\";
         CREATE TABLE \"{schema}\".\"users\" (\"id\" bigint PRIMARY KEY, \"email\" text);
         COMMENT ON TABLE \"{schema}\".\"users\" IS '用户表';
         COMMENT ON COLUMN \"{schema}\".\"users\".\"email\" IS '邮箱';
         CREATE INDEX \"users_email_idx\" ON \"{schema}\".\"users\" (\"email\");
         CREATE FUNCTION \"{schema}\".\"answer\"() RETURNS integer LANGUAGE sql IMMUTABLE AS $$ SELECT 42 $$;
         COMMENT ON FUNCTION \"{schema}\".\"answer\"() IS '答案';"
    );
    client.batch_execute(&setup).await?;

    let result = async {
        let model = sqltrail_cli::introspection::introspect(&client).await?;
        let table = model
            .tables
            .iter()
            .find(|table| table.schema == schema && table.name == "users")
            .ok_or_else(|| anyhow::anyhow!("未读取到测试表"))?;
        anyhow::ensure!(table.comment.as_deref() == Some("用户表"), "表注释不一致");
        anyhow::ensure!(
            table.columns[1].comment.as_deref() == Some("邮箱"),
            "字段注释不一致"
        );
        anyhow::ensure!(
            table.indexes[0].name.as_deref() == Some("users_email_idx"),
            "索引读取不一致"
        );
        let function = model
            .functions
            .iter()
            .find(|function| function.schema == schema && function.name == "answer")
            .ok_or_else(|| anyhow::anyhow!("未读取到测试函数"))?;
        anyhow::ensure!(
            function.comment.as_deref() == Some("答案"),
            "函数注释不一致"
        );
        Ok::<(), anyhow::Error>(())
    }
    .await;

    client
        .batch_execute(&format!("DROP SCHEMA \"{schema}\" CASCADE"))
        .await?;
    let row = client
        .query_one("SELECT to_regnamespace($1)::text", &[&schema])
        .await?;
    let remaining_schema: Option<String> = row.get(0);
    anyhow::ensure!(remaining_schema.is_none(), "测试 schema 清理失败");
    result
}

#[tokio::test]
async fn failed_transaction_does_not_leave_schema_changes() -> anyhow::Result<()> {
    let Some(url) = test_database_url() else {
        return Ok(());
    };
    let mut client = sqltrail_cli::database::connect(&url).await?;
    let history_existed = sqltrail_cli::history::history_table_exists(&client).await?;
    sqltrail_cli::history::ensure_history_table(&client).await?;
    let table = format!("sqltrail_atomic_{}", std::process::id());
    let sql = format!("CREATE TABLE public.\"{table}\" (id bigint); THIS IS NOT VALID SQL;");
    let file = MigrationFile {
        version: 9_000_000 + i64::from(std::process::id()),
        name: "atomic_failure".into(),
        path: PathBuf::from("atomic_failure.sql"),
        checksum: compute_checksum(&sql),
        sql,
    };
    let apply_failed = sqltrail_cli::history::apply(&mut client, &[file], false)
        .await
        .is_err();
    let qualified = format!("public.{table}");
    let row = client
        .query_one("SELECT to_regclass($1)::text", &[&qualified])
        .await?;
    let relation: Option<String> = row.get(0);
    if !history_existed {
        client
            .batch_execute("DROP TABLE public._sqltrail_migrations")
            .await?;
    }
    anyhow::ensure!(
        sqltrail_cli::history::history_table_exists(&client).await? == history_existed,
        "迁移历史表状态没有恢复"
    );
    assert!(apply_failed, "包含非法 SQL 的迁移必须失败");
    assert!(relation.is_none(), "失败事务不应留下已创建的表");
    Ok(())
}