Json Datatypes are not natively supported

[CaptainYarb]

When sending a JSON object or JSON stringified string I get the following errors. I can't tell if I should be doing something different.

JSON Object

target: readcted.-.primary: vttablet: rpc error: code = Unknown desc = Invalid JSON text: \"Invalid value.\" at position 1 in value for `data`) values (:vtg1,)\", BindVars: {vtg1: \"type:VARCHAR value:\\\"[object Object]\\\"\}

JSON Stringified

target: redacted.-.primary: vttablet: rpc error: code = Unknown desc = Invalid JSON text: \"Invalid value.\" at position 0 in value for column 'Redacted.data'. (errno 3140) (sqlstate 22032) (CallerID: planetscale-admin): Sql: \"insert into Redacted(`data`) values (:vtg1)\", BindVars: {vtg1: \"type:VARCHAR value:\\\"{\\\\\\\"foo\\\\\\\":\\\\\\\"bar\\\\\\\"}\\\"\}

Table Schema

CREATE TABLE `Redacted` (
	`id` varchar(191) NOT NULL,
	`createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`data` json NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_unicode_ci;

[iheanyi]

Thanks for reporting this @CaptainYarb. Could you provide us more information on how you're executing this query so we can try and reproduce it?

What happens when you replace the format function with something like Sqlstring? Check it out in the docs here: https://github.com/planetscale/database-js#custom-query-parameter-format-function

[dgraham]

One error in the original SQL is a missing id column value that's required because it's defined as not null. Does this work for you with the id provided?

import { connect } from '@planetscale/database'

const config = {
  username: '<user>',
  host: '<host>',
  password: '<password>'
}

const conn = connect(config)
const document = JSON.stringify({ foo: 'bar' })
const insert = await conn.execute('insert into Redacted(id, `data`) values (?, ?)', [1, document])
console.log(insert.statement)

// => insert into Redacted(id, `data`) values (1, '{\"foo\":\"bar\"}')