Drizzle Team•11mo ago

An issue when creating string[] elements

I'm getting an error: DatabaseErrorException: ERROR: column "images" is of type text[] but expression is of type text; Hint: You will need to rewrite or cast the expression. For the following code:

const newListings = []
const newListing = {
  images: [...Array(8)].map(() => faker.image.url()),
  ...others
};
newListings.push(newListing)
await db.insert(listings).values(newListings)

const newListings = []
const newListing = {
  images: [...Array(8)].map(() => faker.image.url()),
  ...others
};
newListings.push(newListing)
await db.insert(listings).values(newListings)

I'm using aws rds data api

31 Replies

Vision2023OP•11mo ago

This is the schema

export const listings = pgTable(
  'Listing',
  {
    ...others
    images: text('images')
      .array()
      .default(sql`'{}'`) // Empty array
      .notNull(),
  },
);

export const listings = pgTable(
  'Listing',
  {
    ...others
    images: text('images')
      .array()
      .default(sql`'{}'`) // Empty array
      .notNull(),
  },
);

@Angelelz @solo

Mykhailo•11mo ago

Hi, @Vision2023! Do you use Aurora PostgreSQL or just PostgreSQL?

Vision2023OP•11mo ago

Aurora Postgresql

Angelelz•11mo ago

Can you show the resulting SQL query? You can pass logger: true to the options when instantiating the DB object

Vision2023OP•11mo ago

@Angelelz

Query: insert into "Listing" ("createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId") values (default, :1, default, :2, :3, :4, default, :5, :6, :7, :8, :9, :10, :11, :12, default, default, :13, :14, :15, default, :16) returning "createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId" -- params: [...others, {"name":"4","value":{"stringValue":"{\"https://fastly.picsum.photos/id/59/640/480.jpg?hmac=KL7YRtEDnGdOf0-ba6VvzBE96RyZhIHIItw8gJY0uMI\",\"https://loremflickr.com/cache/resized/65535_52343894968_1e1d9f9a94_z_640_480_nofilter.jpg\",\"https://fastly.picsum.photos/id/1078/640/480.jpg?hmac=AWyYpZM1TMIBU4GkNVU00YQDixss_2ltK0KeFE5WirE\",\"https://loremflickr.com/cache/resized/65535_52295363701_cce36404e0_z_640_480_nofilter.jpg\",\"https://fastly.picsum.photos/id/1072/640/480.jpg?hmac=TwM_RLKBR1UFtdRQfzzEUU7OqImUi3LKOFp2Pb-THuY\"}"}}, ...others]

Query: insert into "Listing" ("createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId") values (default, :1, default, :2, :3, :4, default, :5, :6, :7, :8, :9, :10, :11, :12, default, default, :13, :14, :15, default, :16) returning "createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId" -- params: [...others, {"name":"4","value":{"stringValue":"{\"https://fastly.picsum.photos/id/59/640/480.jpg?hmac=KL7YRtEDnGdOf0-ba6VvzBE96RyZhIHIItw8gJY0uMI\",\"https://loremflickr.com/cache/resized/65535_52343894968_1e1d9f9a94_z_640_480_nofilter.jpg\",\"https://fastly.picsum.photos/id/1078/640/480.jpg?hmac=AWyYpZM1TMIBU4GkNVU00YQDixss_2ltK0KeFE5WirE\",\"https://loremflickr.com/cache/resized/65535_52295363701_cce36404e0_z_640_480_nofilter.jpg\",\"https://fastly.picsum.photos/id/1072/640/480.jpg?hmac=TwM_RLKBR1UFtdRQfzzEUU7OqImUi3LKOFp2Pb-THuY\"}"}}, ...others]

@solo any ideas why?

Mykhailo•11mo ago

@Vision2023 I am testing with aws-data api now. With postgres-js driver everything works

Vision2023OP•11mo ago

Thats weird. Lemme try executing raw sql in drizzle I get same error with this:

