Ecto v0.13.0 → Overview → Ecto → Migration

Ecto.Migration

Migrations are used to modify your database schema over time.

This module provides many helpers for migrating the database, allowing developers to use Elixir to alter their storage in a way that is database independent.

Here is an example:

defmodule MyRepo.Migrations.CreatePosts do
  use Ecto.Migration

  def up do
    create table(:weather) do
      add :city,    :string, size: 40
      add :temp_lo, :integer
      add :temp_hi, :integer
      add :prcp,    :float

      timestamps
    end
  end

  def down do
    drop table(:weather)
  end
end

Note migrations have an up/0 and down/0 instructions, where up/0 is used to update your database and down/0 rolls back the prompted changes.

Ecto provides some mix tasks to help developers work with migrations:

Run the mix help COMMAND for more information.

Change

Migrations can also be automatically reversible by implementing change/0 instead of up/0 and down/0. For example, the migration above can be written as:

defmodule MyRepo.Migrations.CreatePosts do
  use Ecto.Migration

  def change do
    create table(:weather) do
      add :city,    :string, size: 40
      add :temp_lo, :integer
      add :temp_hi, :integer
      add :prcp,    :float

      timestamps
    end
  end
end

Notice not all commands are reversible though. Trying to rollback a non-reversible command will raise an Ecto.MigrationError.

Transactions

By default, Ecto runs all migrations inside a transaction. That’s not always ideal: for example, PostgreSQL allows to create/drop indexes concurrently but only outside of any transaction (see the PostgreSQL docs).

Migrations can be forced to run outside a transaction by setting the @disable_ddl_transaction module attribute to true:

defmodule MyRepo.Migrations.CreateIndexes do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create index(:posts, [:slug], concurrently: true)
  end
end

Since running migrations outside a transaction can be dangerous, consider performing very few operations in such migrations.

See the index/3 function for more information on creating/dropping indexes concurrently.

Source

Summary↑

add(column, type \\ :string, opts \\ [])

Adds a column when creating or altering a table
alter(object, list)

Alters a table
create(object)

Creates an index
create(object, list)

Creates a table
direction()

Gets the migrator direction
drop(object)

Drops a table or index
execute(command)

Executes arbitrary SQL
exists?(object)

Checks if a table or index exists
fragment(expr)

Generates a fragment to be used as default value
index(table, columns, opts \\ [])

Returns an index struct that can be used on create, drop, etc
modify(column, type, opts \\ [])

Modifies the type of column when altering a table
references(table, opts \\ [])

Defines a foreign key
remove(column)

Removes a column when altering a table
table(name, opts \\ [])

Returns a table struct that can be given on create, alter, etc
timestamps(opts \\ [])

Adds :inserted_at and :updated_at timestamps columns

Functions

add(column, type \\ :string, opts \\ [])
(function) # ↑

Adds a column when creating or altering a table.

This function also accepts Ecto primitive types as column types and they are normalized by the database adapter. For example, string is converted to varchar, datetime to the underlying datetime or timestamp type, binary to bits or blob, and so on.

However, the column type is not always the same as the type in your model. For example, a model that has a string field, can be supported by columns of types char, varchar, text and others. For this reason, this function also accepts text and other columns, which are sent as is to the underlying database.

To sum up, the column type may be either an Ecto primitive type, which is normalized in cases the database does not understand it, like string or binary, or a database type which is passed as is. Custom Ecto types, like Ecto.Datetime, are not supported because they are application level concern and may not always map to the database.

Examples

create table(:posts) do
  add :title, :string, default: "Untitled"
end

alter table(:posts) do
  add :summary, :text # Database type
  add :object,  :json
end

Options

  • :primary_key - when true, marks this field as the primary key
  • :default - the column’s default value. can be a string, number or a fragment generated by fragment/1
  • :null - when false, the column does not allow null values
  • :size - the size of the type (for example the numbers of characters). Default is no size, except for :string that defaults to 255.
  • :precision - the precision for a numeric type. Default is no precision
  • :scale - the scale of a numeric type. Default is 0 scale
Source
create(object)
(function) # ↑

Creates an index.

When reversing (in change running backward) indexes are only dropped if they exist and no errors are raised. To enforce dropping an index use drop/1.

Examples

