Client driver for Crystal Language

[ansarizafar]

Please provide a client driver for Crystal language http://crystal-lang.org/

[jordanlewis]

Hi @ansarizafar, have you tried using an established Postgres driver for Crystal? CockroachDB is wire compatible with Postgres, so you should be able to just use one if it already exists.

We don't currently have sufficient bandwidth to create custom client drivers.

[ansarizafar]

CockroachDB is not fully compatible with Postgresql that's why Postgresql driver for Crystal https://github.com/will/crystal-pg don't work with CockroachDB.

[a-robinson]

Do you have any information on what doesn't work? There should be an error message somewhere if you've tried it out and it didn't work, and that will help us understand what's missing from CockroachDB.

[ansarizafar]

Here is the error when I try to connect to latest 1.1 beta version of CockroachDB with Cryatal via Postgresql driver for Crystal https://github.com/will/crystal-pg.

zafar@home:~/crystal/webware/server$ crystal run src/main.cr
 (DB::ConnectionRefused)
0x503fe7: *CallStack::unwind:Array(Pointer(Void)) at ??
0x62d9c4: initialize at /home/zafar/crystal/webware/server/lib/pg/src/pg/connection.cr 16:9
0x62d7ff: new at /home/zafar/crystal/webware/server/lib/pg/src/pg/connection.cr 7:5
0x62d5ed: build_connection at /home/zafar/crystal/webware/server/lib/pg/src/pg/driver.cr 4:5
0x64be50: build_resource at /home/zafar/crystal/webware/server/lib/db/src/db/pool.cr 255:3
0x64bdc6: initialize at /home/zafar/crystal/webware/server/lib/db/src/db/pool.cr 17:34
0x68644c: *DB::Pool(T)::new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay<Int32, Int32, Int32, Float64, Int32, Float64, &Proc(DB::Connection+)>:DB::Pool(DB::Connection+) at ??
0x629f42: initialize at /home/zafar/crystal/webware/server/lib/db/src/db/database.cr 50:7
0x629d4d: new at /home/zafar/crystal/webware/server/lib/db/src/db/database.cr 43:5
0x626ef6: build_database at /home/zafar/crystal/webware/server/lib/db/src/db.cr 140:5
0x626ede: build_database at /home/zafar/crystal/webware/server/lib/db/src/db.cr 136:5
0x626ec6: open at /home/zafar/crystal/webware/server/lib/db/src/db.cr 104:5
0x4f937f: ~WebWare::Db:init at /home/zafar/crystal/webware/server/src/webware/modules/db.cr 1:1
0x4dd68e: __crystal_main at /home/zafar/crystal/webware/server/src/webware/modules/db.cr 1:1
0x4f0ce9: main at /opt/crystal/src/main.cr 12:15
0x7fed509fa830: __libc_start_main at ??
0x4dc759: _start at ??
0x0: ??? at ??

[a-robinson]

The fact that it's a "ConnectionRefused" error means that the issue probably isn't any sort of missing functionality, but an incorrect configuration.

To figure out what's misconfigured, would you mind sharing the command-line parameters that you started your CockroachDB cluster with and the connection string/options that you're passing to the driver?

[ansarizafar]

Here is how, I have started CockroackDB server

zafar@home:~/softwares/cockroach-v1.1-beta.20170928.linux-amd64$ ./cockroach start --insecure --background
*
* WARNING: RUNNING IN INSECURE MODE!
* 
* - Your cluster is open for any client that can access <all your IP addresses>.
* - Any user, even root, can log in without providing a password.
* - Any user, connecting as root, can read or write any data in your cluster.
* - There is no network encryption nor authentication, and thus no confidentiality.
* 
* Check out how to secure your cluster: https://www.cockroachlabs.com/docs/stable/secure-a-cluster.html
*
CockroachDB node starting at 2017-10-02 19:14:49.212091266 +0500 PKT (took 2.7s)
build:      CCL v1.1-beta.20170928 @ 2017/09/28 20:01:14 (go1.8.3)
admin:      http://home:8080
sql:        postgresql://root@home:26257?application_name=cockroach&sslmode=disable
logs:       /home/zafar/softwares/cockroach-v1.1-beta.20170928.linux-amd64/cockroach-data/logs
store[0]:   path=/home/zafar/softwares/cockroach-v1.1-beta.20170928.linux-amd64/cockroach-data
status:     restarted pre-existing node
clusterID:  6deac684-e6ed-4650-a296-1065e704db87
nodeID:     1

