# Call migration script

## Call migration script

Clear offers a migration system. Migration allow you to handle state update of your database.

Migration is a list of change going through a direction, up (commit changes) or down (rollback changes).

In clear, a migration is defined like this:

```ruby
class MyMigration1
  include Clear::Migration

  def change(direction)
    direction.up do
      #do something on commit
    end

    direction.down do
      #do something on rollback
    end
  end
end
```

## Executing custom SQL

The basic usage is to execute your own SQL (e.g. CREATE TABLE, ALTER, GRANT etc...).

To do it, just call `execute` into your direction block.

### Example

```ruby
class MyMigration1
  include Clear::Migration

  def change(direction)
    direction.up do
      execute("CREATE TABLE my_models...")
    end

    direction.down do
      execute("DROP TABLE my_models")
    end
  end
end
```

## Built-in helpers

Clear offers helpers for simplify declaring your migration

### Creating a table

Clear provides DSL looking like ActiveRecord for creating a table.

```ruby
create_table(:users) do |t|
    t.column :first_name, :string, index: true
    t.column :last_name, :string, unique: true

    # Will create a "user_info_id" field of type longint with a foreign key constraint
    # This reference can be null, and if the user_info is deleted then the user is deleted too.
    t.references to: "user_infos", name: "user_info_id", on_delete: "cascade", null: true

    # Example of creating index on full name
    t.index "lower(first_name || ' ' || last_name)", using: :btree

    t.timestamps
end
```

## Migration ordering

Migration should be ordered by a number. This number can be written in different way:

* In case of mixing multiple classes into the same file, you can append the number at the end of the class name:

```ruby
class Migration1
  include Clear::Migration

  def change(dir)
    #...
  end
end
```

If you're using one file per migration, you can prepend the ordering number at the start of the file name:

```
1234_migration.cr
```

Finally, if you feel more rock'n'roll and build a complex dynamic migration system on top of Clear, you can override the uid method:

```ruby
class Migration1
  include Clear::Migration

  def uid
    123_i64 #Number must be a signed 64bits integer !
  end

  def change(dir)
    #...
  end
end
```

## Calling your migration

Clear will offers soon a CLI; meanwhile, you can call migration update using methods in the Migration Manager:

```ruby
# Activate all the migrations. Will call change with up direction for each down migrations
Clear::Migration::Manager.instance.apply_all
```

```ruby
# Go to a specific migration. All migration with a number above than the version number will be downed if not yet down.
# All migrations with a version number below will be activated if not yet up.
Clear::Migration::Manager.instance.apply_to(version_number)
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://clear.gitbook.io/project/migrations/call-migration-script.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.