Schemas And Migrations
Mix.install([
{:jason, "~> 1.4"},
{:kino, "~> 0.9", override: true},
{:youtube, github: "brooklinjazz/youtube"},
{:hidden_cell, github: "brooklinjazz/hidden_cell"},
{:ecto, "~> 3.9.5"}
])Navigation
Home Report An Issue Blog: SearchBlog: MigrationReview Questions
Upon completing this lesson, a student should be able to answer the following questions.
- How do you generate a new migration?
- How do you create or alter a table in the database?
- How do you create, alter, or remove a field from a table in the database?
- Why might you validate data in the schema vs the migration?
Overview
In Elixir, migrations and schemas are used to manage and manipulate data stored in a database.
Migrations are modules that define changes to be made to the structure of a database. A migration can create, modify, or delete tables and columns in a database, and can be used to update the structure of the database to match the latest version of the application.
Schemas are modules that define the structure of data that will be stored in a database. A schema specifies the fields that a record in the database will have, and the data types of those fields.
When using migrations and schemas together in Elixir, the schema is typically used to define the structure of the data that will be stored in the database, and the migration is used to create and modify the tables and columns in the database to match the schema.
For example, if you have a schema that defines a users table with name, email, and password_hash fields, you might create a migration that creates a users table in the database with the same fields as defined in the schema. Then, you can use the schema to insert, update, and query records in the users table.
Migrations and schemas can be used together to make it easier to manage and manipulate data in a database. By defining the structure of the data in a schema and the structure of the database in a migration, you can ensure that the data in the database is always consistent with the schema and the latest version of the application.
Ecto is a library for the Elixir programming language that is used for working with databases. Ecto.Schema is a module within Ecto that defines how data is mapped to and from a database. Ecto.Migration is a module that is used for defining and running database migrations, which are a way to change the structure of a database over time in a predictable and organized manner. Ecto.Changeset is a module that is used to apply changes to data in a database, ensuring that the changes are valid and conform to the constraints defined in the associated Ecto.Schema.
Here is an example of how these three modules might be used together:
Schema
First, you would define your Ecto.Schema, which specifies the structure of the data you want to store in the database. For example:
defmodule MyApp.User do
use Ecto.Schema
schema "users" do
field :name, :string
field :email, :string
field :age, :integer
timestamps()
end
end
This schema defines a MyApp.User module that can be used to represent a user in a database. The schema specifies that the user data will be stored in a table named users, and it defines three fields: name, email, and age. Each field has a corresponding data type, such as :string for the name and email fields, and :integer for the age field.
The timestamps/0 function is also called at the end of the schema, which will add inserted_at and updated_at fields to the users table. These fields will be automatically updated by Ecto to track the creation and modification timestamps for each user record.
With this schema in place, you can use the MyApp.User module to define and manipulate user data in your Elixir application. Ecto will automatically convert between Elixir data structures and database records, making it easy to work with user data in a consistent and efficient way.
Migration
Next, you would use Ecto.Migration to define and run a migration for the “users” table in the database. This would involve creating a new module for the migration, in which you would use Ecto.Migrate to define the changes you want to make to the database structure:
defmodule MyApp.Repo.Migrations.CreateUser do
use Ecto.Migration
def change do
create table(:users) do
add :name, :string
add :email, :string
end
end
endA database migration is a way of changing the structure or content of a database over time. This can include things like adding or removing tables, columns, or rows; changing the data types or constraints of existing columns; or updating the data in existing rows.
Database migrations are often used in software development to evolve the structure of a database as the underlying software changes and grows over time. They can help ensure that the database remains consistent and correct, even as the software and its requirements evolve.
In general, database migrations involve writing code that specifies the changes to be made to the database. This code is typically run using a tool or library designed for managing database migrations, which will automatically apply the changes to the database in a safe and orderly manner. This can help ensure that the migration process is consistent, repeatable, and reversible, which is important for maintaining the integrity of the database over time.
You can use any of the Elixir Primitive Types and they will be converted to the appropriate Field Type for your database.
To run this migration, you would use the mix ecto.migrate task provided by Ecto. This task will automatically apply the migration to the database, creating the users table and adding the name, email, and age columns to it.
It’s important to note that Ecto will track which migrations have been applied to the database, and it will only apply migrations that haven’t been run before. This means that you can safely run the mix ecto.migrate task multiple times, and it will only apply new or unapplied migrations. This can be very useful for managing the evolution of your database over time.
Migration Generator
To generate an Ecto migration in Elixir, you can use the mix ecto.gen.migration command. This command is provided by the Ecto library, and it can be used to create a new Ecto migration module, which you can use to define and apply changes to your database.
To generate an Ecto migration, you can run the following command from the root directory of your Elixir project:
mix ecto.gen.migration
Replace ` with the desired name for your migration. This name should be a descriptive and unique identifier for the migration, such asadduserstableorupdate_product_prices. When you run this command, Ecto will generate a new migration module and place it in thepriv/repo/migrations/directory of your project. The generated module will contain some basic skeleton code for defining and applying changes to your database. You can then edit this module to add the specific changes that you want to make. Once your migration is ready, you can apply it to your database using themix ecto.migrateMix task provided by Ecto. This task will automatically apply any unapplied migrations to your database, ensuring that it stays up-to-date and consistent with your application code. ## Schema Generatormix phx.gen.schemais a Mix task provided by Phoenix. It is used to generate a new schema file for a given table in the database. This can save time and reduce the potential for errors when creating a schema, as it allows you to quickly generate the basic structure of the schema based on the structure of the existing database table. To usemix phx.gen.schema, you need to provide the name of the schema you want to generate, as well as the name of the database table that the schema will be based on. For example, if you have a table calledusersin your database and you want to generate a schema calledUser, you can use the following command: ``` mix phx.gen.schema User users ``` This will generate a new schema file calleduser.exin thelib/my_app/directory (assuming the default directory structure for an Elixir project). The file will contain the basic structure of the schema, with fields for each column in the users table. You can then customize the schema as needed, adding additional fields or modifying the types of the existing fields. You can also include fields in the initial creation of a schema. ``` mix ecto.gen.schema User users name:string email:string age:integer ``` This would generate the following schema. ```elixir defmodule MyApp.User do use Ecto.Schema import Ecto.Changeset schema "users" do field :age, :integer field :email, :string field :name, :string timestamps() end @doc false def changeset(user, attrs) do user |> cast(attrs, [:name, :email, :age]) |> validate_required([:name, :email, :age]) end end ``` And the following migration. ```elixir defmodule MyApp.Repo.Migrations.CreateUsers do use Ecto.Migration def change do create table(:users) do add :name, :string add :email, :string add :age, :integer timestamps() end end end ``` ## Manipulating Tables Ecto provides functions for manipulating tables in a database. Here are a few common functions: * [create/2](https://hexdocs.pm/ecto_sql/Ecto.Migration.html#create/2): creates a new table in the database * [alter/2](https://hexdocs.pm/ecto_sql/Ecto.Migration.html#alter/2): modifies an existing table in the database * [rename/2](https://hexdocs.pm/ecto_sql/Ecto.Migration.html#rename/2): renames an existing table in the database * [drop/2](https://hexdocs.pm/ecto_sql/Ecto.Migration.html#drop/2): removes an existing table from the database ## Manipulating Fields To alter a table in an Elixir Ecto migration, you can use thealter/2function provided by Ecto. This function allows you to make changes to an existing table in the database, such as adding or removing columns, changing data types, or modifying constraints. [Ecto.Migration](https://hexdocs.pm/ecto_sql/Ecto.Migration.html) provides functions for manipulating fields in a table. Here are a few to get you started. * [add/3](https://hexdocs.pm/ecto_sql/Ecto.Migration.html#add/3) adds a new field to the table. * [modify/2](https://hexdocs.pm/ecto_sql/Ecto.Migration.html#modify/3) modifies an existing field in the table. * [remove/2](https://hexdocs.pm/ecto/Ecto.Migration.html#remove/1) removes an existing field from the table. Here is an example migration that uses some of these Ecto functions to manipulate fields on a table: ```elixir defmodule MyApp.Repo.Migrations.AlterUsersTable do use Ecto.Migration def change do alter table(:users) do add :phone, :string modify :email, :string, unique: true modify :name, :string, null: false remove :age end end end ``` In this example, thealter/2function is used to make several changes to the users table in the database. Theadd/2function is called to add a new phone column to the table, with a data type of :string. Themodify/3function is then used to update the email and name columns, adding a unique constraint to the email column and making the name column non-nullable. Finally, theremove/1function is called to remove the age column from the table. Once this migration is run using themix ecto.migrateMix task, the changes will be applied to the users table in the database, resulting in a new phone column, a modified email column, a modified name column, and a removed age column. It's important to note that Ecto will only apply unapplied migrations, so you can safely run the ecto.migrate task multiple times without worrying about making the same changes to the database more than once. This can be very useful for managing the evolution of your database over time. ## Ensure Schemas Reflect Migrations Elixir schemas and migrations work together to define and manage the structure and content of a database. Schemas are used to define the shape and structure of data in a database, while migrations are used to apply changes to the database over time. When we alter our database table with a migration, we also have to modify the schema to reflect those changes. For example, if we want to modify our user table to add aphonefield, remove theagefield, modify theemailfield to be unique, and modify thenamefield to not be null, our schema should also reflect these changes otherwise we'll encounter issues when reading or writing to the database. ```elixir defmodule MyApp.User do use Ecto.Schema schema "users" do field :name, :string field :email, :string # add the phone field field :phone, :string # remove the age field # field :age, :integer timestamps() end # Modify changeset to reflect database constraints. def changeset(user, params \\ %{}) do user |> cast(params, [:name, :email, :phone]) |> validate_required([:name]) |> validate_format(:email, ~r/@/) |> unique_constraint(:email) end end ``` If our schema usesEcto.Changesetto validate data in achangeset/2function, we may also want to modify any validations to reflect the database constraints. ## Database Constraints Ecto provides the ability to add constraints to your database tables using migrations. Constraints are rules that specify the allowed values for fields in your table, such as the minimum and maximum values for numeric fields, or the maximum length for string fields. To add constraints to your table using Ecto, you can use the [constraint/3](https://hexdocs.pm/ecto_sql/Ecto.Migration.html#constraint/3) function in your migration. This function takes the name of the constraint as its first argument, followed by the options for the constraint. Here is an example migration that adds some constraints to auserstable: ```elixir defmodule MyApp.Repo.Migrations.AddConstraints do use Ecto.Migration def change do alter table(:users) do constraint(:users_email_unique, unique: true) constraint(:users_age_non_negative, check: "age >= 0") constraint(:users_name_length, check: "char_length(name) <= 100") end end end ``` This migration adds three constraints to theuserstable. The first constraint, calledusers_email_unique, ensures that theemailfield in the table has unique values. The second constraint, calledusers_age_non_negative, ensures that theagefield in the table is non-negative. The third constraint, calledusers_name_length, ensures that thenamefield in the table has a maximum length of 100 characters. In Ecto, it is generally recommended to enforce constraints in both the migration and the schema. This provides two layers of protection against invalid data being inserted into your database. The migration is responsible for defining the structure of the database, including the fields and constraints for each table. By adding constraints to the migration, you can ensure that the structure of the database is correct and that the fields in each table have the correct data types and constraints. The schema, on the other hand, is responsible for defining the structure of the data that will be stored in the database. By adding constraints to the schema, you can ensure that the data being inserted into the database conforms to the constraints you have defined. This can prevent invalid data from being inserted into the database, and can help ensure the integrity of your data. Therefore, it is generally recommended to enforce constraints in both the migration and the schema. This provides a robust and reliable way to ensure that the data in your database is valid and consistent. ## Rollbacks In an Elixir Ecto migration, theupanddownfunctions are used to define the changes that will be made to the database when the migration is run. Theupfunction contains the code that will be executed to apply the changes, and the down function contains the code that will be executed to undo those changes. Here's an example of how you might use theupanddownfunctions in a migration: ```elixir defmodule MyApp.Repo.Migrations.CreateUsersTable do use Ecto.Migration def up do create table(:users) do add :name, :string add :email, :string add :password_hash, :string timestamps() end end def down do drop table(:users) end end ``` In this example, theupfunction uses thecreateEcto function to create a newuserstable in the database. Thedownfunction uses thedropEcto function to delete the users table from the database. When this migration is run using themix ecto.migratecommand, theupfunction will be executed and theuserstable will be created in the database. If you ever need to undo the changes made by this migration, you can run themix ecto.rollbackcommand, which will run thedownfunction and delete theuserstable from the database. In general, theupanddownfunctions should be used together to define a set of changes that can be applied to the database and then undone if necessary. This allows you to easily roll back changes if you encounter any problems or need to make changes to your database schema. ## Ecto Commands In Elixir, themix ectocommands are used to manage an Ecto project. Here is a list of all the mix ecto. commands and a brief description of what each one does: *mix ecto.create: This command creates a new database for an Ecto project. *mix ecto.drop: This command drops the database for an Ecto project. *mix ecto.gen.migration: This command generates a new migration file for an Ecto project. *mix ecto.gen.schema: This command generates a new schema file for an Ecto project. *mix ecto.migrate: This command runs all pending migrations for an Ecto project. *mix ecto.rollback: This command rolls back the latest migration for an Ecto project. *mix ecto.reset: This command rolls back all migrations and then runs them again for an Ecto project. *mix ecto.setup: This command sets up a new Ecto project by creating the database and running any pending migrations. *mix ecto.version: This command prints the current version of Ecto that is installed in the project. These commands can be used to perform various tasks related to managing an Ecto project, such as creating and dropping the database, generating and running migrations, and resetting the database schema. ## Your Turn ### Generate Todo List Application We're going to create a todo-list application to learn more about Ecto schemas and migrations. Generate a phoenix project. ``` mix phx.new todo_list ``` Create the Postgres database. ``` mix ecto.create ``` Ensure the project compiles and all tests pass. ``` mix test ``` We can generate both the migration and the schema with a single command. Note that we can create these files manually, generators are purely for convenience. ``` mix phx.gen.schema TodoItems.TodoItem todo_items title:string ``` This command generated the create todo items migration inpriv/repo/migrations. ```elixir defmodule TodoList.Repo.Migrations.CreateTodoItems do use Ecto.Migration def change do create table(:todo_items) do add :title, :string timestamps() end end end ``` The command also generated theTodoItemschema inlib/todo_list/todo_items/```elixir defmodule TodoList.TodoItems.TodoItem do use Ecto.Schema import Ecto.Changeset schema "todo_items" do field :title, :string timestamps() end @doc false def changeset(todo_item, attrs) do todo_item |> cast(attrs, [:title]) |> validate_required([:title]) end end ``` ### Run Migrations And Create A TodoItem Record Run migrations to add thetodo_itemstable to your database. ``` mix ecto.migrate ``` Open the IEx shell so we can ensure our schema and migration work as expected. ```elixir $ iex -S mix iex> alias TodoList.TodoItems.TodoItem iex> changeset = TodoItem.changeset(%TodoItem{}, %{title: "finish schemas and migrations reading"}) iex> TodoList.Repo.insert(changeset) iex> TodoList.Repo.all(TodoItem) ``` This Elixir code performs the following actions: 1. It uses the alias keyword to create an alias for the TodoList.TodoItems.TodoItem module. This allows the code to refer to the module using the shorter TodoItem alias. 2. It uses theTodoItem.changeset/2function to create a changeset for a newTodoItemrecord. The changeset is created by passing the emptyTodoItemstruct%TodoItem{}and a map of field values%{title: “finish schemas and migrations reading”}to the changeset/2 function. 3. It uses theTodoList.Repo.insert/1function to insert the newTodoItemrecord into the database. This function is called by passing the changeset created in the previous step as an argument. 4. It uses theTodoList.Repo.all/1function to fetch allTodoItemrecords from the database. This function is called by passing theTodoItemmodule as an argument. In summary, this code creates a newTodoItemrecord in the database and then fetches all TodoItem records from the database. You should see aTodoItem` record similar to the following:
```elixir
[debug] QUERY OK source=”todo_items” db=0.3ms queue=0.5ms idle=1322.0ms
SELECT t0.”id”, t0.”title”, t0.”inserted_at”, t0.”updated_at” FROM “todo_items” AS t0 []
↳ :erl_eval.do_apply/7, at: erl_eval.erl:744
[
%TodoList.TodoItems.TodoItem{
__meta: #Ecto.Schema.Metadata<:loaded, “todo_items”>,
id: 1,
inserted_at: ~N[2022-12-13 05:56:42],
title: “finish schemas and migrations reading”,
updated_at: ~N[2022-12-13 05:56:42]
}
]
### Add A `completed` Field Now that we have a working `TodoItem` schema and migration, let's add a `:completed` boolean field. First, we'll create a new migration. Once again, we can create this file manually, but it's more convenient to use a generator.
mix ecto.gen.migration add_completed_to_todo_items
In the generated `priv/repo/migrations/TIMESTAMP_add_completed_to_todo_items` migration, alter the `todo_items` table to include a `completed` boolean field. We're going to make `completed` a required field so it cannot be `null`.elixir
defmodule TodoList.Repo.Migrations.AddCompletedToTodoItems do
use Ecto.Migration
def change do
alter table(:todo_items) do
add :completed, :boolean, null: false
end
end
end
Run migrations. You'll notice an error because the existing database data has TodoItem records with no completed field.
$ mix ecto.migrate
19:29:06.032 [info] == Running 20221211032143 TodoList.Repo.Migrations.AddCompletedToTodoItems.change/0 forward
19:29:06.035 [info] alter table todo_items
* (Postgrex.Error) ERROR 23502 (not_null_violation) column “completed” contains null values
There are multiple ways to resolve this issue: 1. In database where we don't need to preserve existing data (such as our local development environment), we could simply drop the database with `mix ecto.drop` then re-create the database with `mix ecto.create` and migrate the database with `mix ecto.migrate`. To accomplish all of the above we could simply run `mix ecto.reset`. This is far simpler, and perfectly fine in the early-stages of a project before deployment. 2. In a database where we do need to preserve existing data, we can add the `completed` field, populate the `completed` field in the database, then add the `null: false` constraint to the `completed` field. We're going to pretend we need to preserve existing data for the sake of learning the process. Modify our existing migration to the following:elixir
defmodule TodoList.Repo.Migrations.AddCompletedToTodoItems do
use Ecto.Migration
import Ecto.Query
def change do
alter table(:todo_items) do
add :completed, :boolean
end
# Immediately alter the todo_items table to ensure the completed field exists.
flush()
from(“todo_items”, update: [set: [completed: false]])
|> TodoList.Repo.update_all([])
alter table(:todo_items) do
modify :completed, :boolean, null: false
end
end
end
The code above defines an Ecto migration that adds a `completed` field to the `todo_items` table in a database. The migration starts by using the `alter` function to add a new `completed` field to the todo_items table. The `completed` field has a data type of `boolean`, which means it can store either true or false values. Next, the `flush/1` function is called to immediately execute the changes and add the `completed` field to the `todo_items` table. Then, the `from/2` function is used to update all records in the `todo_items` table. The `update` option is used to specify that the `set` function should be used to set the `completed` field to `false` for all records in the table. Finally, the `alter` function is used again to modify the `completed` field. The `modify` function is used to set the `null` option to `false`, which means that the `completed` field cannot be empty. This ensures that all records in the todo_items table have a value for the `completed` field. In summary, this code adds a new `completed` field to the `todo_items` table, sets the `completed` field to false for all existing records in the table, and ensures that the `completed` field cannot be empty. Now we should be able to migrate the database without any issue.
mix ecto.migrate
Since we've added a new `completed` field with a migration, we also have to update the corresponding schema.elixir
defmodule TodoList.TodoItems.TodoItem do
use Ecto.Schema
import Ecto.Changeset
schema “todo_items” do
field :title, :string
field :completed, :boolean
timestamps()
end
@doc false
def changeset(todo_item, attrs) do
todo_item
|> cast(attrs, [:title, :completed])
|> validate_required([:title, :completed])
end
end
Test your solution in the IEx shell.elixir
$ iex -S mix
iex> alias TodoList.TodoItems.TodoItem
iex> changeset = TodoItem.changeset(%TodoItem{}, %{title: “finish schemas and migrations reading”, completed: true})
iex> TodoList.Repo.insert(changeset)
iex> TodoList.Repo.all(TodoItem)
```
## Further Reading
Consider the following resource(s) to deepen your understanding of the topic.
Elixir Schools: Ecto Basics
Programming Ecto by Darin Wilson and Eric Meadows-Jönsson
HexDocs: Ecto.Migration
* HexDocs: Ecto.Schema
## Commit Your Progress
DockYard Academy now recommends you use the latest Release rather than forking or cloning our repository.
Run git status to ensure there are no undesirable changes.
Then run the following in your command line from the curriculum folder to commit your progress.
$ git add . $ git commit -m "finish Schemas And Migrations reading" $ git push
We’re proud to offer our open-source curriculum free of charge for anyone to learn from at their own pace.
We also offer a paid course where you can learn from an instructor alongside a cohort of your peers.
We will accept applications for the June-August 2023 cohort soon.
## Navigation
Home
https://github.com/DockYard-Academy/curriculum/issues/new?assignees=&labels=&template=issue.md&title=Schemas And Migrations”>Report An Issue
Blog: Search
Blog: Migration