1. Introduction to Databases
Introduction to working with data through databases
Migration files are small Ruby scripts that make changes to your database.
Some examples of cases where a migration is used: • To create a new table in your database and define the columns it will contain as well as their data types • To drop a table in your database (completely erase it and all of its data) • To add a column to an existing table in your database • To rename a column or table in your database • To add an index to a database column
Changing a migration file will not make a change to your database until the file is run i.e. with
Migrations aren’t used to change add or remove data in your database. They’re only used to define the schema or structure of your database. We use them to set up the tables and define the columns that exist.
A migration is basically a set of instructions written in Ruby that define what structure we want to have in our database. Each migration normally contains a version of the database structure. As the structure of our database might change over the course of the project, migrations are like a version history of our database and how its evolved (e.g. migration 1: create our first table, migration 2: add a new column to the table, migration 3: a new table is added, etc.)
To create a migration, we use rake from the command line:
rake db:create_migration NAME=a_descriptive_name_for_my_migration
For example, if I wanted to create a task management tool, I’d need a table to store all of my tasks. To begin, I’d create a migration with a nice name that tells me what I’m about to put in this particular migration (basically to make it easier to find later.) To make a migration that’ll hold the instructions to create my new table I’d do something like this:
rake db:create_migration NAME=create_tasks_table
Notice some conventions: a migration name is all lowercase separated by underscores (“_“)
Also, you should give it a pretty descriptive name. Good practices says you should keep one or two discrete activities to a migration, even if that means creating lots of migrations e.g. create_tablename_table, add_column_name_to_tablename, remove_column_names_from_tablename, etc.
Once the migration has been generated, you can edit the file created. Go to the
/db/migrate folder inside of your project directory (ActiveRecord will create this automagically for you). Migration files are named in the format:
And inside your migration you’ll find some boilerplate code like this:
class CreateTasksTable < ActiveRecord::Migration def change end end
Inside of this file, we put a series of activerecord instructions that allow us to define the changes we’d like to make to the database. These go inside the
change method, like so
class CreateTasks < ActiveRecord::Migration[5.0] def change create_table :tasks do |t| t.string :name end end end
This tells activerecord to create a table named tasks in our database and add a column named ‘name’ with a string type. What other types exist, good question! Our columns could also be
t.boolean :column_name #a boolean (true or false) t.integer :column_name #a whole number t.string :column_name #a text string up to 256 characters long t.text :column_name # a long text string t.datetime :column_name # a date time object t.decimal :column_name # a decimal number t.float :column_name # a floating point decimal number
Remember once again that just because we’ve defined a migration file, it doesn’t mean anything has been done to our database. In order for our changes to take effect, we need to run the database migration command:
rake db:migrate —— creates and adds the migration
If your migration ran successfully, you should see something like this:
== CreateTasksTable: migrating =============================================== -- create_table(:tasks) -> 0.0009s == CreateTasksTable: migrated (0.0010s) ======================================
At this point, pop open the database in something like Base. If you look at the table structure you’ll notice that it’s created an extra column named
id for you. This is the primary key for the database table and it’s used to uniquely identify all of the records. ActiveRecord knows every table will need this so it sets it up for you by default.
One other great thing active record can do is track when new data is added and modified on our tables. These ‘timestamps’ are super useful, but not included by default. Let’s change the table creation to add them.
What if you realize you made a mistake? Well thankfully activerecord handles this. You can use a rollback to undo a migration.
If you type:
Your database will be stepped back one version (one migration). You can do this over and over - if you have multiple migrations to undo.
Open the migration and add the following
class CreateTasks < ActiveRecord::Migration[5.0] def change create_table :tasks do |t| t.string :name t.timestamps end end end
Apply the changes
And voila you’ll have two additional columns
updated_at that will be automatically managed by activerecord. It’ll give you timestamps of any new changes to data.
As our project grows, we’ll add new migrations creating other tables. What happens if we want to add a new column to our table? We don’t want to have to rollback everything just to do that because we’ll loose all the data we’ve added to our database. Instead we can create a migration just for this action.
Let’s say we want to add a boolean flag to keep tabs on if a task is completed. We’ll need to ads this column in. First we’ll create a new migration
rake db:create_migration NAME=add_is_complete_to_tasks
Notice the descriptive name again! Then we’ll open it up
class AddIsCompleteToTasks < ActiveRecord::Migration[5.0] def change end end
To change a table and add a column to it, activerecord gives us a command called
add_column. It takes the following parameters:
add_column table_name, column_name, type, options
So we can do the following:
add_column :tasks, :is_complete, :boolean
Basically, we’ve said add a column on the table ‘tasks’ that’s called ‘is_complete’ and is of type ‘boolean’
We can also add an optional parameter called
default that’ll specify the default information to add to any new column
add_column :tasks, :is_complete, :boolean, default: false
Finally we apply this to the database by using a migration
Open up your database to take a look, it should be added now too!
In addition to
add_column, there’s two other common actions you might use in your migrations these are:
remove_column :table_name, :column_name drop_table :table_name
Hopefully these are self-explanatory!
If you rollback a create_table migration for a table that has had data inserted into it since you initially ran the migration, you will lose all of your data. Instead, create an entirely new migration file to make the changes you’re looking to make in this case.
The dates (the numbers) in your migration have to be unique. These identify the version of the migration and tell Sinatra/ActiveRecord what migrations have been applied to the database. You can’t and shouldn’t just copy and paste these files. Always use the command line helper with rake to generate a migration
If a migration has already been run, it will not be run again unless it has been rolled back
There are conventions on how you name your tables
This series guides you through working with databases, from setting up and installing your database, storing and retrieving data and deploying to heroku
Introduction to working with data through databases
Getting started - installing your database engine
Getting started - adding dependencies to your project
Getting started - adding activerecord rake commands
Migration files are small Ruby scripts that make changes to your database
Schemas define the structure of your database table and allow ActiveRecord to structure requests and changes for data
Use Models to map a database table onto native ruby objects.
Linking Models to Routes. Making a CRUD API
Linking Models to Models - Adding Associations
How to deploy your project to Heroku and add a database
How to check the information you're adding to the database
How to create initial data with a Seed File