Learning Goals

• Describe what a database is and how it relates to web applications
• Describe what SQL is
• Define key database vocabulary including “table”, “column”, “row”, “primary key”, and “schema”
• Use Postico to visualize the Database
• Describe what an ORM is and how it relates to the database
• Use built in ActiveRecord methods to CRUD resources in the Rails Console

Lesson

Discussion Questions pt. 1

• What is a database?
• Compare and contrast a database with a collection of CSV files.
• How does a DB relate to web applications?
• Define each of the following terms
  • Table
  • Column
  • Row
  • Primary Key
  • Schema
• What is SQL? How does it relate to the DB?

Postico Activity

Use Postico to do the following:

• Open your task_manager_development database
• View all tasks
• Add some new rows to your tasks table
• Change one of the attributes of a task
• Delete any new rows that you made
• Execute the following SQL queries
  • Retrieve all Tasks
  • Retrieve all Tasks with a title of “Laundry”

Then, answer the following questions:

• How many databases do you see in Postico?
• How would you describe your task_manager_development database’s schema?
• Why is there a task_manager_development database and a task_manager_test database?
• Why isn’t there any data in the task_manager_test database?

Extensions

If you complete the activity and answer the follow up questions, try to execute the following SQL queries:

• Add a new task to the Tasks table
• Retrieve all tasks sorted by title in reverse alphabetical order
• Find all Tasks where the description contains the string “shop”. (Need help? See this)
• Delete a task
• Create a new table called “People”. A Person should have a name and an age.

Discussion Questions pt. 2

• What is an ORM? How does it relate to the DB?
• Why would you want/need to use an ORM?
• What is ActiveRecord?
• What is a Model in a Rails application?
• What is the Rails Console? Why would you use the Rails console?

Rails Console Activity

1. First, open Postico and double check that you have deleted any tasks you made during the Postico Activity
2. In TaskManager, open up the Rails console by running rails c from the command line (make sure that you navigate to your TaskManager repo first)
3. Open up your Tasks table in Postico. Now run tasks = Task.all in your Rails console. Is this what you would expect? What type of object is returned from this method call? What SQL query do you see in the Rails Console after running this command? Now run Task.all.to_sql. How does this compare to the SQL command you saw in the Rails Console?
4. In the Rails Console, run Task.create(title: "Laundry", description: "Clean the clothes"). Now open up the Tasks table in Postico. Do you see your new Task? If not, try to refresh your table. What SQL query do you see in the Rails Console after running this command? What does the create method do?
5. In the Rails Console, run task = Task.new(title: "Grocery Shopping", description: "need to eat"). What id do you see stored in the new Task object? Now open up the Tasks table in Postico. Do you see your new Task? In the Rails Console, run task.save. Now what id do you see for the Task object? Do you see it stored in the Tasks table in Postico? What is the difference between new and create?
6. In the Rails Console, run task.update(description: "go buy food"). Now check your Tasks table in Postico. Did it change? Is this what you expected?
7. In the Rails Console, run task.destroy. Now check your Tasks table in Postico. Did it change? Is this what you expected?
8. In the Rails Console, run Task.find(<id>) where <id> is the id of a Task in your database. What does this return? Now run the same command with an id that is not in the database. What does this produce?

Key Takeaways:

• ActiveRecord objects are a representation of what is stored in the database
• ActiveRecord has many built in methods for querying the database
• All ActiveRecord commands execute SQL under the hood.

Checks for Understanding

• What is a database? How does a database relate to web applications? What is Postgresql?
• What is the database schema?
• What is an ORM? What is ActiveRecord? How do they relate to the database?
• What is a model in a Rails application?
• Assuming you have a Song model, what ActiveRecord methods would you use to do the following?
  • Retrieve a Song from the DB with a specific ID
  • Retrieve all Songs from the DB
  • Store a new Song in the database
  • Change the attributes of an existing Song in the DB
  • Delete a particular Song from the DB