await tx.execute(sql`
      INSERT into ${listings} ("createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId")
      VALUES (default, ${data.description}, default, ${handle}, ${crypto.randomUUID()}, ${`{ ${data.images
        .map((image) => `"${image}"`)
        .join(', ')} }`}, default, ${false}, ${data.isDraftListing}, ${data.isPersonalizable}, ${listingId}, ${
        config.marketplaceIntegrationId
      }, ${data.personalizationInstructions}, ${data.personalizationRequired}, ${
        data.personalizationResponseCharacterLimit
      }, default, default, ${data.shippingProfileId}, ${`{ ${data.tags.map((tag) => `"${tag}"`).join(', ')} }`}, ${
        data.title
      }, default, ${userId})
      RETURNING  "createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId"
    `);

await tx.execute(sql`
      INSERT into ${listings} ("createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId")
      VALUES (default, ${data.description}, default, ${handle}, ${crypto.randomUUID()}, ${`{ ${data.images
        .map((image) => `"${image}"`)
        .join(', ')} }`}, default, ${false}, ${data.isDraftListing}, ${data.isPersonalizable}, ${listingId}, ${
        config.marketplaceIntegrationId
      }, ${data.personalizationInstructions}, ${data.personalizationRequired}, ${
        data.personalizationResponseCharacterLimit
      }, default, default, ${data.shippingProfileId}, ${`{ ${data.tags.map((tag) => `"${tag}"`).join(', ')} }`}, ${
        data.title
      }, default, ${userId})
      RETURNING  "createdAt", "description", "digitalListingId", "handle", "id", "images", "isDeleted", "isDigital", "isDraftListing", "isPersonalizable", "listingId", "marketplaceIntegrationId", "personalizationInstructions", "personalizationRequired", "personalizationResponseCharacterLimit", "physicalListingId", "propertyImageBasedOnId", "shippingProfileId", "tags", "title", "updatedAt", "userId"
    `);

DatabaseErrorException: ERROR: column "images" is of type text[] but expression is of type text; Hint: You will need to rewrite or cast the expression.; Position: 461; SQLState: 4280

DatabaseErrorException: ERROR: column "images" is of type text[] but expression is of type text; Hint: You will need to rewrite or cast the expression.; Position: 461; SQLState: 4280

Mykhailo•11mo ago

@Vision2023 your query:

const newListings = [];
const newListing = {
  images: [...Array(8)].map(() => faker.image.url()),
};
newListings.push(newListing);
await db.insert(listings).values(newListings);

const newListings = [];
const newListing = {
  images: [...Array(8)].map(() => faker.image.url()),
};
newListings.push(newListing);
await db.insert(listings).values(newListings);

postgres-js

{
  sql: 'insert into "listings" ("id", "images") values (default, $1)',
  params: [
    '{"https://picsum.photos/seed/YCfBTd/640/480","https://loremflickr.com/640/480?lock=4896008374845440","https://loremflickr.com/640/480?lock=1150658977726464","https://loremflickr.com/640/480?lock=7960465292394496","https://picsum.photos/seed/flwkiz/640/480","https://picsum.photos/seed/LOGcSOHFeK/640/480","https://picsum.photos/seed/qFMnou/640/480","https://picsum.photos/seed/R7z95fPx6/640/480"}'
  ]
}

{
  sql: 'insert into "listings" ("id", "images") values (default, $1)',
  params: [
    '{"https://picsum.photos/seed/YCfBTd/640/480","https://loremflickr.com/640/480?lock=4896008374845440","https://loremflickr.com/640/480?lock=1150658977726464","https://loremflickr.com/640/480?lock=7960465292394496","https://picsum.photos/seed/flwkiz/640/480","https://picsum.photos/seed/LOGcSOHFeK/640/480","https://picsum.photos/seed/qFMnou/640/480","https://picsum.photos/seed/R7z95fPx6/640/480"}'
  ]
}

aws-data-api

{
  sql: 'insert into "listings" ("id", "images") values (default, :1)',
  params: [
    '{"https://picsum.photos/seed/qTc9Q4nr/640/480","https://loremflickr.com/640/480?lock=8177015154802688","https://loremflickr.com/640/480?lock=6794557643554816","https://picsum.photos/seed/iDr0yUQQb/640/480","https://loremflickr.com/640/480?lock=3313232053796864","https://loremflickr.com/640/480?lock=1118154489069568","https://picsum.photos/seed/8Hz2H/640/480","https://picsum.photos/seed/LWH5BZ9FDF/640/480"}'
  ]
}

