Nested arrays can't be encoded: PgHasArrayType is not satisfied

[seanlinsley]

Bug Description

Unlike the code in #1945 implies, passing nested arrays as bind params does not work:

Slices of strings

let a = &[&["a".to_string()], &["b".to_string()]];
let mut query = sqlx::query("SELECT $1::text[]");
query = query.bind(&a[..]);
query.execute(&db).await.unwrap();

// the trait bound `&[std::string::String; 1]: PgHasArrayType` is not satisfied

Vecs of strings

let a = vec![vec!["a".to_string()], vec!["b".to_string()]];
let mut query = sqlx::query("SELECT $1::text[]");
query = query.bind(a);
query.execute(&db).await.unwrap();

// the trait bound `Vec<String>: PgHasArrayType` is not satisfied

Vecs of integers

let a: Vec<Vec<i32>> = vec![vec![1], vec![2]];
let mut query = sqlx::query("SELECT $1::int[]");
query = query.bind(a);
query.execute(&db).await.unwrap();

// the trait bound `Vec<i32>: PgHasArrayType` is not satisfied

Info

• SQLx version: I've tried both 0.6.x and 0.5.x
• SQLx features enabled: postgres, all-types, runtime-tokio-native-tls
• Database server and version: Postgres 14
• Operating system: mac
• rustc --version: 1.63

[seanlinsley]

However, JSON support seems to be much better. Maybe the documentation should point users to jsonb[] / Vec<serde_json::Value> with examples.

use sqlx::Row;
use serde_json::{json, Value};

let a = vec![json!([1]), json!([2])];
let mut query = sqlx::query("select * from unnest($1::jsonb[])");
query = query.bind(&a);

let results = query.fetch_all(&db).await.unwrap();
let result: Value = results[0].get(0);
assert_eq!(result, json!([1]));

[rustonaut]

Some helpful context if someone wants to fix it:

• the is currently no way in sqlx to specify a type like FLOAT4[][]
• but to quote the postgres doc: "The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation."

What that means is that for postgres FLOAT4[], FLOAT4[][], FLOAT4[][][] are currently all the same type.

It might be worth to ping postgres developers about what future plans they have there (after checking if someone already did so). But with them documenting it as "simply documentation" I don't expect it to change.

If implementing Vec<Vec<T>> and similar isn't possible (or too complex) due to rust orphan rules (I didn't check) a option for the sqlx::Type derive to support nested array new types would probably be good enough IMHO, especially if it supports new-types in the nesting.

[abonander]

Unlike the code in https://github.com/launchbadge/sqlx/issues/1945 implies, passing nested arrays as bind params does not work:

Yeah, in that context I was focused more on the SQL part of the solution of handling nested arrays than binding nested arrays from SQLx.

We could theoretically support nested arrays as Vec<Vec<_>>, &[Vec<_>], etc., but the worst part is that the dimensionality of arrays is dynamic and not encoded in the type info at all, so we really have no way to guarantee at compile time that the Rust side matches the SQL side.

That's also an issue with 1-dimensional arrays but I feel like it's less significantly less likely for one to accidentally mix 1 dimensional arrays with N dimensional arrays than it is to mix N-dimentional arrays with M-dimensional arrays, so we can mostly get away with not checking for the former.

Even worse, individual subarrays within a dimension aren't even required to be the same length, which is why you only have functions to get the upper and lower bounds.

I think the best approach would be to provide a type that can directly support this insanity. It would probably have to be bespoke as I don't know of any crate that supports something like this, besides the postgres crate as postgres_array::Array which I'm not a big fan of the API anyway.

[mikonieminen]

I was struggling with this issue too and thought it might be worth of sharing my approach. While it's far from a perfect solution, it allowed me to move forward and wait for a proper one.

Here is an example code: https://gist.github.com/mikonieminen/4a0275643ec3378714d1955a6337b3ca

In short, I created a custom type PgArray2D that wraps Vec<Vec<T>> and implements Encode that outputs two dimensional array. Implementation supports only i64 mapping to BIGINT[][] and &str mapping to TEXT[][]. The code should be relatively simple, so when needed it's quite easy to modify for other types.