migration snippets

How to fix "Mysql::Error: Duplicate entry '2147483647' for key 3: INSERT INTO xxx"

Tagged numeric, error, gotcha, migration, mysql  Languages ruby

2147483647 is the maximum for an integer column in MySQL, so this error probably means you've exceeded this limit somewhere in your code.

Rails automatically detects the best type for your columns, so be sure to specify the correct limit when creating the column with migrations:

# from activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb
        case limit
        when 1; 'tinyint'
        when 2; 'smallint'
        when 3; 'mediumint'
        when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
        when 5..8; 'bigint'
        else raise(ActiveRecordError, "No integer type has byte size #{limit}")
        end

This Rails migration code would create a big integer column:

t.integer :product_id, :null => false, :limit => 8

See the section on Numeric Types in the MySQL documentation for more information.

How to dump Postgres extensions and all schemas with Rails

Tagged migration, rails, wtf, lol  Languages bash, ruby

When running rake db:reset you might get one of the following informative errors:

  function public.gen_random_uuid() does not exist
  db/structure.sql:22: ERROR:  schema "public" already exists

To fix them tell Ruby to dump everything by editing config/application.rb:

module XXX
  class Application < Rails::Application
    config.active_record.dump_schemas = :all

Tested with Rails 6.

References: https://github.com/rails/rails/issues/17157#issuecomment-77400517

How to use ActiveRecord without Rails

Tagged migration, activerecord, standalone, rails, ruby  Languages ruby

This is an example of how to use ActiveRecord without Rails. Tested with Rails 6.

Short version

require 'active_record'
require 'pg' # postgresql

ActiveRecord::Base.establish_connection(
  adapter: "postgresql",
  encoding: "unicode",
  database: "matrix",
  username: "postgres",
  password: ""
)

class User < ActiveRecord::Base
end

Long version

app.rb:

APP_ENV = ENV.fetch('APP_ENV').to_sym
puts "Starting app in #{APP_ENV} mode..."

require 'active_record'
require 'sqlite3'
require 'logger'

ActiveRecord::Base.logger = Logger.new('debug.log')
DB_CONF = YAML.load(Erubis::Eruby.new(IO.read('config/database.yml')).result)

ActiveRecord::Base.schema_format = :sql
ActiveRecord::Base.logger = LOG
ActiveRecord::Base.configurations = DB_CONF
ActiveRecord::Base.establish_connection(DB_CONF.fetch(APP_ENV.to_s))


class User < ActiveRecord::Base
end

db/migrate/001_schema.rb

class Schema < ActiveRecord::Migration
  def change
    create_table :users, force: true do |t|
      t.string :name
    end
  end
end

Rakefile:

task :environment do
  RAKE_PATH = File.expand_path('.')
  RAKE_ENV  = ENV.fetch('APP_ENV', 'development')
  ENV['RAILS_ENV'] = RAKE_ENV

  Bundler.require :default, RAKE_ENV

  ActiveRecord::Tasks::DatabaseTasks.database_configuration = ActiveRecord::Base.configurations
  ActiveRecord::Tasks::DatabaseTasks.root             = RAKE_PATH
  ActiveRecord::Tasks::DatabaseTasks.env              = RAKE_ENV
  ActiveRecord::Tasks::DatabaseTasks.db_dir           = 'db'
  ActiveRecord::Tasks::DatabaseTasks.migrations_paths = ['db/migrate']
  ActiveRecord::Tasks::DatabaseTasks.seed_loader      = OpenStruct.new(load_seed: nil)
end

# Use Rails 6 migrations
load 'active_record/railties/databases.rake'

database.yml:

development:
  adapter: sqlite3
  database: db/data.sqlite3
  pool: 5
  timeout: 5000

References: https://api.rubyonrails.org/classes/ActiveRecord/Tasks/DatabaseTasks.html