AWS RDS Array Parameters are not Supported

Question

I am trying to save an array of values in AWS RDS. When I go into the RDS query editor I am able to run this insert without any issues:

insert into picture_tags (picture_id, tag_id)
values (picId, unnest(ARRAY[tagId1, tagId2])::uuid);
(the IDs are UUIDs but to save space I just left them as variable names).

I am using the data-api-client for all of my queries except for this one because it cannot handle arrays per the documentation.

Using just the basic AWS-SDK I have the following code:

const pictureId = '3e1eb325-95fa-4229-9597-4e2f9f27a2df'
const tagIds = [
  'cd4bb6dc-9c74-4ed1-b66c-f0865a792aaa',
  '517f1d68-e964-4564-a9d0-d4b776c0af4d'
]

const db = new aws.RDSDataService()

const sql = `
  INSERT INTO picture_tags (picture_id, tag_id)
  VALUES (:pictureId, unnest(ARRAY[:tagIds]));
`

const params = {
  sql,
  parameters: [
    { name: 'picture_id', value: { stringValue: pictureId }},
    { name: 'tag_id', value: { arrayValue: { stringValues: tagIds }}}
  ],
  secretArn: 'secretArn',
  resourceArn: 'resourceArn',
  database: 'databaseName',
}

const res = await db.executeStatement(params).promise()

I then get the following errors:

BadRequestException: Array parameters are not supported.

Answer 1

Turns out this is possible using the data-api-client with a little workaround:

const pictureId = '3e1eb325-95fa-4229-9597-4e2f9f27a2df'
const tagIds = [
  'cd4bb6dc-9c74-4ed1-b66c-f0865a792aaa',
  '517f1d68-e964-4564-a9d0-d4b776c0af4d'
]

const sql = `
  INSERT INTO picture_tags (picture_id, tag_id)
  VALUES (:pictureId, unnest(array[:tagIds]));
`

const sqlValues = [
  { name: 'pictureId', value: pictureId, cast: 'uuid' },
  { name: 'tagIds', value: `{${tagIds.join(',')}}`, cast: 'uuid[]' },
]

try {
  await rdsService.query(sql, sqlValues)
  return { status: 200 }
} catch (err) {
  console.log('Error : ', err)
  throw err
}

{${tagIds.join(',')}} is the part to pay attention to. Converting the array into a string will make it work. I'm not sure if this causes any issues with SQL injections but it's a working solution.

Replace tagIds with your array, change the cast and you should be good. Feel free to read more here:

https://github.com/aws/aws-sdk/issues/9