8/14 Sara Ho

[saraashleyho]

Today I Learned

SEQUEL to SQL

CREATE TABLE IF NOT EXISTS tablename

• remember we need to specify if not exists so that the server doesn't throw an error

CREATE UNIQUE INDEX IF NOT EXISTS indexname ON tablename (column1, column2);

• we used to above to constrain the user from entering in multiple entries on the same day

SERVER SET UP

In the terminal:

• yarn add express
• year add pg
• touch .gitignore and add node_modules to this file

In the top of your index.js:

const PORT = process.env.PORT || 3000;

const express = require('express');
const pg = require('pg');

const app = express();

app.use(express.static('public'));
app.use(express.json());

const db = new pg.Pool({ connectionString: process.env.DATABASE_URL });

And at the bottom:

app.listen(PORT, () =>
  console.log(`Server is up and running at port ${PORT} 🚀`)
);

Creating Your Table

• Query the database and insert the CREATE TABLE sql Example:

  db.query(`
  CREATE TABLE IF NOT EXISTS votes(
  id SERIAL PRIMARY KEY,
  donut VARCHAR(128) NOT NULL,
  voter VARCHAR(128) NOT NULL,
  date DATE DEFAULT CURRENT_DATE NOT NULL
  );
  CREATE UNIQUE INDEX IF NOT EXISTS duplicate_votes_per_day ON votes(voter, date);
  `);

  Adding your End points We now end our endpoints (in this case, get and post requests), which allow us to get data from the database as well as add votes to our database.

Example:

app.get('/votes', async (_request, response) => {
  const result = await db.query(
    `SELECT * FROM votes WHERE date = CURRENT_DATE;`
  );
  response.json(result.rows);
}); 

CONNECTING THE FRONT END TO THE SERVER

• In our "renderDonut" function, we need to store our html in a variable and use the createContextualFragment method to create a fragment. This allows us to the add an Event Listener to the button.
• We then built the voteForDonut function to await the POST request from our server. This is how the front and back ends connect

async function voteForDonut(event) {
  const theButtonThatGotClicked = event.currentTarget;
  const theClosestDonut = theButtonThatGotClicked.closest('.donut');
  const donutId = theClosestDonut.dataset.donut;
  const url = '/votes';
  await fetch(url, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Accept: 'application/json',
    },
    body: JSON.stringify({
      voter: voterId,
      donut: donutId,
    }),
  });
  await tallyVotes();
}

• When the user clicks the button of their favorite donut, the .closest method returns the closest donut
• The post request adds this new information to the server through the url /votes.

For a full example, reference the "donut of the day" project or the "get things done" project.