Intro to Databases and ORMs
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 atask_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
- First, open Postico and double check that you have deleted any tasks you made during the Postico Activity
- 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) - Open up your
Tasks
table in Postico. Now runtasks = 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 runTask.all.to_sql
. How does this compare to the SQL command you saw in the Rails Console? - 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 thecreate
method do? - In the Rails Console, run
task = Task.new(title: "Grocery Shopping", description: "need to eat")
. Whatid
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, runtask.save
. Now whatid
do you see for the Task object? Do you see it stored in the Tasks table in Postico? What is the difference betweennew
andcreate
? - 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? - In the Rails Console, run
task.destroy
. Now check your Tasks table in Postico. Did it change? Is this what you expected? - In the Rails Console, run
Task.find(<id>)
where<id>
is theid
of a Task in your database. What does this return? Now run the same command with anid
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