Sequelize: Cannot read property '0' of undefined with pg 7

[tapz]

What you are doing?

I just upgraded to Sequelize 4.4.2 and node-postgres (pg) version 7.0.2

Then, when I call sync() to connecto the db and create tables. I get an error

It happens, for example, when I run this command:

models.sequelize.sync({force: true}).

What do you expect to happen?

Sequelize should connect to Postgres and the create tables.

What is actually happening?

TypeError: Cannot read property 'Symbol(Symbol.iterator)' of undefined
    at Promise.then.result (C:\test\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:157:31)
    at tryCatcher (C:\test\node_modules\bluebird\js\release\util.js:16:23)
    at Promise._settlePromiseFromHandler (C:\test\node_modules\bluebird\js\release\promise.js:512:31)
    at Promise._settlePromise (C:\test\node_modules\bluebird\js\release\promise.js:569:18)
    at Promise._settlePromise0 (C:\test\node_modules\bluebird\js\release\promise.js:614:10)
    at Promise._settlePromises (C:\test\node_modules\bluebird\js\release\promise.js:693:18)
    at Async._drainQueue (C:\test\node_modules\bluebird\js\release\async.js:133:16)
    at Async._drainQueues (C:\test\node_modules\bluebird\js\release\async.js:143:10)
    at Immediate.Async.drainQueues (C:\test\node_modules\bluebird\js\release\async.js:17:14)
    at runCallback (timers.js:651:20)
    at tryOnImmediate (timers.js:624:5)
    at processImmediate [as _immediateCallback] (timers.js:596:5)

__Dialect: postgres
__Database version: 9.6.2 (pg 7.0.2)
__Sequelize version: 4.4.2

[chaserelock]

I get this as well, I can solve it by checking that the rows array exists in query.js:112

        // change to (rows && rows[0] && ...)
        if (rows[0] && rows[0].sequelize_caught_exception !== undefined) {
          if (rows[0].sequelize_caught_exception !== null) {
            throw this.formatError({
              code: '23505',
              detail: rows[0].sequelize_caught_exception
            });
          } else {
            for (const row of rows) {
              delete row.sequelize_caught_exception;
            }
          }
        }

[wavded]

Also confirm this is an issue for me.

[fnando]

I just got this error and the code is something like this:

db
  .query(sql, {raw: true})
  .then(done)
  .catch((error) => { throw error })

And this is the backtrace:

