Representing Rust enums in databases

Question

Although I love Rust's enums (and the idea of making illegal states unrepresentable), I'm struggling to represent (ironic, yes) them in databases like PostgreSQL when the variants have data associated with them.

Example problem

Suppose we have something like this:

enum PetKind {
    Dog,
    Cat,
    Bird { can_fly: bool },
}

struct Pet {
    name: String,
    kind: PetKind,
}

From what I can tell, traditional databases simply don't support sum types. This makes it difficult to not only store rich enums (like PetKind) but also query them.

Imperfect solutions I found

Serialize to JSON

For example, we could serialize

let my_pet_kind = PetKind::Bird { can_fly: true };

as

{
    "Bird": {
        "can_fly": true
    }
}

and store it in a column of table pets.

This might work, but with this we probably sacrifice some of the benefits that a relational database offers.

Nullable columns with check constraints

Alternatively, we might have something like this, where data associated with variants comes in the form of nullable columns, while check constraints ensure data consistency:

CREATE TYPE pet_kind AS ENUM ('dog', 'cat', 'bird');

CREATE TABLE pets (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  kind pet_kind NOT NULL,
  can_fly BOOLEAN,
);

ALTER TABLE pets ADD CHECK (
  (kind = 'dog' AND can_fly IS NULL)
  OR
  (kind = 'cat' AND can_fly IS NULL)
  OR
  (kind = 'bird' AND can_fly IS NOT NULL)
);

However, the querying of data in this schema in Rust seems very awkward. E.g., one may need to define intermediate data types in Rust, like

enum PetKindSql {
    Dog,
    Cat,
    Bird,
}

struct PetSql {
    name: String,
    kind: PetKindSql,
    can_fly: Option<bool>,
}

and convert them to the proper ones later.

Question

Is this the best we can do? Is there a way to avoid serializing enums to JSON while preserving easy retrieval of data in Rust?

I've searched through a lot of pages—and maybe I'm searching for the wrong things—but I just can't find an elegant and robust solution. But maybe that's simply because conventional databases favour product types over sum types? If so, is anyone working on the latter?

I'm a little confused about what you are trying to accomplish here. It seems like you are trying to attach configurable data to an enum in the DB but it's not clear what problem that solves because in Rust, these enums are static, right? How are these properties manifested in the Rust side of things? — JimmyJames
– JimmyJames, Commented Jul 26, 2023 at 21:28

Ewan · Accepted Answer · 2023-07-26 17:19:59Z

2

Essentially this is just the same as a OOP class hierarchy and you can store it using the Table per Type methodology

table Pet
  Id : GUID
  Type : "Bird"/"Cat" etc

table Bird
  Id : GUID, FK Pet.Id
  canfly

table Cat
  Id : GUID, FK Pet.Id
  purVolume

//get all birds
select * 
  from Pet
  left join Bird
    on pet.Id = bird.Id
where Pet.Type='Bird'

answered Jul 26, 2023 at 17:19

Ewan

84.5k5 gold badges91 silver badges189 bronze badges

Add a comment |

Stack Exchange Network

Representing Rust enums in databases

Example problem

Imperfect solutions I found

Serialize to JSON

Nullable columns with check constraints

Question

1 Answer 1

Your Answer

Hot Network Questions

Representing Rust enums in databases

Example problem

Imperfect solutions I found

Serialize to JSON

Nullable columns with check constraints

Question

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions