CODEWITHSUNDEEP
ruby-on-railsruby
Overload119
Overload119

Reputation: 5396

Rails adding artifacts to schema.rb after migration

In Rails 7:

Migration:

class AddSavedSearchAPIKeyIndex < ActiveRecord::Migration[7.0]
  def change
    execute <<~SQL
      ALTER TABLE saved_searches
      ADD INDEX ecomm_analyze_api_key ((
        CAST(configuration->>"$.ecomm_analyze_api_key" as CHAR(255)) COLLATE utf8mb4_bin
      )) USING BTREE;
    SQL
  end
end

My schema ends up looking like this:

t.index "(cast(json_unquote(json_extract(`configuration`,_utf8mb4\\'$.ecomm_analyze_api_key\\')) as char(255) charset utf8mb4) collate utf8mb4_bin)", name: "ecomm_analyze_api_key"

I can't load this schema.rb without getting an error when I use:

RAILS_ENV=test rake db:test:prepare

Error message

ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

I am able to fix this by manually editing the schema.rb but this will just break again on the next migration.

Upvotes: 1

Views: 168

Answers (1)

max
max

Reputation: 101811

The Ruby schema dumper only actually understands a limited subset of SQL that can be created by the migrations DSL and is only actually useful until your app reaches the degree of complexity where you want to use database specific features.

Anything else that the Ruby schema dumper doesn't properly understand like this index will either be mangled or just lost in translation when it tries to parse the resulting SQL from dumping the database schema.

The solution is to use SQL schema dumps instead:

module YourApp
  class Application < Rails::Application
    # Add this line:
    config.active_record.schema_format = :sql
  end
end

This will dump the file structure.sql which should serve as the source of truth for the database schema. Check this file into version control and delete schema.rb to avoid confusion.

See:

Upvotes: 2

Related Questions