{
  sql: 'insert into "listings" ("id", "images") values (default, :1)',
  params: [
    '{"https://picsum.photos/seed/qTc9Q4nr/640/480","https://loremflickr.com/640/480?lock=8177015154802688","https://loremflickr.com/640/480?lock=6794557643554816","https://picsum.photos/seed/iDr0yUQQb/640/480","https://loremflickr.com/640/480?lock=3313232053796864","https://loremflickr.com/640/480?lock=1118154489069568","https://picsum.photos/seed/8Hz2H/640/480","https://picsum.photos/seed/LWH5BZ9FDF/640/480"}'
  ]
}

also, I executed this and it worked out:

await db.execute(
  sql.raw(
    `insert into "listings" ("id", "images") values (default, '{"https://picsum.photos/seed/qTc9Q4nr/640/480","https://loremflickr.com/640/480?lock=8177015154802688","https://loremflickr.com/640/480?lock=6794557643554816","https://picsum.photos/seed/iDr0yUQQb/640/480","https://loremflickr.com/640/480?lock=3313232053796864","https://loremflickr.com/640/480?lock=1118154489069568","https://picsum.photos/seed/8Hz2H/640/480","https://picsum.photos/seed/LWH5BZ9FDF/640/480"}')`,
  ),
)

await db.execute(
  sql.raw(
    `insert into "listings" ("id", "images") values (default, '{"https://picsum.photos/seed/qTc9Q4nr/640/480","https://loremflickr.com/640/480?lock=8177015154802688","https://loremflickr.com/640/480?lock=6794557643554816","https://picsum.photos/seed/iDr0yUQQb/640/480","https://loremflickr.com/640/480?lock=3313232053796864","https://loremflickr.com/640/480?lock=1118154489069568","https://picsum.photos/seed/8Hz2H/640/480","https://picsum.photos/seed/LWH5BZ9FDF/640/480"}')`,
  ),
)

Vision2023OP•11mo ago

Please whats ur schema like. Maybe its my schema

 id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
    images: text('images')
      .array()
      .default(sql`'{}'`) // Empty array
      .notNull(),

 id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
    images: text('images')
      .array()
      .default(sql`'{}'`) // Empty array
      .notNull(),

This is mine @solo

Mykhailo•11mo ago

export const tests = pgTable('tests', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => sql`gen_random_uuid()::text`)
    .notNull(),
  images: text('images')
    .array()
    .default(sql`'{}'`) // Empty array
    .notNull(),
});

export const tests = pgTable('tests', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => sql`gen_random_uuid()::text`)
    .notNull(),
  images: text('images')
    .array()
    .default(sql`'{}'`) // Empty array
    .notNull(),
});

Mykhailo•11mo ago

schemas are the samew, I think problem is here

Mykhailo•11mo ago

because when I execute raw query as an example above, everything works out, but when we use query builder and then log query, we can see, that there is :1 instead of $1. In postgres-js you can see this difference So, there is no problem with images in your schema You can create github issue for that or I can do it if you want

Vision2023OP•11mo ago

Ok so the problem is :1 instead of $1? I'm really sorry but I dont really get the issue Maybe the issue lies with the rds data api or sth

Angelelz•11mo ago

Your query doesn't seem right to me. Seems like you're passing a jsonb object? Console log the objects you are inserting. That might be your problem

Mykhailo•11mo ago

I am sorry, I was wrong about :1, but I don't understand now what cause the problem

await db.insert(schema.tests).values({
  images: ['a', 'c'],
}); // This will fail

await db.insert(schema.tests).values({
  images: sql`ARRAY['a', 'c']`,
}); // This will succeed

await db.insert(schema.tests).values({
  images: ['a', 'c'],
}); // This will fail

await db.insert(schema.tests).values({
  images: sql`ARRAY['a', 'c']`,
}); // This will succeed

SQL Query:

{
  sql: 'insert into "tests" ("id", "images", "test_field") values (gen_random_uuid()::text, :1, default)',
  params: [ '{"a","c"}' ]
}
{
  sql: `insert into "tests" ("id", "images", "test_field") values (gen_random_uuid()::text, ARRAY['a', 'c'], default)`,
  params: []
}

