Postgres - Change createdb/dropdb CLI commands to SQL statements

[vladimirtcats]

Hi everyone.

when i run the command:

HANAMI_ENV=test bundle exec hanami db create

I get error:

bundler: failed to load command: hanami (/builds/fake/vendor/ruby/ruby/3.3.0/bin/hanami)
/usr/local/lib/ruby/3.3.0/open3.rb:534:in `spawn': No such file or directory - createdb (Errno::ENOENT)
	from /usr/local/lib/ruby/3.3.0/open3.rb:534:in `popen_run'
	from /usr/local/lib/ruby/3.3.0/open3.rb:235:in `popen3'
	from /builds/fake/vendor/ruby/ruby/3.3.0/gems/hanami-cli-2.2.1/lib/hanami/cli/system_call.rb:94:in `block in call'
	from /builds/fake/vendor/ruby/ruby/3.3.0/gems/bundler-2.5.23/lib/bundler.rb:413:in `block in with_unbundled_env'

It works normally in development but in gitlab ci it crashes. I can't find the problem

As I understand it is related to createdb util that can't be found

[vladimirtcats]

I've found workaround

rspec:
  needs: ["bundle-install"]
  extends: .tests-base
  services:
    - postgres:14-alpine
  variables:
    POSTGRES_DB: beresta_ci
    POSTGRES_USER: postgres
    POSTGRES_PASSWORD: postgres
    DATABASE_URL: postgres://postgres:postgres@postgres/beresta_ci
    RACK_ENV: test
    HANAMI_ENV: test
    POSTGRES_HOST_AUTH_METHOD: "trust"
  before_script:
    - apt-get update
    - apt-get install postgresql-client -y
  script:
    - bundle install
    - bundle exec hanami db create
    - bundle exec hanami db migrate
    - bundle exec rspec

But It seems to me that it is stange to install postgres-client

[cllns]

Ah, sorry about that. Glad you figured it out! I agree it's not ideal and we can make this smoother for others in the future.

We should change from using the createdb command to just executing CREATE DATABASE via psql. That's the approach we take for mysql and Postgres's docs say:

createdb is a wrapper around the SQL command CREATE DATABASE. There is no effective difference between creating databases via this utility and via other methods for accessing the server.

Same for dropdb.

[katafrakt]

Are we sure this does not have unintended consequences? I assume that issuing the CREATE DATABASE statement would happen using the user configured in the applications, but this user quite often might not be a superuser with database creation rights. At the same time it might be configured that createdb program might be run and would create a database. From my experience using the program is much widely adopted than using the SQL statements.