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?

• JimmyJames: 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?

Answer 1

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'