Cannot Connect to Postgres Database (DB::ConnectionRefused)

[jonjondev]

I have been trying to get Crecto to work in my environment and although other adapters seem to function just fine, Crecto cannot connect to my database:

Unhandled exception:  (DB::ConnectionRefused)
  from lib/pg/src/pg/connection.cr:16:9 in 'initialize'
  from lib/pg/src/pg/connection.cr:7:5 in 'new'
  from lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from lib/db/src/db/database.cr:51:9 in '->'
  from lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from lib/db/src/db/pool.cr:17:34 in 'initialize'
  from lib/db/src/db/pool.cr:15:5 in 'new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay'
  from lib/db/src/db/database.cr:50:7 in 'initialize'
  from lib/db/src/db/database.cr:43:5 in 'new'
  from lib/db/src/db.cr:139:5 in 'build_database'
  from lib/db/src/db.cr:135:5 in 'build_database'
  from lib/db/src/db.cr:103:5 in 'open'
  from lib/crecto/src/crecto/repo/config.cr:39:9 in 'get_connection'
  from lib/crecto/src/crecto/repo.cr:69:30 in 'all'
  from src/model/example.cr:16:1 in '__crystal_main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:86:7 in 'main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:106:3 in 'main'
Caused by: Cannot establish connection (PQ::ConnectionError)
  from lib/pg/src/pq/connection.cr:42:7 in 'initialize'
  from lib/pg/src/pq/connection.cr:21:5 in 'new'
  from lib/pg/src/pg/connection.cr:13:23 in 'initialize'
  from lib/pg/src/pg/connection.cr:7:5 in 'new'
  from lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from lib/db/src/db/database.cr:51:9 in '->'
  from lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from lib/db/src/db/pool.cr:17:34 in 'initialize'
  from lib/db/src/db/pool.cr:15:5 in 'new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay'
  from lib/db/src/db/database.cr:50:7 in 'initialize'
  from lib/db/src/db/database.cr:43:5 in 'new'
  from lib/db/src/db.cr:139:5 in 'build_database'
  from lib/db/src/db.cr:135:5 in 'build_database'
  from lib/db/src/db.cr:103:5 in 'open'
  from lib/crecto/src/crecto/repo/config.cr:39:9 in 'get_connection'
  from lib/crecto/src/crecto/repo.cr:69:30 in 'all'
  from src/model/example.cr:16:1 in '__crystal_main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:86:7 in 'main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:106:3 in 'main'
Caused by: Index out of bounds (IndexError)
  from /usr/local/Cellar/crystal/0.26.1_1/src/string.cr:830:16 in '[]'
  from lib/pg/src/pq/connection.cr:31:12 in 'initialize'
  from lib/pg/src/pq/connection.cr:21:5 in 'new'
  from lib/pg/src/pg/connection.cr:13:23 in 'initialize'
  from lib/pg/src/pg/connection.cr:7:5 in 'new'
  from lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from lib/db/src/db/database.cr:51:9 in '->'
  from lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from lib/db/src/db/pool.cr:17:34 in 'initialize'
  from lib/db/src/db/pool.cr:15:5 in 'new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay'
  from lib/db/src/db/database.cr:50:7 in 'initialize'
  from lib/db/src/db/database.cr:43:5 in 'new'
  from lib/db/src/db.cr:139:5 in 'build_database'
  from lib/db/src/db.cr:135:5 in 'build_database'
  from lib/db/src/db.cr:103:5 in 'open'
  from lib/crecto/src/crecto/repo/config.cr:39:9 in 'get_connection'
  from lib/crecto/src/crecto/repo.cr:69:30 in 'all'
  from src/model/example.cr:16:1 in '__crystal_main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:86:7 in 'main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:106:3 in 'main'

I have now installed and uninstalled Postgres, dropped all my local databases, and rebuilt them to see if that might fix the issue. Below is my setup:

module Frost::DatabaseHelper
  extend Crecto::Repo

  config do |conf|
    conf.adapter = Crecto::Adapters::Postgres
    conf.uri = "postgres://frost:frost@localhost:5432/frost"
  end
end

Repo  = Frost::DatabaseHelper
Query = Crecto::Repo::Query


class Example < Crecto::Model
  # Relationships
  # belongs_to :user, User

  # Fields
  schema "examples" do
    field :name, String
    field :description, String
  end

  # Object methods
end

query = Query.new
query = query.where(description: "User")
puts Repo.all(Example, query)

(I have also tried this without the uri method, to similar result)

[faultyserver]

It looks like the error is happening because the connection info isn't being passed down to the driver properly (from /usr/local/Cellar/crystal/0.26.1_1/src/string.cr:830:16 in '[]').

Are you on a release version of crecto? Or the current master? I'm not currently able to reproduce this with the code you have here.

My initial guess would be that Repo = Frost::DatabaseHelper is doing something weird with duplicating the module and that you should use alias Repo = Frost::DatabaseHelper instead (which I think you should anyway) but that didn't affect anything in my quick testing just now.

[jonjondev]

So I'm using the latest release, 0.9.0 from the master with:

dependencies:
  toro:
    github: soveran/toro
  pg:
    github: will/crystal-pg
  crecto:
    github: Crecto/crecto
  redis:
    github: stefanwille/crystal-redis

