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 makes 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]