Error: Database with the same name as the username does not exist

[patorash]

When using hanami-cli 2.1.1 with PostgreSQL, an error occurs when calling create_command. It seems that the error happens when specifying a PostgreSQL user, and there is no database with the same name as the user.

cli/lib/hanami/cli/commands/app/db/utils/postgres.rb

Line 17 in 5bfd953

existing_stdout, status = Open3.capture2(cli_env_vars, "psql -t -c '\\l #{escaped_name}'")

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  database "{{username}}" does not exist

[cllns]

I think this is a quirk of how Postgres infers connections, rather than an issue with Hanami's CLI. See their docs:

DBNAME: Defaults to be the same as the user name.

I think your PR #229 would prevent people from setting PGDATABASE themselves, which we don't want to do.

To clarify, you're specifying a PGUSER, is that right? If so, then you can set PGDATABASE as well. Does that sound like an acceptable solution to you @patorash?

[patorash]

@cllns This process is meant to check if escaped_name exists in the database, right? So, if you specify the same database name as escaped_name in PGDATABASE here, you’ll get an error saying that the database doesn’t exist if it isn’t there. That’s why you need to specify ‘postgres’ as the database name here.

[timriley]

@patorash Thanks for raising this.

Can you please give us reproduction instructions for the error you encountered? I'd like to make sure we're doing the right thing in this circumstance, but I'm not sure that setting PGDATABASE is the way to go, so being able to explore this directly would be helpful.

[patorash]

@timriley

Normally, this always happens when there is no database with the same name as the username. Probably, most users do not encounter this error because they use “postgres” as the username during development. This is because a database named “postgres” always exists. Therefore, we set the PGDATABASE only at the timing of executing the psql command on line 17.

I apologize if this is due to my lack of knowledge, but if you know a way to avoid this issue when there is no database with the same name as the username, please let me know.

[timriley]

Hi @patorash, I've just found the time to look into this. Here's how I tested it.

I installed postgres via Homebrew, to replicate a common installation approach on macOS:

brew install postgres

Then I ran postgres manually, according to the instructions in the post-installation message:

$ /opt/homebrew/opt/postgresql@14/bin/postgres -D /opt/homebrew/var/postgresql@14
2024-10-28 20:42:27.508 AEDT [22959] LOG:  starting PostgreSQL 14.13 (Homebrew) on aarch64-apple-darwin23.4.0, compiled by Apple clang version 15.0.0 (clang-1500.3.9.4), 64-bit
2024-10-28 20:42:27.509 AEDT [22959] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-10-28 20:42:27.509 AEDT [22959] LOG:  listening on IPv6 address "::1", port 5432
2024-10-28 20:42:27.510 AEDT [22959] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-10-28 20:42:27.515 AEDT [22960] LOG:  database system was shut down at 2024-10-28 20:35:04 AEDT
2024-10-28 20:42:27.519 AEDT [22959] LOG:  database system is ready to accept connections

Trying to connect, you can see that there is no database matching my username:

$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "tim" does not exist

After this, I generated a new Hanami app, created a migration, migrated, connected to the database and created a record successfully (output below skipped where it is not relevant):

$ hanami new elephant --database=postgres

$ cat .env
# This is checked into source control, so put sensitive values into `.env.local`
DATABASE_URL=postgres://localhost/elephant

$ be hanami g migration create_posts

$ cat config/db/migrate/20241028204329_create_posts.rb
# frozen_string_literal: true

ROM::SQL.migration do
  # Add your migration here.
  #
  # See https://sequel.jeremyevans.net/rdoc/files/doc/migration_rdoc.html for details.
  change do
    create_table :posts do
      column :title, :text
    end
  end
end

$ be hanami db prepare
=> database elephant created
=> database elephant migrated in 0.0028s
=> elephant structure dumped to config/db/structure.sql in 0.0353s

