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 it 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:
mix ecto.gen.migration add_weather_table
- generates a migration that the user can fill in with particular commandsmix ecto.migrate
- migrates a repositorymix ecto.rollback
- rolls back a particular migration
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.
Summary↑
add(column, type \\ :string, opts \\ []) | Adds a column when creating or altering a table |
alter(object, list2) | Alters a table |
create(object) | Creates an index |
create(object, list2) | Creates a table |
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 |
modify(column, type, opts \\ []) | Modifies the type of column when altering a table |
references(table, opts \\ []) | Adds 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() | Adds |
Functions
Adds a column when creating or altering a table.
In order to support database-specific types, in addition to standard
Ecto types, arbitrary atoms can be used for type names, for example,
:json
(if supported by the underlying database).
Examples
create table(:posts) do
add :title, :string, default: "Untitled"
end
alter table(:posts) do
add :summary, :text
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 byfragment/1
:null
- whenfalse
, the column does not allow null values: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
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])
Drops a table or index.
Examples
drop index(:posts, [:name])
drop table(:posts)
Executes arbitrary SQL.
Examples
execute "UPDATE posts SET published_at = NULL"
Checks if a table or index exists.
Examples
exists? table(:products)
Generates a fragment to be used as default value.
Examples
create table(:posts) do
add :inserted_at, :datetime, default: fragment("now()")
end
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)
Modifies the type of column when altering a table.
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.
Adds a foreign key.
Examples
create table(:product) do
add :category_id, references(:category)
end
Options
:column
- The foreign key column, default is:id
:type
- The foreign key type, default is:integer
Removes a column when altering a table.
Examples
alter table(:posts) do
remove :title
end
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
Adds :inserted_at
and :updated_at
timestamps columns.
Those columns are of :datetime
type and cannot be null.
Macros
Alters a table.
Examples
alter table(:posts) do
add :summary, :text
modify :title, :text
remove :views
end
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