Is it possible to deserialize tokio_postgres rows without Struct?

Question

I am new to Rust and trying to build a simple API server which connects to a Postgresql db which has a API route that runs a direct sql query and output JSON as the result.

I did google and found that all the examples used in all the packages available required to unwrap the data per row into a Struct first and this is something I am trying to bypass. I would like the ability to run a dynamic sql query and output it as JSON data to the client.

I am using actix-web, deadpool-postgres and tokio_postgres

Here is what I have so far main.rs

use actix_web::{dev::ServiceRequest, web, App, HttpServer};
use deadpool_postgres::{Manager, Pool};
use tokio_postgres::{Config, NoTls};

mod handlers;

#[actix_rt::main]
async fn main() -> std::io::Result<()> {
   dotenv::dotenv().ok();
   std::env::set_var("RUST_LOG", "actix_web=debug");

   let mut cfg = Config::new();
   cfg.host("localhost");
   cfg.port(5432);
   cfg.user("postgres");
   cfg.password("postgres");
   cfg.dbname("testdb");

   let mgr = Manager::new(cfg, NoTls);
   let pool = Pool::new(mgr, 100);

   // Start http server
   HttpServer::new(move || {
       App::new()
          .data(pool.clone())
          .route("/ExecuteQuery", web::get().to(handlers::execute_query))
   })
   .bind("127.0.0.1:8081")?
   .run()
   .await
}

Here's the handlers.rs

use actix_web::{web, HttpResponse, Error}; // Responder};
use deadpool_postgres::{Pool};
// use tokio_postgres::{Error};

pub async fn execute_query(db: web::Data<Pool>) -> Result<HttpResponse, Error> {
    let mut conn = db.get().await.unwrap();
    let statment = conn.prepare("Select * From People").await.unwrap();

    let rows = conn.query(&statment, &[]).await?;

    // I am trying to use do the following lines but its giving an type mismatched compile error
    // let people = serde_postgres::from_rows(&rows).unwrap();
    // let json = rustc_serialize::json::encode(people).unwrap();

    Ok(HttpResponse::Ok().json("Route called successfully"))
}

Could someone please share your code snippet if you are able to do this without Struct. Thanks

Answer 1

As far as I know, the structure of all query results in the postgres database is not in json format.If you need json format data, you can only get the data first and then manually convert it to json format.At present, there should be no crates that will automatically help you Convert the data to json format, because compared to json, it is obviously easier to use to parse the result directly into the structure

Answer 2

Solution A:

postgres-derive/src/fromsql.rs might be the easist way, every field's value came from postgres-types/src/private.rs, we don't need a prepared struct beacuse of all types based on postgres-types/src/type_gen.rs, that's enough for basic usage. And in theory we can get all kinds of oid through postgresql query even it is user defined structure.

Solution B:

SELECT some_compression_algorithm(json_agg(t), compression_level) FROM (
    your query here
) t;

---

the problem is there might have user defined structure, I am confused either...

but...

there is something interesting, tokio-postgres-mapper use the quote crate as a proc-macro to make mapping from postgresql tables to structs, so why we not use quote or something like it to build another crate, even just use it in our project?

I'll try and update my answer in a few days, otherwise I must come back to kotlin & vert.x(just for fun)