cannot insert array with PostgreSQL

[theDevelopper]

Describe the bug Using local-data-api 0.6.7 with PostgreSQL I get an error when trying to pass an array as parameters in AWS.RDSDataService function executeStatement using JavaScript. Postgres supposts Arrays as data type and RDSDataService does as well. But using arrays with local-data-api seems to not be supported as I get the following error in the docker logs:

2021-04-08 11:59:22.921 [eventLoopGroupProxy-4-2] ERROR Application - Unhandled: POST - /Execute kotlinx.serialization.json.internal.JsonDecodingException: Unexpected JSON token at offset 1146: Encountered an unknown key 'arrayValue'

To Reproduce Steps to reproduce the behavior:

• Have setup using ProstgreSQL
• Create a simple table with a column called col of type TEXT[] CREATE TABLE test ( col TEXT[] )

• have simple node script trying to insert data

  const AWS = require('aws-sdk');
  const client = new AWS.RDSDataService({
    region: 'us-east-1',
    endpoint: 'http://127.0.0.1:8080',
  });
  
  (async function() {
    try {
        await client.executeStatement({
            resourceArn: 'arn:aws:rds:us-east-1:123456789012:cluster:dummy',
            secretArn: 'arn:aws:secretsmanager:us-east-1:123456789012:secret:dummy',
            database: 'test',
            sql: 'INSERT INTO test (col) VALUES (:data)',
            parameters: [{
                name: 'data',
                value: {
                    arrayValue: {
                        stringValues: ['a', 'b']
                    },
                },
            }]
        }).promise();
    }
    catch (e) {
        console.log(e);
    }
  })();

Expected behavior a new entry in table test is created with the array ['a', 'b'] as value for column col.

Additional context AWS documentation: https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html

[koxudaxi]

@theDevelopper Thank you for creating this issue. Yes, I have not implemented it yet. I will add the feature.

[ormu5]

FYI, for those Python/boto3 users out there, I do not believe boto3 yet supports this: https://github.com/boto/boto3/issues/2272. So you will run the chance of this working correctly locally but breaking upon deploy.

I have taken to inserting arrays to Postgres as strings and explicitly casting them in the SQL statement.

[koxudaxi]

@theDevelopper I ran your code. But, I can get this error.

BadRequestException: Array parameters are not supported.
....

You use aws-sdk. But, aws-sdk doesn't support array parameters. https://github.com/aws/aws-sdk/issues/9

I think the feature is priority low.

[koxudaxi]

@theDevelopper I confirm we can't insert array value into real data-api. And Latest local-data-api returns the same error when you give arrayValue. If you want to insert array value you should try this workaround. https://github.com/koxudaxi/local-data-api#how-to-insert-array-data-into-postgresql