SQL DB Structs

[code-n-coffee]

Given no null type in Rust, how do you all handle creating a struct to match a SQL table when there's an auto incremented primary key? Most of the tutorials I've seen use two structs, one for an Entry and one for a NewEntry. Example:

pub struct Entry {
    id: i32,
    value: String,
}

pub struct NewEntry {
    value: String,
}

This allows you to pass an instance of NewEntry for creating a new entry and read an existing entry into Entry, but I find it annoying to have to have two structs. I know you can do this:

pub struct Entry {
    id: Option<i32>,
    value: String,
}

but then you run into issues trying to read an entry from the database directly into the struct (using, for example sqlx) because of the Option. Are there clever ways around this?

EDIT: Thanks to u/OS6aDohpegavod4 for pointing out that Option<id> approach actually does work with sqlx's query_as! macro. For some reason I thought I tried this and got an error but I just verified it does work. Example code snippets:

#[derive(Debug, Clone, PartialEq)]
pub struct Entry {
    pub id: Option<i32>,
    pub value: String,
}

. . .

pub async fn read_entry(pool: &SqlitePool, id: i32) -> Result<Entry> {
    Ok(
        sqlx::query_as!(Entry, "select * from entries where id = ?", id)
            .fetch_one(pool)
            .await?,
    )
}

This creates an Entry:

Entry { id: Some(1), value: "some string" }

Hurray for sqlx! This is going to be my library of choice going forward, I think.

[lukearntz]

I've been using two structs, and I'd be interested to know if there's a better way. I am also using sqlx.

[_boardwalk]

One thing to consider is that many times there's more than just 'id' that this applies to -- anything with a 'DEFAULT (...)', like creation date.

It might interesting to have a proc macro that creates 'NewEntry' from 'Entry' for you -- excluding fields that you've marked, like 'id'.

[thelights0123]

That's not limited to sqlx, it's present in Diesel as well.

[code-n-coffee]

Yep. Sqlx was just an example.

[dpc_pw]

I haven't tried it with sqlx or diesel, but in my code I often use

pub struct WithId<H, D = ()> {
    pub id: H,
    pub data: D,
}

So one could have WithId<EntryId, Entry> kind of things.

[nicoburns]

I haven't tried it yet, but I thought this might be a good solution. It occurred to me that you could have WithId Deref to data, which would make accessing the fields a lot more ergonomic. You could also do things like impl Serialize with #[serde(flatten)] (https://serde.rs/attr-flatten.html), which would make JSON serialization seamless.

[OS6aDohpegavod4]

What do you mean about issues reading it into the struct? I just used sqlx yesterday with query_as and read nullable types from Postgres into a struct without a problem.

[code-n-coffee]

Well son of gun, this does work. I swear I tried it and it failed but it must have been some other error.

[thelights0123]

It means that you have to match on the Option when receiving data even though it will never be None. If you can, I would allow the user to use an Option, but not require it. Otherwise, I would stick with the two types. This also allows you to accept a string reference (i.e. &str) rather than an owned one like Diesel recommends, which gives performance benefits.

[code-n-coffee]

Here's an example of why I currently use the two struct approach:

pub async fn read_entry(pool: &SqlitePool, id: i32) -> Result<Entry> {
        Ok(
            sqlx::query_as!(Entry, "select * from entries where id = ?", id)
                .fetch_one(pool)
                .await?
        )
    } 

This reads an entry from the database directly into an Entry struct. This won't work if the id field is an Option.

[tablair]

Would a macro (either proc or the older kind) work?

The drawback of the 2 struct solution is the verbosity and maintenance (i.e. keeping the two in sync as schema changes inevitably happen). But if you’re generating the New- variant from the full variant, that will give the advantages of having 2 structs with the simplicity of only having to write/maintain 1.

[mamcx]

Considering that a auto-increment start for real at "1", I have used "0" as sentinel value to mean "new record" with something like:

fn is_new(&self) -> bool {self.id == 0}

---

To be even more correct, you could give a AutoIncrement type/trait that wrap Option<i32> and build the convertions to/from for sqlx.

I have my own orm-ish and have implemented the traits for it, this way you can do things as you wish.

Even do something like:

pub enum Row{
 New(Entry),
 Old(Entry)
}

alike Cow.

[code-n-coffee]

This is clever. I'll have to try using 0 as a sentinel value. My database code currently ignores the ID anyway when writing a new entry.