Trait sqlx::Row as param in place of postgres::PgRow and mysql::MySqlRow

Hi,

Please help with the following issue. While this is strictly not a problem, I managed to get what I wanted to work. But would just like to reduce code duplication, and more importantly improve my understanding on generic.

I'm experimenting with supporting multiple database types in an application (which I've done before in Delphi.)

The databases are MySQL and PostgreSQL. And the database crate is sqlx.

I have an employees table, and several related structs to hold only relevant fields that I need for specific purposes. And several different functions which to query the database and serialise results into appropriate struct.

For each struct, I plan to implement an extract method to copy the data from the query row to the struct. E.g., for struct Employee:

impl Employee {
    pub fn extract(row: &sqlx::postgres::PgRow) -> Employee {
    }
}
impl Employee {
    pub fn extract(row: &sqlx::mysql::MySqlRow) -> Employee {
    }
}

-- Both Struct sqlx::postgres::PgRow and Struct sqlx::mysql::MySqlRow implement Trait sqlx::Row.

I tried to use Trait sqlx::Row as parameter in place of these two structs, but so far I've not been able to do it yet.

I'm listing the relevant snippets below.

Common declarations

use sqlx::{FromRow, Row};

use sqlx::types::time::Date;
use serde::{Serialize, Deserialize};

use crate::utils;

...

#[derive(FromRow, Serialize, Debug)]
pub struct Employee {
    pub emp_no: i32,
    #[serde(with = "utils::australian_date_format")]
    pub birth_date: Date,
    pub first_name: String,
    pub last_name: String,    
    pub gender: String,
    #[serde(with = "utils::australian_date_format")]
    pub hire_date: Date,
}

pub trait Extract<T, U> {
    fn extract(row: &U) -> T;
}

Please note, pub trait Extract...

PostgreSQL implementation

use sqlx::{Row, Pool, Postgres};

use async_std::task;

impl Employee {
    pub fn extract(row: &sqlx::postgres::PgRow) -> Employee {
        Employee {
            emp_no: row.get(0),
            birth_date: row.get(3),
            first_name: row.get(4),
            last_name: row.get(5),
            gender: row.get(6),
            hire_date: row.get(7),
        }
    }
}

impl Extract<Employee, sqlx::postgres::PgRow> for Employee {
    fn extract(row: &sqlx::postgres::PgRow) -> Employee {
        Employee::extract(row)
    }
}

pub async fn select_employee<T>(
    pool: &Pool<Postgres>,
    email: &str
) -> Option<T> 
where
    T: Extract<T, sqlx::postgres::PgRow>,
{
    let sql = format!("select * from employees where email = '{}'", email);

    match sqlx::query(&sql).fetch_one(pool).await {
        Err(_) => return None,
        Ok(row) => Some(T::extract(&row))
    }
}

fn main() {
    let postgres_pool = task::block_on(connect_to_postgres_server());

    match task::block_on(select_employee::<Employee>(&postgres_pool, "zdislav.nastansky.10191@gmail.com")) {
        None => println!("postgres -- No employee found."),
        Some(e) => println!("postgres -- Employee: {:#?}", e),
    }
}

MySQL implementation

use sqlx::{Row, Pool, MySql};

use async_std::task;

impl Employee {
    pub fn extract(row: &sqlx::mysql::MySqlRow) -> Employee {
        Employee {
            emp_no: row.get(0),
            birth_date: row.get(3),
            first_name: row.get(4),
            last_name: row.get(5),
            gender: row.get(6),
            hire_date: row.get(7)
        }
    }
}

impl Extract<Employee, sqlx::mysql::MySqlRow> for Employee {
    fn extract(row: &sqlx::mysql::MySqlRow) -> Employee {
        Employee::extract(row)
    }
}

pub async fn select_employee<T>(
    pool: &Pool<MySql>,
    email: &str
) -> Option<T> 
where
    T: Extract<T, sqlx::mysql::MySqlRow>,
{
    let sql = format!("select * from employees where email = '{}'", email);

    match sqlx::query(&sql).fetch_one(pool).await {
        Err(_) => return None,
        Ok(row) => Some(T::extract(&row))
    }
}

fn main() {
    let mysql_pool = task::block_on(connect_to_mysql_server());

    match task::block_on(select_employee::<Employee>(&mysql_pool, "zdislav.nastansky.10191@gmail.com")) {
        None => println!("mysql -- No employee found."),
        Some(e) => println!("mysql -- Employee: {:#?}", e),
    }
}

I have tried the following

impl Employee {
    pub fn extract<T>(row: &T) -> Employee 
    where
        T: Row,
    {
        Employee {
            emp_no: row.get(0),
            email: row.get(1),            
            birth_date: row.get(3),
            first_name: row.get(4),
            last_name: row.get(5),
            gender: row.get(6),
            hire_date: row.get(7),
        }
    }
}

And the error:

error[E0277]: the trait bound `i32: sqlx::Decode<'_, <T as Row>::Database>` is not satisfied
  --> src\postgres_funcs.rs:19:25
   |
19 |             emp_no: row.get(0),
   |                         ^^^ the trait `sqlx::Decode<'_, <T as Row>::Database>` is not implemented for `i32`
   |
note: required by a bound in `sqlx::Row::get`
  --> C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\sqlx-core-0.7.1\src\row.rs:72:12
   |
72 |         T: Decode<'r, Self::Database> + Type<Self::Database>,
   |            ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `Row::get`
help: consider introducing a `where` clause, but there might be an alternative better way to express this requirement
   |
13 | impl Employee where i32: sqlx::Decode<'_, <T as Row>::Database> {
   |               +++++++++++++++++++++++++++++++++++++++++++++++++

Could you please help with this question? Or what I am trying to to is wrong?

Thank you and best regards,

...behai.

P.S.

My final objective is having only a single implementation for these two pairs:

// a
impl Extract<Employee, sqlx::postgres::PgRow> for Employee
// a
impl Extract<Employee, sqlx::mysql::MySqlRow> for Employee

// b
pub async fn select_employee<T>(
    pool: &Pool<Postgres>,
    email: &str
) -> Option<T> 
where
    T: Extract<T, sqlx::postgres::PgRow>,
	
// b  
pub async fn select_employee<T>(
    pool: &Pool<MySql>,
    email: &str
) -> Option<T> 
where
    T: Extract<T, sqlx::mysql::MySqlRow>,

This is called deserialization. Don't write manual implementations for this. Simply leverage Serde's Deserialize macro.

1 Like

Hi moy2010,

Thank you for your suggestion. It led me to remember macro sqlx::query_as.

-- It does what I was trying to do in a single call:

async fn test(pool: &MySqlPool) {
    let query_result = sqlx::query_as!(Employee, "SELECT * FROM employees WHERE email = 'zdislav.nastansky.10191@gmail.com'")
        .fetch_one(pool)
        .await
        .unwrap();

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

async fn test1(pool: &MySqlPool) {
    let query_result = sqlx::query_as!(Employee, "SELECT * FROM employees WHERE (last_name like '%chi') and (first_name like '%ak')")
        .fetch_all(pool)
        .await
        .unwrap();

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

fn main() {
    let mysql_pool = task::block_on(connect_to_mysql_server());

    task::block_on(test(&mysql_pool));

    println!("\n------\n");

    task::block_on(test1(&mysql_pool));
}

It seems that this macro, as per others, require .env file exists (at the same level as Cargo.toml file), and DATABASE_URL entry. I.e.:

DATABASE_URL=mysql://root:pcb.2176310315865259@localhost:3306/employees

Thank you and best regards,

...behai.

1 Like

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.