Define the length of varchar for MySQL in rails

Question

I want to define the size of the varchar in rails for example name varchar(20), age varchar(6),through git-bash in process of generating the model.

I searched on the stack but couldn't find any answer.

Solution

There's a number of options when creating columns that are documented for the column method. They also apply to add_column when doing subsequent modifications.

The most concise way to make a more limited column is:

t.string :name, :limit => 20
t.string :age, :limit => 6

As a note, it's highly unusual to impose limits like this in your database and a better solution is to limit on the model using validates. For example:

validates :name,
  :presence => true,
  :length => { :maximum => 20 }

MySQL has a tendency to truncate values that are too long without telling you, so not having a length limit will eventually lead to lost data, especially with such a short length.

Remember that VARCHAR columns in the database are variable length, so there's no storage advantage to a ten character value in a VARCHAR(255) versus a VARCHAR(20).

Comments

[Sachin Prasad]

One question please .. why we don't define it through command line in this way 'name :varchar(20)' while generating model, I was just eager to know? .. Please don't mind for a vague question.

[tadman]

Defining models through the command line is supported, but a lot more limited in what you can achieve. It's also a rather peculiar feature to have and I'm not sure why it would be necessary to do that. What's the advantage to using that over rendering out your own fully customized migration file from a script? Usually the model requires at least some editing to add indexes, for instance, except in all but the most trivial of cases.

[fatfrog]

I think it's better to do this at the database model. You may not save on storage, but having varchar 255 on every string in your database increases the memory footprint of the object.

[tadman]

@fatfrog A string only takes up as much memory as it has characters that are populated. VARCHAR(255) has zero effect on Ruby. By extension, would you expect that a LONGTEXT automatically takes up 2GB of memory?

[fatfrog]

@tadman if your going to index them, yes there is a hit for searches. Besides, it's just sloppy database design doing everything like zip codes in 255.

[tadman]

@fatfrog Unless you're dealing with large tables (>1MM records) that have considerable amounts of data that is indexed, then this will not be a problem. I think you're making a big deal out of nothing here.

[Kris]

Newer versions of MySQL throw errors instead of silently truncating.