create index(:posts, [:name])
Source
direction()
(function) # ↑

Specs:

  • direction :: :up | :down

Gets the migrator direction.

Source
drop(object)
(function) # ↑

Drops a table or index.

Examples

drop index(:posts, [:name])
drop table(:posts)
Source
execute(command)
(function) # ↑

Executes arbitrary SQL.

Examples

execute "UPDATE posts SET published_at = NULL"
Source
exists?(object)
(function) # ↑

Checks if a table or index exists.

Examples

exists? table(:products)
Source
fragment(expr)
(function) # ↑

Generates a fragment to be used as default value.

Examples

create table(:posts) do
  add :inserted_at, :datetime, default: fragment("now()")
end
Source
index(table, columns, opts \\ [])
(function) # ↑

Returns an index struct that can be used on create, drop, etc.

Expects the table name as first argument and the index fields as second. The field can be an atom, representing a column, or a string representing an expression that is sent as is to the database.

Indexes are non-unique by default.

Adding/dropping indexes concurrently

PostgreSQL supports adding/dropping indexes concurrently (see the docs). In order to take advantage of this, the :concurrently option needs to be set to true when the index is created/dropped.

Note: in order for the :concurrently option to work, the migration must not be run inside a transaction. See the Ecto.Migration docs for more information on running migrations outside of a transaction.

Index types

PostgreSQL supports several index types like B-tree, Hash or GiST. When creating an index, the index type defaults to B-tree, but it can be specified with the :using option. The :using option can be an atom or a string; its value is passed to the USING clause as is.

More information on index types can be found in the PostgreSQL docs.

Examples

# Without a name, index defaults to products_category_id_sku_index
create index(:products, [:category_id, :sku], unique: true)

# Name can be given explicitly though
drop index(:products, [:category_id, :sku], name: :my_special_name)

# Indexes can be added concurrently
create index(:products, [:category_id, :sku], concurrently: true)

# The index type can be specified
create index(:products, [:name], using: :hash)

# Create an index on custom expressions
create index(:products, ["lower(name)"], name: :products_lower_name_index)
Source
modify(column, type, opts \\ [])
(function) # ↑

Modifies the type of column when altering a table.

See add/3 for more information on supported types.

Examples

alter table(:posts) do
  modify :title, :text
end

Options

  • :size - the size of the type (for example the numbers of characters). Default is no size.
  • :precision - the precision for a numberic type. Default is no precision.
  • :scale - the scale of a numberic type. Default is 0 scale.
Source
references(table, opts \\ [])
(function) # ↑

Defines a foreign key.

Examples

create table(:products) do
  add :group_id, references(:groups)
end

Options

  • :column - The foreign key column, default is :id
  • :type - The foreign key type, default is :serial
  • :on_delete - What to perform if the entry is deleted. May be :nothing, :delete_all or :nilify_all. Defaults to :nothing.
Source
remove(column)
(function) # ↑

Removes a column when altering a table.

Examples

alter table(:posts) do
  remove :title
end
Source
table(name, opts \\ [])
(function) # ↑

Returns a table struct that can be given on create, alter, etc.

Examples

create table(:products) do
  add :name, :string
  add :price, :decimal
end

drop table(:products)

create table(:products, primary_key: false) do
  add :name, :string
  add :price, :decimal
end

Options

  • :primary_key - when false, does not generate primary key on table creation
  • :engine - customizes the table storage for supported databases. For MySQL, the default is InnoDB
  • :options - provide custom options that will be appended after generated statement, for example “WITH”, “INHERITS” or “ON COMMIT” clauses
Source
timestamps(opts \\ [])
(function) # ↑

Adds :inserted_at and :updated_at timestamps columns.

Those columns are of :datetime type and by default cannot be null. opts can be given to customize the generated fields.

Source

Macros

alter(object, list)
(macro) # ↑

Alters a table.

Examples

alter table(:posts) do
  add :summary, :text
  modify :title, :text
  remove :views
end
Source
create(object, list)
(macro) # ↑

Creates a table.

By default, the table will also include a primary_key of name :id and type :serial. Check table/2 docs for more information.

Examples

create table(:posts) do
  add :title, :string, default: "Untitled"
  add :body,  :text

  timestamps
end
Source