# Possible to add columns to a database table from a controller? (not from a migration)

**URL:** <https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551>\
**Category:** Rails\
**Created:** [July 30, 2008, 9:06pm UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551 "2008-07-30T21:06:11Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [July 30, 2008, 9:06pm UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/1 "2008-07-30T21:06:11Z")

</div>

I am trying to figure out how to add columns to a database table from  
within a controller. I have a client who wants to be able to add  
their own attributes to a model from within the running application,  
so I don’t think using migrations is an option.

Basically, I have a Person model which starts off with some basic  
attributes (like first\_name and last\_name), and my client wants to be  
able to add new attributes on the fly through the application (like  
birthdate or hometown, or anything he can think of). My plan for  
implementing this is to use another model (PersonField) to keep track  
of each new attribute’s name, label, data type, and default value, and  
then tie the creation, updating, or destruction of these PersonField  
objects to the addition, changing, or removing of columns to the  
‘people’ table in the database.

To restate it in another way, I’m looking for the equivalent to  
‘add\_column’ in a migration, but I’d like to call it from a  
controller.

I have not been able to find a solution to this problem, but I’m  
relatively new to Rails (just a few months experience), so I may not  
be looking in the right places.

I’d appreciate any help or suggestions anyone can give me. Thanks for  
reading this!

---

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [July 31, 2008, 9:54am UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/2 "2008-07-31T09:54:51Z")

</div>

On Wed, Jul 30, 2008 at 3:34 AM, mikebind [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> implementing this is to use another model (PersonField) to keep track  
> of each new attribute’s name, label, data type, and default value, and  
> then tie the creation, updating, or destruction of these PersonField  
> objects to the addition, changing, or removing of columns to the  
> ‘people’ table in the database.
> 
> To restate it in another way, I’m looking for the equivalent to  
> ‘add\_column’ in a migration, but I’d like to call it from a  
> controller.

You can always execute arbitrary sql commands by using Model.find\_by\_sql

A better way may be to store these customs fields in the database:

Person  
has\_many :person\_fields

PersonField  
belongs\_to :custom\_field  
belongs\_to :person

CustomField  
has\_many :person\_fields

---

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [July 31, 2008, 9:56am UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/3 "2008-07-31T09:56:37Z")

</div>

Looking around some more, I found this thread  
[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/bfb700028cf5e696#](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/bfb700028cf5e696#)  
, where the poster was trying to do something similar. Generally, he  
was advised that this was a bad idea and might be better addressed in  
other ways, however, these other possible ways weren’t discussed. I’m  
not at all committed to implementing this feature (custom information  
fields added on the fly from the application) in the way I outlined  
above; if there’s a better way to handle it, that would be fine.

Basically, what my client wants is to be able to add new information  
fields to records about people. For example, he would like to be able  
to add a field for ‘Home Town’ to a Person record. Then, whenever he  
creates or edits a Person, he wants there to be a place to enter ‘Home  
Town’ information. Obviously this would be easy to do with a  
migration (just add a ‘home\_town’ column to the ‘people’ table in the  
database), but he wants to be able to do this from within the running  
application.

It seems most natural to me to alter the database table structure, but  
apparently this is a bad idea because of caching that Rails does for  
you to improve performance. It sounds as though you can turn off  
model caching, but that this typically causes a significant  
performance hit. Is there a way to turn it off for just one model?  
In my case, the Person model is the only one which would need to be  
dynamically altered.

Thanks for any help or suggestions!

-Mike Bindschadler

---

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [July 31, 2008, 11:20am UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/4 "2008-07-31T11:20:21Z")

</div>

Thanks for the suggestions! I’ll chew on these some more when it’s  
not 2am, but I think the idea from Miles G. sounds very  
promising. The bit about a column for each data type really helped  
the idea to begin to crystalize for me. Christopher Kintner’s  
solution may be similar, but I don’t see yet how having the  
CustomField model would help.

I’ll post back when I’ve settled on something and have it working or  
if I hit another wall. Thanks again!

-Mike

---

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [July 31, 2008, 3:20pm UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/5 "2008-07-31T15:20:30Z")

</div>

The reason there’s no easy method for doing what you want to do is  
because it’s a terrible idea! Letting the customer arbitrarily change  
the structure of the database is guaranteed to get you an unstable  
database.

You already have a second table in mind to store data about the  
arbitrary fields the customer wants to create. Have a third table that  
stores the data and includes pointers back to the attribute table and  
the persons table. You can set this sort of thing up easily with a  
has\_many :through relationship. This is much more stable and requires  
no special programming. The customer can create or delete data fields  
on the fly, use them inconsistently or not at all, and the data remain  
easily accessible.

---

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [August 11, 2008, 9:25am UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/6 "2008-08-11T09:25:20Z")

</div>

Thanks for all the help! I ended up doing a slight variation on the  
solutions suggested here. The client wanted every Person to have the  
same fields rather than adding single custom fields to an individual  
person, so I dropped the has\_many :people\_fields from the Person model  
(since all people have all PeopleFields). In outline it looks  
something like this:

class Person \< ActiveRecord::Base  
has\_many :custom\_people\_field\_values

class CustomPeopleField \< ActiveRecord::Base  
has\_many :custom\_people\_field\_values

class CustomPeopleFieldValue \< ActiveRecord::Base  
belongs\_to :person  
belongs\_to :custom\_people\_field

In the custom\_people\_fields table there are columns for the field  
name, label, data type, default value, and order of appearance. In  
the custom\_people\_field\_values table there are id columns for person  
and custom people field, as well as columns for data type and one  
column for each allowed data type (to hold the appropriate value in  
the appropriate format, as suggested by Miles G. above).

I wanted to be able to refer to custom fields just like regular  
fields, so if ‘hometown’ was a custom-created field, I wanted to be  
able to do things like this  
dave = Person.new  
dave.hometown = ‘Seattle’  
current\_town = dave.hometown

To set this up, I added a method\_missing method to the Person model  
which looks something like this

def method\_missing(m, \*args)  
if   
if   
# create a new or update a current CustomPeopleFieldValue which  
points to the current Person and the matching CustomPeopleField with  
the requested value  
else # there’s not an = on the end of the method call  
if   
# return the existing value  
else  
# return the default value (or nil if no default value) for  
this CustomPeopleField  
end  
end  
else # there was no matching field, just treat it like a regular  
missing method  
super  
end

Hope this helps someone else in similar predicament! And thanks again  
to everyone who responded!

---

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [August 11, 2008, 7:22pm UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/7 "2008-08-11T19:22:43Z")

</div>

Very good idea.  
I was searching some solution exactly like this.  
Thanks for the detailed suggestion!

---

<div class="post-metadata">

**Author:** ![mikebind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/71e660/32.png) [@mikebind](https://www.ruby-forum.com/u/mikebind)\
**Post date:** [July 31, 2008, 9:58am UTC](https://www.ruby-forum.com/t/possible-to-add-columns-to-a-database-table-from-a-controller-not-from-a-migration/144551/8 "2008-07-31T09:58:23Z")

</div>

You could run the execute method and pass it the query that creates the  
column

I’d personally create a table to hold these attributes, and add rows to  
it,  
rather than creating/dropping columns dynamically. That is I’d create  
an  
attribute model and give it a foreign key to the row in the other table,  
the  
name, and the value (and possibly a column for each possible type + a  
type  
column.)