Postgres: Remove need of installing local Postgres when using remote database

[micdahl]

Hanami::Model::Migrator::PostgresAdapter uses commands like createdb and dropdb which require a local Postgres installation, even when a non-local database is used.
Would it be possible to use the corresponding SQL-queries (e.g. CREATE DATABASE) instead?

[cllns]

For context, see #200. I've run into issues with createdb not being in my PATH, which causes problems. I hadn't thought about non-local installations, though.

I wish there was some way we could use CREATE DATABASE, but it seems like createdb is the best solution :/

[micdahl]

Just forked and got CREATE DATABASE and DROP DATABASE running in PostgresAdapter. Even I seem to have managed load_structure via File.read.
So at least hanami db prepare and hanami db drop are running without dependency of local postgres installation.

Problems I still see are the pg_dump related commands, as there AFAIK is no alternative way of doing this. Possibly could be done via SELECT commands, but I am not so far into postgres that I could do this offhand.

Generally I think it is problematic to force the user to have a local db installation. Remote databases are a common use case and in some environments users may not have the ability to install the databases' tools locally.

[cllns]

createdb and dropdb should be available by installing the postgres client utilities without the server. I use OS X and homebrew, I can't figure out how you'd do it with that (brew install postgresql gives you both server and client), but I know you could do it on debian/ubuntu as sudo apt-get install postresql-client.

https://www.postgresql.org/docs/9.6/static/reference-client.html

The common feature of these applications [including createdb and dropdb] is that they can be run on any host, independent of where the database server resides.

[micdahl]

Yes, (under good circumstances) one can install the client only or even the full server.
But I still think it is an additional dependency which does not necessary belong to the application server. Just my opinion.

[jodosha]

@micdahl If you can make this to happen via a PR, we'll be more than happy to accept it.

I'm also wondering about psql. That is useful to execute manual queries and it powers hanami db console commands. Do you have any plan for that too? Thanks!

[micdahl]

@jodosha Pull request is available.
I think to use hanami db console you will not get away from psql, even rails does this. But this should be ok, as this is only used in development and not in production and the user is not forced to use it, as he can run psql on the database server, if it is not identical to the application server.

[jodosha]

@micdahl
Thanks for the PR, but the CI build doesn't pass with that changes.

But this should be ok, as this is only used in development and not in production and the user is not forced to use it

I'm confused by this sentence ☝️ . I assumed that your problems with PG createdb/dropdb commands were in development mode, not in production.

Please be aware that hanami db create and hanami db drop are disabled in production env. We can't guess where you're are gonna deploy your software, and we can't assume that PG commands are installed on your server.

Is your PR an attempt to remove our limitations in production env? Thanks.

[micdahl]

@jodosha

Is your PR an attempt to remove our limitations in production env? Thanks.

Yes, generally, I think so.
But I even need this in my development environment.
I am used to rails, where my development setup is like follows:
I use docker-compose with separated docker containers for each, database and rails application. So the PG commands are not available from the application container.
I have tried to adapt the same setup to run hanami, where I got into this issue.

But even in production this would be helpful, I think. If the database related commands are disabled in production, the user has to create the tables manually? I think it would be more elegant if hanami would do the job.

[jodosha]

@micdahl It would be great if we can remove these issues in development with Docker. Please iterate again with that PR.

If the database related commands are disabled in production, the user has to create the tables manually?

Nope. Of course db migrate and db rollback are available in production.

We disabled destructive operations (eg db drop), or hard to solve problems (eg. db create).

[apohllo]

@micdahl I use the very same Docker setup. As a workaround I have created noops for these commands in the application server, i.e.

RUN echo "#!/bin/bash" > /usr/bin/createdb
RUN chmod +x /usr/bin/createdb
RUN echo "#!/bin/bash" > /usr/bin/psql
RUN chmod +x /usr/bin/psql

This was necessary in order for the schema_migrations table to be created. But I think that decoupling hanami db prepare from creation of the database would be another option. I can have the DB schemas created in the Docker script.

BTW how this problem (namely creation of the schema_migrations table) is solved in the production environment?

[jodosha]

Closing as stale discussion.
The PR #448 tries to solve this problem, but it's stale as well.

If someone would take care of this, we'll be more than happy to merge it. Thank you all.