CODEWITHSUNDEEP
ruby-on-railsrubyenums
George
George

Reputation: 3943

How to store enum as string to database in rails

How do I create a migration in ruby where the default is a string rather than an Integer, I want to store enum into the database, but I do not want to store it as Integer, because then it does not make sense to another application that wants to use the same table. How do I do default: "female" instead of default:0

class AddSexToUsers < ActiveRecord::Migration
  def change
    add_column :users, :sex, :integer, default: 0
  end
end
class User < ActiveRecord::Base
  enum sex: [:female, :male]
  has_secure_password
end

I

Upvotes: 54

Views: 50702

Answers (6)

Lee Hericks
Lee Hericks

Reputation: 83

Courtesy of DHH in the Once Campfire source, you can .index_by(&:itself) as the most readable option for string ActiveRecord enums.

class User < ActiveRecord::Base
  enum :sex, %w[female male].index_by(&:itself)
end

Rails documentation now properly states that you can use hashes for string enums, but take care that querying a string rather than an integer column will be slower. You could add an index to the column if you are often ordering or filtering on it.

Upvotes: 4

nikolayp
nikolayp

Reputation: 17919

There's steps to add enum as string to model Company

bin/rails g migration AddStatusToCompanies status

class AddStatusToCompanies < ActiveRecord::Migration[7.0]
  def change
    add_column :companies, :status, :string, null: false, default: 'claimed'
    add_index  :companies, :status
  end
end

bin/rails db:migrate
  • Values are strings (symbols not working)
  • add Default
  • add Prefix
enum status: {
  claimed: 'claimed',
  unverified: 'unverified',
  verified: 'verified',
}, default: 'claimed'
  • Add validation (or will raise sql exception)
validates :status, inclusion: { in: statuses.keys }, allow_nil: true

Upvotes: 1

schmijos
schmijos

Reputation: 8695

I normally do the following:

# in the migration in db/migrate/…
def self.up
  add_column :works, :status, :string, null: false, default: 'offering'
end

# in app/models/work.rb
class Work < ApplicationRecord
  ALL_STATES = %w[canceled offering running payment rating done].freeze

  enum status: ALL_STATES.zip(ALL_STATES).to_h
end

By using a hash as argument for enum (see docs) this stores strings in the database. At the same time this still allows you to use all the cool Rails helper methods:

w = Work.new
#=>  #<Work id: nil, status: "offering">
w.rating?
#=> false
w.offering?
#=> true
w.status
#=> "offering"
w[:status]
#=> "offering"
w.done!
# INSERT transaction...
w.status
#=> "done"
w[:status]
#=> "done"

Update for one-liner:

I overlooked completely we've got index_by since Rails 1.2.6. This makes the solution a one-liner even:

enum status: %w[canceled offering running payment rating done].index_by(&:to_sym)

Alternatively we've got index_with since Rails 6.0.0:

enum status: %i[canceled offering running payment rating done].index_with(&:to_s)

Upvotes: 56

Doguita
Doguita

Reputation: 15703

Reading the enum documentation, you can see Rails use the value index of the Array explained as:

Note that when an Array is used, the implicit mapping from the values to database integers is derived from the order the values appear in the array.

But it is also stated that you can use a Hash:

it's also possible to explicitly map the relation between attribute and database integer with a Hash.

With the example: 

class Conversation < ActiveRecord::Base  
  enum status: { active: 0, archived: 1 }  
end

So I tested using Rails 4.2.4 and sqlite3 and created an User class with a string type for sex type and a Hash in the enum with string values(I am using fem and mal values to differ from female and male):

Migration: 

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string :sex, default: 'fem'
    end
  end
end

Model: 

class User < ActiveRecord::Base
  enum sex: { female: 'fem', male: 'mal' }
end

And in console: 

u = User.new
#=>  #<User id: nil, sex: "fem">
u.male?
#=> false
u.female?
#=> true
u.sex
#=> "female"
u[:sex]
#=> "fem"
u.male!
# INSERT transaction...
u.sex
#=> "male"
u[:sex]
#=> "mal"

Upvotes: 79

The Ancient
The Ancient

Reputation: 380

To my knowledge it is not possible with standard Rails enum. Look at https://github.com/lwe/simple_enum, it is more functionally rich, and also allows storing of enum values as strings to DB (column type string, i.e. varchar in terms of DB).

Upvotes: -1

Aleksey Shein
Aleksey Shein

Reputation: 7482

enum in Rails and ENUM type in MySQL are 2 different things.

  1. enum in Rails is just a wrapper around your integer column so it's easier for you to use strings in queries, rather than integers. But on database level it's all converted to integers (automatically by Rails), since that's the type of the column.

  2. ENUM type in MySQL is vendor-specific column type (for example, SQLite doesn't support it, but PostgreSQL does). In MySQL :

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+

For the migration, you need to do this:

class AddSexToUsers < ActiveRecord::Migration
  def change
    add_column :users, :sex, "ENUM('female', 'male') DEFAULT 'female'"
  end
end

Upvotes: 21

Related Questions