Cockroach: sql: compatibility with KnexJs query builder

[ansarizafar]

Please provide support for KnexJS query builder.

[tamird]

Hi @ansarizafar, thanks for the request. Have tried using CRDB with KnexJS? What problems did you run into?

[ansarizafar]

I had tried to use CRDB with KnexJs few weeks back without any luck. I was not able to connect.

[tamird]

Could you please post the full script you used, along with any errors you
encountered?

On Mon, Apr 24, 2017 at 1:49 PM, Zafar Ansari [e-mail]
wrote:

I had tried to use CRDB with KnexJs few weeks back without any luck. I was
not able to connect.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/cockroachdb/cockroach/issues/15299#issuecomment-296767733,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABdsPJdGdPXyoXj_V7TM5grUvTFXJg60ks5rzOCogaJpZM4NGfgG
.

[ansarizafar]

I switched to Postgresql after an unsuccessful attempt. I had used the following code but don't exactly remember the error message.

var pg = require('knex')({
  client: 'pg',
  connection: process.env.PG_CONNECTION_STRING,
  searchPath: 'knex,public'
});

[tamird]

Perhaps you could try again with the current beta and let us know what you
get.

On Mon, Apr 24, 2017 at 1:56 PM, Zafar Ansari [e-mail]
wrote:

I switched to Postgresql after an unsuccessful attempt. I had used the
following code but don't exactly remember the error message.

var pg = require('knex')({
client: 'pg',
connection: process.env.PG_CONNECTION_STRING,
searchPath: 'knex,public'
});

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/cockroachdb/cockroach/issues/15299#issuecomment-296770217,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABdsPGCsABG2ujOYij0H-K4lhXgdUcflks5rzOJHgaJpZM4NGfgG
.

[ansarizafar]

I am using an old machine to play with beta software and current beta don't work on my test machine.

[tamird]

Ah, that's probably due to
https://github.com/cockroachdb/cockroach/commit/aa067e3fdafb8de94f5c64ec894997ed85855706
.

Could you try again after this week's beta? It should clear up this issue.

On Mon, Apr 24, 2017 at 2:01 PM, Zafar Ansari [e-mail]
wrote:

I am using an old machine to play with beta software and current beta
don't work on my test machine.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/cockroachdb/cockroach/issues/15299#issuecomment-296772285,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABdsPGz19-ieA7U9VTJrc5rzgc3G-_vNks5rzOOSgaJpZM4NGfgG
.

[ansarizafar]

Ok. In my opinion KenxJs is better than Sequelizejs and CRDB should support it.

[ansarizafar]

I tried to use CRDB with KenxJs . I am gettinf this error when trying to execute a query

{ Error: connect ECONNREFUSED 104.131.159.76:26257
9:00 PM
    at Object.exports._errnoException (util.js:1022:11)
9:00 PM
    at exports._exceptionWithHostPort (util.js:1045:20)
9:00 PM
    at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1087:14)
9:00 PM
  code: 'ECONNREFUSED',
9:00 PM
  errno: 'ECONNREFUSED',
9:00 PM
  syscall: 'connect',
9:00 PM
  address: '104.131.159.76',
9:00 PM
  port: 26257 }

Connection string postgresql://root@host:26257/restock?sslmode=disable

[tamird]

Connection refused suggests that the server isn't listening on that host:port. Are you able to reach your CRDB server with cockroach sql --url postgresql://root@host:26257/restock?sslmode=disable?

[tamird]

Also, it would be helpful if you could share the complete knexjs code you're trying to run.

[ansarizafar]

Here is my Knexjs code

 var client = knex({
  client: 'pg',
  connection: 'postgresql://root@host:4000/restock?sslmode=disable',
  searchPath: 'knex,public'
});

user = await client.select().from('users');
              console.log(user);
              return user

I have fixed port issue now I am getting this error

Error: Connection terminated
11:11 PM
    at Connection.<anonymous> (/app/node_modules/pg/lib/client.js:193:17)
11:11 PM
    at Connection.g (events.js:291:16)
11:11 PM
    at emitNone (events.js:86:13)
11:11 PM
    at Connection.emit (events.js:185:7)
11:11 PM
    at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:141:10)
11:11 PM
    at emitNone (events.js:91:20)
11:11 PM
    at Socket.emit (events.js:185:7)
11:11 PM
    at endReadableNT (_stream_readable.js:974:12)