Here is the connection string passed to Crystal PG driver

"postgresql://root@home:26257?application_name=cockroach&sslmode=disable"

[a-robinson]

Thanks @ansarizafar. I don't see anything obviously wrong. The two threads I'd pull on next would be:

1. Does the driver only recognize the postgres:// prefix (as mentioned in its docs, not the postgresql:// prefix? It may be worth trying "postgres://root@home:26257?application_name=cockroach&sslmode=disable"
2. This seems much less likely, but is the home domain resolvable locally? To test, you could try running ./cockroach sql --url "postgresql://root@home:26257?application_name=cockroach&sslmode=disable" to make sure that it can connect.

[ansarizafar]

Yes home domain is resolving locally. This connection string "postgres://root@home:26257?application_name=cockroach&sslmode=disable" also giving connection refused error in Crystal.

[a-robinson]

Is there a reason you deleted your previous message? The fact that ./cockroach sql --url "postgresql://root@home:26257?application_name=cockroach&sslmode=disable" couldn't connect is pretty indicative that something is wrong in the networking, since the cockroach client is definitely compatible.

[ansarizafar]

I forgot to start CockroachDB cluster before executing ./cockroach sql --url "postgresql://root@home:26257?application_name=cockroach&sslmode=disable" but then I started the cluster and executes the ./cockroach sql command. There was no issue, home domain was resolved successfully.

[a-robinson]

Thanks for the clarification. In that case, I'm not sure what may be going on, and we may need to investigate manually to understand what's happening. Do you have easy repro steps for trying out the driver locally? If not, the logs from the Cockroach process and more verbose logs from the Crystal process might contain something to go off of.

[ansarizafar]

Crystal is a very exciting up coming general purpose language which is faster than Go lang. It has low memory/CPU usage and a simply syntax.

Here are the step.

1. Install cyrstal from http://crystal-lang.org

2. Run "crystal init app cockroachdb"

3. cd into cockroachdb folder

4. add following lines to shard.yml
   dependencies:
   pg:
   github: will/crystal-pg

5. run "shards install"

6. Open src/crockroachdb.cr in editor

7. Add following lines
   Db = DB.open(cockroachdb_url)
   at_exit {
   Db.close
   }

8. Add crockrochDB cluster url in Db = DB.open(crockroachdb_url)

9. Run "crystal run src/cockroachdb.cr"

[ansarizafar]

