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.

[brkn]

Relying on system's pg_dump for the DB::Utils::Postgres#exec_dump_command is also prone to failure. My local pg_dump version is 14, but docker-compose specifies the latest, which should be v18.

As noted in postgresql documentation

pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump.

[katafrakt]

@brkn what is the alternative to use pg_dump? I don't think there is any.

What could be nice though is to compare versions and display more helpful message in the Hanami itself.

[brkn]

Can we exec pg_dump inisde the test db container?

Dump command could check if the env is test and the postgres uri matches the docker compose?

I know it's a smell to branch the code for environment. Doesn't sound like a robust solution either.

But also I saw similarly named re_running_in_test? method