Eventonica - Part 6 - Postgres Database

Optional Step -1

Feel free to do this work however you want, but keep in mind that having API tests is going to make this work much easier, because your API responses should not change as a result of this work. If they do, you’ll need to modify your jQuery AJAX calls in your code.

See the API Test Activity (WIP) for more details about API testing.

Step 0

Before doing anything else, create a new folder Eventonica-Part-6 and seed it with a copy of your part 5 code. This will make it easier to have a reference point.

Overview

Previously, your data was stored in memory in Express, so your data would disappear if the application restarted. For production applications, data must be stored in a more permanent way. In this lesson, you will move your EventRecommender data to a Postgres database and connect to that database in your Express APIs.

How to Submit

In addition to the usual steps:

• Add a clear README for how volunteers can run your app, including DB setup
  • One good way to do this is to add a database dump to the project that includes your table structure and any test data you’re using so that volunteer reviewers can run your app and begin using it immediately
• Include any unit or API tests and how to run them in the README

Instructions

1. Ensure that you have Postgres installed on your machine and that you can use either PGAdmin or psql - see instructions here.

2. Create a new database named eventonica.

3. In your eventonica database, create a table named users that contains the same fields as your User class in eventRecommender.js.

   • Use the datatype serial for id to create an auto-incrementing integer id.
   • Make the id column a primary key so that every user has a unique id.
   • Try running the following SQL insert multiple times to see how the serial type works: INSERT INTO users (name) values ('jane');. Your table should have automatically filled the id field for you!

4. Create a table named events that contains the same fields as your Event class in eventRecommender.js. Create the id column like you did for the users table.

5. Install pg-promise in your project folder - this module connects your Express application to a Postgres database.

6. Copy the setup instructions for pg-promise in your index.js file. Your connection string is probably something like postgres://localhost:5432/eventonica. You should not need a username or password if you setup posgres correctly.

7. Update your EventRecomender methods (addEvent,etc) to use SQL commands.

   • Use psql or PGAdmin to test your SQL commands.
   • Add them to your JS using the package pg-promise - you can find example queries here.
   • Note that pg-promise requires you to specify how many rows, if any, a query should return. For example, db.any indicates that the query can return any number of rows, db.one indicates that the query should return a single row, and db.none indicates that the query must return nothing.

   Ex: Adding a user

   
   // in Express, e.g. index.js
   app.post('/users', (req, res) => {
     eventRecommender.addUser(res.body)
       .then(() => res.sendStatus(204));
   });
   
   
   // in EventRecommender.js
   
   addUser(data) {
     return db.one('INSERT INTO users (name) values ($1) RETURNING id, name', [data.name]);
     // note: this returns a PROMISE
   }

8. Test that your new APIs work using Postman and your webpage. Using PGAdmin or psql, check that the database contains the information you would expect.

9. Restart your Express application - your data from previous sessions should still be there! Your database is independent of your application and continues to store the data even when the application is not running.

Additional Requirements After the Basics are Working

1. Create a user_events table in your database with two columns: user_id and event_id. Use this table to store which events have been saved for each user, replacing whichever method you used before. When creating the table,

   • Add foreign keys to link user_id to the users table and event_id to the events table. Specifying ON DELETE CASCADE for each column means that deleting a user/event will also delete all linked entries in this table. This ensures that you won’t have deleted events saved for users, or events saved for deleted users. Test that your constraints work by saving events for users and deleting the user or event.
   • These columns should be unique together (i.e., you do not want to save an event for a user more than once), see unique constraints. Test what happens when you try to save the same event for a user twice.

2. (Only if you created the user_events table): Now, when displaying users and their events on the webpage, can you use SQL joins to get a list of event names that each user has saved?

Troubleshooting

If you are getting HTTP 304 back from your GET requests, it means that the contents of the JSON is identical to when the browser fetched it before. If you’re seeing this and you believe the data should be different, i.e. you’ve added or deleted data in the database, it may be a timing issue. Make sure you are waiting for the database calls to resolve their promises before sending back your Express response.

Challenge

• Add API test coverage for your endpoints using supertest
  • example test, use POST/PUT to create a new user and then GET the users to confirm that user was added and saved
• Add not-null constraints to all fields in users and events that must have a value. Test what happens when you try to insert a null value into those fields.
• Create a unique constraint on your events table using event name, category, and date fields. This will prevent users from adding the same event multiple times. Test what happens when you try to insert the same event twice.
• For either of the above constraints, decide how best to show this error to the user? How will you tell the browser code that something went wrong? Remember, HTTP Status Codes are your friend.

Reviewing Instructions

TL;DR - they are taking their in-memory backend data objects from Part 5 and using Postgres to store them!

• Main criteria is being able to perform all supported actions above
• Review assignment details above

Common Issues

• README should contain instructions on how to load the testing database schema (likely with data)
  • A big part of reviewing this is checking it out and making sure it works
  • I’ve been using a new beta GitHub CLI that can quickly checkout a PR
  • README should also mention how to run any tests
• SQL commands should be in the EventRecommender “DAO” object, not in the Express app route handlers
  • If the code is all stuffed into the handlers, send your preferred explanatory link about the concept of system layers
• If there are no unit tests or API tests, flag that as an area of possible improvement