@a-robinson Is ther any progress on this issue. I am waiting for crystal support (The fastest language https://github.com/tbrand/which_is_the_fastest)

[a-robinson]

It doesn't look like there's an easy way to get more info out of the crystal process, and I'm not seeing anything on the cockroach side. If this is blocking you, the fastest way to get a resolution will be for you to try investigating where the issue is happening.

Someone from our team who's a little more familiar with the pg wire protocol may be able to take a look eventually, but no promises on it happening anytime soon since we have no familiarity with Crystal and have a lot of other work going on. cc @asubiotto as one possible person. I can provide you with a Docker image with all the installation steps done if you'd like.

[maddyblue]

I can't even get this to work with a stock postgres database, so I'm not convinced this is a cockroach problem:

require "./cr/*"
require "db"
require "pg"

module Cr
  Db = DB.open("postgres://postgres@localhost:5432/?sslmode=disable")
  at_exit {
    Db.close
  }
end

[ansarizafar]

Postgresql works perfectly with Crystal language. I am using it from last three months without any issue.

[maddyblue]

Fantastic. Can you look at my code and tell me what I'm doing wrong? I started up a stock postgres server that I was able to connect to with other clients, but I couldn't get this crystal driver to work. I need some help before I can make progress on the cockroach issue.

[ansarizafar]

Why require "db"???

Here is my code

require "pg"

Db = DB.open("postgres://zafar:abdf763@localhost:5432/restock")

at_exit { 
Db.close 
}

[maddyblue]

I don't know what to tell you, but I can't get this working, and we don't have the resources now to have someone learn crystal and figure out what's going on. Here's what I tried:

$ psql --port 26257 --host localhost --user root
psql (9.5.9, server 9.5.0)
Type "help" for help.

root=> select version();
                                               version()                                                
--------------------------------------------------------------------------------------------------------
 CockroachDB CCL v1.2-alpha.20170901-748-gbf8d544-dirty (linux amd64, built 2017/10/11 19:05:54, go1.9)
(1 row)

This shows that psql is able to connect at root@localhost:26257. src/cr.cr:

require "pg"

Db = DB.open("postgres://root@localhost:26257/?sslmode=disable")

at_exit { 
Db.close 
}

$ crystal run src/cr.cr
 (DB::ConnectionRefused)
0x494a47: *CallStack::unwind:Array(Pointer(Void)) at ??
0x50da74: initialize at /home/mjibson/src/github.com/cockroachdb/cr/lib/pg/src/pg/connection.cr 16:9
0x50d8af: new at /home/mjibson/src/github.com/cockroachdb/cr/lib/pg/src/pg/connection.cr 7:5
0x50d69d: build_connection at /home/mjibson/src/github.com/cockroachdb/cr/lib/pg/src/pg/driver.cr 4:5
0x520810: build_resource at /home/mjibson/src/github.com/cockroachdb/cr/lib/db/src/db/pool.cr 255:3
0x520786: initialize at /home/mjibson/src/github.com/cockroachdb/cr/lib/db/src/db/pool.cr 17:34
0x54a8bc: *DB::Pool(T)::new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay<Int32, Int32, Int32, Float64, Int32, Float64, &Proc(DB::Connection+)>:DB::Pool(DB::Connection+) at ??
0x50a4a2: initialize at /home/mjibson/src/github.com/cockroachdb/cr/lib/db/src/db/database.cr 50:7
0x50a2ad: new at /home/mjibson/src/github.com/cockroachdb/cr/lib/db/src/db/database.cr 43:5
0x506446: build_database at /home/mjibson/src/github.com/cockroachdb/cr/lib/db/src/db.cr 140:5
0x50642e: build_database at /home/mjibson/src/github.com/cockroachdb/cr/lib/db/src/db.cr 136:5
0x506416: open at /home/mjibson/src/github.com/cockroachdb/cr/lib/db/src/db.cr 104:5
0x48e5a0: ~Db:init at /home/mjibson/src/github.com/cockroachdb/cr/lib/pg/src/pg/decoder.cr 308:22
0x4783fa: __crystal_main at /home/mjibson/src/github.com/cockroachdb/cr/lib/pg/src/pg/version.cr 1:1
0x48b2e9: main at /opt/crystal/src/main.cr 12:15
0x7f4aeb74a830: __libc_start_main at ??
0x477829: _start at ??
0x0: ??? at ??

[ansarizafar]

I have decided to stick with PostgreSQL.

[jordanlewis]

Thanks for checking us out!

[RX14]

For the record, the problem was partly exception detail being lost (DB::ConnectionRefused wasn't the root error) and an incomplete/invalid postgres protocol implementation, see: will/crystal-pg#136. Should be a super simple fix.

[RX14]

will/crystal-pg#138