YAML Migration Examples

This creates a table called users with the columns name, age and is_active. You will notice that the name column has no options passed, so it will be given a default type of string, and set as not_null.

  UP:
    create_table:
      users:
        name:
        age: int
        is_active: bool
  DOWN:
    drop_table: users

This second example will add an extra field to the users table: last_name. We want both to use a type of string, so we don't need to pass any options to each. In which case, we can simply do this:

  UP:
    add_field:
      users: last_name
  DOWN:
    drop_field:
      users: last_name

You can add multiple columns using YAML's square brackets:

  UP:
    add_field:
      users: [last_name, middle_name]
  DOWN:
    drop_field:
      users: [last_name, middle_name]

To specify the types of the fields along with extra fields, just use the same format as when you create a table:

  UP:
    add_field:
      users:
        last_name:
          type: string
          length: 10
  DOWN:
    drop_field:
      users: last_name

Or better still, this is shorter and does the same thing:

  UP:
    add_field:
      users:
        last_name: 10
  DOWN:
    drop_field:
      users: last_name

Let's rename the name field, so that it is now called first_name:

  UP:
    rename_field:
      users:
        name: first_name
  DOWN:
    rename_field:
      users:
        first_name: name

We now want to limit the first_name field to 16 characters in length, and we want to specify a default value of 'Bob'. And then let's specify the field to be not null.

  UP:
    alter_field:
      users:
        first_name: [16, Bob, notnull]
  DOWN:
    alter_field:
      users:
        first_name: string

Think we will now rename the table from users to my_users:

  UP:
    rename_table:
      users: my_users
  DOWN:
    rename_table:
      my_users: users

Let's insert our first user, by running a raw SQL query:

  UP:
    query:
      users: INSERT INTO my_users SET first_name = 'joel'
  DOWN:
    query:
      users: DELETE FROM my_users WHERE first_name = 'joel'

And finally, lets run multiple SQL statements.

  UP:
    query:
     - INSERT INTO my_users SET first_name = 'joel'
     - INSERT INTO my_users SET first_name = 'bob'
  DOWN:
    query:
     - DELETE FROM my_users WHERE first_name = 'joel'
     - DELETE FROM my_users WHERE first_name = 'bob'

Creating Multiple Tables

The following snippet will create 3 tables: items, categories and users . Note, instead of create_table we now use create_tables

UP:
  create_tables:
    items:
      title:
      suburb:
      contact:
      email:    
    categories:
      name:
      description: 
    users:
      name:
      age: int
      is_active: bool
DOWN:
  drop_table: [users, categories, items]

Use of foreign key fkey and fkeys

This make user_id a foreign key of the items table

UP:
  create_tables:
    items:
      title:
      suburb:
      contact:
      email:   
      fkey: users
    users:
      name:
      age: int
      is_active: bool
DOWN:
  drop_table: [users, categories, items]

fkeys

This make user_id and category_id foreign keys of the items table

UP:
  create_tables:
    items:
      title:
      suburb:
      contact:
      email:   
      fkeys: [users ,categories]

   categories:
     name:
    users:
      name:
      age: int
      is_active: bool
DOWN:
  drop_table: [users, categories, items]