How to change the DB with a Phoenix Migration

We have a Phoenix web application up and running... but it doesn't do very much, does it?

Before our Phoenix app can do something, we need to think about what it should actually do :)

Our app is super simple.

It's a contacts list.

It holds a person's first name, last name, and their birth date.

Yes, quite the simple app.

But it allows us to cover a bunch of interesting things, all the same.

Making Use of the Database

Seemingly everything I do involves a database, in some capacity.

This app will be no different.

We want to store our contacts in a database.

In order to do this we need to set up a database table that stores all the interesting data about a contact.

Phoenix promotes a good practice when working with a database: migrations.

A migration is a file that typically contains an up and down function.

Every time we make a change to the database we create a new migration file that describes the changes we want to make to the database schema, as it stands currently. This way, each migration file builds on the previous. When run sequentially, a freshly git cloned project can easily and reliably reproduce our exact current schema, and we as developers can describe exactly how it got to be that way.

Likewise, rolling back is equally granular.

For each move 'forward' / up, we also need to say how to roll back / down.

From the Ecto.Migration docs:

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

Generating A Migration File

Fortunately we don't need to remember (or create a template for) how a migration file is structured.

We can run a provided command to generate a migration file for us:

mix ecto.gen.migration {some_description_of_change}

In our case:

mix ecto.gen.migration add_contacts_table

For this example, let's imagine we don't have any of the Elixir / Phoenix dependencies installed locally. Let's imagine all we have is our Docker container.

In this scenario the needed command likely wouldn't work directly.

Instead, we would run the command through Docker.

You can either run as such:

docker-compose run web mix ecto.gen.migration add_contacts_table

Or my preference is to jump onto the web box and run commands directly:

docker-compose exec web /bin/bash

mix ecto.gen.migration add_contact_table

* creating priv/repo/migrations
* creating priv/repo/migrations/20171201115008_add_contacts_table.exs

A migrations directory is created, and a timestamped migration file is created for us.

Strangely, the generated file does not use the concept of up and down, but rather just a single function is suggested: change.

This is a nice example of Ecto helping us out.

By making use of the change function, Ecto can automatically figure out how to roll this change forwards or back.

We only need to add the happy path.

# priv/repo/migrations/20171201115008_add_contacts_table.exs

defmodule HelloPhoenix.Repo.Migrations.AddContactsTable do
  use Ecto.Migration

  def change do
    create table("contacts") do
      add :first,      :string
      add :last,       :string
      add :birth_date, :utc_datetime

      timestamps()
    end
  end
end

For the adventurous, consider changing some of the field types, or adding extra fields of a different type.

The inclusion of the timestamps function will add in an automated created_at, and updated_at facility to our record. Not super essential on a 'contacts' app, but nice to see.

We hit on an issue of working with Docker at this point: Permissions.

The generated migration file, depending on your Docker host OS, will potentially have been created by the root user. As such, you won't be able to write your new migration change functionality.

# from your local PC

sudo chown $(whoami):$(whoami) priv/repo/migrations/20171201115008_add_contacts_table.exs

This should see you through.

This is an unfortunate side effect of working through Docker. You could rely on locally installed tools for this purpose, but this seems to conflict with Dockerising your apps.

If you know of a better, generic solution to this problem then please let me know in the comments section below.

Migrating, Forwards and Back

Migrating forwards, or back is super easy:

root@d46c2e54b6fd:/app# mix ecto.migrate

[info] == Running HelloPhoenix.Repo.Migrations.AddContactsTable.change/0 forward
[info] create table contacts
[info] == Migrated in 0.0s

At this point you should be able to see your new table in your database.

We're working with Postgres. My personal preference for a database GUI is Jetbrain's DataGrip. I like this tool as you can use it for MySQL and Postgres, and a bunch of others, all with the same UI.

The setting's you need to connect to your db are:

• host: 0.0.0.0
• port: 5432
• database: dev_db
• user: 'postgres'
• password: 'postgres'

Of course, this assumes you are following along with this tutorial. If you have changed these values, then of course, update appropriately.

After connecting you should see two tables:

• contact
• schema_migrations

The schema_migrations table holds a record of every applied migration. You shouldn't need to interact with it manually. At this point we only have one entry, and you'll note it corresponds to the timestamp given to the migration file it generated for us (20171201115008).

You could roll this back now for test purposes:

mix ecto.rollback

[info] == Running HelloPhoenix.Repo.Migrations.AddContactsTable.change/0 backward
[info] drop table contacts
[info] == Migrated in 0.0s

Refreshing the database in DataGrip shows the contact table is no more, and the migration table now contains no entries.

We need to have a table to work with, so if you do roll back then remember to migrate forwards afterwards.

There are a few other commands worth knowing about:

mix ecto

Ecto v2.2.6
A database wrapper and language integrated query for Elixir.

Available tasks:

mix ecto.create        # Creates the repository storage
mix ecto.drop          # Drops the repository storage
mix ecto.dump          # Dumps the repository database structure
mix ecto.gen.migration # Generates a new migration for the repo
mix ecto.gen.repo      # Generates a new repository
mix ecto.load          # Loads previously dumped database structure
mix ecto.migrate       # Runs the repository migrations
mix ecto.migrations    # Displays the repository migration status
mix ecto.rollback      # Rolls back the repository migrations

An example would be:

mix ecto.migrations

Repo: HelloPhoenix.Repo

  Status    Migration ID    Migration Name
--------------------------------------------------
  up        20171201115008  add_contacts_table

In the next video we will start working with this new table.