# Portable db table definitions?

**URL:** <https://www.ruby-forum.com/t/portable-db-table-definitions/102493>\
**Category:** Rails\
**Created:** [November 16, 2005, 6:34am UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493 "2005-11-16T06:34:00Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![petermichaux](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/46a35a/32.png) [@petermichaux](https://www.ruby-forum.com/u/petermichaux)\
**Post date:** [November 16, 2005, 6:34am UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493/1 "2005-11-16T06:34:00Z")

</div>

Hi,

I’ve been playing with ActiveRecords create\_table. I created a single  
file  
that I call from the command line using

ruby -rubygems db/tables.rb

I’ve attached the file below. I was just wondering if I am on the right  
track with how this was intended to be used or if there is a better way.  
I  
can’t find any docs. I don’t feel very DRY since I am restating my  
connection parameters in this file.

Thanks!  
Peter

require ‘active\_record’

ActiveRecord::Base.establish\_connection({  
:adapter =\> ‘mysql’,  
:host =\> ‘localhost’,  
:database =\> ‘tshirtshop\_development’,  
:username =\> ‘dev’  
})

ActiveRecord::Schema.define do

drop\_table :authors

create\_table :authors do |t|  
t.column :name, :string, :null =\> false  
end

add\_index :authors, :name, :unique

drop\_table :posts

create\_table :posts do |t|  
t.column :author\_id, :integer, :null =\> false  
t.column :subject, :string  
t.column :body, :text  
t.column :private, :boolean, :default =\> false  
end

add\_index :posts, :author\_id

end

---

<div class="post-metadata">

**Author:** ![petermichaux](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/46a35a/32.png) [@petermichaux](https://www.ruby-forum.com/u/petermichaux)\
**Post date:** [November 16, 2005, 6:50pm UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493/2 "2005-11-16T18:50:07Z")

</div>

In a rails app you can supply a parameter to the establish\_connection  
call that specifies a section in the database.yaml file.

Rick

---

<div class="post-metadata">

**Author:** ![petermichaux](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/46a35a/32.png) [@petermichaux](https://www.ruby-forum.com/u/petermichaux)\
**Post date:** [November 17, 2005, 1:26am UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493/3 "2005-11-17T01:26:45Z")

</div>

Since you are posting in the rails forum I figured you were dealing with  
a rails app. I’m kind of new at this so I’m just trying to kind of  
point you in the right direction.

Rails uses a app/config/database.yml file to define the database  
connection and I was suggesting you might want to use it also.

Rick

---

<div class="post-metadata">

**Author:** ![petermichaux](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/46a35a/32.png) [@petermichaux](https://www.ruby-forum.com/u/petermichaux)\
**Post date:** [November 16, 2005, 7:24pm UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493/4 "2005-11-16T19:24:32Z")

</div>

I’m not using a Rails app to define my tables. That ruby script I posted  
is  
stand alone. Maybe I am going at this all wrong.  
Do people not use the Rails methods to describe their databases to  
ensure  
portability? I thought that we would be avoiding the SQL dialect  
problems by  
using ActiveRecord to define the tables and then YML file to populate  
them.  
Thanks,  
Peter

---

<div class="post-metadata">

**Author:** ![petermichaux](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/46a35a/32.png) [@petermichaux](https://www.ruby-forum.com/u/petermichaux)\
**Post date:** [November 17, 2005, 2:15am UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493/5 "2005-11-17T02:15:04Z")

</div>

Hi Rick,

Sorry, I wasn’t clear. I am using a Rails application. But the scipt I  
posted is a free standing script that I run form the command line.

I suppose I want to do exactly what you are suggesting: use  
database.yml.  
But I don’t know how to call that. My table definition script is  
currently  
in the rails app db directory. How can I call the database.yml file from  
my  
script?

Thanks,  
Peter

---

<div class="post-metadata">

**Author:** ![petermichaux](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/46a35a/32.png) [@petermichaux](https://www.ruby-forum.com/u/petermichaux)\
**Post date:** [November 17, 2005, 3:53pm UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493/6 "2005-11-17T15:53:32Z")

</div>

If this is for a rails app, why not use migrations? No need, then, to  
duplicate the connection info as it gets pulled from database.yml and  
you can gracefully handle schema changes over time…

See [Peak Obsession](http://api.rubyonrails.com/classes/ActiveRecord/Migration.html)

HTH,  
Tim

---

<div class="post-metadata">

**Author:** ![petermichaux](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/46a35a/32.png) [@petermichaux](https://www.ruby-forum.com/u/petermichaux)\
**Post date:** [November 17, 2005, 5:36pm UTC](https://www.ruby-forum.com/t/portable-db-table-definitions/102493/7 "2005-11-17T17:36:15Z")

</div>

Hi Tim,

Thanks for the info and the link. It looks like Migration should be what  
I  
use. For the create table method if I use the options hash for something  
like “DEFAULT CHARSET=UTF-8” will that be portable between MySQL and  
postgresql and future databases supported by migrations?

Thanks again,  
Peter