1

Initial problem

In my Rails application I need to work with data from a legacy database, with large numbers as primary keys. Numbers that exceed the limits of both MySQL's and PostgreSQL's 4-byte INT or INTEGER data type. But that's exactly the data type that Rails' adapters translate "integer" into.

My workaround

That was not an issue in development, since SQLite has only one integer type accepting large numbers. But for staging/production (i.e. for use with anything but SQLite), I had to manually edit the schema.rb file to replace t.integer "id" with t.column "id", 'BIGINT'. Then load the schema, import the data, and it works.

The problem with the workaround

Now, everytime I run a Rails migration to make a minor change to the database schema, the entire schema.rb file is automatically re-generated, and all my id columns are just t.integer again. As before, this isn't a problem in development (SQLite), but it probably will be in production, won't it? I'd hate to have to drop the database, load the schema and reimport all data every time I need to add or rename a column – I'd much rather use migrations for this.

The workaround for me thus far has been to make a backup copy of schema.rb before migrating, and then to merge the additions made by the migration with my own ones. Does anyone have a better idea?

fanaugen
  • 1,107
  • 2
  • 10
  • 22
  • 1
    I *know* I'm not supposed to edit `schema.rb`, but I couldn't find a good alternative, that's one of the reasons I'm asking the question. – fanaugen Aug 22 '12 at 12:56
  • Similar: [Specify custom primary key in migration][1]. Do you can use TEXT instead BIGINT? [1]: http://stackoverflow.com/questions/6402189/specify-custom-primary-key-in-migration – quatermain Aug 22 '12 at 13:06

1 Answers1

1

Here is a solution to hack in bigint primary keys in mysql2 and postgresql:

In environment.rb:

    # Load the rails application
require File.expand_path('../application', __FILE__)

require 'active_record/connection_adapters/mysql2_adapter'
require 'active_record/connection_adapters/postgresql_adapter'

ActiveRecord::ConnectionAdapters::Mysql2Adapter::NATIVE_DATABASE_TYPES[:big_primary_key] = "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY".freeze
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:big_primary_key] = "bigserial primary key".freeze

# Initialize the rails application
YourAppName::Application.initialize!
tommasop
  • 18,495
  • 2
  • 40
  • 51