db.bit.io issues

Question

db.bit.io issues

Closed this issue 3 years ago · 8 comments

I've been suffering a bunch of issues with one of my projects as of late, and while I've resolved most of them, this one's proving particularly annoying. I'm using db.bit.io, which only supports single-quote queries, and as far as I can tell from the documentation, the query cannot be changed.

DB setup

const { Pool } = require('pg');

const pool = new Pool({
  database: 'Nightstrike/WarriorsRISE',
  host: 'db.bit.io',
  port: 5432,
  user: process.env.USER,
  password: process.env.PASS,
  connectionString: process.env.CONN
});
var userlist;
var users = [];
setInterval(function(){
  pool.connect((err, client, release) => {
    if (err) {
      return console.error('Error acquiring client', err.stack)
    }
    client.query('SELECT * FROM "Nightstrike/WarriorsRISE"."users";', (err, result) => {
      release()
      if (err) {
        return console.error('Error executing query', err.stack)
      }
      userlist=result.rows;
      //console.log(result.rows)
    })
  })
},15000);

The actual call

var sessionMiddleware = session({
  store: new (require('connect-pg-simple')(session))({
    pool:pool,
    tableName:'"Nightstrike/WarriorsRISE".sessions',
    conString: process.env.CONN2,
    pruneSessionInterval:false
  }),
  secret: process.env.COOK,
  key: "value",
  resave: true,
  saveUnitializated: true,
  cookie:{
    maxAge: (30*24*60*60*1000)
  }
})

The error:

error: QueryParsingError: Unsupported command: INSERT INTO """Nightstrike/WarriorsRISE"".sessions" (sess, expire, sid) SELECT $1, to_timestamp($2), $3 ON CONFLICT (sid) DO UPDATE SET sess=$1, expire=to_timestamp($2) RETURNING sid

Answer 1 · 2022-05-23T22:50:21.000Z

Try specifying Nightstrike/WarriorsRISE as schemaName rather than tableName

Answer 2 · 2022-05-24T00:51:10.000Z

I shifted the database name to schema, but it returns the same error. Here's the full error dump:

error: QueryParsingError: Unsupported command: INSERT INTO "Nightstrike/WarriorsRISE"."sessions" (sess, expire, sid) SELECT $1, to_timestamp($2), $3 ON CONFLICT (sid) DO UPDATE SET sess=$1, expire=to_timestamp($2) RETURNING sid
Insert command: does not support on conflict
    at Parser.parseErrorMessage (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.<anonymous> (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/index.js:11:42)
    at TLSSocket.emit (node:events:390:28)
    at TLSSocket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
error: QueryParsingError: Unsupported command: INSERT INTO "Nightstrike/WarriorsRISE"."sessions" (sess, expire, sid) SELECT $1, to_timestamp($2), $3 ON CONFLICT (sid) DO UPDATE SET sess=$1, expire=to_timestamp($2) RETURNING sid
Insert command: does not support on conflict
    at Parser.parseErrorMessage (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.<anonymous> (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/index.js:11:42)
    at TLSSocket.emit (node:events:390:28)
    at TLSSocket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
error: QueryParsingError: Unsupported command: INSERT INTO "Nightstrike/WarriorsRISE"."sessions" (sess, expire, sid) SELECT $1, to_timestamp($2), $3 ON CONFLICT (sid) DO UPDATE SET sess=$1, expire=to_timestamp($2) RETURNING sid
Insert command: does not support on conflict
    at Parser.parseErrorMessage (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.<anonymous> (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/index.js:11:42)
    at TLSSocket.emit (node:events:390:28)
    at TLSSocket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
error: QueryParsingError: Unsupported command: INSERT INTO "Nightstrike/WarriorsRISE"."sessions" (sess, expire, sid) SELECT $1, to_timestamp($2), $3 ON CONFLICT (sid) DO UPDATE SET sess=$1, expire=to_timestamp($2) RETURNING sid
Insert command: does not support on conflict
    at Parser.parseErrorMessage (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.<anonymous> (/home/runner/WHRHS-Network-Test-Beta/node_modules/pg-protocol/dist/index.js:11:42)
    at TLSSocket.emit (node:events:390:28)
    at TLSSocket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
Session {
  cookie: {
    path: '/',
    _expires: 2022-06-23T00:46:55.863Z,
    originalMaxAge: 2592000000,
    httpOnly: true
  }
}

So right now, the session looks like this:

var sessionMiddleware = session({
  store: new (require('connect-pg-simple')(session))({
    pool:pool,
    schemaName:'Nightstrike/WarriorsRISE',
    tableName:'sessions',
    conString: process.env.CONN2,
    pruneSessionInterval:false
  }),
  secret: process.env.COOK,
  key: "value",
  resave: true,
  saveUnitializated: true,
  cookie:{
    maxAge: (30*24*60*60*1000)
  }
})

Answer 3 · 2022-05-24T09:24:34.000Z

@CharaHarimata Looks like bit.io isn't as Postgres compatible as they state that they are, the ON CONFLICT upsert feature was added in Postgres 9.5 over 6 years ago: https://www.postgresql.org/about/news/postgresql-95-upsert-row-level-security-and-big-data-1636/

And this module requires a PostgreSQL version of at least 9.5 due to that reason.

Would be interesting to know which version of Postgres that bit.io claims to be compatible with 🤔

Edit: I tweeted them: https://twitter.com/voxpelli/status/1529030796618645509

Answer 4 · 2022-05-24T11:42:12.000Z

bit.io does seem to be updated, but has an implementation of PostgreSQL that's limited in usable datatypes, so those limits may extend to other bits of syntax. It would help if their documentation included actually important details.

Answer 5 · 2022-05-25T12:43:31.000Z

Would it be possible to implement an option to customize the query or how it's handled?

Answer 6 · 2022-05-25T13:56:14.000Z

Would it be possible to implement an option to customize the query or how it's handled?

Not really, upserts are really key and making things too customizable also adds overhead and moves away from this being a “simple” module.

My advice then would be to fork and make a bit.io specific session module :/

Ideally they will answer me on Twitter. Feel free to ping them as well. An official answer would be great.

Answer 7 · 2022-05-26T02:01:38.000Z

Okay, they're more outdated than we thought. They don't support LOCK TABLE

Answer 8 · 2022-05-31T12:38:10.000Z

Right, for now I'm going to close this as not planned, sorry 😕