Unhandled rejection TypeError: Cannot read property '0' of undefined
    at query.catch.then.then.queryResult (myproject/node_modules/sequelize/lib/dialects/postgres/query.js:112:17)
    at tryCatcher (myproject/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (myproject/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (myproject/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (myproject/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (myproject/node_modules/bluebird/js/release/promise.js:693:18)
    at Async._drainQueue (myproject/node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (myproject/node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (myproject/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:672:20)
    at tryOnImmediate (timers.js:645:5)
    at processImmediate [as _immediateCallback] (timers.js:617:5)

Diff:

diff --git a/package.json b/package.json
index 6375a9ef..a23925d8 100644
--- a/package.json
+++ b/package.json
@@ -88,7 +88,7 @@
-    "pg": "6.2.4",
+    "pg": "^7.0.2",
@@ -119,7 +119,7 @@
-    "sequelize": "4.1.0",
+    "sequelize": "^4.4.2",

[ghost]

I just installed [email protected]. it may be an issue related with the support for pg ^7.0.2

[igrek8]

@tapz, that also has happened to me. Any ideas/workarounds?

[igrek8]

Mine throws right after db.sequelize.sync & findOrCreate are invoked.
However when commenting db.sequelize.sync & calling just findOrCreate it worked.

Hm..

[igrek8]

@Anatoly-Ktitarov thanks, that fixed for me..

but still looking to this being resolved any time soon

[sparkida]

similar to @fnando, the issue I'm seeing is when running raw queries with multiple statements, where the rows param is now a Result array containing rows and the first result was empty

[ Result {
    command: 'SELECT',
    rowCount: 0,
    oid: NaN,
    rows: [],
    fields: [ ... ],
    _parsers: [ ... ],
    RowCtor: [Function: anonymous],
    rowAsArray: false,
    _getTypeParser: [Function: bound ] },
  Result {
    command: 'SELECT',
    rowCount: 1,
    oid: NaN,
    rows: [ [Object] ],
    fields: [ [Object], [Object], [Object] ],
    _parsers: [ [Function: noParse], [Function: noParse], [Function: noParse] ],
    RowCtor: [Function: anonymous],
    rowAsArray: false } ]

thx @Anatoly-Ktitarov for quick fix, can confirm was on 7.0.2 and downgrading pg to 6.4.1 works

[vertigo7x]

Without testing on other conditions (complex queries or other cases) I found a solution that works on pg:latest and don't give an error during init, because the @Anatoly-Ktitarov solution fail on project startup because queryResult is an array and doesn't have a rows property.
Change line 98 of query.js

   const rows = queryResult.rows || queryResult[1].rows;

I think the solution not cover all cases, because some queries could have more than two or three results inside and you would need to access other.

But at the moment, it works for me.

[shime]

Happening to me too, when using findOrCreate with [email protected]. Downgrading to [email protected] fixes the issue.

[morficus]

to anyone who downgraded to 6.4.1 to get around this issue... you should go to 6.4.2 since a security vulnerability was recently patched in some old versions: https://node-postgres.com/announcements

cc: @Anatoly-Ktitarov, @shime , @crabicode , @yszk0123

[mmase]

still seeing this problem with [email protected] as well.. as @morficus said, 6.4.2 is the correct and last version that does not have this issue.

[andrewgoodchild]

+1 for a fix for this.

I tried sync: false and commenting out sync all together, but it didnt work

I also tried to work around the upsert error, by just doing an update instead, but the same error still occurs.

The downgrade to 6.4.2 works for me

[saudpunjwani101]

no solution so far?

[lerit]

expect to reslove it

[yss14]

Got the same error running sync({force: true})

TypeError: Cannot read property '0' of undefined
    at query.catch.then.then.queryResult (<path>/node_modules/sequelize/lib/dialects/postgres/query.js:112:17)
    at tryCatcher (<path>/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (<path>/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (<path>/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (<path>/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (<path>/node_modules/bluebird/js/release/promise.js:693:18)
    at Async._drainQueue (<path>/node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (<path>/node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (<path>/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:781:20)
    at tryOnImmediate (timers.js:743:5)
    at processImmediate [as _immediateCallback] (timers.js:714:5)

[abeMedia]

I'm getting the same error when running sync({force: true}) on postgres 9.6.5

[dsullivan7]

I found that switching from findOrCreate to findCreateFind (documentation here) solved this issue for me

[aryeharmon]

this happens to me while migration queryInterface.changeColumn

[dayer3]

I've had to change from pg-native to pg(v6.4.2). As well as from findOrCreate to findCreateFind.

[DiegoRBaquero]

This worked for me, keeping all row and row count. Added in line

        if (Array.isArray(queryResult)) {
          let result = queryResult[0]
          for (let i = 1; i < queryResult.length; i++) {
            let tempResult = queryResult[i]
            result.rows.concat(tempResult.rows)
            result.rowCount += tempResult.rowCount
          }
          queryResult = result
        }

Hope it does the job for someone else

[farzd]

@dsullivan7 that worked for me
according to docs

A more performant findOrCreate that will not work under a transaction (at least not in postgres) Will execute a find call, if empty then attempt to create, if unique constraint then attempt to find again

what does transaction mean?

[levibostian]

@farzd transaction is a term that is not specific to Sequelize.

What is a database transaction
how to do Sequelize transactions

[TitaneBoy]

Hi Guys...I'm having this issue too. For me, it happens when using the method "upsert".

Using [email protected] does not trigger the error..

Do you have any news about a possible fix for this ?
Thank you very much for your answer

[dayer3]

@TitaneBoy: I have no problem with upsert and pg v6.4.2. Although I'm using transactions and it may change the behavior.
What sequelize version are you using? Are you using the _native_ option?

[TitaneBoy]

@dayer3 ...Sorry..I think I was not specific in my last post..

I'm using [email protected] and I got the error above with [email protected]... I have no issue with [email protected] (as I said before: "Using [email protected] does not trigger the error")

[tikidave]

I just bumped into this too after using sync({ force: true}) like the three other folks above.

PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit

[emhagman]

@vertigo7x That fix works for me as well.

[DiegoRBaquero]

Didn't this get fixed with https://github.com/sequelize/sequelize/commit/f5927a6a1350e4fe949a8b9fb2f40ec3f3b78d82#diff-bbc546fc401d8a57580520f92b50a5fb ?

[emhagman]

@DiegoRBaquero I use Sequelize 4.11.1 and presumably that fix got into https://github.com/sequelize/sequelize/releases/tag/v4.4.2 so I believe it is still an issue. My issue is when executing queries, not on start of a connection.

Manually changing the line on query.js 97 like @vertigo7x said fixes the problem for me:

 const rows = queryResult.rows || queryResult[1].rows;

[DiegoRBaquero]

@emhagman 4.11 > 4.4 (?)

Why not update, no breaking changes have happened yet.

Also, it should be using last row result, not necessarily the second (?)

[emhagman]

??? I use 4.11.1 which is greater than 4.4 in semver world so I have the fix.

https://github.com/sequelize/sequelize/releases/tag/v4.11.1 published 29 days ago
https://github.com/sequelize/sequelize/releases/tag/v4.4.2 published on July 22nd

[DiegoRBaquero]

@emhagman Understood wrongly, my bad. Yes, 4.11 should have the fix of 4.4. Still an issue.

[jeremyputeaux]

I was using [email protected] and had the same error while doing findOrCreate(). I also tried with the latest version of [email protected] but it did not fix it.
I tried going back to [email protected] and it works properly.

[ashok-sc]

Is there an update here? I'm running into this issue when I try Model.upsert(args).

[swimclan]

@dsullivan7 thank you for the fix. Worked for me as well....

I found that switching from findOrCreate to findCreateFind (documentation here) solved this issue for me

[damianobarbati]

+1 for this, I had to downgrade to [email protected]. Did anyone report this on pg repo? I can't create a repro right now.

[WaylonOKC]

I'm running [email protected] and tried upgrading [email protected]. When doing a sync, PG runs in to an issue because it's expecting an object like this (which it gets):
{"command":"DROP","rowCount":null,"oid":null,"rows":[],"fields":[],"_parsers":[],"RowCtor":null,"rowAsArray":false}
but as it goes through the process, it gets an array of them like this:
[{"command":"DROP","rowCount":null,"oid":null,"rows":[],"fields":[],"_parsers":[],"RowCtor":null,"rowAsArray":false},{"command":"CREATE","rowCount":null,"oid":null,"rows":[],"fields":[],"_parsers":[],"RowCtor":null,"rowAsArray":false}]

So when it tries to find the queryResult.rows[0] it can't find it because it needs a loop in the process to get queryResult[x].rows[0]

[damianobarbati]

@mickhansen is pg@7 support planned for 5.0 milestone?

[mickhansen]

@damianobarbati I wouldn't know

[andineck]

Just ran into the same issue with MyModel.upsert()

-> TypeError: Cannot read property '0' of undefined

versions:

• [email protected]
• [email protected]

[EdHuamani]

I have the same problem with "pg": "^ 7.4.0" what is the milestone of this?

[perrosnk]

+1 for this, I had to downgrade to [email protected].

[mhuggins]

I ran into this while trying to create an index. Downgrading from [email protected] to 6.4.2 made the error go away.

return db.addIndex("blocks", [Sequelize.literal('CAST("time" AS date)')], {
  name: "blocks_date_index"
});

// Error: Cannot read property '0' of undefined

[tikidave]

Bumped into this again, this time on an upsert() like the others above.

Full stack trace:

Cannot read property '0' of undefined","name":"TypeError","stack":"TypeError: Cannot read property '0' of undefined at query.catch.then.then.queryResult (/app/node_modules/sequelize/lib/dialects/postgres/query.js:112:17) at tryCatcher (/app/node_modules/bluebird/js/release/util.js:16:23) at Promise._settlePromiseFromHandler (/app/node_modules/bluebird/js/release/promise.js:512:31) at Promise._settlePromise (/app/node_modules/bluebird/js/release/promise.js:569:18) at Promise._settlePromise0 (/app/node_modules/bluebird/js/release/promise.js:614:10) at Promise._settlePromises (/app/node_modules/bluebird/js/release/promise.js:693:18) at Async._drainQueue (/app/node_modules/bluebird/js/release/async.js:133:16) at Async._drainQueues (/app/node_modules/bluebird/js/release/async.js:143:10) at Immediate.Async.drainQueues (/app/node_modules/bluebird/js/release/async.js:17:14) at runCallback (timers.js:789:20)\n at tryOnImmediate (timers.js:751:5) at processImmediate [as _immediateCallback] (timers.js:722:5)"}

[fix]

same here, downgrading to [email protected] solve the issue

[didac-wh]

@fix Check @morficus comment out.

[mfilip]

I think the downgrade the pg package is not a permament solution because in this case we can loose some new functionalities that new versions of postgresql will give to programmers in time so I think will be best to investigate the code of sequalize in order to match the changes in the package and go to follow those changes instead of avoid them in long run!

[sfali16]

I'm also seeing this while using sync({ force: true }) in sequelize v4.20.1 with PostgreSQL server 10 running on Windows.
The workaround of downgrading pg to 6.4.2 worked for me as well.

[prsamy]

@sfali16 Thanks! Your workaround works for me as well.

[grahamj]

+1 for this, and I can't downgrade 😕

[jacinto123]

Is there any update on this? :)

[salguerooo]

+1

[Vincz]

+1

[DCtheTall]

+1 for this, had to downgrade pg to 6.4.2 to fix our production app

[RazerM]

Please stop posting +1. Emoji responses serve that purpose without sending an email to everyone subscribed for updates.

[massimonewsuk]

We get the following exception:

migrations_1  | TypeError: Cannot read property '0' of undefined
migrations_1  |     at query.catch.then.then.queryResult (/migrations/node_modules/sequelize/lib/dialects/postgres/query.js:112:17)
migrations_1  |     at tryCatcher (/migrations/node_modules/bluebird/js/release/util.js:16:23)
migrations_1  |     at Promise._settlePromiseFromHandler (/migrations/node_modules/bluebird/js/release/promise.js:512:31)
migrations_1  |     at Promise._settlePromise (/migrations/node_modules/bluebird/js/release/promise.js:569:18)
migrations_1  |     at Promise._settlePromise0 (/migrations/node_modules/bluebird/js/release/promise.js:614:10)
migrations_1  |     at Promise._settlePromises (/migrations/node_modules/bluebird/js/release/promise.js:693:18)
migrations_1  |     at Async._drainQueue (/migrations/node_modules/bluebird/js/release/async.js:133:16)
migrations_1  |     at Async._drainQueues (/migrations/node_modules/bluebird/js/release/async.js:143:10)
migrations_1  |     at Immediate.Async.drainQueues (/migrations/node_modules/bluebird/js/release/async.js:17:14)
migrations_1  |     at runCallback (timers.js:789:20)
migrations_1  |     at tryOnImmediate (timers.js:751:5)
migrations_1  |     at processImmediate [as _immediateCallback] (timers.js:722:5)
migrations_1  |
migrations_1  | ERROR: Cannot read property '0' of undefined

This happens when using sequelize to run a DB migration which creates some tables using a blob of raw SQL. Making the change mentioned here https://github.com/sequelize/sequelize/issues/8043#issuecomment-319499298 fixes it. But obviously we can't make that change everywhere so we've had to downgrade to 6.4.2 for now.

[TitaneBoy]

Hi Guys...Any news on the development of the solution for this issue ? Already 6 months since the beginning of this issue and we had no information about a possible fix of this.. Do you plan to support pg7 in a near future ? Thanks in advance for your answers

[flatirontek]

👍

[Vincz]

I think the problem is related to this change

Result from multi-statement text queries such as SELECT 1; SELECT 2; are now returned as an array of results instead of a single result with 1 array containing rows from both queries.

From https://github.com/brianc/node-postgres/blob/master/CHANGELOG.md

[Vincz]

So, I would say there is a problem as soon as their is more than one query executed at a time.
Given the following code:

const { Client } = require('pg')
const query = "SELECT 'hello'; SELECT 'world';";
const client = new Client()

const f = async () => {
    await client.connect()
    const res = await client.query(query);
    console.log(res);
    await client.end()
};

f();

It outputs with pg 6:

➜  /tmp node pg6/index.js
Result {
  command: 'SELECT',
  rowCount: 1,
  oid: NaN,
  rows:
   [ anonymous { '?column?': 'hello' },
     anonymous { '?column?': 'world' } ],
  fields:
   [ Field {
       name: '?column?',
       tableID: 0,
       columnID: 0,
       dataTypeID: 705,
       dataTypeSize: -2,
       dataTypeModifier: -1,
       format: 'text' } ],
  _parsers: [ [Function: noParse] ],
  RowCtor: [Function: anonymous],
  rowAsArray: false,
  _getTypeParser: [Function: bound ] }

And it outputs with pg 7 :

➜  /tmp node pg7/index.js
[ Result {
    command: 'SELECT',
    rowCount: 1,
    oid: null,
    rows: [ [anonymous] ],
    fields: [ [Field] ],
    _parsers: [ [Function: noParse] ],
    RowCtor: [Function: anonymous],
    rowAsArray: false,
    _getTypeParser: [Function: bound ] },
  Result {
    command: 'SELECT',
    rowCount: 1,
    oid: null,
    rows: [ [anonymous] ],
    fields: [ [Field] ],
    _parsers: [ [Function: noParse] ],
    RowCtor: [Function: anonymous],
    rowAsArray: false } ]
➜  /tmp

So the way to access the result is not the same anymore.

In sequelize, after the query completion with the postgres dialect:

https://github.com/sequelize/sequelize/blob/bb25c19b34f47eeec8f3556b087c2926898d5bbd/lib/dialects/postgres/query.js#L97-L99

Sequelize assumes that queryResult is an object, but in fact, if multiple queries were executed, it's an array.

Hope it'll help.

[suratovvlad]

Hope it'll help.

Thanks, @Vincz, it's helped a lot!
I've changed my query for migrations from "QUERY 1; QUERY 2; QUERY 3" to multiple splitted queries, and the error has gone.
PostgreSQL 10.1, Sequelize CLI [Node: 9.4.0, CLI: 3.1.0, ORM: 4.22.6]

[medanat]

This is amazing! Thanks!

[chadwhitaker]

A lot of people are upgrading to pg@7 this weekend 😁 Thanks @sushantdhiman!!

[rzorzal]

Is the pg@7 now supported ? Now we can do sequelize.sync({force: true}) on sequelize: 4.x.x ?

[dmead]

I'm still having this problem on pg 7.4.1

[iansu]

@dmead Make sure you also upgrade the sequelize package in addition to pg.

[rzorzal]

I had upgraded both, but the error continue appearing too. I do not thinking I doing something wrong, but every time I upgrade to sequelize 4.x.x and pg to 7.x.x it is occurring. ;/

[TitaneBoy]

@rzorzal @dmead ..I will suggest you to remove all your packages AND to delete the npm (or yarn) .lock file where some informations about packages are.. Then install all your packages with npm install (or yarn install if you use yarn). Maybe it will resolve your issue.

[rzorzal]

Today I was trying to resolve this issue, thx @TitaneBoy. To fixed my issue, I deleted node_modules, the lock file and all global libs(I don't know why, but I needed to did that). After that, The Error did not fire again. And now I have other issue, when I starting up my application with sync({alter: true}) it have removing all my data from Postgres and all foreign keys too ;/ I really afraid of using sequelize 4.x.x in production, I'm waiting for more others upgrades of sequelize, I hope this do not occur in the future. So thanks for helping me, I continous using sequelize in production and love this ORM.