What does the `execute <<-SQL` mean in Rails?

Question

When I'm referring to active_record_migrations online in http://guides.rubyonrails.org/active_record_migrations.html

In the following snippet:

class ExampleMigration < ActiveRecord::Migration[5.0]
  def up
    create_table :distributors do |t|
      t.string :zipcode
    end

    # add a CHECK constraint
    execute <<-SQL
      ALTER TABLE distributors
        ADD CONSTRAINT zipchk
        CHECK (char_length(zipcode) = 5);
    SQL

    add_column :users, :home_page_url, :string
    rename_column :users, :email, :email_address
  end

  def down
    rename_column :users, :email_address, :email
    remove_column :users, :home_page_url

    execute <<-SQL
      ALTER TABLE distributors
        DROP CONSTRAINT zipchk
    SQL

    drop_table :distributors
  end
end

what does the following statement mean?

execute <<-SQL
  ALTER TABLE distributors
    ADD CONSTRAINT zipchk
    CHECK (char_length(zipcode) = 5);
SQL

Running this migration with rails db:migrate, I get the error:

SQLite3::SQLException: near "CONSTRAINT": syntax error:           ALTER TABLE distributors
            ADD CONSTRAINT zipchk
              CHECK (char_length(zipcode) = 5) NO INHERIT;

Refer to 3.9 Using reversible for more details.

Tub

Answer 1

It is called heredoc and has nothing to do with migrations, SQL or anything else specifically:

If you are writing a large block of text you may use a “here document” or “heredoc”:

expected_result = <<HEREDOC

This would contain specially formatted text.

That might span many lines
HEREDOC

The heredoc starts on the line following << HEREDOC and ends with the next line that starts with HEREDOC. The result includes the ending newline.

You may use any identifier with a heredoc, but all-uppercase identifiers are typically used.

You may indent the ending identifier if you place a “-” after <<:

  expected_result = <<-INDENTED_HEREDOC
This would contain specially formatted text.

That might span many lines
  INDENTED_HEREDOC

Note that the while the closing identifier may be indented, the content is always treated as if it is flush left. If you indent the content those spaces will appear in the output.

Since ActiveRecord::ConnectionAdapters::DatabaseStatements#execute takes a string as an argument, you are passing this string, just well formatted.

Stitching

Answer 2

This syntax is used to have migrations created in sql fashion rather than following rails active record way.

Benefit: It provides more control over the SQL operations, adding indexes, constraints etc.

Syntax:

execute <<-EOSQL your_sql_here EOSQL

class CreateEmployers < ActiveRecord::Migration def up execute <<-EOSQL CREATE TABLE employers ( id bigint(20) NOT NULL AUTO_INCREMENT, designation_id int(11) NOT NULL, salary int(11) DEFAULT NULL); EOSQL end def down execute <<-EOSQL DROP TABLE employers; EOSQL end end

Thielen

I'm using sqlite as the database. Is my syntax about creating CHECK constraint wrong? – Tub

heredoc has nothing to do with special migrations syntax. It also does not provide any more control over SQL operations. heredoc is used to write a large block of text you. Period. – Stitching

@Andrey Deineko How should I do to fix this error? Is the sql syntax wrong? – Tub