Json Datatypes are not natively supported
CaptainYarb opened this issue · 2 comments
CaptainYarb commented
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 commented
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 commented
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\"}')