如何使用sqlx在PostgreSQL中存储带数据的富枚举类型？

方案一：JSONB + 类型标识字段（推荐） #

仅需单表存储，用一个字段标记枚举变体，另一个JSONB字段存储对应变体的关联数据，结构简洁且扩展性强。

数据库表结构 #

CREATE TABLE animals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type TEXT NOT NULL CHECK (type IN ('cat', 'dog')), -- 标记枚举类型
    data JSONB NOT NULL -- 存储对应变体的关联数据
);

Rust代码实现 #

通过Serde序列化/反序列化实现枚举与JSONB的转换，配合sqlx完成数据库交互：

use sqlx::{FromRow, PgPool, types::Uuid, types::Json};
use anyhow::Result;
use serde::{Deserialize, Serialize};

#[derive(Debug, Deserialize, Serialize)]
enum Animal {
    Cat { name: String },
    Dog { age: i32 },
}

// 数据库行映射结构体
#[derive(Debug, FromRow)]
struct AnimalDbRow {
    id: Uuid,
    r#type: String,
    data: Json<serde_json::Value>,
}

async fn list_animals(pool: &PgPool) -> Result<Vec<Animal>> {
    let rows = sqlx::query_as!(AnimalDbRow, "SELECT id, type, data FROM animals")
        .fetch_all(pool)
        .await?;

    let animals = rows.into_iter()
        .map(|row| match row.r#type.as_str() {
            "cat" => serde_json::from_value(row.data.0).map(Animal::Cat)?,
            "dog" => serde_json::from_value(row.data.0).map(Animal::Dog)?,
            _ => anyhow::bail!("Unknown animal type: {}", row.r#type),
        })
        .collect::<Result<Vec<_>>>()?;

    Ok(animals)
}

// 插入示例：新增Cat
async fn insert_cat(pool: &PgPool, name: String) -> Result<Uuid> {
    let animal = Animal::Cat { name };
    let data = serde_json::to_value(&animal)?;

    let id = sqlx::query!(
        "INSERT INTO animals (type, data) VALUES ($1, $2) RETURNING id",
        "cat",
        data as _
    )
    .fetch_one(pool)
    .await?
    .id;

    Ok(id)
}

优点：

• 单表结构，查询与维护逻辑简单
• 扩展性强：新增枚举变体仅需添加Serde转换逻辑，无需修改表结构
• 支持JSONB过滤：可通过SELECT * FROM animals WHERE type = 'cat' AND data->>'name' = 'Whiskers'精准查询

缺点：

• 数据类型约束需在应用层保证，数据库层仅能通过CHECK约束做基础校验

方案二：SQLx自动化多表转换（保留范式） #

若想保留多表结构，可通过UNION ALL简化查询，再配合通用转换逻辑减少手动匹配代码。

优化后的查询语句 #

SELECT id, 'cat' as type, jsonb_build_object('name', name) as data FROM cats
UNION ALL
SELECT id, 'dog' as type, jsonb_build_object('age', age) as data FROM dogs

Rust代码实现 #

use sqlx::{FromRow, PgPool, types::Uuid, types::Json};
use anyhow::Result;
use serde::{Deserialize, Serialize};

#[derive(Debug, Deserialize, Serialize)]
enum Animal {
    Cat { name: String },
    Dog { age: i32 },
}

#[derive(Debug, FromRow)]
struct AnimalUnionRow {
    id: Uuid,
    r#type: String,
    data: Json<serde_json::Value>,
}

async fn list_animals(pool: &PgPool) -> Result<Vec<Animal>> {
    let rows = sqlx::query_as!(AnimalUnionRow, r#"
        SELECT id, 'cat' as type, jsonb_build_object('name', name) as data FROM cats
        UNION ALL
        SELECT id, 'dog' as type, jsonb_build_object('age', age) as data FROM dogs
    "#)
    .fetch_all(pool)
    .await?;

    let animals = rows.into_iter()
        .map(|row| match row.r#type.as_str() {
            "cat" => serde_json::from_value(row.data.0).map(Animal::Cat)?,
            "dog" => serde_json::from_value(row.data.0).map(Animal::Dog)?,
            _ => anyhow::bail!("Unknown animal type: {}", row.r#type),
        })
        .collect::<Result<Vec<_>>>()?;

    Ok(animals)
}

优点：

• 保留关系型数据库范式，数据完整性由数据库层保证
• 避免复杂LEFT JOIN，查询效率更高

缺点：

• 新增枚举变体需修改UNION查询语句
• 多表维护成本略高于单表方案

方案三：PostgreSQL继承表（进阶） #

利用PostgreSQL表继承特性，父表animals统一存储公共字段，子表cats、dogs存储变体专属数据。

数据库表结构 #

CREATE TABLE animals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

CREATE TABLE cats (
    name TEXT NOT NULL
) INHERITS (animals);

CREATE TABLE dogs (
    age INT NOT NULL
) INHERITS (animals);

Rust代码实现 #

通过tableoid区分不同子表，完成枚举转换：

use sqlx::{FromRow, PgPool, types::Uuid, types::Oid};
use anyhow::Result;

#[derive(Debug)]
enum Animal {
    Cat { name: String },
    Dog { age: i32 },
}

#[derive(Debug, FromRow)]
struct AnimalInheritRow {
    id: Uuid,
    tableoid: Oid,
    name: Option<String>,
    age: Option<i32>,
}

async fn list_animals(pool: &PgPool) -> Result<Vec<Animal>> {
    // 获取子表OID用于区分类型
    let cat_oid = sqlx::query!("SELECT oid FROM pg_class WHERE relname = 'cats'")
        .fetch_one(pool)
        .await?
        .oid;
    let dog_oid = sqlx::query!("SELECT oid FROM pg_class WHERE relname = 'dogs'")
        .fetch_one(pool)
        .await?
        .oid;

    let rows = sqlx::query_as!(AnimalInheritRow, r#"
        SELECT id, tableoid, name, age FROM animals
    "#)
    .fetch_all(pool)
    .await?;

    let animals = rows.into_iter()
        .map(|row| match row.tableoid {
            o if o == cat_oid => row.name.ok_or_else(|| anyhow::anyhow!("Cat missing name"))
                .map(|name| Animal::Cat { name }),
            o if o == dog_oid => row.age.ok_or_else(|| anyhow::anyhow!("Dog missing age"))
                .map(|age| Animal::Dog { age }),
            _ => anyhow::bail!("Unknown animal table OID: {}", row.tableoid),
        })
        .collect::<Result<Vec<_>>>()?;

    Ok(animals)
}

优点：

• 严格遵循关系型范式，数据完整性强
• 可针对子表单独做性能优化

缺点：

• 表结构复杂度高，查询需处理OID逻辑
• PostgreSQL部分特性（如外键）对继承表支持有限

内容的提问来源于stack exchange，提问作者felixinho