Adds a column when creating or altering a table.

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

However, the column type is not always the same as the type used in your schema. For example, a schema that has a :string field can be supported by columns of type :char, :varchar, :text, and others. For this reason, this function also accepts :text and other type annotations that are native to the database. These are passed to the database as-is.

To sum up, the column type may be either an Ecto primitive type, which is normalized in cases where the database does not understand it, such as :string or :binary, or a database type which is passed as-is. Custom Ecto types like Ecto.UUID are not supported because they are application-level concerns 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,  :map  # Elixir type which is handled by the database
end

Options

• :primary_key - when true, marks this field as the primary key. If multiple fields are marked, a composite primary key will be created.
• :default - the column's default value. It can be a string, number, empty list, list of strings, list of numbers, 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 number of characters). The default is no size, except for :string, which defaults to 255.
• :precision - the precision for a numeric type. Required when :scale is specified.
• :scale - the scale of a numeric type. Defaults to 0.
• :after - positions field after the specified one. Only supported on MySQL, it is ignored by other databases.

Adds a column if it not exists yet when altering a table.

If the type value is a %Reference{}, it is used to add a constraint.

type and opts are exactly the same as in add/3.

This command is not reversible as Ecto does not know about column existence before the creation attempt.

Examples

alter table("posts") do
  add_if_not_exists :title, :string, default: ""
end

Alters a table.

Examples

alter table("posts") do
  add :summary, :text
  modify :title, :text
  remove :views
end

Defines a constraint (either a check constraint or an exclusion constraint) to be evaluated by the database when a row is inserted or updated.

Examples

create constraint("users", :price_must_be_positive, check: "price > 0")
create constraint("size_ranges", :no_overlap, exclude: ~s|gist (int4range("from", "to", '[]') WITH &&)|)
drop   constraint("products", "price_must_be_positive")

Options

• :check - A check constraint expression. Required when creating a check constraint.
• :exclude - An exclusion constraint expression. Required when creating an exclusion constraint.
• :prefix - The prefix for the table.
• :validate - Whether or not to validate the constraint on creation (true by default). Only available in PostgreSQL, and should be followed by a command to validate the new constraint in a following migration if false.

Creates one of the following:

• an index
• a table with only the :id primary key
• a constraint

When reversing (in a change/0 running backwards), 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])
create table("version")
create constraint("products", "price_must_be_positive", check: "price > 0")

Creates a table.

By default, the table will also include an :id primary key field that has a type of :bigserial. Check the table/2 docs for more information.

Examples

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

  timestamps()
end

Creates an index or a table with only :id field if one does not yet exist.

Examples

create_if_not_exists index("posts", [:name])

create_if_not_exists table("version")

Creates a table if it does not exist.

Works just like create/2 but does not raise an error when the table already exists.

Specs

direction() :: :up | :down

Gets the migrator direction.

Drops one of the following:

• an index
• a table
• a constraint

Examples

drop index("posts", [:name])
drop table("posts")
drop constraint("products", "price_must_be_positive")
drop index("posts", [:name]), mode: :cascade
drop table("posts"), mode: :cascade

Options

• :mode - when set to :cascade, automatically drop objects that depend on the index, and in turn all objects that depend on those objects on the table. Default is :restrict

Drops a table or index if it exists.

Does not raise an error if the specified table or index does not exist.

Examples

