Using diesel for PostgreSQL access

mvolkmann · January 23, 2021, 1:40am

I'm trying to follow the instructions on this "Getting Started" page:
https://diesel.rs/guides/getting-started/

My basic attempt at using diesel is in GitHub.
I'm getting an error from the compiler here:

mvolkmann/rust-diesel-demo/blob/master/src/bin/main.rs#L12


extern crate diesel_demo;
use self::models::*;
use diesel::prelude::*;
use diesel_demo::*;
fn main() {
    use self::schema::dogs::dsl::*;
    let connection = establish_connection();
    //TODO: The next line gives the error: the trait
    //TODO: `diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>`
    //TODO: is not implemented for `*const str`
    let results = dogs.load::<Dog>(&connection).expect("error loading dogs");
    println!("Displaying {} dogs", results.len());
    for dog in results {
        println!("{} is a {}.", dog.name, dog.breed);
    }
}

It says "the trait
diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, _>
is not implemented for *const str
I could use some help figuring out what I'm doing wrong.

mbrubeck · January 23, 2021, 2:04am

Since your schema uses Nullable<Text> for the breed and name fields, your Rust type should use Option<String> for those fields:

#[derive(Queryable)]
pub struct Dog {
    pub id: i32,
    pub name: Option<String>,
    pub breed: Option<String>,
}

mvolkmann · January 23, 2021, 12:49pm

It turns out I wanted "NOT NULL" on the name and breed columns, so I made that change to the table. That didn't resolve the error I'm getting here though: rust-diesel-demo/main.rs at master · mvolkmann/rust-diesel-demo · GitHub

alice · January 23, 2021, 12:57pm

You have not removed Nullable from your schema file. Until you do so, diesel will still think that the field is nullable.

mvolkmann · January 23, 2021, 1:39pm

Ah, I had changed the table schema in PostgreSQL, but didn't modify the schema.rs file. Doing that fixed the problem. I removed Nullable from that file manually, but I suppose I could have run diesel setup again to regenerate it. But I think that would have created new migration files which I didn't want.

alice · January 23, 2021, 1:47pm

Usually when I have used diesel, I have managed the schema file manually instead of using the diesel command-line tool.

mvolkmann · January 23, 2021, 2:12pm

Now that I can query a Postgres table using diesel I want to learn how to insert, update, and delete rows. I want to start by deleting all the rows in a table. Here is my attempt. There are two errors. The first is how I'm passing the database connection to my delete_dogs function. It's not clear to me what the type needs to be and I couldn't find any examples of passing a connection. The second issue is the return type of this function. I think it should return Ok with the number of deleted rows or Error containing a diesel::result::Error value, but I couldn't get it to be happy with my return type.

github.com

mvolkmann/rust-diesel-demo/blob/master/src/bin/main.rs#L9


extern crate diesel;
extern crate diesel_demo;
use self::models::*;
use diesel::prelude::*;
use diesel::result::Error;
use diesel_demo::*;
fn delete_dogs(conn: &dyn Connection) -> Result<usize, Error> {
    diesel::delete(dogs).execute(&conn)?
}
/*
fn insert_dog(conn: &Connection, name: String, breed: String) {
    let dog = Dog { name, breed };
    diesel::insert_into(dogs::table)
        .values(&dog)
        .get_result(conn)
        .expect("error inserting dog")

alice · January 23, 2021, 2:32pm

How does it fail?

mvolkmann · January 23, 2021, 2:41pm

It says "the value of the associated types Backend (from trait diesel::Connection), TransactionManager (from trait diesel::Connection) must be specified".
So the issue must be the way I am specifying the type of the conn parameter.

alice · January 23, 2021, 3:11pm

I would probably go for either generics

fn delete_dogs<C: Connection>(conn: &C) -> Result<usize, Error> {
    diesel::delete(dogs).execute(conn)?
}

or just use the fully specified type.

fn delete_dogs(conn: &PgConnection) -> Result<usize, Error> {
    diesel::delete(dogs).execute(conn)?
}

mvolkmann · January 23, 2021, 8:59pm

I have this working now. Thanks so much to @mbrubeck and @Alice for all the help! I think it's a pretty good example of implementing all the CRUD operations on a Postgres table using diesel. I'm certainly open to feedback if there is anything I could have done better. In any case, I'm hoping others new to Rust will find this useful. I've made the code as simple as I could.