Ecto SQL v3.2.0 Ecto.Migration behaviour View Source
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.
Migrations typically provide two operations: up
and down
,
allowing us to migrate the database forward or roll it back
in case of errors.
In order to manage migrations, Ecto creates a table called
schema_migrations
in the database, which stores all migrations
that have already been executed. You can configure the name of
this table with the :migration_source
configuration option.
Ecto also locks the schema_migrations
table when running
migrations, guaranteeing two different servers cannot run the same
migration at the same time.
Creating your first migration
Migrations are defined inside the "priv/REPO/migrations" where REPO
is the last part of the repository name in underscore. For example,
migrations for MyApp.Repo
would be found in "priv/repo/migrations".
For MyApp.CustomRepo
, it would be found in "priv/custom_repo/migrations".
Each file in the migratitons directory have the following structure:
NUMBER_NAME.exs
The NUMBER is a unique number that identifies the migration. It is usually the timestamp of when the migration was created. The NAME must also be unique and it quickly identifies what the migration does. For example, if you need to track the "weather" in your system, you can start a new file at "priv/repo/migraitons/2019041714000_add_weather_table.exs" that will have the following contents:
defmodule MyRepo.Migrations.AddWeatherTable 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
The up/0
function is responsible to migrate your database forward.
the down/0
function is executed whenever you want to rollback.
The down/0
function must always do the opposite of up/0
.
Inside those functions, we invoke the API defined in this module,
you will find conveniences for managing tables, indexes, columns,
references, as well as running custom SQL commands.
To run a migration, we generally use Mix tasks. For example, you can run the migration above by going to the root of your project and typing:
$ mix ecto.migrate
You can also it rollback by calling
$ mix ecto.rollback --step 1
Note rollback requires us to say how much we want to rollback.
On the other hand, mix ecto.migrate
will always run all pending
migrations.
In practice, we don't create migration files by hand either, we
typically use mix ecto.gen.migration
to generate the file with
the proper timestamp and then we just fill in its contents:
$ mix ecto.gen.migration add_weather_table
Mix tasks
As seen above, Ecto provides many Mix tasks to help developers work with migrations. We summarize them below:
mix ecto.gen.migration
- generates a migration that the user can fill in with particular commandsmix ecto.migrate
- migrates a repositorymix ecto.migrations
- shows all migrations and their statusmix ecto.rollback
- rolls back a particular migration
Run mix help COMMAND
for more information on a particular command.
For a lower level API for running migrations, see Ecto.Migrator
.
Change
Having to write both up/0
and down/0
functions for every
migration is tedious and error prone. For this reason, Ecto allows
you to defined a change/0
callback with all of the code you want
to execute when migrating and Ecto will automatically figure out
the down/0
for you. For example, the migration above can be
written as:
defmodule MyRepo.Migrations.AddWeatherTable 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
However, note that not all commands are reversible. Trying to rollback
a non-reversible command will raise an Ecto.MigrationError
.
A notable command in this regard is execute/2
, which is reversible in
change/0
by accepting a pair of plain SQL strings. The first is run on
forward migrations (up/0
) and the second when rolling back (down/0
).
If up/0
and down/0
are implemented in a migration, they take precedence,
and change/0
isn't invoked.
Field Types
The Ecto primitive types are mapped to the appropriate database
type by the various database adapters. For example, :string
is converted to
:varchar
, :binary
to :bits
or :blob
, and so on.
Similarly, you can pass any field type supported by your database
as long as it maps to an Ecto type. For instance, you can use :text
,
:varchar
, or :char
in your migrations as add :field_name, :text
.
In your Ecto schema, they will all map to the same :string
type.
Remember, atoms can contain arbitrary characters by enclosing in
double quotes the characters following the colon. So, if you want to use a
field type with database-specific options, you can pass atoms containing
these options like :"int unsigned"
, :"time without time zone"
, etc.
Flushing
Instructions inside migrations are not executed immedidately. Instead
they are performed once the relevant up
, change
, or down
callback
terminates.
However, under certain situations, you may want to guarantee all of the
previous steps have been executed before continuing. This is generally
useful in case you need to apply some changes to the table before
continuing with the migration. This can be done with flush/0
:
def up do
...
flush()
...
end
Comments
Migrations where you create or alter a table support specifying table and column comments. The same can be done when creating constraints and indexes. Not all databases support this feature.
def up do
create index("posts", [:name], comment: "Index Comment")
create constraint("products", "price_must_be_positive", check: "price > 0", comment: "Constraint Comment")
create table("weather", prefix: "north_america", comment: "Table Comment") do
add :city, :string, size: 40, comment: "Column Comment"
timestamps()
end
end
Repo configuration
The following migration configuration options are available for a given repository:
:migration_source
- Version numbers of migrations will be saved in a table namedschema_migrations
by default. You can configure the name of the table via:config :app, App.Repo, migration_source: "my_migrations"
:migration_primary_key
- By default, Ecto uses the:id
column with type:bigserial
, but you can configure it via:config :app, App.Repo, migration_primary_key: [name: :uuid, type: :binary_id]
:migration_timestamps
- By default, Ecto uses the:naive_datetime
type, but you can configure it via:config :app, App.Repo, migration_timestamps: [type: :utc_datetime]
:migration_lock
- By default, Ecto will lock the migration table. This allows multiple nodes to attempt to run migrations at the same time but only one will succeed. You can disable themigration_lock
by setting it tonil
:config :app, App.Repo, migration_lock: nil
:migration_default_prefix
- Ecto defaults tonil
for the database prefix for migrations, but you can configure it via:config :app, App.Repo, migration_default_prefix: "my_prefix"
:start_apps_before_migration
- A list of applications to be started before running migrations. Used byEcto.Migrator.with_repo/3
and the migration tasks:config :app, App.Repo, start_apps_before_migration: [:ssl, :some_custom_logger]
Prefixes
Migrations support specifying a table prefix or index prefix which will target either a schema (if using PostgreSQL) or a different database (if using MySQL). If no prefix is provided, the default schema or database is used.
Any reference declared in the table migration refers by default to the table with the same declared prefix. The prefix is specified in the table options:
def up do
create table("weather", prefix: "north_america") do
add :city, :string, size: 40
add :temp_lo, :integer
add :temp_hi, :integer
add :prcp, :float
add :group_id, references(:groups)
timestamps()
end
create index("weather", [:city], prefix: "north_america")
end
Note: if using MySQL with a prefixed table, you must use the same prefix for the references since cross-database references are not supported.
When using a prefixed table with either MySQL or PostgreSQL, you must use the same prefix for the index field to ensure that you index the prefix-qualified table.
Transaction Callbacks
If possible, each migration runs inside a transaction. This is true for Postgres, but not true for MySQL, as the later does not support DDL transactions.
In some rare cases, you may need to execute some common behavior after beginning
a migration transaction, or before commiting that transaction. For instance, one
might desire to set a lock_timeout
for each lock in the migration transaction.
You can do so by defining after_begin/0
and before_commit/0
callbacks to
your migration.
However, if you need do so for every migration module, implemment this callback for every migration can be quite repetitive. Luckily, you can handle this by providing your migration module:
defmodule MyApp.Migration do
defmacro __using__(_) do
quote do
use Ecto.Migration
def after_begin() do
repo().query! "SET lock_timeout TO '5s'", "SET lock_timeout TO '10s'"
end
end
end
end
Then in your migrations you can use MyApp.Migration
to share this behavior
among all your migrations.
Link to this section Summary
Functions
Adds a column when creating or altering a table.
Adds a column if it not exists yet when altering a table.
Alters a table.
Defines a constraint (either a check constraint or an exclusion constraint) to be evaluated by the database when a row is inserted or updated.
Creates one of the following
Creates a table.
Creates an index or a table with only :id
field if one does not yet exist.
Creates a table if it does not exist.
Gets the migrator direction.
Drops one of the following
Drops a table or index if it exists.
Executes arbitrary SQL or a keyword command.
Executes reversible SQL commands.
Executes queue migration commands.
Generates a fragment to be used as a default value.
Modifies the type of a column when altering a table.
Gets the migrator prefix.
Defines a foreign key.
Removes a column when altering a table.
Removes a column in a reversible way when altering a table.
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.
Renames a table.
Renames a column outside of the alter
statement.
Gets the migrator repo.
Adds :inserted_at
and :updated_at
timestamp columns.
Shortcut for creating a unique index.
Callbacks
Migration code to run immediately after the transaction is opened.
Migration code to run immediately before the transaction is closed.
Link to this section Functions
add(column, type, opts \\ []) View Source
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
- whentrue
, 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 byfragment/1
.:null
- whenfalse
, 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 to255
.:precision
- the precision for a numeric type. Required when:scale
is specified.:scale
- the scale of a numeric type. Defaults to0
.
add_if_not_exists(column, type, opts \\ []) View Source
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
alter(object, list) View Source (macro)
Alters a table.
Examples
alter table("posts") do
add :summary, :text
modify :title, :text
remove :views
end
constraint(table, name, opts \\ []) View Source
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.
create(index) View Source
Creates one of the following:
- an index
- a table with only an
:id
field - 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")
create(object, list) View Source (macro)
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
create_if_not_exists(index) View Source
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")
create_if_not_exists(object, list) View Source (macro)
Creates a table if it does not exist.
Works just like create/2
but does not raise an error when the table
already exists.
direction()
View Source
direction() :: :up | :down
direction() :: :up | :down
Gets the migrator direction.
drop(index_or_table_or_constraint) View Source
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_if_exists(index_or_table) View Source
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")
execute(command) View Source
Executes arbitrary SQL or a keyword command.
Reversible commands can be defined by calling execute/2
.
Examples
execute "CREATE EXTENSION postgres_fdw"
execute create: "posts", capped: true, size: 1024
execute(up, down) View Source
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.
Examples
execute "CREATE EXTENSION postgres_fdw", "DROP EXTENSION postgres_fdw"
flush() View Source
Executes queue migration commands.
Reverses the order in which commands are executed when doing a rollback
on a change/0
function and resets the commands queue.
fragment(expr) View Source
Generates a fragment to be used as a default value.
Examples
create table("posts") do
add :inserted_at, :naive_datetime, default: fragment("now()")
end
index(table, columns, opts \\ []) View Source
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 tofalse
.: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.
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
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)
# Partial indexes are created by specifying a :where option
create index("products", [:user_id], where: "price = 0", name: :free_products_index)
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")
modify(column, type, opts \\ []) View Source
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.
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 to0
.
prefix() View Source
Gets the migrator prefix.
references(table, opts \\ []) View Source
Defines a foreign key.
Examples
create table("products") do
add :group_id, references("groups")
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 reference of the table if present, ornil
.: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
.
remove(column) View Source
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
remove(column, type, opts \\ []) View Source
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
remove_if_exists(column, type) View Source
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
rename(table_current, list) View Source
Renames a table.
Examples
rename table("posts"), to: table("new_posts")
rename(table, current_column, list) View Source
Renames a column outside of the alter
statement.
Examples
rename table("posts"), :title, to: :summary
repo()
View Source
repo() :: Ecto.Repo.t()
repo() :: Ecto.Repo.t()
Gets the migrator repo.
table(name, opts \\ []) View Source
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
- whenfalse
, 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.:options
- provide custom options that will be appended after the generated statement. For example, "WITH", "INHERITS", or "ON COMMIT" clauses.
timestamps(opts \\ []) View Source
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 tofalse
disables the column.:updated_at
- the name of the column for storing last-updated-at times. Setting it tofalse
disables the column.:type
- the type of the:inserted_at
and:updated_at
columns. Defaults to:naive_datetime
.
unique_index(table, columns, opts \\ []) View Source
Shortcut for creating a unique index.
See index/3
for more information.
Link to this section Callbacks
after_begin()
View Source
(optional)
after_begin() :: term()
after_begin() :: term()
Migration code to run immediately after the transaction is opened.
Keep in mind that it is treated like any normal migration code, and should consider both the up and down cases of the migration.
before_commit()
View Source
(optional)
before_commit() :: term()
before_commit() :: term()
Migration code to run immediately before the transaction is closed.
Keep in mind that it is treated like any normal migration code, and should consider both the up and down cases of the migration.