Section 24: SQL & Website Backend Code - Using SQL In Our Website Code

The goals

💪🏻How to connect to databases in code

✌🏻Performing CRUD operations in code

👍🏻Outputting database data

 

Why On The Backend?

We should NOT connect to a database from inside our frontend JavaScript code (i.e. from inside the browser).

All our HTML, CSS & browser-side JS code is exposed to our website visitors (e.g. via browser dev tools).

-Just to be clear: Such changes will NOT be mirrored to the server-they will only affect the user who makes the change and disappear if the page is reloaded

Database credentials could be looked up, queries could be edited.

 

What We Will Build

A basic 'blog' Website with CRUD Operations

 

Planning Our Database Structure

 

Start modifying table

We made tables as we designed and we put some initial data.

 

mysql.createPool()

If we need to connect our route to database every time, it would be quite burdensome. So, in this case, we can use package to prevent it and make it convenient.

We installed mysql2 here which lets us creating connection function to create a connection.

Or it can give us creating pool function which creates a pool of connections. It is really helpful especially if we have  bigger websites where we might be handling a lot of concurrent requests, so a lot of concurrent requests trying to reach our server, then such a pool of connection is more efficient than creating inidividual connections all the time.

 

Connection to the pool

Why pool and not connection?

 

Database connection pooling is a method used to keep database connections open so they can be reused by others. Typically, opening a database connection is an expensive operation, especially if the database is remote. You have to open up network sessions, authenticate, have authorisation checked, and so on. Pooling keeps the connections active so that, when a connection is later requested, one of the active ones is used in preference to having to create another one.

 

 

mysql.createConnection() vs mysql.createPool()

mysql.createConnection

When you create a connection with mysql.createConnection, you only have one connection and it lasts until you close it OR connection closed by MySQL.

A single connection is blocking. While executing one query, it cannot execute others. hence, your application will not perform good.
Example:

var mysql = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});

mysql.createPool

mysql.createPool is a place where connections get stored.
When you request a connection from a pool,you will receive a connection that is not currently being used, or a new connection.
If you're already at the connection limit, it will wait until a connection is available before it continues.

A pool while one connection is busy running a query, others can be used to execute subsequent queries. Hence, your application will perform good.
Example:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});

 

And we used like this

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: 'localhost',
  database: 'blog',
  user: 'root',
  password: 'wrote my password'
});

module.exports = pool;

 

Let's check again what's the difference between 'async/await' and 'promise/then'

1. js에서 비동기 처리는 왜 필요할까?

• js는 동기적인 언어이지만, 서버에 데이터를 요청하는 등 대기시간 긴 작업의 경우 비동기 작업을 한다.
• 하지만 이 때문에 발생하는 문제가 있는데, 아래 예시를 살펴보자
• getAnimals()는 서버에서 동물의 데이터를 받아오는 함수이다.
• 그리고 우리는 getAnimals()의 응답값을 받아 출력하고 싶다.
• 아래와 같이 작성하면 animals()의 값이 출력될까? 답은 아니다.

function printAnimals() {
  const animals = getAnimals();
  console.log(animals);
}

• js는 동기적인 언어이지만, 대기시간 긴 작업의 경우 비동기로 작업 한다.
• 그렇기 때문에 getAnimals()보다 콘솔 출력이 먼저 발생하고, undefined가 출력되는 것이다.

function printAnimals() {
  const animals = getAnimals(); // 대기시간이 기네?
  console.log(animals);         // 우선 실행합니다~
}

printAnimals();  // undefined

• 그럼 만약 우리가 getAnimals()의 응답값(response)를 사용하고 싶다면 어떻게 해야할까?
• 비동기 처리방식인 promise, async await를 사용하는 것이다.

 

잠시만요! promise와 async await 둘 다 비동기 처리를 위해 사용되는데, 똑같은 거 아닌가?

 

