Homework 6: Add a Database

Up to this point, your app has been able to run but not store any information.  Each session starts fresh with no memory of what happened before. In this assignment, you will add a database to the “backend” of your web application.  This will create a structured file on the server that contains information that will be retrieved and available for any users the next time that the application is run.  

In keeping with the Ruby on Rails philosophy, Rails will do much of the work for you, accepting an Active Record command from you and creating the actual SQLlite database.  Most of your work comes in the form of planning …. What type of data do you need to keep between sessions and make available to ALL users of the app? And then, how do you access and display it best?

At a minimum, for this assignment:

  • Create a database table (using Active Record, this will be generating a model)
  • Create a view that displays the contents of that table (either a single record or the whole table or some subsection of the table)
  • You should seed the database with some dummy data (or real data!) so you have something to show
  • You will need to also change the router and the controller (which might be a new controller!) for the view 

Resources

  • Creating a Simple Model and Database is a barebones tutorial that will walk you through the steps of creating, seeding, and showing data from a single-table model.  This is a minimum, and you should read through the rest of the resources for more details and explanation of why these steps work.
  • To create your model/database: Hartl Section 6.1 and possibly Chapter 13 (although you may be creating a very different type of records to be stored in your database), or Active Record Basics 
  • To seed your database: Active Record Migrations section 8 or Seeding Database From CSV
  • To display your data on a page: Hartl Sections 7.1, and 10.3
  • Understanding the basics of Ruby on Rails with SQL Databases and how they work from freeCodeCamp.  Access online OR access the Getting Started with Rails guide
  • Undoing Things in Rails
  • If you want to look inside your database file, you will need to download something like DB Browser for SQLite. This is not required, but it can be reassuring to see the results of creating and running migrations.
  • Hartl Section 7.2 to create an input form so that you can avoid using rails console or db/seed.rb to create test records, but creating forms is the task for the next homework assignment – only try this if you have extra time
  • Kaggle and UCI have datasets that you could use to make your database more interesting (but they must fit the theme of your website and you must credit the source!)

What to Do

  • I strongly suggest that you create a new branch in git before starting this process.  Although you can undo database creation and modification commands, it is sometimes easier to reset and start over entirely.  
  • Create a plan for what information your app needs to save between sessions.  If you were writing a C or Java program, what would you save in a file between times that you ran the app?  If you cannot think of anything else, you could follow the Hartl tutorial and work toward creating users to record who logs in and give them a personalized message when they return.  Keep it simple for the first attempt.
  • Generate a controller and model, as needed.  This will store data persistently in a database on the server.  You do not need to understand SQL in order to do this, Rails will create the database for you.  (Although it can be helpful to know what is being done behind the scenes.)
  • See Hartl section 6.1 for how to get test data into your database before you have working forms.  Or use the db/seeds.rb file to populate the database with some basic records and then run rails db:seed to populate the database with those records. Using the seed file is recommended so that you can have some initial data to display on Heroku.  
  • You will need to create or modify views and controllers in order to view records and display your app’s data.  
  • You may need to add a gem to your Gemfile to get the rails console to work.  Add the gem ‘rb-readline’ to the “development and testing” group. You don’t need to specify the version of the gem.  

Important Notes

Getting your app to run with a database on Heroku will take a couple of (not obvious!!) steps:

  1. Heroku only runs your master branch.  So, test everything carefully. Run your tests.  Test the app locally. Push your (not master) branch up to GitHub and then go ahead and merge this branch into master and push the master to GitHub and Heroku.  (You can always reset your local and GitHub master branches if things go very badly.)
  2. Heroku is running in production mode on a PostgreSQL server.  So you will need to run your migration(s) on Heroku to create the database on that server.  From your local command line, you should be able to type: heroku run rails db:migrate.  This creates the database on Heroku. (You should see a long series of SQL commands. If it doesn’t tell you to do something and try again, it probably worked.) If you aren’t using local command line, you can enter the same commands on the Heroku dashboard by clicking More > Run console.
  3. Then seed that database (using a db/seed.rb file is very handy here ….) using the local command heroku run rails db:seed. (You should see a long series of SQL commands.)
  4. At this point, you should be able to run your app, access the page you created to display the data and see the data!  

What to Hand In

  1. Submit a text document in which you describe the structure of the database that you designed and added to your project.    You may use UML or a set of tables to show your ideas. Or simply write a concise description. 
  2. Your new commit should include your new model, a seeded database, and updated router and controller files
  3. Include a link to your GitHub repo (showing a new commit with “HW7” as part of the commit message).
  4. In the same document, include the link to the Heroku app.

Rubric (10 points)

  1. Describes (planned) structure of the database
  2. New model
  3. A seeded database (seed.rb or fixtures.yml) 
  4. If the seed.rb is used, make sure there is a “create” or “save” (not just a “new” method call, which doesn’t save anything to the database)
  5. Updated router if there’s a new controller or view
  6. New or updated controller
  7. Updated or new view for the data in the database
  8. On Github
  9. On Heroku
  10. “Sparkle” or some theme that makes the website a unified whole rather than just a collection of pages for homework.