main.rs

use sqlx::prelude::*;

#[derive(PartialEq, Debug, sqlx::Type)]
#[sqlx(rename = "mood")]
#[sqlx(rename_all = "lowercase")]
enum Mood {
    Ok,
    Happy,
    Sad,
}

#[derive(sqlx::FromRow)]
struct PeopleRow {
    id: i32,
    mood: Mood,
}

#[async_std::main]
async fn main() -> anyhow::Result<()> {
    let dburl = std::env::var("DATABASE_URL")?;
    let pool = sqlx::PgPool::new(&dburl).await?;
    let mut conn = pool.acquire().await?;

    conn.execute(
        r#"
        DO $$ BEGIN

        CREATE TYPE mood AS ENUM ( 'ok', 'happy', 'sad' );

        EXCEPTION
            WHEN duplicate_object THEN null;
        END $$;

        CREATE TABLE IF NOT EXISTS people (
            id      INT PRIMARY KEY,
            mood    mood not null
        );

        TRUNCATE people;

        INSERT INTO people (id, mood) VALUES (1, 'sad'::mood);
        "#,
    )
    .await?;

    // Drop the pool and recreate it because the bug only when the prior insert is not cached.
    drop(conn);
    drop(pool);

    let pool = sqlx::PgPool::new(&dburl).await?;
    let people_id: i32 = 1;

    let rec: PeopleRow = sqlx::query_as("SELECT id, mood FROM people WHERE id = $1")
        .bind(people_id)
        .fetch_one(&pool)
        .await?;

    assert_eq!(rec.id, people_id);
    assert_eq!(rec.mood, Mood::Sad);
    
    Ok(())
}