We worked with databases in fiddle using SQL. We created a table and added rows and values to the table suign SQL commands.
Some of the commands learned:
Current Date:
date DATE DEFAULT CURRENT_DATE
Giving a. unique property to one of the tables column.
CREATE UNIQUE INDEX unique_voter
ON votes (voter, date)
Notes:
Important to call out that we can choose all the table's columns we need to show by using
select *
from TABLE
Afternoon Exercise
We built the donut voter in sections.
First starting with the back end section using the following code:
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 });
.
.
.
.
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); );
app.listen(PORT, () =>
console.log(Server is up and running at port ${PORT} 🚀)
);
Then we worked in the connection of the Back End to the front woking on donut JS in the public folder using:
Morning Exercise
We worked with databases in fiddle using SQL. We created a table and added rows and values to the table suign SQL commands.
Some of the commands learned:
Current Date: date DATE DEFAULT CURRENT_DATE
Giving a. unique property to one of the tables column. CREATE UNIQUE INDEX unique_voter ON votes (voter, date)
Notes: Important to call out that we can choose all the table's columns we need to show by using
select * from TABLE
Afternoon Exercise
We built the donut voter in sections.
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 }); . . . . 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);); app.listen(PORT, () => console.log(Server is up and running at port ${PORT} 🚀) );function renderDonut(donut) { const html = `
${donut.votes}
const fragment = document.createRange().createContextualFragment(html); const button = fragment.querySelector('button'); button.addEventListener('click', voteForDonut); return fragment;
app.get('/votes', async (_request, response) => { const result = await db.query(
SELECT * FROM votes WHERE date = CURRENT_DATE;); response.json(result.rows); });app.post('/votes', async function(request, response) { const { voter, donut } = request.body; if (!voter || !donut) { response.status(406).json({ error: 'voter and donut requried' }); } else { await db.query(
DELETE FROM votes WHERE voter = $1 AND date = CURRENT_DATE;, [voter] ); const result = await db.query(INSERT INTO votes (donut, voter) VALUES ($1, $2) RETURNING *;, [donut, voter] ); response.json(result.rows[0]); } });