Assignment 7 - Building a PostgreSQL-Integrated API

In this assignment, you’ll be creating a user database in PostgreSQL, developing a basic API with Node and Express to interact with this database, and integrating it with a frontend application. The focus will be on implementing and connecting a RESTful API to manage user data effectively.

Core requirements

1. Database Setup

  • Set up a PostgreSQL database with one table named users. This table should have the following schema:
    • id: Primary key that uniquely identifies each user. Use the SERIAL or UUID datatype.
    • first_name: String, stores the first name of the user.
    • last_name: String, stores the last name of the user.
    • email: String, stores the user’s email address (make this unique if possible).

2. API Development

  • Create your backend repository

    • Create a new public GitHub repository specifically for your API code. You can name it something like "users-api".
    • Create a new branch called hw-7. All your work for this assignment should be pushed to this branch. The main/master branch should be empty.
    • As you work, use clear, consistent commit messages to document your progress.

  • Safely store sensitive information

    • Store database credentials (host, port, database name, user, password) in an .env file to keep them secure.
    • Double-check that .env is included in your .gitignore to prevent pushing it to GitHub.
    • Use process.env to access your .env variables from your JavaScript code.

  • Build your API

    • Using Node and Express, create a new server with one endpoint to interact with the users table:

      • GET /users: Retrieve all users from the database.
        • Return the full list of users in JSON format.

    • Ensure this endpoint is connected to the users table and successfully retrieves data. You can make requests to this endpoint using your browser, curl, or Postman.

3. API Usage

Integrate this API with your React frontend.

  • In your frontend repository, make a new branch called hw-7. This branch should branch off of the most recent version of your frontend code. All your work for this section of the assignment should be done on this branch.

  • Set up your environment:

    • Use an .env file to store sensitive information (e.g., REACT_APP_DB_PASSWORD, REACT_APP_API_KEY).
    • Double-check that .env is included in your .gitignore to prevent pushing it to GitHub.
    • Use process.env to access your .env variables from your React code.

  • Fetch all the users using your new endpoint and display the fetched users.

    • If you did assignment 5, you will simply adjust your existing fetch request to fetch from your newly created endpoint instead of the one we provided.
    • If you did not do assignment 5, check out workshop 5 to learn how to make API requests from the frontend.
    • Hint: your URL domain should have localhost in it somewhere.

Good luck, and remember to carefully structure your code and keep your environment variables secure!

Additional Requirements

  • Enhanced Database Schema
    Add additional columns to the users table that might be relevant (e.g., date_of_birth, profile_picture_url, etc.) to further enrich the user data.

  • Additional API Endpoints
    Enhance your API by adding more functionality:

    • POST /users: Create a new user and add them to the database.

    • DELETE /users/:id: Delete a user by ID.

    • PUT /users/:id: Update a user’s information by ID.

    • Connect your new endpoints to the frontend.

Resources

Submission

For feedback from DISC exec

Follow these instructions if you are submitting to get feedback from DISC exec. The deadline for your submission is 1 week after the associated workshop.

  1. Database

    • Take a screenshot of your database setup and connection in Render. Name this screenshot database_verification.png and store it in the root folder of your API repository.
    • Run SHOW TABLES; (or the equivalent command) to display the schema of the users table. You will submit a screenshot of the output.
    • Run SELECT * FROM users; to display the data in your table. You will submit a screenshot of the output.

  2. API Repository

    • In your API repository, add a README.md file that explains your approach, key takeaways, and any challenges you encountered during the assignment. This will help your reviewers understand your work in detail.
    • Make a pull request from your hw-7 branch into main. You will submit the link to this PR.

  3. Frontend repository

    • Make a pull request from your hw-7 branch into main. You will submit the link to this PR.

  4. Fill out this Google form

For the Discover Program Application

If you are not submitting for DISC exec feedback, but for your Discover Program Application, follow the submission instructions in the application form.