# Opening database prompt based on database.yml

**URL:** <https://www.ruby-forum.com/t/opening-database-prompt-based-on-database-yml/168171>\
**Category:** Rails\
**Created:** [May 17, 2009, 4:13pm UTC](https://www.ruby-forum.com/t/opening-database-prompt-based-on-database-yml/168171 "2009-05-17T16:13:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![stephanwehner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/0ea827/32.png) [@stephanwehner](https://www.ruby-forum.com/u/stephanwehner)\
**Post date:** [May 17, 2009, 4:13pm UTC](https://www.ruby-forum.com/t/opening-database-prompt-based-on-database-yml/168171/1 "2009-05-17T16:13:01Z")

</div>

Hello everybody,

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

See the rdb\_prompt directory under

[GitHub - stephanwehner/railsgoodies: Various goodies related to Ruby on Rails](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-security-user.html](http://dev.mysql.com/doc/refman/5.0/en/password-security-user.html)

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/rdoc/classes/OptionParser.html](http://www.ruby-doc.org/stdlib/libdoc/optparse/rdoc/classes/OptionParser.html)

– still work in progress.

---

<div class="post-metadata">

**Author:** ![stephanwehner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/0ea827/32.png) [@stephanwehner](https://www.ruby-forum.com/u/stephanwehner)\
**Post date:** [May 17, 2009, 10:51pm UTC](https://www.ruby-forum.com/t/opening-database-prompt-based-on-database-yml/168171/2 "2009-05-17T22:51:27Z")

</div>

On May 17, 3:13 pm, Stephan W. [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![stephanwehner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/0ea827/32.png) [@stephanwehner](https://www.ruby-forum.com/u/stephanwehner)\
**Post date:** [May 18, 2009, 6:48am UTC](https://www.ruby-forum.com/t/opening-database-prompt-based-on-database-yml/168171/3 "2009-05-18T06:48:53Z")

</div>

Frederick C. wrote:

> On May 17, 3:13�pm, Stephan W. [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![stephanwehner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/0ea827/32.png) [@stephanwehner](https://www.ruby-forum.com/u/stephanwehner)\
**Post date:** [May 24, 2009, 6:34am UTC](https://www.ruby-forum.com/t/opening-database-prompt-based-on-database-yml/168171/4 "2009-05-24T06:34:51Z")

</div>

Stephan W. wrote:

> Frederick C. wrote:
> 
> > On May 17, 3:13�pm, Stephan W. [removed\_email\_address@domain.invalid](mailto: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

[#2707 dbconsole support password passing through /dev/fd/ - Ruby on Rails - rails](https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/2707)

Please review.

Stephan