Rust and PostgreSQL with tokio_postgres

Question

I have a PostgreSQL database with one simple table:

CREATE DATABASE test_database;
\c test_database;
CREATE TABLE users ( id int primary key NOT NULL, name varchar(40) NOT NULL );
INSERT INTO users (id,name) VALUES (1, 'user_one');
INSERT INTO users (id,name) VALUES (2, 'user_two');

Now I want to write a rust program that retrieves users with SELECT id, name FROM users and returns them. For this I picked tokio_postgres, as it is asynchronous and it seems to have support with Tokio async runtime.

This is my program:

mod domain;
mod types;
use tokio_postgres::NoTls;
use types::AsyncResult;

// Using Tokio runtime for async behaviour: https://crates.io/crates/tokio
#[tokio::main]
async fn main() -> AsyncResult<()> {
  // Get DB client and connection
  let (client, connection) = tokio_postgres::connect(
    "postgres://test_user:secret_password@localhost/test_database",
    NoTls,
  )
  .await?;

  // Spawn connection
  tokio::spawn(async move {
    if let Err(error) = connection.await {
      eprintln!("Connection error: {}", error);
    }
  });

  // Do the query
  let users = client.query("SELECT id, name FROM users", &[]).await?;

  println!("{:#?}", users);

  Ok(())
}

From this I expect to get something similar to:

[
  {
    "id": 1,
    "name": "user_1"
  },
  {
    "id": 2,
    "name": "user_2"
  }
]

But instead I receive the table structure:

[
  Row {
    columns: [
      Column {
        name: "id",
        type: Int4,
      },
      Column {
        name: "name",
        type: Varchar,
      },
    ],
  },
  Row {
    columns: [
      Column {
        name: "id",
        type: Int4,
      },
      Column {
        name: "name",
        type: Varchar,
      },
    ],
  },
]

I tried everything, but I cant't see what I'm doing wrong.

did you try to read the documentation of the library you used? https://docs.rs/tokio-postgres/0.7.5/tokio_postgres/row/struct.Row.html https://docs.rs/tokio-postgres/0.7.5/tokio_postgres/struct.Column.html — Netwave, Nov 18 '21 at 14:25
Yes, I did try to read the documentation of the library I used. — miravelardo, Nov 18 '21 at 14:30
You need a mapper [(see tokio_pg_mapper)](https://docs.rs/tokio-pg-mapper/0.2.0/tokio_pg_mapper/) — Jerboas86, Nov 18 '21 at 14:39
Thanks a lot. Is there any reason why the sql client doesnt include it? — miravelardo, Nov 18 '21 at 14:43
You can do it without a mapper using [`FromSql trait`](https://docs.rs/tokio-postgres/0.7.5/tokio_postgres/types/trait.FromSql.html#tymethod.from_sql). If you take a look into `tokio_pg_mapper` it is certainly what they do. — Jerboas86, Nov 18 '21 at 15:10
@Jerboas86 got it, I'm on it with `FromSql` trait. Once the trait is set, how do you trigger the from_sql function? Any good example out there? I'm trying to find something clear, but I can't find any implemented minimal example. — miravelardo, Nov 18 '21 at 15:48

miravelardo · Answer 1 · 2021-11-18T17:56:25.153

Found a way to map PosgreSQL to my User model. I used impl From<Row>, and then:

let result = client.query("SELECT id, name FROM users", &[]).await?;

let users: Vec<User> = result.into_iter().map(|row| User::from(row)).collect();

Using iter() wont work, I suppose because it returns &T instead of T, but I'm not sure yet. into_iter(), which consumes the vector, returns T, works. This is not documented anywhere, or at least it was impossible to find a proper example, which I think is one of the main drawbacks of Rust.

Here a playground: https://play.rust-lang.org/?version=stable&mode=debug&edition=2021&gist=39cb31181880457313c2d0e47a692b7a

And here the code for reference:

mod domain;
mod types;
use tokio_postgres::{NoTls, Row};
use types::AsyncResult;

#[derive(Debug)]
pub struct User {
  pub id: i32,
  pub name: String,
}

impl From<Row> for User {
  fn from(row: Row) -> Self {
    Self {
      id: row.get("id"),
      name: row.get("name"),
    }
  }
}

// Using Tokio runtime for async behaviour: https://crates.io/crates/tokio
#[tokio::main]
async fn main() -> AsyncResult<()> {
  // Get DB client and connection
  let (client, connection) = tokio_postgres::connect(
    "postgres://test_user:secret_password@localhost/test_database",
    NoTls,
  )
  .await?;

  // Spawn connection
  tokio::spawn(async move {
    if let Err(error) = connection.await {
      eprintln!("Connection error: {}", error);
    }
  });

  // Do the query
  let result = client.query("SELECT id, name FROM users", &[]).await?;

  let users: Vec<User> = result.into_iter().map(|row| User::from(row)).collect();

  println!("{:#?}", users);
  Ok(())
}

I'm not sure that this is the proper way to do it, if its good practice or if there are better ways to do it. Any advice will be welcome.

Rust and PostgreSQL with tokio_postgres

1 Answers1