• 용도는 똑같지만 다른 점이 분명 존재한다.
• 그럼 promise,async await에는 어떤 차이점 있는지 알아보자!

 

2. promise vs async await

(1) async는 간결하다

• 앞선 예시를 다시 사용해보자.
• 우리는 getAnimals()를 호출하여 응답값을 받고, 이 응답값을 출력하고 싶다.
• promise의 then방식이라면 아래와 같이 작성할 것이다.

function printAnimals() {
  getAnimals().then((data) => {
    console.log(data);
  })
}

 

• 그럼 async await로 어떻게 표현할까?
• 함수에 async 키워드를 적고, 비동기 대상에 await를 추가해주면 된다.
• 비동기 대상 함수에 await를 추가하면, '이 함수의 처리를 기다려!' 라는 의미가 되기에
• await 함수 아래에 해당 함수의 응답값을 사용하는 구문을 추가해주면 된다.

async function printAnimals() {
  const animals = await getAnimals();  // 이 함수처리 끝날때까지 기다려!
  console.log(animals)                 // 응답값이 출력됨!
}

 

어? 그런데 간결성 측면에서 둘 다 큰 차이가 없는 거 같은데요?

 

• 짧은 예시로만 봤을 때 큰 차이를 못 느낄 것이다.
• 그렇다면 데이터를 요청하여 처리까지 한다면? promise의 then을 사용하면 아래와 같다.

function printAnimals() {
  return getAnimals()
    .then(data => {
      if (data.property) {
        return sampleFunc1(data)
          .then(anotherData => {
            console.log(anotherData)
          })
      }else {
        console.log(data)
      }
    })
}

• then방식을 보면 라인 수가 많은 것은 물론 들여쓰기도 많아 복잡한 느낌이 든다.
• 그럼 async await를 쓰면 어떨까?

async function printAnimals() {
  const animals = await getAnimals();
  if (animals.property) {
    const sampleData = await sampleFunc1(animals);
    console.log(sampleData);
  }else {
    console.log(animals);
  }
}

• then에 비해 많은 들여쓰기는 물론 라인도 차지 않는다.
• 또한 응답값을 명시적인 변수에 담아 사용하므로 직관적으로 변수를 인식할 수 있다.
• 이처럼 async await는 then 방식에 비해 간결하다는 장점을 가지고 있다.

 

(2) async는 에러 핸들링에 유리하다

• 서버에 데이터를 요청하는 작업을 하다보면, 에러가 발생할 수 있다.
• 이 때문에 우리는 에러 핸들링도 해주어야 한다.
• 이번에는 printAnimals()에서 에러가 발생한 게 아니라, JSON.parse에서 에러가 발생했다고 가정하자.
• 이 경우, then을 사용하면 내부에 추가적인 catch문을 적어줘야한다.

function printAnimals() {
  try {
      getAnimals()
      .then((response) => {
      const data = JSON.parse(response); // 여기서 에러 발생한다고 가정
      console.log(data);
    })
    .catch((err)=> {   // 추가적인 에러
      console.log(err)
    })
  }
  catch(err) {
    console.log(err)
  }
}

• 이 방식은 직관적이지 않을 뿐더러 에러를 처리하는 catch문이 중복된다ㅜ

 

• 하지만 async await를 사용하게 되면 하나의 catch만 해주면된다!
• 해당 catch문에서는 try 내부에서 발생하는 모든 에러를 접근할 수 있다.

