Having trouble getting up and running

[robacarp]

I'm tinkering with Jennifer, and you've got a lot here! It looks like a great project and I'm excited to get started.

However, I'm having a bit of trouble getting the database connected. It's printing a stack trace which seems to indicate that it's having trouble getting connected, but I'm not sure why because there's no actual error message. I've configured the logging to debug level and still no error messages.

I attempted to use a database.yml file as I'm used to using in Rails, where the default user is whoami and the password is unnecessary because I'm locally authenticated:

default: &default
  host: localhost
  adapter: postgres

development:
  <<: *default
  db: beetle_development

That didn't work:

./sam db:setup
lib/jennifer/src/jennifer/adapter/command_shell/i_command_shell.cr:19:9 in 'invoke'
lib/jennifer/src/jennifer/adapter/command_shell/bash.cr:14:9 in 'execute'
lib/jennifer/src/jennifer/adapter/db_command_interface.cr:24:9 in 'execute'
lib/jennifer/src/jennifer/adapter/postgres/command_interface.cr:23:9 in 'create_database'
lib/jennifer/src/jennifer/adapter/base.cr:220:9 in 'create_database'
lib/jennifer/src/jennifer/migration/runner.cr:36:11 in 'create'
lib/jennifer/src/jennifer/sam.cr:36:5 in '->'
lib/sam/src/sam/task.cr:56:39 in 'call'
lib/sam/src/sam/execution.cr:19:7 in 'invoke'
lib/sam/src/sam/task.cr:45:29 in 'call'
lib/sam/src/sam/execution.cr:19:7 in 'invoke'
lib/sam/src/sam.cr:38:5 in 'invoke'
lib/sam/src/sam.cr:57:7 in 'process_tasks'
lib/sam/src/sam.cr:42:12 in 'help'
lib/sam/src/sam.cr:75:3 in '->'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/at_exit_handlers.cr:14:19 in 'run'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:64:14 in 'exit'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:59:5 in 'main'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:141:3 in 'main'
DB command interface exit code 1: createdb: error: database creation failed: ERROR:  zero-length delimited identifier at or near """"
LINE 1: CREATE DATABASE beetle_development OWNER "";
                                                 ^

That's ok, so I created the database manually: createdb beetle_development

And then I switched to using a database uri:

require "jennifer"
require "jennifer/adapter/postgres"

app_env = ENV.fetch("KEMAL_ENV") { "development" }

database_uri = ENV.fetch("DATABASE_URL") {
  "postgres://localhost/beetle_#{app_env}"
}

Jennifer::Config.configure do |conf|
  conf.from_uri database_uri
end

Which left me with less information about why it's failing:

> ./sam db:migrate
lib/pg/src/pg/connection.cr:22:9 in 'initialize'
lib/pg/src/pg/connection.cr:13:7 in 'initialize'
lib/pg/src/pg/connection.cr:7:5 in 'new'
lib/pg/src/pg/driver.cr:7:7 in 'build'
lib/db/src/db.cr:160:19 in '->'
lib/db/src/db/database.cr:50:24 in '->'
lib/db/src/db/pool.cr:260:27 in 'build_resource'
lib/db/src/db/pool.cr:89:34 in 'initialize'
lib/db/src/db/pool.cr:76:5 in 'new'
lib/db/src/db/database.cr:49:15 in 'initialize'
lib/db/src/db/database.cr:45:5 in 'new'
lib/db/src/db.cr:161:5 in 'build_database'
lib/db/src/db.cr:151:5 in 'build_database'
lib/db/src/db.cr:119:5 in 'open'
lib/jennifer/src/jennifer/adapter/base.cr:39:17 in 'db'
lib/jennifer/src/jennifer/adapter/transactions.cr:19:9 in 'scalar'
lib/jennifer/src/jennifer/adapter/postgres.cr:224:9 in 'exists?'
lib/jennifer/src/jennifer/query_builder/executables.cr:158:9 in 'exists?'
lib/jennifer/src/jennifer/adapter/postgres.cr:160:9 in 'table_exists?'
lib/jennifer/src/jennifer/adapter/base.cr:288:19 in 'ready_to_migrate!'
lib/jennifer/src/jennifer/migration/runner.cr:11:9 in 'migrate'
lib/jennifer/src/jennifer/sam.cr:6:5 in '->'
lib/sam/src/sam/task.cr:56:39 in 'call'
lib/sam/src/sam/execution.cr:19:7 in 'invoke'
lib/sam/src/sam.cr:38:5 in 'invoke'
lib/sam/src/sam.cr:57:7 in 'process_tasks'
lib/sam/src/sam.cr:42:12 in 'help'
lib/sam/src/sam.cr:75:3 in '->'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/at_exit_handlers.cr:14:19 in 'run'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:64:14 in 'exit'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:59:5 in 'main'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:141:3 in 'main'

I have to be done for the day but I wonder if I'm still tripping on something by using implicit user and password in the connection string. I modified my jennifer.cr to emit the connection string like this:

database_uri = ENV.fetch("DATABASE_URL") {
  url = "postgres://localhost/beetle_#{app_env}"
  puts "DATABASE_URL not set, using default: #{url}"
  url
}

And now I'm able to prove that the url itself works, like this:

> ./sam db:migrate
DATABASE_URL not set, using default: postgres://localhost/beetle_development
lib/pg/src/pg/connection.cr:22:9 in 'initialize'
lib/pg/src/pg/connection.cr:13:7 in 'initialize'
# snip stacktrace

> psql postgres://localhost/beetle_development
psql (14.10 (Homebrew))
Type "help" for help.

beetle_development=# 

Is there a way to turn on error messages, so I can have a better idea what's happening?

[robacarp]

Ok, I was able to get past the initial connection with this patch to Jennifer::Adapter::Base#connection_string:

module Jennifer
  module Adapter
    abstract class Base
      def connection_string(type : ConnectionType)
        URI.new(
          self.class.protocol,
          config.host,
          config.port.try(&.>(0)) ? config.port : nil,
          type.db? ? config.db : "",
          connection_query,
-         config.user,
+         config.user.blank? ? nil : config.user,
          config.password && !config.password.empty? ? config.password : nil
        ).to_s
      end
    end
  end
end

I seem to have run into another issue with creating the migration metadata table:

> ./sam db:setup
beetle_development is already exists
lib/jennifer/src/jennifer/adapter/base.cr:83:9 in 'results'
lib/jennifer/src/jennifer/query_builder/executables.cr:308:9 in 'to_a'
lib/jennifer/src/jennifer/adapter/postgres.cr:143:9 in 'tables_column_count'
lib/jennifer/src/jennifer/adapter/base.cr:54:9 in 'prepare'
lib/jennifer/src/jennifer/adapter/base.cr:40:11 in 'db'
lib/jennifer/src/jennifer/adapter/transactions.cr:19:9 in 'scalar'
lib/jennifer/src/jennifer/adapter/postgres.cr:224:9 in 'exists?'
lib/jennifer/src/jennifer/query_builder/executables.cr:158:9 in 'exists?'
lib/jennifer/src/jennifer/adapter/postgres.cr:160:9 in 'table_exists?'
lib/jennifer/src/jennifer/adapter/base.cr:288:19 in 'ready_to_migrate!'
lib/jennifer/src/jennifer/migration/runner.cr:11:9 in 'migrate'
lib/jennifer/src/jennifer/sam.cr:6:5 in '->'
lib/sam/src/sam/task.cr:56:39 in 'call'
lib/sam/src/sam/execution.cr:19:7 in 'invoke'
lib/sam/src/sam/task.cr:45:29 in 'call'
lib/sam/src/sam/execution.cr:19:7 in 'invoke'
lib/sam/src/sam.cr:38:5 in 'invoke'
lib/sam/src/sam.cr:57:7 in 'process_tasks'
lib/sam/src/sam.cr:42:12 in 'help'
lib/sam/src/sam.cr:75:3 in '->'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/at_exit_handlers.cr:14:19 in 'run'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:64:14 in 'exit'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:59:5 in 'main'
/Users/robert/.asdf/installs/crystal/1.11.2/src/crystal/main.cr:141:3 in 'main'
Channel is closed.
Original query was:
SELECT "information_schema"."columns"."table_name", COUNT(*) AS count FROM "information_schema"."columns" WHERE "information_schema"."columns"."table_name" IN($1) GROUP BY "information_schema"."columns"."table_name"  UNION SELECT "pg_class"."relname" AS "table_name", COUNT(*) AS count FROM "pg_attribute" JOIN "pg_class" ON "pg_attribute"."attrelid" = "pg_class"."oid"
 JOIN "pg_namespace" ON "pg_namespace"."oid" = "pg_class"."relnamespace"
WHERE "pg_attribute"."attnum" > $2 AND "pg_namespace"."nspname" = $3 AND "pg_class"."relname" IN($4) AND NOT ("pg_attribute"."attisdropped") GROUP BY table_name  | ["migration_versions", 0, "public", "migration_versions"]
Original query was:
SELECT EXISTS(SELECT 1 FROM "information_schema"."tables" WHERE "information_schema"."tables"."table_name" = $1 ) | ["migration_versions"]

Both of those queries execute fine in a local postgres shell. No results are returned. Since the message is "Channel is closed" I don't think I believe that it's a query problem anyway.

[robacarp]

I spent some time collaborating with @crimson-knight on this, and he suggested just bypassing the migrations and creating the tables manually for now, which got me up and running.

Having done that, I'm able to run a full CRUD on my tables. I don't know what the problem is with the receive channel, but I'm able to move forward without issue by just managing the schema myself.

[wJoenn]

I'm having the same issue where trying to run databases without specifying a user and a password in the config doesn't work.
Just setting them up makes everything works fine but it means the database.yml file needs to be gitignored to avoid sharing credentials (unless there's a way to pass a ENV in the config file that I'm not aware of ?)

It would be nice if I could just use whoami and omit the password like Rob suggested

[imdrasil]

the fix was merged into the master branch (but hasn't been release yet). Please take a look did it address your use case

[wJoenn]

It finds my username now but it still requires a password apparently

➜  kemal git:(master) ✗ sam db:create               
Password for user joenn: 
lib/jennifer/src/jennifer/adapter/command_shell/i_command_shell.cr:19:9 in 'invoke'
lib/jennifer/src/jennifer/adapter/command_shell/bash.cr:14:9 in 'execute'
lib/jennifer/src/jennifer/adapter/db_command_interface.cr:24:9 in 'execute'
lib/jennifer/src/jennifer/adapter/postgres/command_interface.cr:33:9 in 'database_exists?'
lib/jennifer/src/jennifer/adapter/base.cr:236:9 in 'database_exists?'
lib/jennifer/src/jennifer/migration/runner.cr:35:12 in 'create'
lib/jennifer/src/jennifer/sam.cr:36:5 in '->'
lib/sam/src/sam/task.cr:56:39 in 'call'
lib/sam/src/sam/execution.cr:19:7 in 'invoke'
lib/sam/src/sam.cr:38:5 in 'invoke'
lib/sam/src/sam.cr:57:7 in 'process_tasks'
lib/sam/src/sam.cr:42:12 in 'help'
sam.cr:15:1 in '__crystal_main'
/home/joenn/.asdf/installs/crystal/1.11.2/share/crystal/src/crystal/main.cr:129:5 in 'main_user_code'
/home/joenn/.asdf/installs/crystal/1.11.2/share/crystal/src/crystal/main.cr:115:7 in 'main'
/home/joenn/.asdf/installs/crystal/1.11.2/share/crystal/src/crystal/main.cr:141:3 in 'main'
/lib/x86_64-linux-gnu/libc.so.6 in '??'
/lib/x86_64-linux-gnu/libc.so.6 in '__libc_start_main'
/home/joenn/.cache/crystal/crystal-run-sam.tmp in '_start'
???
DB command interface exit code 2: psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: fe_sendauth: no password supplied

When I try to enter an empty password I get a no password supplied error

[imdrasil]

Hi @wJoenn. Please checkout a branch with a fix and try it out with your setup. @robacarp if it possible please double check that my adjustments work correctly for you as well