development_dependencies:
  mass_spec:
    github: c910335/mass-spec

Aliasing didn't change anything but good call nonetheless.

[fridgerator]

@J-Mo63 You are able to connect using the postgres cli? psql -h localhost -U frost frost

edit: just noticed you mentioned other adapters are working fine

[jonjondev]

Yes, while this pointed out to me that I did have issues with privileges since updating to Postgres 10.5, it unfortunately did not change the connection error.

psql -h localhost -U frost frost

psql (10.5)
Type "help" for help.

frost=> INSERT INTO examples (name, description) VALUES ('this should work', 'testing');
INSERT 0 1
frost=> SELECT * FROM examples;
 id |       name       |     description
----+------------------+---------------------
  1 | a name           | a description
  2 | another name     | another description
  3 | this should work | testing
(3 rows)

While I have complete access on that user, on that table, I still get the same error:

Unhandled exception:  (DB::ConnectionRefused)
  from lib/pg/src/pg/connection.cr:16:9 in 'initialize'
  from lib/pg/src/pg/connection.cr:7:5 in 'new'
  from lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from lib/db/src/db/database.cr:51:9 in '->'
  from lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from lib/db/src/db/pool.cr:17:34 in 'initialize'
  from lib/db/src/db/pool.cr:15:5 in 'new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay'
  from lib/db/src/db/database.cr:50:7 in 'initialize'
  from lib/db/src/db/database.cr:43:5 in 'new'
  from lib/db/src/db.cr:139:5 in 'build_database'
  from lib/db/src/db.cr:135:5 in 'build_database'
  from lib/db/src/db.cr:103:5 in 'open'
  from lib/crecto/src/crecto/repo/config.cr:39:9 in 'get_connection'
  from lib/crecto/src/crecto/repo.cr:69:30 in 'all'
  from src/model/example.cr:15:1 in '__crystal_main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:86:7 in 'main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:106:3 in 'main'
Caused by: Cannot establish connection (PQ::ConnectionError)
  from lib/pg/src/pq/connection.cr:42:7 in 'initialize'
  from lib/pg/src/pq/connection.cr:21:5 in 'new'
  from lib/pg/src/pg/connection.cr:13:23 in 'initialize'
  from lib/pg/src/pg/connection.cr:7:5 in 'new'
  from lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from lib/db/src/db/database.cr:51:9 in '->'
  from lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from lib/db/src/db/pool.cr:17:34 in 'initialize'
  from lib/db/src/db/pool.cr:15:5 in 'new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay'
  from lib/db/src/db/database.cr:50:7 in 'initialize'
  from lib/db/src/db/database.cr:43:5 in 'new'
  from lib/db/src/db.cr:139:5 in 'build_database'
  from lib/db/src/db.cr:135:5 in 'build_database'
  from lib/db/src/db.cr:103:5 in 'open'
  from lib/crecto/src/crecto/repo/config.cr:39:9 in 'get_connection'
  from lib/crecto/src/crecto/repo.cr:69:30 in 'all'
  from src/model/example.cr:15:1 in '__crystal_main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:86:7 in 'main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:106:3 in 'main'
Caused by: Index out of bounds (IndexError)
  from /usr/local/Cellar/crystal/0.26.1_1/src/string.cr:830:16 in '[]'
  from lib/pg/src/pq/connection.cr:31:12 in 'initialize'
  from lib/pg/src/pq/connection.cr:21:5 in 'new'
  from lib/pg/src/pg/connection.cr:13:23 in 'initialize'
  from lib/pg/src/pg/connection.cr:7:5 in 'new'
  from lib/pg/src/pg/driver.cr:3:5 in 'build_connection'
  from lib/db/src/db/database.cr:51:9 in '->'
  from lib/db/src/db/pool.cr:255:3 in 'build_resource'
  from lib/db/src/db/pool.cr:17:34 in 'initialize'
  from lib/db/src/db/pool.cr:15:5 in 'new:initial_pool_size:max_pool_size:max_idle_pool_size:checkout_timeout:retry_attempts:retry_delay'
  from lib/db/src/db/database.cr:50:7 in 'initialize'
  from lib/db/src/db/database.cr:43:5 in 'new'
  from lib/db/src/db.cr:139:5 in 'build_database'
  from lib/db/src/db.cr:135:5 in 'build_database'
  from lib/db/src/db.cr:103:5 in 'open'
  from lib/crecto/src/crecto/repo/config.cr:39:9 in 'get_connection'
  from lib/crecto/src/crecto/repo.cr:69:30 in 'all'
  from src/model/example.cr:15:1 in '__crystal_main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:86:7 in 'main'
  from /usr/local/Cellar/crystal/0.26.1_1/src/crystal/main.cr:106:3 in 'main'

[fridgerator]

Using postgres cli were you prompted for a password for the user frost?

Sorry for asking trivial questions, just trying to start at the bottom and work up.

[jonjondev]

That's fine, I understand we all miss silly things sometimes. It didn't ask for anything on top of the call psql -h localhost -U frost frost.

[fridgerator]

Does changing the uri to exclude the password change anything? "postgres://frost@localhost:5432/frost"

[jonjondev]

No change, same thing.