Skip to main content

Lab 5: Social Media Platform Database

Due Date

Friday, October 10, 11:59pm EST

Learning Goals​

The learning goals are:

  • Server-Side Node.js and JavaScript: Develop more familiarity with server-side JavaScript using Node.js and how to handle HTTP requests and responses.
  • Databases and SQLite: Gain experience using SQLite to create and manage a relational database, including designing tables, inserting data, and querying data.

1. In-Lab Walkthrough​

For the in-lab walkthrough this week, we'll continue using the GitHub repository we were using in class.

1.1 Set up your virtual environment.​

Create a python virtual environment:

$ git clone git@github.swarthmore.edu:cs77-f25/inclass-wk6.git
$ cd inclass-wk6 
$ pwd 
$ /home/username/scratch/inclass-wk6
$ virtualenv venv

Activate your virtual environment. You’ll need to do this every time you want to access your node project.

$ source venv/bin/activate

To deactivate it:

(venv) $ deactivate
$

Install nodeenv and create a node environment inside your python virtual environment:

$ pip install nodeenv
$ nodeenv -p

1.2 Install node.js modules​

$ npm install

1.3 Run your node server with nodemon​

$ npm run dev

1.4 Try It Out​

Currently, when you visit /posts/create, we see two text entry areas and a submit button. They don't work. Fix this page so that it actually creates a new post in our database.

When a user fills out the title and content fields and clicks the Click me to send data button, the postCreate.js code for this page should use fetch() to send this data through a POST request to the server (to /posts/create). The server should create a new post in the database using the data sent from the client. After the post is created, the server should redirect the user to the page for that post (/post/id, where id is a number).

2. Lab Assignment​

You'll have to copy over your files from lab4 into a new directory for lab5. The easiest way to do this is to (1) clone the lab 5 repository, (2) copy over your files from lab4 into the lab5 directory, and (3) commit and push your changes to the lab5 repository.

2.1 Requirements​

  1. Create a new file in the models directory called dbconnection.js. This file should contain the code to create your SQLite database using the sqlite3 module and connect to it.
  • In dbconnection.js, create a table called "Posts" that has an id field (column) as well as additional fields that you would like to display on your website. There must be at minimum 3 additional fields.
  1. Update your server.js file to import the DB object created in dbconnection.js and use it to insert or query your database.
    • Whenever a user loads the homepage (/ or /home), the server should query Reddit using the fetch() API (or access a local JSON file) and store the posts in your database using the INSERT SQL statement.
    • Query the database using the SELECT SQL statement to retrieve all the posts and render them on the homepage using EJS.
  2. Create a separate route (/home/:keyword) that queries that database to only display posts that contain the keyword in the title. For example, if the user navigates to /home/javascript, only posts with "javascript" in the title should be displayed. If no posts match the keyword, display a message indicating that no posts were found.
  3. Create a README.md file in the root directory of your project that contains a Google Drive link to a screen recording of you demonstrating your project. The recording should show: (1) the database being created, (2) posts being inserted into the database, (3) the homepage displaying posts from the database, and (4) querying the database for a specific term. You can take a video on your phone, just make sure you zoom in enough so that the text is readable.

New files to be included:

  • models/
    • dbconnection.js
  • README.md: contains a Google Drive link for your screen recording.
  • .gitignore: 
    # Dependencies
    /node_modules

    # Production
    /build
    /venv

    # Generated files
    .docusaurus
    .cache-loader

    # Misc
    .DS_Store
    .env.local
    .env.development.local
    .env.test.local
    .env.production.local

    npm-debug.log*
    yarn-debug.log*
    yarn-error.log*

3. Resources​