async function printAnimals() {
  try {
      const data = await JSON.parse((getAnimals())
    console.log(data);
  }
  catch(err) {
    console.log(err)
  }
}

 

(3) async는 에러 위치를 찾기 쉽다

• 만약 프로미스를 연속으로 호출한다고 해보자.
• 이때 만약 어느 지점에서 에러가 발생하면 어떤 then에서 에러가 발생했는지 찾기가 어렵다.

function sample() {
  return sampleFunc()
    .then(data => return data)
    .then(data2 => return data2)
    .then(data3 => return data3)
    .catch(err => console.log(err))  // 결과적으로 문제가 발생했다
}

• 하지만 async를 사용하게 되면, 어떤 지점에서 에러가 발생했는지 쉽게 찾을 수 있다.

async function sample() {
  const data1 = await sampleFunc();      // 문제 발생시 data1값이 유효치 않음
  const data2 = await sampleFunc2(data1);
  return data2;
}

이처럼 promise의 then, async await는 용도는 같지만, 간결성, 에러 핸들링, 에러 위치 확인 측면에서 차이가 있음을 알게 되었다. 이외에도 async await은 디버그를 할 때 then과 달리 정확한 위치를 지정할 수 있는 장점이 있다.

 

Since we will use authors in code, we bring this from table using query.

 <div class="form-control">
        <label for="author">Select Author</label>
        <select id="author" name="author">
          ...
        </select>
      </div>

And we want to show our author list here.

 

So, if we organize the steps which are to connect our code (node.js) with database,

1. we made data folder to put database.js file and we define mysql and pool to make many requests and not repeating

2. we went to blog.js file which stores routers and connect db with importing a variable 'db' from data folder, database file.

3. since we connected, we can use db and bring some resources from our database. So, we will bring the authors data to show it up on select menu.

4. used destructuring of array to bring authors data from our database. This work can take some time, so we worked it with 'async/await' to make it work asynchronously.

5. we wrote query directly with using  

router.get('/new-post', async function(req, res) {
  const [authors] = await db.query('SELECT * FROM authors');
  res.render('create-post', { authors: authors});
});

6. Now we can use authors value in our code!

So, we go to 'create-post' page and in the select menu, write ejs code.

<div class="form-control">
        <label for="author">Select Author</label>
        <select id="author" name="author">
          <% for (const author of authors) { %>
            <option value="<%= author.id %>"><%= author.name %></option>
          <% } %>
          </select>
      </div>

result

 

app.use(express.urlencoded({ extended: true }))

With this code, we can parse the incoming the body of request and Express makes it available.

Why all of a sudden I care this code?

Because we need to use body of request in our code.

 

router.post('/posts', function(req, res) {
  req.body;
  db.query('INSERT INTO posts (title, summary, body, author_id) VALUES (?)', []);
});

The question mark means dynamic values.

And then the query method takes a second parameter. 

MySQL package will automatically take all the values we add to this array and replace all the question marks in our query with those values.

So we can write question marks for all the items like

db.query('INSERT INTO posts (title, summary, body, author_id) VALUES (?, ?, ?, ?)', []);

But our MySQL package has convenience. 

Let's change our code to utilize MySQL package.

router.post('/posts', function (req, res) {
  const data = [
    req.body.title,
    req.body.summary,
    req.body.content,
    req.body.author
  ]
  db.query('INSERT INTO posts (title, summary, body, author_id) VALUES (?)', [data]);
});

Here the name after req.body. ~ should be the same as we set on the post page.

Order also matters!

So if we see it directly how it will match each other.

router.post('/posts', function (req, res) {
  const data = [
    req.body.title,
    req.body.summary,
    req.body.content,
    req.body.author
  ]
  db.query(
    'INSERT INTO posts (title, summary, body, author_id) VALUES (?, ?, ?, ?)', 
    [data[0], data[1], data[2], data[3]]);
});

↑this code is just to show how our code works. 

<form action="/posts" method="POST">
      <div class="form-control">
        <label for="title">Title</label>
        <input type="text" id="title" name="title" required>
      </div>
      <div class="form-control">
        <label for="summary">Summary</label>
        <input type="text" id="summary" name="summary" required maxlength="255">
      </div>
      <div class="form-control">
        <label for="content">Post Content</label>
        <textarea id="content" name="content" required rows="5"></textarea>
      </div>
      <div class="form-control">
        <label for="author">Select Author</label>
        <select id="author" name="author">
          <% for (const author of authors) { %>
            <option value="<%= author.id %>"><%= author.name %></option>
          <% } %>
          </select>
      </div>
      <button class="btn">Add Post</button>
    </form>

We should check the name of each field where we will input some data to post. And it should be connected to each column of database so we can save our data.

 

We can see that our database has got our data.

 

Now it's the time to bring the list of posts

router.get('/posts', function (req, res) {
  db.query('SELECT posts.*, authors.name AS author_name FROM posts INNER JOIN authors ON posts.author_id = authors.id')
  res.render('posts-list');
});

Since this code is too long, let's shorten it

router.get('/posts', async function (req, res) {
  const query = `
  SELECT posts.*, authors.name AS author_name FROM posts 
  INNER JOIN authors ON posts.author_id = authors.id
  `
  const [posts] = await db.query(query)
  res.render('posts-list');
});

We used backtick here (``) to separate the lines.

Also, destructurized array and put it in a variable (we can name it whatever we want)

 

And we can give this extra data to the page which will be rendered to use there.

'posts:' is just a key which will be available in a template.

 

So here post is equal to the post we used in for loop.

And the key 'post' is just a name we will use in 'includes/post-item' template.

 

Now we need to make the detail link for the each item. So if we click the 'view post' button, we can see the detail view.

 

<article class="post-item">
  <h2><%= post.title %></h2>
  <p class="post-item-author">By <%= post.author_name %></p>
  <p><%= post.summary %></p>
  <div class="post-actions">
    <button class="btn btn-alt">Delete Post</button>
    <a href="...">Edit Post</a>
    <a class="btn" href="/posts/<%= post.id %>">View Post</a>
  </div>
</article>

In this code, we put href in <a> tag with ejs code. So, if we click the button it will lead us to the link which url is '/posts/post.id'

The first post id is '1' so we got 1.

But we didn't make that detail page yet. 

So now we need to go to 'blog.js' file which storing our all routers.

 

router.get('/posts/:id', function(req, res) {
  const query = `
    SELECT * FROM posts WHERE id = ?
  `
  res.render('post-detail');
})

So, to bring the data from database we wrote this query. The one thing we should check here is that we used question mark here.

Before we orgarnized this one time, but let's check once again!

 

Using Question Marks (?) in SQL Statements

Use a question mark in place of a single parameter that the service expects as input. When you test the service in Integration Server Administrator, it recognizes the question mark and prompts you for the required input value.

Example 1 - replacing "?" with the value of our condition

To select all rows from the Names table that match the values specified for the Last_Name column, specify the following SQL statement:

select * from Names where Last_Name=?

The service expects an input value to use to match rows in the Last_Name column.

Example 2 - avoiding the repetition

To add a new row to the Addresses table and populate it with specified values, specify the following SQL statement:

INSERT INTO Addresses (name,street,city,state,zip)
VALUES (?,?,?,?,?)

The service expects input values to use to populate the new row.

 

Answer 1

Prepared statments use the '?' in MySQL to allow for binding params to the statement. Highly regarded as more secure against SQL injections if used properly. This also allows for quicker SQL queries as the request only has to be compiled once and can be reused.

 

Answer2

The question mark represents a parameter that will later be replaced. Using parameterized queries is more secure than embedding the parameters right into the query.

SQL Server calls this parameterize queries, and Oracle calls it bind variables.

The usage varies with the language that you are executing the query from.

Here is an example of how it is used from PHP.

assuming that $mysqli is a database connection and people is a table with 4 columns.

$stmt = $mysqli->prepare("INSERT INTO People VALUES (?, ?, ?, ?)");

$stmt->bind_param('sssd', $firstName, $lastName, $email, $age);

The 'sssd' is a flag identifying the rest of the parameters, where s represents string and d represents digits.

 

Question mark (?) as placeholder

Escape question mark are used as placeholder for MYSQL query in Node to prevent SQL injections

 const query = 'SELECT * FROM tablename WHERE id = ?';

pool.query(query, [ req.params.id ], (err, results) => {

The ID value is content in req.params.id
You can escape many values using an array [ value1 , value2 , value3 ]

const query = 'SELECT * FROM fjitest.fji2_users WHERE  username = ? || email = ? ';
pool.query(query, [ userOrmail, userOrmail ], async (err, results) => {

Double question mark (??) can be used to identifiers you’d like to have escaped:

const userId = 1;
const columns = ['username', 'email'];
const query = pool.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function (error, results, fields) {
  if (error) throw error;
  // ...
});

SELECT ?? FROM ?? WHERE id = ? –> SELECT username, email FROM users WHERE id = userId

 

 

 

 

So, in our case, we are using this question mark to replace it with the data later.

 

Here, we wrote code like this. 

So we will fetch the data which has the same id with our id.

Since we used only one question mark, we will give only one parameter after query.

And it should be 'req.params.id'

router.get('/posts/:id', function(req, res) {
  const query = `
    SELECT * FROM posts WHERE id = ?
  `;

  db.query(query, [req.params.id])
  res.render('post-detail');
});

So to say, we send our id instead of question mark. And we will get it from [req.params.id].

But also, we want to bring the author data to show with the post. And here we need join.

 

router.get('/posts/:id', async function(req, res) {
  const query = `
    SELECT posts.*, authors.name AS author_name, authors.email AS author_email FROM posts 
    INNER JOIN authors ON posts.author_id = authors.id
    WHERE posts.id = ?
  `;

  const result = await db.query(query, [req.params.id])
  res.render('post-detail');
});

And again, result is an array with two items.

The first items are our fetched records and the second item is the fetched data.

We are not interested in meta data, but we are interested in the first data.

So, here we use array destructuring again.

 

router.get('/posts/:id', async function(req, res) {
  const query = `
    SELECT posts.*, authors.name AS author_name, authors.email AS author_email FROM posts 
    INNER JOIN authors ON posts.author_id = authors.id
    WHERE posts.id = ?
  `;

  const [posts] = await db.query(query, [req.params.id])
  res.render('post-detail');
});

We named it with plural form cause even we fetch only one data, still it could be more than one.

So our SQL package will not know that there is only one post we can bring.

Therefore, we name it plural form.

 

And when we render this, I wanna forward this post also to show it and use it in our code.

So in this case, we will give it with a key (name) we wanna use in that code.

 

router.get('/posts/:id', async function(req, res) {
  const query = `
    SELECT posts.*, authors.name AS author_name, authors.email AS author_email FROM posts 
    INNER JOIN authors ON posts.author_id = authors.id
    WHERE posts.id = ?
  `;

  const [posts] = await db.query(query, [req.params.id])
  res.render('post-detail', {post: posts[0]});
});

We will use the first item cause we will only have one result.

so we call it as 'posts[0]'.

 

And we should prepare for the case that user enter with the 'post id' which doesn't exist or if the post doesn't exist at all.

So here we should use if statement.

 

router.get('/posts/:id', async function(req, res) {
  const query = `
    SELECT posts.*, authors.name AS author_name, authors.email AS author_email FROM posts 
    INNER JOIN authors ON posts.author_id = authors.id
    WHERE posts.id = ?
  `;

  const [posts] = await db.query(query, [req.params.id])
  
  if (!posts || posts.length === 0) {
    return res.status(404).render('404');
  }
  
  res.render('post-detail', {post: posts[0]});
});

We wrote 'return' here, so that the code thereafter won't be executed.

Otherwise after rendering 404 error page, still the left code will also be executed.

 

So now we move to 'post-detail' page, to show the data we fetched.

 

<body>
  <%- include('includes/header') %>
  <main id="post-detail">
    <h1><%= post.title %></h1>
    <section id="post-meta">
      <address></address>
    </section>
    <hr>
    <section>
      <p id="body">...</p>
    </section>
  </main>
</body>

Here we put the post title first.

And now we will show the data of author as meta data.

We will use basic html tag <address> which is useful when we show some contact details.

We can use this for home address or email address.

 

<body>
  <%- include('includes/header') %>
  <main id="post-detail">
    <h1><%= post.title %></h1>
    <section id="post-meta">
      <address><a href="mailto:<% post.author_email %>"><%= post.author_name%></a></address> |
      <time datetime="<%= post.date %>"><%= post.date %></time>
    </section>
    <hr>
    <section>
      <p id="body"><%= post.body %></p>
    </section>
  </main>
</body>

 

Mailto?

Mailto links are used to redirect to an email address instead of a web page URL. When a user clicks on the Mailto link, the default email client on the visitor's computer opens and suggests sending a message to the email address mentioned in the Mailto link.

To create a Mailto link, you need to use the HTML <a> tag with its href attribute, and insert a "mailto:" parameter after it, like the following:

<a href="mailto:email@example.com">Send Email</a>

If you want to receive the email to more than one address, separate your email addresses with a comma:

<a href="mailto:email@example.com, secondemail@example.com">Send Email</a>

The following fields can be filled out beforehand:

• subject - for the subject line,
• cc - for sending a carbon copy,
• bcc - for sending a blind carbon copy,
• body - for the message's body text.

 

If you want to have a subject field, which is already filled out, add the “subject” parameter to the href attribute:

<a href="mailto:email@example.com?subject=Mail from our Website">Send Email</a>

To add CC and BCC to your email, use the "cc" or "bcc" parameter on the href attribute:

<a href="mailto:email@example.com?cc=secondemail@example.com, anotheremail@example.com, &bcc=lastemail@example.com&subject=Mail from our Website">Send Email</a>

To add a body text, use the "body" parameter with other parameters:

<a href="mailto:email@example.com?cc=secondemail@example.com, anotheremail@example.com, &bcc=lastemail@example.com&subject=Mail from our Website&body=Some body text here">Send Email</a>

Putting all together, we'll have the following example.

 

HTML datetime Attribute

The <time> datetime Attribute in HTML is used to defines the machine-readable date/time of the <time> element. The date-time is inserted in the format YYYY-MM-DDThh:mm:ssTZD. 

The datetime attribute specifies the date and time when the text was deleted/inserted.

When used together with the <time> element, it represents a date and/or time of the <time> element.

Syntax:

<time datetime="YYYY-MM-DDThh:mm:ssTZD"> 

Attribute Values: This attribute contains single value YYYY-MM-DDThh:mm:ssTZD which is used to specify the date and time when the text was deleted. 
The explanation of datetime components are listed below: 
 

• YYYY: It sets the year of datetime object (e.g. 2009).
• MM: It sets the month of datetime object (e.g. 05 for March).
• DD: It sets the day of the month of datetime object (e.g. 04).
• T: It is a required separator.
• hh: It sets the hour of datetime object (e.g. 18 for 06.00pm).
• mm: It sets the minutes of datetime object (e.g. 34).
• ss: It sets the seconds of datetime object (e.g. 40).
• TZD: Time Zone Designator (Z denotes Zulu, also known as Greenwich Mean Time)

Example: 

<!DOCTYPE html>
<html>
<head>
    <title>
        HTML Time dateTime Attribute
    </title>
</head>
<body style="text-align:center;">
    <h1>GeeksforGeeks</h1>
    <h2>
        HTML <Time>dateTime Attribute
    </h2>
	<p>Jawahar lal Nehru birthday is celebrated on 
        <time datetime="2018--11-14 12:00">
            children's day.
        </time>
    </p> 
</body>
</html>

Output :

 

CSS white-space: pre-wrap;

#body {
white-space: pre-wrap; /* This ensures that line breaks and whitespace are kept */
}

If we remove this line from CSS

If we have this line in CSS

 

Let's fix the date format!

router.get('/posts/:id', async function(req, res) {
  const query = `
    SELECT posts.*, authors.name AS author_name, authors.email AS author_email FROM posts 
    INNER JOIN authors ON posts.author_id = authors.id
    WHERE posts.id = ?
  `;

  const [posts] = await db.query(query, [req.params.id])
  
  if (!posts || posts.length === 0) {
    return res.status(404).render('404');
  }
  
  const postData = {
    ...posts[0]
  }
  
  res.render('post-detail', {post: posts[0]});
});

First of all, we need to get all the data off our single post into the object we define here.

This spread operator ensures that we take all the key value pairs which are parts of this single post object.

This single post is that we have as the first item in the posts array.

We did this all thing to enrich this copy.

To be precise, we overwrite the date property

 

[JavaScript]Date.prototype.toLocaleDateString()

The toLocaleDateString() method returns a string with a language-sensitive representation of the date portion of the specified date in the user agent's timezone. In implementations with Intl.DateTimeFormat API support, this method simply calls Intl.DateTimeFormat.

The toLocaleDateString() method returns the date (not the time) of a date object as a string, using locale conventions.

 

Syntax

toLocaleDateString()
toLocaleDateString(locales)
toLocaleDateString(locales, options)

Example

const date = new Date(Date.UTC(2012, 11, 20, 3, 0, 0));

// formats below assume the local time zone of the locale;
// America/Los_Angeles for the US

// US English uses month-day-year order
console.log(date.toLocaleDateString('en-US'));
// → "12/20/2012"

// British English uses day-month-year order
console.log(date.toLocaleDateString('en-GB'));
// → "20/12/2012"

// Korean uses year-month-day order
console.log(date.toLocaleDateString('ko-KR'));
// → "2012. 12. 20."

const date = new Date(Date.UTC(2012, 11, 20, 3, 0, 0));

// request a weekday along with a long date
const options = { weekday: 'long', year: 'numeric', month: 'long', day: 'numeric' };
console.log(date.toLocaleDateString('de-DE', options));
// → "Donnerstag, 20. Dezember 2012"

// an application may want to use UTC and make that visible
options.timeZone = 'UTC';
options.timeZoneName = 'short';
console.log(date.toLocaleDateString('en-US', options));
// → "Thursday, December 20, 2012, UTC"

 

So, we can organise our code like this.

router.get('/posts/:id', async function(req, res) {
  const query = `
    SELECT posts.*, authors.name AS author_name, authors.email AS author_email FROM posts 
    INNER JOIN authors ON posts.author_id = authors.id
    WHERE posts.id = ?
  `;

  const [posts] = await db.query(query, [req.params.id])
  
  if (!posts || posts.length === 0) {
    return res.status(404).render('404');
  }
  
  const postData = {
    ...posts[0],
    date: posts[0].date.toISOString(),
    humanReadableDate: posts[0].date.toLocaleDateString('en-US', {
      weekday: 'long',
      year: 'numeric',
      month: 'long',
      day: 'numeric'
    }),
  };
  
  res.render('post-detail', {post: posts});
});

And fix here also

<body>
  <%- include('includes/header') %>
  <main id="post-detail">
    <h1><%= post.title %></h1>
    <section id="post-meta">
      <address><a href="mailto:<% post.author_email %>"><%= post.author_name%></a></address> |
      <time datetime="<%= post.date %>"><%= post.humanReadableDate %></time>
    </section>
    <hr>
    <section>
      <p id="body"><%= post.body %></p>
    </section>
  </main>
</body>

With the humanReadableDate that we set in upper code.

 

Original

Now the date and time look different

 

Now we will go to make update page so we set url first and go to routing page ('blog.js')

<article class="post-item">
  <h2><%= post.title %></h2>
  <p class="post-item-author">By <%= post.author_name %></p>
  <p><%= post.summary %></p>
  <div class="post-actions">
    <button class="btn btn-alt">Delete Post</button>
    <a href="/posts/<%= post.id %>/edit">Edit Post</a>
    <a class="btn" href="/posts/<%= post.id %>">View Post</a>
  </div>
</article>

 here we set it as '/posts/<%= post.id %>/edit '

And we will make this route works in routing app file

 

In update-post.ejs, we need to set basic value for each menu, so we can have basic value and then can change it.

<body>
  <%- include('includes/header') %>
  <main>
    <h1>Update post</h1>
    <form action="/posts/<%= post.id %>/edit" method="POST">
      <div class="form-control">
        <label for="title">Title</label>
        <input type="text" id="title" value="<%= post.title %>" name="title" required>
      </div>
      <div class="form-control">
        <label for="summary">Summary</label>
        <input type="text" id="summary" value="<%= post.summary %>" name="summary" required maxlength="255">
      </div>
      <div class="form-control">
        <label for="content">Post Content</label>
        <textarea id="content" name="content" required rows="5"><%= post.body %></textarea>
      </div>
      <button class="btn">Update Post</button>
    </form>
  </main>
</body>

So here we set value. But we can change whenver we want.

The line is long but we can't just change the line cause we set CSS file attribute.

And check once again each section's name so we can use it when we update!

 

Now let's see how to update data on our page and database

 

router.post('/posts/:id/edit', async function (req, res) {
  const query = `
  UPDATE posts SET title = ?, summary = ?, body = ?
  WHERE id = ?
  `;

  await db.query(query, [req.body.title, req.body.summary, req.body.content, req.params.id])

  res.redirect('/posts');
});

 

res.render() vs res.redirect()

res.redirect(someURL) is for you want to return a 30x status code (often 302) to the browser and tell the browser to go to a new URL. This is often done on the server as part of a login process and occasionally when the user finds themselves at an old URL that has been changed to something different. res.redirect() should only be used for these types of forced navigation to a different URL and should never be part of any standard rendering process.

res.render(filename, data) is how one would typically use EJS (or any other template engine plugged into Express) to fill in a template with some data and return the "rendered" HTML page back to the requesting browser so the browser can render it.

 

In the specific case you show in your question, when you "approve" the login, you may then want to do a res.redirect() to whatever URL you want the user to start on after the login and then create a route for that URL which you will use res.render() to render that page.

The steps will then look like this:

1. User goes to /login.
2. User submits login data with form POST.
3. Server validates data and establishes login session.
4. Server does res.redirect('/home') (or whatever URL you want here) to tell the browser to go to the new URL.
5. Browser processes the redirect and sends request for that new page.
6. Server sees request for the new page and uses res.render() to render the data for that page.
7. Browser displays rendered page on the new URL.

 

We can't delete post with <a> tag (link) so we used button here

 

It's gone!

 

 

Sources

https://mong-blog.tistory.com/entry/promise%EC%99%80-async-await%EC%9D%98-%EC%B0%A8%EC%9D%B4%EC%A0%90

promise와 async await의 차이점

https://elvanov.com/2597

[Javascript] 비동기, Promise, async, await 확실하게 이해하기 – Under The Pencil

https://documentation.softwareag.com/webmethods/microservices_container/msc10-7/10-7_MSC_PIE_webhelp/index.html#page/integration-server-integrated-webhelp/to-accessing_databases_with_services_7.html

Reverb

https://www.finalmarco.com/mysql-in-nodejs-pools-select-insert-update-delete-and-question-marks/

Mysql in NodeJs > pools, select, insert, update, delete and question marks - Finalmarco's corner

https://css-tricks.com/snippets/html/mailto-links/

Mailto Links | CSS-Tricks

https://www.w3docs.com/snippets/html/how-to-create-mailto-links.html

How to Create Mailto Links