TLDR: Given a query that returns rows with a column containing a Json array:
id | name | post_ids
---- --------- ----------
1 | JohnDoe | [1,2]
Serializing it using tokio_postgres into the User model —which contains a post_ids: Vec<i32> field— seems impossible.
#[derive(Debug, Serialize, Deserialize, JsonSchema)]
pub struct User {
pub id: String,
pub name: String,
pub post_ids: Vec<i32>
}
Tried implementing tokio_postgres::types::FromSql for i32, but it is only allowed for structs.
I wonder what is the common way to convert between this Json and Vec<i32>.
Long version with full code:
I have a Postgres Database with a User and Post models:
id | name
---- ---------
1 | JohnDoe
id | title | user_id
---- ------------- ---------
1 | first post | 1
2 | second post | 1
I have a function to retrieve the user along with posts from database. I know I can do it better, but it is intended, as my question is about the posts_ids that this function is returning:
create function user_get_one(
user_id int
)
returns table (
"id" text,
"name" text,
post_ids json
) AS $$
select
"user"."id",
"user"."name",
(select to_json(array_agg("posts"."id"))
from (
select
"id"
from
"post"
where
"post"."user_id" = user_id
) posts
) posts_ids
from "user"
where
"user".id = user_id;
$$ language sql;
Here is a fiddle with all DB.
Now, for testing purposes, I want an API in Rust that connects to this DB, calls this function, instantiates the appropriate models and print the data in the console, returning nothing:
Cargo.toml:
[package]
name = "test"
version = "0.1.0"
edition = "2018"
[dependencies]
tokio-postgres = "0.7.5"
tokio = { version = "1.14.0", features = ["full"] }
The imports:
use tokio_postgres::{NoTls, Row};
The models:
#[derive(Debug, Serialize, Deserialize, JsonSchema)]
pub struct User {
pub id: String,
pub name: String,
// pub post_ids: Vec<i32>, // Uncomment
}
impl From<Row> for User {
fn from(row: Row) -> Self {
Self {
id: row.get("id"),
name: row.get("name"),
// post_ids: row.get("post_ids"), // Uncomment
}
}
}
And the main function:
#[tokio::main]
async fn main() -> () {
let (client, connection) = tokio_postgres::connect(
"postgresql://localhost/rc_forum?user=test_user&password=secret_password ",
NoTls,
)
.await
.unwrap();
tokio::spawn(async move {
if let Err(e) = connection.await {
eprintln!("connection error: {}", e);
}
});
// Now we can execute a simple statement that just returns its parameter.
let result = client
.query_one("select * from user_get_one($1)", &[&1])
.await
.unwrap();
let user: User = User::from(result);
println!("-----------");
println!("{:#?}", user);
println!("-----------");
}
Here is the full code in Rust playground: https://play.rust-lang.org/?version=stable&mode=debug&edition=2021&gist=677a4f7710f6a29c7e33d3228679881f
Now, with posts_ids commented out the user is loaded and instantiated; but if we uncomment posts_ids, postgres_tokio panicks as can not convert types:
error retrieving column post_ids: error deserializing column 2:
cannot convert between the Rust type `alloc::vec::Vec<i32>` and the Postgres type `json`',
What is the common way to convert between this Json and Vec<i32>?
CodePudding user response：
I don't know much about Rust or tokio_postgres, but I gather that a RUST vector is basically an array that can grow in size - the equivalent of a Postgres array. Vec<i32> is a RUST vector of 4-byte integers. I don't think that can take a Postgres json value. Use a Postgres array of integer (int[]) instead:
While being at it, I simplified your convoluted function a bit:
CREATE FUNCTION user_get_one(_user_id int)
RETURNS TABLE (id text, name text, post_ids int[])
LANGUAGE sql AS
$func$
SELECT u.id
, u.name
, ARRAY(SELECT p.id FROM post p
WHERE p.user_id = _user_id) AS posts_ids
FROM "user" u -- very unfortunate name!
WHERE u.id = _user_id;
$func$;
db<>fiddle here
About the ARRAY constructor:
I would add an ORDER BY clause to the subquery to get a deterministic result. Else, the next call may report the same array with a different sort order. So:
...
, ARRAY(SELECT p.id FROM post p
WHERE p.user_id = _user_id ORDER BY 1) AS posts_ids
...
In case you already have a JSON array, here's how to convert it:
Aside: don't use reserved words like "user" as Postgres identifier.