解决konga不兼容postgresql12及以上版本

1. 背景

由于项目需要，postgresql由9.6版本升级到12，但是konga官方已经停止维护更新了，官方发布的说明是konga最好兼容版本是pg9.6。
konga地址：https://github.com/pantsel/konga

2. 问题

升级pg版本为12后启动konga报错，错误信息如下：

A hook (`orm`) failed to load!
error: column r.consrc does not exist
    at Connection.parseE (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:539:11)
    at Connection.parseMessage (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:366:17)
    at Socket.<anonymous> (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:105:22)
    at Socket.emit (events.js:189:13)
    at Socket.EventEmitter.emit (domain.js:441:20)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
/usr/local/konga-master/node_modules/sails-postgresql/lib/adapter.js:158
        var collection = connectionObject.collections[table];
                                          ^

TypeError: Cannot read property 'collections' of undefined
    at __DESCRIBE__ (/usr/local/konga-master/node_modules/sails-postgresql/lib/adapter.js:158:43)
    at after (/usr/local/konga-master/node_modules/sails-postgresql/lib/adapter.js:1292:7)
    at /usr/local/konga-master/node_modules/sails-postgresql/lib/adapter.js:1181:7
    at /usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/pool.js:84:11
    at dispense (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/node_modules/generic-pool/lib/generic-pool.js:250:16)
    at Object.me.release (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/node_modules/generic-pool/lib/generic-pool.js:349:5)
    at /usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/pool.js:88:20
    at /usr/local/konga-master/node_modules/sails-postgresql/lib/adapter.js:1295:9
    at Query.callback (/usr/local/konga-master/node_modules/sails-postgresql/lib/adapter.js:195:26)
    at Query.handleError (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/query.js:106:17)
    at Connection.<anonymous> (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/client.js:171:26)
    at Connection.emit (events.js:189:13)
    at Connection.EventEmitter.emit (domain.js:441:20)
    at Socket.<anonymous> (/usr/local/konga-master/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:109:12)
    at Socket.emit (events.js:189:13)
    at Socket.EventEmitter.emit (domain.js:441:20)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

3. 解决问题

从异常信息中可得知是/usr/local/konga-master/node_modules/sails-postgresql/lib/adapter.js:158:43这里出了问题。因此需要调整一下源码。我这里一共调整了三个文件：
konga部署包/.env，
konga部署包/config/connections.js，
konga部署包/node_modules/sails-postgresql/lib/adapter.js。

• .env文件
  新增属性参数：

  #新增参数判断当前连接数据库是否是pg12或以上版本
  pgVersion12OrNewer=true
  

• connections.js文件
  调整pg连接属性配置：

  postgres: {
      adapter: 'sails-postgresql',
      url: process.env.DB_URI,
      host: process.env.DB_HOST || 'localhost',
      user:  process.env.DB_USER || 'postgres',
      password: process.env.DB_PASSWORD || 'admin1!',
      port: process.env.DB_PORT || 5432,
      database: process.env.DB_DATABASE ||'konga_database',
      // schema: process.env.DB_PG_SCHEMA ||'public',
      poolSize: process.env.DB_POOLSIZE || 10,
      ssl: process.env.DB_SSL ? true : false, // If set, assume it's true
      pgVersion12OrNewer: process.env.pgVersion12OrNewer ? true : false// .env文件中新增的参数
  },
  

• adapter.js文件
  找到上面报错信息的位置，在describe函数中新增内容，并做调整。

  describe: function(connectionName, table, cb) {
    spawnConnection(connectionName, function __DESCRIBE__(client, cb) {
  
      var connectionObject = connections[connectionName];
      var collection = connectionObject.collections[table];
      var tableName = table;
      var schemaName = getSchema(connectionName, table);
      // 新增如下两行
      var is12OrNewer = connectionObject.config.pgVersion12OrNewer; // look for parameter
      var compatible = function (s) { return is12OrNewer ? "" : s; }; // ignore the field if v12+
      // Build query to get a bunch of info from the information_schema
      // It's not super important to understand it only that it returns the following fields:
      // [Table, #, Column, Type, Null, Constraint, C, consrc, F Key, Default]
      
      //下面对query语句中不存在字段进行去除
      var query = "SELECT x.nspname || '.' || x.relname as \"Table\", x.attnum as \"#\", x.attname as \"Column\", x.\"Type\"," +
        " case x.attnotnull when true then 'NOT NULL' else '' end as \"NULL\", r.conname as \"Constraint\", r.contype as \"C\", " +
        compatible("r.consrc,") + " fn.nspname || '.' || f.relname as \"F Key\"" + compatible(", d.adsrc as \"Default\"") + " FROM (" +
        "SELECT c.oid, a.attrelid, a.attnum, n.nspname, c.relname, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as \"Type\", " +
        "a.attnotnull FROM pg_catalog.pg_attribute a, pg_namespace n, pg_class c WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = c.oid " +
        "and c.relkind not in ('S','v') and c.relnamespace = n.oid and n.nspname not in ('pg_catalog','pg_toast','information_schema')) x " +
        compatible("left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum ") +
        "left join pg_constraint r on r.conrelid = x.oid and r.conkey[1] = x.attnum " +
        "left join pg_class f on r.confrelid = f.oid " +
        "left join pg_namespace fn on f.relnamespace = fn.oid " +
        "where x.relname = '" + tableName + "' and x.nspname = '" + schemaName + "' order by 1,2;";
  

4. 参考文章

https://github.com/pantsel/konga/issues/487
https://github.com/balderdashy/sails/issues/6957