SequelizeJS - insert JSONB to Postgres with raw query - how to escape

4
votes

I want to insert a row with JSONB content as a raw query. The problem is that I get error wherever I insert any "nontrivial" JSON object.

These are examples of my raw SQL queries:

Error for all of these: SequelizeBaseError: invalid input syntax for type json

'INSERT INTO "sometable" ("id", "createdAt", "updatedAt", "custom_data") VALUES (DEFAULT, NOW(), NOW(), \'"{something:\"value\"}"\')';
'INSERT INTO "sometable" ("id", "createdAt", "updatedAt", "custom_data") VALUES (DEFAULT, NOW(), NOW(), \'"{\"aaa\":\"ss15\"}"\')';

This last one works, but only with integer values:

'INSERT INTO "sometable" ("id", "createdAt", "updatedAt", "custom_data") VALUES (DEFAULT, NOW(), NOW(), \'"{a:10}"\')';

I've tried so many possible combinations of escaping the JSON-like string, even inserting real JS object, and nothing worked.

1
node.jspostgresqlsequelize.js

1 Answers

7
votes

Try to JSON.stringify() first:

var customData = JSON.stringify({"name":"George Washington","presidentNo":1});