{
  sql: 'insert into "tests" ("id", "images", "test_field") values (gen_random_uuid()::text, :1, default)',
  params: [ '{"a","c"}' ]
}
{
  sql: `insert into "tests" ("id", "images", "test_field") values (gen_random_uuid()::text, ARRAY['a', 'c'], default)`,
  params: []
}

  await db.insert(schema.tests).values({
    images: sql`'{"a","c"}'`,
  }); // also works

  await db.insert(schema.tests).values({
    images: sql`'{"a","c"}'`,
  }); // also works

Vision2023OP•11mo ago

Ok I just realised the issue is prolly because of rds data api because when I was using postgres-js driver, it was working

Mykhailo•11mo ago

@Vision2023 +, I wrote about it above, but I'm curious whether the problem lies with the Drizzle AWS Data API package or with the RDS Data API.

kieranmch•11mo ago

We're running into this issue too 😦

Vision2023OP•11mo ago

@solo Its an issue with rds data api Lack of support for arrays. However, theres a workaround tho Manually casting the field as an array(text[] in my case) in the query Like

INSERT INTO ..... VALUES (:1, :2, cast(:3 as text[])

INSERT INTO ..... VALUES (:1, :2, cast(:3 as text[])

@kieranmch do u have any workarounds?

Vision2023OP•11mo ago

@solo Thinking of making a lil update to do this automatically for data api in drizzle-orm. I dont think the pr would be accepted tho but at least I can use the fork in my project. Wdyt BTW Source: https://github.com/koxudaxi/local-data-api#how-to-insert-array-data-into-postgresql

GitHub

GitHub - koxudaxi/local-data-api: Data API for local, you can write...

Data API for local, you can write unittest for AWS Aurora Serverless's Data API - GitHub - koxudaxi/local-data-api: Data API for local, you can write unittest for AWS Aurora Serverless&am...

Mykhailo•11mo ago

@Vision2023 I think you are right, that issue is with rds fata api. parameters: Array parameters are not supported. https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html#API_ExecuteStatement_RequestBody Also, you can try another workaround, cast js array to postgres array and then pass it to your query:

const arr = [1, 2, 3];

const postgresArray = `{${arr.join(',')}}`;

const arr = [1, 2, 3];

const postgresArray = `{${arr.join(',')}}`;

ExecuteStatement - RDS Data API

Runs a SQL statement against a database.

Vision2023OP•11mo ago

Mykhailo•11mo ago

btw, you can create an issue on github (enhancement tag) for this

Vision2023OP•11mo ago

Ok Will do

kieranmch•11mo ago

We tried the raw SQL solution and it broke the typeHint feature in a horrendous way, the types all ended up getting shifted along one and mismatched with the values Like it was submitting 7 values but only 6 types

Vision2023OP•11mo ago

So what did u end up with @kieranmch

Vision2023OP•11mo ago

@solo @Angelelz This change works for me. Wdyt about it

Vision2023OP•11mo ago

In drizzle-orm/src/aws-data-api/pg/driver.ts

Vision2023OP•11mo ago

@solo @Angelelz Created an issue https://github.com/drizzle-team/drizzle-orm/issues/1912 and pull request https://github.com/drizzle-team/drizzle-orm/pull/1911

GitHub

[FEATURE]: Add support for inserting and updating arrays in aws-dat...

Describe what you want Currently in drizzle, if you want to insert or update an array, there is a typing issue like this: DatabaseErrorException: ERROR: column "images" is of type text[] ...

GitHub

[Pg] Add insert/update array support in aws-data-api by livingforj...

Currently in drizzle, if you want to insert or update an array, there is a typing issue like this: DatabaseErrorException: ERROR: column "images" is of type text[] but expression is of ty...

Mykhailo•11mo ago

@Vision2023 Sorry for the late response. If it works, then that's great. It is cool that you've also created tests for it. The Drizzle team will check it as needed or if the opportunity arises.

Vision2023OP•11mo ago

@Andrew Sherman is it possible to get a timeline on how long it'll take sth like this to be reviewed and potentially merged in

Gaming

Programming

An issue when creating string[] elements