$ cat config/db/structure.sql | grep posts
-- Name: posts; Type: TABLE; Schema: public; Owner: -
CREATE TABLE public.posts (
('20241028204329_create_posts.rb');

$ be hanami g relation posts

$ be hanami c
elephant[development]> app["relations.posts"]
=> #<Elephant::Relations::Posts name=ROM::Relation::Name(posts) dataset=#<Sequel::Postgres::Dataset: "SELECT \"posts\".\"title\" FROM \"posts\"">>

elephant[development]> app["relations.posts"].insert(title: "test")

elephant[development]> app["relations.posts"].to_a
=> [{:title=>"test"}]

So everything works as expected.

Since I can't replicate your issue, so I'm reluctant to merge the PR you've proposed, at least not right at this minute.

Could you please help supply a replication?

Thank you 🙏🏼

[patorash]

@timriley
I am in the process of upgrading from 2.1 to 2.2 right now, and I am sharing the results of the error.

First, I tried to delete the DB (it shows as Dropped, but it is not actually deleted)

vscode ➜ /workspaces/******** (chore/hanami-update) $ hanami db drop   
=> database ********_development dropped
=> database ********_test dropped

If it is deleted, you should be able to create it, but you will get an error.

vscode ➜ /workspaces/******** (chore/hanami-update) $ hanami db create
=> failed to create database ********_development
createdb: error: database creation failed: ERROR: database “********_development” already exists

The reason is that in this line, as previously reported, the database with the same name as the user name does not exist. If you omit the database name, it will try to connect to the database with the same name as the user name.

cli/lib/hanami/cli/commands/app/db/utils/postgres.rb

Line 35 in 13fbe2c

result = system_call.call("psql -t -A -c '\\list #{escaped_name}'", env: cli_env_vars)

As a result, the result of result.successful? is false, so return true unless exists? in exec_drop_command returns true. At this point, we are not sure if the database for excaped_name exists or not.

However, since the result is true, we see database ********_development dropped here, even though it was not actually deleted.

cli/lib/hanami/cli/commands/app/db/drop.rb

Line 21 in 13fbe2c

if result == true || result.successful?

This happens when the same database as the user name does not exist. To work around this, I think you need to create a database in advance that is the same as the user name, or specify a database that always exists, such as postgres.

Translated with DeepL.com (free version)

[patorash]

To summarize the above, there is a bug where the database recreation fails if a database with the same name as the username does not exist.
Currently, we are reluctantly creating a database with the same name as the username to work around this bug.
However, ideally, there should be no need to create a database with the same name as the username, so we hope this bug can be fixed.

[cllns]

Hi @patorash, can you provide a reproducible list of instructions for us to replicate this, from an empty/default postgres install?

[patorash]

@cllns @timriley
I performed a clean installation of Ubuntu 24.04 on WSL.

1. Upgrade all packages using apt.

sudo apt update && sudo apt full-upgrade -y

2. Install PostgreSQL

sudo apt install postgresql postgresql-contrib libpq-dev
sudo systemctl start postgresql
sudo systemctl enable postgresql

3. Check the version.

$ sudo su postgres
$ psql
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type "help" for help.

postgres=#

4. Install Ruby

sudo apt-get install autoconf patch build-essential rustc libssl-dev libyaml-dev libreadline6-dev zlib1g-dev libgmp-dev libncurses5-dev libffi-dev libgdbm6 libgdbm-dev libdb-dev uuid-dev
git clone https://github.com/rbenv/rbenv.git ~/.rbenv
git clone https://github.com/rbenv/ruby-build.git ~/.rbenv/plugins/ruby-build
echo 'export PATH="$HOME/.rbenv/bin:$PATH"' >> ~/.bash_profile
~/.rbenv/bin/rbenv init
source ~/.bash_profile
rbenv install 3.3.6
rbenv global 3.3.6

5. Install Node.js

sudo apt install nodejs npm

6. Install Hanami

gem install hanami

7. Create project

mkdir sources
cd sources
hanami new elephant --database=postgres

8. Check the contents of the .env file.

$ cat .env
# This is checked into source control, so put sensitive values into `.env.local`
DATABASE_URL=postgres://localhost/elephant

9. Create a PostgreSQL user named tim and grant database creation privileges. The password is withheld.

$ sudo su postgres
$ createuser -d -P tim
Enter password for new role: ********
Enter it again: ********

10. Edit the .env file. Since PostgreSQL 16 installed via apt is somehow running on port 5433, specify the port as 5433.

# This is checked into source control, so put sensitive values into `.env.local`
DATABASE_URL=postgres://tim:********@localhost:5433/elephant

11. Create database

$ bundle exec hanami db create
=> database elephant created
=> database elephant_test created

12. Drop database

$ bundle exec hanami db drop
=> database elephant dropped
=> database elephant_test dropped

13. Create database again(error)

$ bundle exec hanami db create
=> failed to create database elephant
createdb: error: database creation failed: ERROR:  database "elephant" already exists

[katafrakt]

@patorash I can reproduce it. From my investigation, the problem is with drop command not being executed, not create. However the root cause is as you found - no database with the same name as the username.

I wonder why Hanami uses psql -t -A -c '\\list #{escaped_name}' to check for database existence. What is funny is that even dropping apostrophes around the command makes it work. But I really think it should use psql -l instead. Maybe even psql -l --csv, parse the output and make sure the database is really listed there.

[patorash]

@katafrakt Thank you for your confirmation! Since the issue is being discussed elsewhere, I will wait for the conclusion from that discussion.