11:11 PM
    at _combinedTickCallback (internal/process/next_tick.js:74:11)
11:11 PM
    at process._tickCallback (internal/process/next_tick.js:98:9)

[benesch]

In the sample code you posted, you're attempting to connect to port 4000. Are you sure that's the port you started your Cockroach server on?

As @tamird suggested, please post the output of cockroach sql --url postgresql://root@host:4000/restock?sslmode=disable, assuming that 4000 is the right port.

[ansarizafar]

Here is the output

# Welcome to the cockroach SQL interface.                                                                 
# All statements must be terminated by a semicolon.                                                       
# To exit: CTRL + D.                                                                                      
[email protected]:4000/restock>   

[tamird]

I am unable to reproduce your error. Here's what I ran locally:

#!/usr/bin/env node

var knex = require('./knex')

 var client = knex({
  client: 'pg',
  connection: 'postgresql://root@localhost:26257/knex_test?sslmode=disable',
  searchPath: 'knex,public'
});

client.select().from('users').then(function(user) {
    console.log(user);
});

and the result:

/Users/tamird/src/knex/lib/dialects/postgres/index.js:182
        resolver(/^PostgreSQL (.*?)( |$)/.exec(resp.rows[0].version)[1]);
                                                                    ^

TypeError: Cannot read property '1' of null
    at Query.<anonymous> (/Users/tamird/src/knex/lib/dialects/postgres/index.js:182:69)
    at Query.handleReadyForQuery (/Users/tamird/src/knex/node_modules/pg/lib/query.js:124:10)
    at Connection.<anonymous> (/Users/tamird/src/knex/node_modules/pg/lib/client.js:172:19)
    at emitOne (events.js:96:13)
    at Connection.emit (events.js:191:7)
    at Socket.<anonymous> (/Users/tamird/src/knex/node_modules/pg/lib/connection.js:136:12)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:191:7)
    at readableAddChunk (_stream_readable.js:178:18)
    at Socket.Readable.push (_stream_readable.js:136:10)
    at TCP.onread (net.js:560:20)

This is because knex is overly aggressive in trying to parse the postgres server's version (for feature detection). It assumes that the return value of select version() begins with "PostgreSQL", which is not the case in CDB.

You might wish to file an issue in https://github.com/tgriesser/knex.

[ansarizafar]

Great news, Knexjs has added support for CockroachDB on my request. CRDB support is coming in release knex 0.13

[tamird]

That is great news! Can you share where that was announced or implemented?
I was testing against knex's master branch when I saw the error above.

On Thu, Apr 27, 2017 at 3:44 PM, Zafar Ansari [e-mail]
wrote:

Great news Knexjs has added support for CockroachDB on my request. CRDB
support is coming in release knex 0.13

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/cockroachdb/cockroach/issues/15299#issuecomment-297819076,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABdsPM9m5bgSX2JH6YuNGT7-qxfNg5qGks5r0PAdgaJpZM4NGfgG
.

[ansarizafar]

Here is the link https://github.com/tgriesser/knex/issues/2002

[tamird]

Ah, I see; you need to manually set the version. Great! Giving this a close for now but please let us know if you uncover more issues.

[nicck]

Unfortunately it is still not possible to use cockroachdb with knexjs. Issues I have found:

• select * for update is not supported by cockroach
• column name "current_schema" not found b/c current_schema is not enclosed in ' '
• migrations lock does not work
• uuid and json types are not supported
• .primary() in migrations fails with something like "multiple primary keys not allowed";

As I can see cockroachdb is NOT drop-in replacement for postgresql.

[JorgeCeja]

Any updates? Thanks!

[josdotso]

@tamird Would you be willing to reopen this based on @nicck's comment?

[tamird]

@josdotso: @nicck's comment is a laundry list of features - I don't think any of them are specific to knexjs. I'm not longer primarily working on the project but it seems likely that there are already issues for each feature mentioned. @benesch might be able to give a more specific answer.

Reopening this issue doesn't seem appropriate to me.

[josdotso]

Thanks @tamird !

[petermattis]

@knz for triage.

[Bessonov]

SELECT FOR UPDATE placebo is a part of https://github.com/cockroachdb/cockroach/issues/6583. Some workarounds are described in https://github.com/tgriesser/knex/issues/2002#issuecomment-383766677.

[awoods187]

@jordanlewis we should add this one to the list

[intech]

Up! https://github.com/knex/knex/issues/2002#issuecomment-719885770