drop_if_exists index("posts", [:name])
drop_if_exists table("posts")
drop_if_exists index("posts, [:name]), mode: :cascade
drop_if_exists table("posts"), mode: :cascade

Options

• :mode - when set to :cascade, automatically drop objects that depend on the index, and in turn all objects that depend on those objects on the table. Default is :restrict

Executes arbitrary SQL, anonymous function or a keyword command.

The argument is typically a string, containing the SQL command to be executed. Keyword commands exist for non-SQL adapters and are not used in most situations.

Supplying an anonymous function does allow for arbitrary code to execute as part of the migration. This is most often used in combination with repo/0 by library authors who want to create high-level migration helpers.

Reversible commands can be defined by calling execute/2.

Examples

execute "CREATE EXTENSION postgres_fdw"

execute create: "posts", capped: true, size: 1024

execute(fn -> repo().query!("SELECT $1::integer + $2", [40, 2], [log: :info]) end)

execute(fn -> repo().update_all("posts", set: [published: true]) end)

Executes reversible SQL commands.

This is useful for database-specific functionality that does not warrant special support in Ecto, for example, creating and dropping a PostgreSQL extension. The execute/2 form avoids having to define separate up/0 and down/0 blocks that each contain an execute/1 expression.

The allowed parameters are explained in execute/1.

Examples

defmodule MyApp.MyMigration do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION postgres_fdw", "DROP EXTENSION postgres_fdw"
    execute(&execute_up/0, &execute_down/0)
  end

  defp execute_up, do: repo().query!("select 'Up query …';", [], [log: :info])
  defp execute_down, do: repo().query!("select 'Down query …';", [], [log: :info])
end

Executes queue migration commands.

Generates a fragment to be used as a default value.

Examples

create table("posts") do
  add :inserted_at, :naive_datetime, default: fragment("now()")
end

Returns an index struct that can be given to create/1, drop/1, etc.

Expects the table name as the first argument and the index field(s) as the second. The fields can be atoms, representing columns, or strings, representing expressions that are sent as-is to the database.

Options

• :name - the name of the index. Defaults to "#{table}_#{column}_index".
• :unique - indicates whether the index should be unique. Defaults to false.
• :concurrently - indicates whether the index should be created/dropped concurrently.
• :using - configures the index type.
• :prefix - specify an optional prefix for the index.
• :where - specify conditions for a partial index.
• :include - specify fields for a covering index. This is not supported by all databases. For more information on PostgreSQL support, please read the official docs.

Adding/dropping indexes concurrently

PostgreSQL supports adding/dropping indexes concurrently (see the docs). However, this feature does not work well with the transactions used by Ecto to guarantee integrity during migrations.

Therefore, to migrate indexes concurrently, you need to set both @disable_ddl_transaction and @disable_migration_lock to true:

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

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

Disabling DDL transactions removes the guarantee that all of the changes in the migration will happen at once. Disabling the migration lock removes the guarantee only a single node will run a given migration if multiple nodes are attempting to migrate at the same time.

Since running migrations outside a transaction and without locks can be dangerous, consider performing very few operations in migrations that add concurrent indexes. We recommend to run migrations with concurrent indexes in isolation and disable those features only temporarily.

Index types

When creating an index, the index type can be specified with the :using option. The :using option can be an atom or a string, and its value is passed to the generated USING clause as-is.

For example, PostgreSQL supports several index types like B-tree (the default), Hash, GIN, and GiST. More information on index types can be found in the PostgreSQL docs.

Partial indexes

Databases like PostgreSQL and MSSQL support partial indexes.

A partial index is an index built over a subset of a table. The subset is defined by a conditional expression using the :where option. The :where option can be an atom or a string; its value is passed to the generated WHERE clause as-is.

More information on partial indexes can be found in the PostgreSQL docs.

Examples

# With no name provided, the name of the below index defaults to
# products_category_id_sku_index
create index("products", [:category_id, :sku], unique: true)

# The name can also be set explicitly
create 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)

# Partial indexes are created by specifying a :where option
create index("products", [:user_id], where: "price = 0", name: :free_products_index)

# Covering indexes are created by specifying a :include option
create index("products", [:user_id], include: [:category_id])

Indexes also support custom expressions. Some databases may require the index expression to be written between parentheses:

# Create an index on a custom expression
create index("products", ["(lower(name))"], name: :products_lower_name_index)

