PostgreSQL "syntax error at end of input" after INSERT INTO with GraphQL and NodeJS

Question

Using pg-promise to insert a user into the database like so:

const { db } = require('../../utils/pgAdaptor');
const query = 'INSERT INTO "Users" (email, password, "createdAt", "updatedAt") VALUES';
    const values = [email, password, new Date(), new Date()];
    console.log(values) // [ 'test', 'test', '2019-03-01T05:08:34.523Z', '2019-03-01T05:08:34.523Z' ]
    return db
      .none(query, values)
      .then(res => res)
      .catch(err => err);

I can insert a row myself using pgAdmin

INSERT INTO "Users" (email, password, "createdAt", "updatedAt") VALUES ('test', 'test', '2019-03-01T05:08:34.523Z', '2019-03-01T05:08:34.523Z');

I've tried using db.one and I've tried a couple of the examples from PostgreSQL Docs but I get the error "message": "syntax error at end of input" from the GraphiQL interface either way. What am I missing here? What is wrong with the input exactly?

Golo Roden · Accepted Answer · 2019-03-01 05:58:51Z

2

You need to add placeholders such as $1, $2, … into your query so that the PostgreSQL driver nows where and how to insert the values you provide. See the documentation of the pg module for more details.

This could then look like this:

INSERT INTO "Users" (email, password, "createdAt", "updatedAt") VALUES ($1, $2, $3, $4)

The way you wrote it you actually try to run

INSERT INTO "Users" (email, password, "createdAt", "updatedAt") VALUES

which, as the error message states, is a syntax error.

Besides that: What is this .none() method? I have never seen it, and the documentation for the pg module doesn't mention it either. From my point of view this should be .query().

answered Mar 1, 2019 at 5:58

Golo Roden

152k102 gold badges318 silver badges445 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathan Over a year ago

From the methods sections of the pg page here, "All query methods of the library are based off generic method query." So you're right, query() would work too. .none() just means no rows are returned, .one() is one row returned, etc.

Golo Roden Over a year ago

Ah, okay, thanks for the explanation! I didn't know that.

Ashok · Accepted Answer · 2019-03-01 05:59:01Z

MySQL make statements using ? as the parameter, And PostgreSQL uses $1, $2 etc.

Now your current query is

const query = `INSERT INTO "Users" 
              (email, password, "createdAt", "updatedAt") 
              VALUES`;

You need to convert your query into this format

const query = `INSERT INTO "Users" 
               (email, password, "createdAt", "updatedAt")
               VALUES 
               ($1, $2, $3, $4)`;

Now in your query become

db.Query(`INSERT INTO "Users" 
          (email, password, "createdAt", "updatedAt")
          VALUES 
          ($1, $2, $3, $4)`, 
          ['test', 'test', '2019-03-01T05:08:34.523Z','2019-03-01T05:08:34.523Z'])

You can use string instead of the template string. Just for clearification syntax.

Collectives™ on Stack Overflow

PostgreSQL "syntax error at end of input" after INSERT INTO with GraphQL and NodeJS

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related