Over a million developers have joined DZone.

Using 64 Bit Primary Keys With Migrations

I've got an application which may need more IDs than the standard 32 bit ones typically provide.  MySQL has a BIGINT variable type, but it is a little tricky to convince Rails and Migrations to use it.  The instructions are for MySQL, but should be easily transferable to other DBs.

1.  Monkeypatch ActiveRecord::ConnectionAdapters::MysqlAdapter#native_database_types by appending it to config/environment.rb and add two new types.  I've named them int64 and int64_pk

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
      :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
      :int64_pk    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
      :int64       => { :name => "bigint" },
      :string      => { :name => "varchar", :limit => 255 },
      :text        => { :name => "text" },
      :integer     => { :name => "int", :limit => 11 },
      :float       => { :name => "float" },
      :decimal     => { :name => "decimal" },
      :datetime    => { :name => "datetime" },
      :timestamp   => { :name => "datetime" },
      :time        => { :name => "time" },
      :date        => { :name => "date" },
      :binary      => { :name => "blob" },
      :boolean     => { :name => "tinyint", :limit => 1 },

2.  In the table creation migration, create the table WITHOUT a primary key column, and then add the column manually, referencing int64_pk above:

create_table :slices, :id => false do |t|
       t.column :cheese_id, :integer
       t.column :plant, :string
       t.column :date, :datetime
    add_column :events, :id, :int64_pk

3.  In tables referencing this one, mark the foreign_id fields as int64:

create_table :fondues do |t|
      t.column :cheese_id, :int64
      t.column :party_id, :integer
      t.column :kirsch_id, :integer

4.  It turns out that the c-based mysql bindings do NOT do let you set primary keys via the sequence because it coerces the
insert_id into 32 bits at mysql.c:532.  There are two way to fix this:

a.  Use the pure-ruby gem (ie, remove the c-gem)

b.  Patch and rebuild the c-gem by changing line 352 (in version 2.7):

   return INT2NUM(mysql_insert_id(GetHandler(obj)));

Extending this to use LL2NUM solves the issue:

   return LL2NUM(mysql_insert_id(GetHandler(obj)));

5.  One last thing, in environment.rb:

  # Use SQL instead of Active Record's schema dumper when creating the test database.
  # This is necessary if your schema can't be completely dumped by the schema dumper,
  # like if you have constraints or database-specific column types
  config.active_record.schema_format = :sql

We need to deal with sql, rather than ruby, schemas.


Note that I've decided that ONLY the cheese table needs 64bit IDs -- other references are still integers.  If you wanted ALL primary keys to be 64 bits, you could have the monkeypatch look like:

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
      :primary_key    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",


Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}