Forum: Ruby on Rails opening database prompt based on database.yml

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Stephan W. (Guest)
on 2009-05-17 18:13
Hello everybody,

I wrote a script to open a database prompt based on the contents of
database.yml

See the rdb_prompt directory under

  http://github.com/stephanwehner/railsgoodies

I find it very useful, since I work with several different branches of
Rails projects, each possibly having different migrations which are
applied to different (mysql) databases.

Opening a (mysql) prompt would require looking up the name of the
database, and more -- which a script can do faster than me.

(sqlite3 / postgresql also supported)

The current README is below. To obtain a copy, use this "Public Clone
URL":

  git://github.com/stephanwehner/railsgoodies.git

or use github's download function.

Cheers,

Stephan




=========================== Contents of file README
===========================





== Usage

 ruby rdb_prompt.rb [environment] [YAML file]

== Motivation

Rails developer or database administrator on the Rails team has a
shell open. The current directory is the root of a Rails application.
The Rails application uses a mysql database.

Instead of opening script/console and using ActiveRecord or the table
based Rails models (because it can be slow to start up and/or clumsy for
database work) they want to connect to the mysql database directly. But
they don't want to look up the user name and password, host, port etc.
from
the (Rails standard) config/database.yml

Using the rdb_prompt.rb script they would simply set up and run these
aliases, for example,

$ alias rdb_dev='ruby rdb_prompt.rb development'  # opens database
prompt with development database
$ alias rdb_test='ruby rdb_prompt.rb test'  # opens database prompt with
test database


Result (if the mysql adapter is configured in config/database.yml for
the development environment):

$ rdb_dev
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

(They are now in a mysql prompt.)

As you can see, the rdb_prompt script allows a kind of DRY : why repeat
the database connection options if they are already in the (or a)
database.yml file.

== Databases Supported

 * mysql
 * sqlite3 (no extra options)
 * postgres (no extra options)

sqlite3 / postgres support is basic. The client is invoked with

$ sqlite3 "database"
$ psql "database"

where the "database" is looked up from the environment
(development/test/etc.) in the database.yml file.

For mysql the port, host, socket, user and password are extracted from
the database.yml file and passed on to open the mysql client.

The executable (sqlite3/postgres) can be changed using the -x /
--executable option.

In particular, if the database.yml file contains different adapters
for different environments, the (default) client corresponding to the
adapter is invoked: more DRYness.

== Note on Passwords

Passwords are not exposed more than they are already through the
database.yml file used. In the case of sqlite3 and postgresql no
password
is passed on to the client. For mysql, the mechanism implemented is
based on the first comment to the article at

 http://dev.mysql.com/doc/refman/5.0/en/password-se...

The comment contains this shell snippet

 { printf '[client]\npassword=%s\n' xxxx |
 3<&0 <&4 4<&- mysql --defaults-file=/dev/fd/3 -u myuser
 } 4<&0

in order to switch to a mysql prompt without revealing the password
within the argument list when listing processes (through ps for
example).

== Options

To see the available options, run

$ ruby dp_prompt.rb --help

With Version 0.2 the output is

Usage: rdb_prompt.rb [options] [environment] [database.yml]

Specific options:
    -x, --executable EXECUTABLE      executable to use. Defaults are
sqlite3, psql, mysql
        --mycnf                      Just output my.cnf file (mysql
adapter only)
    -i, --ignore FLAGS               flags in database.yml to ignore,
comma-separated (mysql adapter only)
    -v, --[no-]verbose               Run verbosely
        --version                    dp_prompt version
    -h, --help                       Show this help message

== Note on optparse usage

The implementation uses an experimental interface to the optparse
/ OptionParser class : ExperimentalOptionParserExtension. The
hope is to improve on the example usage given at

  http://www.ruby-doc.org/stdlib/libdoc/optparse/rdo...

-- still work in progress.
Frederick C. (Guest)
on 2009-05-18 00:51
(Received via mailing list)
On May 17, 3:13 pm, Stephan W. <removed_email_address@domain.invalid>
wrote:
> Rails projects, each possibly having different migrations which are
> applied to different (mysql) databases.
>
> Opening a (mysql) prompt would require looking up the name of the
> database, and more -- which a script can do faster than me.
>

Don't want to piss on anyone's parade but Rails comes with a script
that does that eg

ruby script/dbconsole production

Fred
Stephan W. (Guest)
on 2009-05-18 08:48
Frederick C. wrote:
> On May 17, 3:13�pm, Stephan W. <removed_email_address@domain.invalid>
> wrote:
>> Rails projects, each possibly having different migrations which are
>> applied to different (mysql) databases.
>>
>> Opening a (mysql) prompt would require looking up the name of the
>> database, and more -- which a script can do faster than me.
>>
>
> Don't want to piss on anyone's parade but Rails comes with a script
> that does that eg
>
> ruby script/dbconsole production

It looks to me that script/dbconsole doesn't hide the password for mysql
and postgresql on all platforms -- whereas rdb_prompt takes care of that
(for postgresql trivially by not supporting passwords).

So I'll put a patch together.

Stephan
Stephan W. (Guest)
on 2009-05-24 08:34
Stephan W. wrote:
> Frederick C. wrote:
>> On May 17, 3:13�pm, Stephan W. <removed_email_address@domain.invalid>
>> wrote:
>>> Rails projects, each possibly having different migrations which are
>>> applied to different (mysql) databases.
>>>
>>> Opening a (mysql) prompt would require looking up the name of the
>>> database, and more -- which a script can do faster than me.
>>>
>>
>> Don't want to piss on anyone's parade but Rails comes with a script
>> that does that eg
>>
>> ruby script/dbconsole production
>
> It looks to me that script/dbconsole doesn't hide the password for mysql
> and postgresql on all platforms -- whereas rdb_prompt takes care of that
> (for postgresql trivially by not supporting passwords).
>
> So I'll put a patch together.

The patch is attached to this new ticket

 https://rails.lighthouseapp.com/projects/8994-ruby...

Please review.

Stephan
This topic is locked and can not be replied to.