# Create a tsvector GIN index on PostgreSQL
create index("products", ["(to_tsvector('english', name))"],
             name: :products_name_vector, using: "GIN")

Modifies the type of a column when altering a table.

This command is not reversible unless the :from option is provided. If the :from value is a %Reference{}, the adapter will try to drop the corresponding foreign key constraints before modifying the type. Note :from cannot be used to modify primary keys, as those are generally trickier to make reversible.

See add/3 for more information on supported types.

Examples

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

Options

• :null - determines whether the column accepts null values.
• :default - changes the default value of the column.
• :from - specifies the current type of the column.
• :size - specifies the size of the type (for example, the number of characters). The default is no size.
• :precision - the precision for a numeric type. Required when :scale is specified.
• :scale - the scale of a numeric type. Defaults to 0.

Gets the migrator prefix.

Defines a foreign key.

By default it assumes you are linking to the referenced table via its primary key with name :id. If you are using a non-default key setup (e.g. using uuid type keys) you must ensure you set the options, such as :name and :type, to match your target key.

Examples

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

create table("categories") do
  add :group_id, :integer
  # A composite foreign that points from categories (product_id, group_id)
  # to products (id, group_id)
  add :product_id, references("products", with: [group_id: :group_id])
end

Options

• :name - The name of the underlying reference, which defaults to "#{table}_#{column}_fkey".
• :column - The foreign key column name, which defaults to :id.
• :prefix - The prefix for the reference. Defaults to the prefix defined by the block's table/2 struct (the "products" table in the example above), or nil.
• :type - The foreign key type, which defaults to :bigserial.
• :on_delete - What to do if the referenced entry is deleted. May be :nothing (default), :delete_all, :nilify_all, or :restrict.
• :on_update - What to do if the referenced entry is updated. May be :nothing (default), :update_all, :nilify_all, or :restrict.
• :validate - Whether or not to validate the foreign key constraint on creation or not. Only available in PostgreSQL, and should be followed by a command to validate the foreign key in a following migration if false.
• :with - defines additional keys to the foreign key in order to build a composite primary key
• :match - select if the match is :simple, :partial, or :full. This is supported only by PostgreSQL at the moment.

Removes a column when altering a table.

This command is not reversible as Ecto does not know what type it should add the column back as. See remove/3 as a reversible alternative.

Examples

alter table("posts") do
  remove :title
end

Removes a column in a reversible way when altering a table.

type and opts are exactly the same as in add/3, and they are used when the command is reversed.

If the type value is a %Reference{}, it is used to remove the constraint.

Examples

alter table("posts") do
  remove :title, :string, default: ""
end

Removes a column only if the column exists when altering the constraint if the reference type is passed once it only has the constraint name on reference structure.

This command is not reversible as Ecto does not know about column existence before the removal attempt.

Examples

alter table("posts") do
  remove_if_exists :title, :string
end

Renames a table.

Examples

rename table("posts"), to: table("new_posts")

Renames a column outside of the alter statement.

Examples

rename table("posts"), :title, to: :summary

Specs

repo() :: Ecto.Repo.t()

Gets the migrator repo.

Returns a table struct that can be given to create/2, alter/2, drop/1, 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, a primary key field is not generated on table creation.
• :engine - customizes the table storage for supported databases. For MySQL, the default is InnoDB.
• :prefix - the prefix for the table. This prefix will automatically be used for all constraints and references defined for this table unless explicitly overridden in said constraints/references.
• :options - provide custom options that will be appended after the generated statement. For example, "WITH", "INHERITS", or "ON COMMIT" clauses.

Adds :inserted_at and :updated_at timestamp columns.

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

Options

• :inserted_at - the name of the column for storing insertion times. Setting it to false disables the column.
• :updated_at - the name of the column for storing last-updated-at times. Setting it to false disables the column.
• :type - the type of the :inserted_at and :updated_at columns. Defaults to :naive_datetime.

Shortcut for creating a unique index.

See index/3 for more information.