postgresql - sequelize.findOrCreate 即使表为空也没有保存

Question

我的应用程序正在将套接字相关信息保存到 socketlist 表中。这个想法是，如果带有 socket_id 的条目不存在，则在 postgres 表中创建一个条目。否则什么也不做。这是代码:

await SocketList.findOrCreate({ where: {socket_id : socket_id, active: true}, default: {event_id: event_id, user_id: user_id, server_id: server_id}});

使用了

sequelizejs 4.42.0。 socketlist 表是空的，但上面的代码抛出以下错误:

Executing (325bc621-414d-4e3f-9f2b-230f66537631): START TRANSACTION;
Executing (325bc621-414d-4e3f-9f2b-230f66537631): SELECT "id", "user_id", "socket_id", "event_id", "server_id", "active" FROM "socketlists" AS "socketlist" WHERE "socketlist"."socket_id" = '2TPk6DpsxPwttaemAAAA' AND "socketlist"."active" = true LIMIT 1;
Executing (325bc621-414d-4e3f-9f2b-230f66537631): CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response "socketlists", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_4fe094a05f394fe8a0ec032506b86e21$ BEGIN INSERT INTO "socketlists" ("id","socket_id","active") VALUES (NULL,'2TPk6DpsxPwttaemAAAA',true) RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_4fe094a05f394fe8a0ec032506b86e21$ LANGUAGE plpgsql; SELECT (testfunc.response).*, testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();
Executing (325bc621-414d-4e3f-9f2b-230f66537631): COMMIT;
Socket was not saved for  userId: 1   { SequelizeDatabaseError: null value in column "id" violates not-null constraint
    at Query.formatError (C:\d\code\js\emps_bbone\node_modules\sequelize\lib\dialects\postgres\query.js:363:16)
    at query.catch.err (C:\d\code\js\emps_bbone\node_modules\sequelize\lib\dialects\postgres\query.js:86:18)
    at tryCatcher (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\util.js:16:23)
    at Promise._settlePromiseFromHandler (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\promise.js:512:31)
    at Promise._settlePromise (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\promise.js:569:18)
    at Promise._settlePromise0 (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\promise.js:614:10)
    at Promise._settlePromises (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\promise.js:690:18)
    at _drainQueueStep (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\async.js:138:12)
    at _drainQueue (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\async.js:131:9)
    at Async._drainQueues (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\async.js:147:5)
    at Immediate.Async.drainQueues [as _onImmediate] (C:\d\code\js\emps_bbone\node_modules\bluebird\js\release\async.js:17:14)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
  name: 'SequelizeDatabaseError',
  parent:
   { error: null value in column "id" violates not-null constraint
       at Connection.parseE (C:\d\code\js\emps_bbone\node_modules\pg\lib\connection.js:601:11)
       at Connection.parseMessage (C:\d\code\js\emps_bbone\node_modules\pg\lib\connection.js:398:19)
       at Socket.<anonymous> (C:\d\code\js\emps_bbone\node_modules\pg\lib\connection.js:120:22)
       at Socket.emit (events.js:182:13)
       at addChunk (_stream_readable.js:283:12)
       at readableAddChunk (_stream_readable.js:264:11)
       at Socket.Readable.push (_stream_readable.js:219:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     name: 'error',
     length: 465,
     severity: 'ERROR',
     code: '23502',
     detail:
      'Failing row contains (null, null, 2TPk6DpsxPwttaemAAAA, null, null, t).',
     hint: undefined,
     position: undefined,
     internalPosition: undefined,
     internalQuery: undefined,
     where:
      'SQL statement "INSERT INTO "socketlists" ("id","socket_id","active") VALUES (NULL,\'2TPk6DpsxPwttaemAAAA\',true) RETURNING *"\nPL/pgSQL function pg_temp_3.testfunc() line 1 at SQL statement',
     schema: 'public',
     table: 'socketlists',
     column: 'id',
     dataType: undefined,
     constraint: undefined,
     file:
      'd:\\pginstaller.auto\\postgres.windows-x64\\src\\backend\\executor\\execmain.c',
     line: '2041',
     routine: 'ExecConstraints',
     sql:
      'CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response "socketlists", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_4fe094a05f394fe8a0ec032506b86e21$ BEGIN INSERT INTO "socketlists" ("id","socket_id","active") VALUES (NULL,\'2TPk6DpsxPwttaemAAAA\',true) RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_4fe094a05f394fe8a0ec032506b86e21$ LANGUAGE plpgsql; SELECT (testfunc.response).*, testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();' },
  original:
   { error: null value in column "id" violates not-null constraint
       at Connection.parseE (C:\d\code\js\emps_bbone\node_modules\pg\lib\connection.js:601:11)
       at Connection.parseMessage (C:\d\code\js\emps_bbone\node_modules\pg\lib\connection.js:398:19)
       at Socket.<anonymous> (C:\d\code\js\emps_bbone\node_modules\pg\lib\connection.js:120:22)
       at Socket.emit (events.js:182:13)
       at addChunk (_stream_readable.js:283:12)
       at readableAddChunk (_stream_readable.js:264:11)
       at Socket.Readable.push (_stream_readable.js:219:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     name: 'error',
     length: 465,
     severity: 'ERROR',
     code: '23502',
     detail:
      'Failing row contains (null, null, 2TPk6DpsxPwttaemAAAA, null, null, t).',
     hint: undefined,
     position: undefined,
     internalPosition: undefined,
     internalQuery: undefined,
     where:
      'SQL statement "INSERT INTO "socketlists" ("id","socket_id","active") VALUES (NULL,\'2TPk6DpsxPwttaemAAAA\',true) RETURNING *"\nPL/pgSQL function pg_temp_3.testfunc() line 1 at SQL statement',
     schema: 'public',
     table: 'socketlists',
     column: 'id',
     dataType: undefined,
     constraint: undefined,
     file:
      'd:\\pginstaller.auto\\postgres.windows-x64\\src\\backend\\executor\\execmain.c',
     line: '2041',
     routine: 'ExecConstraints',
     sql:
      'CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response "socketlists", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_4fe094a05f394fe8a0ec032506b86e21$ BEGIN INSERT INTO "socketlists" ("id","socket_id","active") VALUES (NULL,\'2TPk6DpsxPwttaemAAAA\',true) RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_4fe094a05f394fe8a0ec032506b86e21$ LANGUAGE plpgsql; SELECT (testfunc.response).*, testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();' },
  sql:
   'CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response "socketlists", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_4fe094a05f394fe8a0ec032506b86e21$ BEGIN INSERT INTO "socketlists" ("id","socket_id","active") VALUES (NULL,\'2TPk6DpsxPwttaemAAAA\',true) RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_4fe094a05f394fe8a0ec032506b86e21$ LANGUAGE plpgsql; SELECT (testfunc.response).*, testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();' }

这是模型定义:

const SocketList = db.define('socketlist', {
    id: {type: Sql.INTEGER,
         primaryKey:true,
         min: 1
    },
    user_id: { type: Sql.INTEGER
    },
    socket_id: {type: Sql.STRING,
                unique: true,
                min: 1
    },
    event_id: {type: Sql.INTEGER,
               min: 1
    },
    server_id: {type: Sql.STRING
    },
    active: {type: Sql.BOOLEAN,
             defaultValue: true,
    },
}....

Answer 1

您必须创建包含 id 的数据试试这个怎么样？

id: {
  type: Sql.INTEGER,
  primaryKey:true,
  autoIncrement: true
}