Relational Database application

This document shows how to make this example.

Initial project setup

Open the command-line (Git Bash or Terminal).
Let’s create a new project
```
express --no-view Node4-Postgres
```
Go into the project folder you created. After this step, assume you are in this folder.
```
cd Node4-Postgres
```
From the command line, install dependencies listed in package.json. This command may take a while to run.
```
npm install
```

Database setup

Download and install:

PostgreSQL, a database server.

Windows

The installer may ask for a password (e.g., postgres. DO NOT PUT real password. Just simple, such as "abc") for the database. If so, REMEMBER!

The installer may ask for a port number(e.g., 5432) for the database. If so, REMEMBER!

Install whatever default and do not install additional packages (e.g., stack builder)

Then, Windows key → Type "Environment" → Add the bin directory of Postgres to your PATH (like this: C:\Program Files\PostgreSQL\<VERSION>\bin).

If you don’t know why we do this step, ask!

Mac

Install Postgres.app.

Move to Applications and Double-click to run.

Then, from the command-line:

# For older MacOS
echo "export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin" >> ~/.bash_profile

# For newer MacOS
touch ~/.zshrc
# This will open text editor. Copy&Paste:
# export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin
open -e ~/.zshrc

Install Node.js bindings to PostgreSQL (analogous to JDBC: Java API to connect to database). We will have a separate database: local and remote PSQL (We will visit this later).
```
npm install pg --save
```

We will need to define DATABASE_URL into .env to access the database.

# Windows users only (replace password with the database password you created during installation)
echo "DATABASE_URL='postgres://postgres:password@localhost/postgres'" > .env
# Mac users
echo "DATABASE_URL='postgres://localhost'" > .env

To read the .env file into process.env, install dotenv:
```
npm install dotenv --save
```
We just created the .env file to store configuration data. Node.js can access system environment variables via process.env. The dotenv module appends variables defined in .env into process.env, to get configuration data into your code.

Let’s test it in your terminal.

node
> process.env # you will see all environment variables
> require('dotenv').config();
> process.env # you will see all environment variables as well as DATABASE_URL
> .exit # exit from node

Did you see all environment variables including DATABASE_URL?

Example: Books

Connect to the local database: You will need to start Postgres first (Windows users: go to git bash and you will need to provide a password)

# Windows users
psql -U postgres         # if you are using defualt port number 5432
psql -U postgres -p 5433 # if you changed default port number to your own (e.g., 5433)

# Mac users
psql

Create table book (nope, it’s not normalized by any stretch):

create table book (id serial primary key, author text, title text, published date, pages integer, language char(2));

Insert rows into book:

insert into book(author,title,published,pages,language) values('Stephen King', 'Misery', '1988-01-01', 300, 'en');
insert into book(author,title,published,pages,language) values('J.K. Rowling', 'Harry Potter', '1999-01-01', 3000, 'en');
insert into book(author,title,published,pages,language) values('Taro Gomi', 'Everyone Poops', '1997-01-17', 27, 'en');
insert into book(author,title,published,pages,language) values('Jeff Kinney', 'Diary of Wimpy Kid', '2007-04-01', 224, 'en');
insert into book(author,title,published,pages,language) values('Malcolm Gladwell', 'Outliers', '2008-11-18',264, 'en');

If you want to play with database:

\? # help
\dt; # display tables
select * from book; # check if you insert properly
\q # quit

Modify routes/index.js and save:

var express = require('express');
var router = express.Router();
var path = require('path');

// Connect to process.env.DATABASE_URL when your app initializes:
// Read only reference value (const)
// get only Client class from pg package
const Client = require('pg').Client;

// create an instance from Client
const client = new Client({
  connectionString: process.env.DATABASE_URL
});

// connect to the DATABASE_URL
client.connect();

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});

router.get('/books', function(req, res, next) {
  res.sendFile(path.join(__dirname,'..', 'public','books.html'));
});

router.get('/booksOut', function(req, res, next) {
  // client object enables issuing SQL queries
  client.query('SELECT * FROM book', function(err, result){
    if (err) {next(err);}
    res.json(result.rows);
    console.log(result.rows);
  });
});

module.exports = router;

Add these lines to app.js and save:

// add this line BEFORE var indexRouter = require('./routes/index');
var env = require('dotenv').config();

Add these lines to public/books.html and save:

<html>
<head>
  <title>Database Books</title>

  <link rel="stylesheet" href="stylesheets/style.css">

 <link href="https://cdn.jsdelivr.net/npm/simple-datatables@latest/dist/style.css" rel="stylesheet" type="text/css">
  <script src="https://cdn.jsdelivr.net/npm/simple-datatables@latest" type="text/javascript"></script>


  <script src="https://unpkg.com/react@18/umd/react.development.js" crossorigin="anonymous"></script>
  <script src="https://unpkg.com/react-dom@18/umd/react-dom.development.js" crossorigin="anonymous"></script>
  <script src="https://unpkg.com/@babel/standalone/babel.min.js" crossorigin="anonymous"></script>

</head>

<body>
  <div id="books">
    <!-- This element's contents will be replaced with your component. -->
  </div>

  <!-- Load our React component. -->
  <script src="javascripts/books.js" type="text/babel"></script>

</body>
</html>

Add these lines to public/javascripts/books.js and save:

const BookComponent = () => {
  React.useEffect(() => {
    fetch('/booksOut')
      .then(response => {
        if (!response.ok) {
          throw new Error('Network response was not ok');
        }
        return response.json(); // assuming response is JSON
      })
      .then(data => {
        // https://datatables.net/
        // https://github.com/fiduswriter/Simple-DataTables
        const dataTable = new simpleDatatables.DataTable("#myTable");
        dataTable.insert(data);
      })
      .catch(error => {
        console.error('There was a problem with the fetch operation:', error);
      });
  }, []); // Empty dependency array ensures this effect runs only once, after initial render

  return (
    <div>
      <h2>Book Database</h2>
      <table id="myTable">
        <thead>
          <tr><th>author</th><th>title</th><th>published</th><th>pages</th><th>language</th></tr>
        </thead>
      </table>
    </div>
  );
};

const book = ReactDOM.createRoot(document.getElementById('books'));
book.render(<BookComponent />);

/*
const BookComponent = () => {
  const [data, setData] = React.useState([]);

  React.useEffect(() => {
    fetch('/booksOut')
      .then(response => {
        if (!response.ok) {
          throw new Error('Network response was not ok');
        }
        return response.json(); // assuming response is JSON
      })
      .then(data => {
        setData(data);
        console.log(data);
      })
      .catch(error => {
        console.error('There was a problem with the fetch operation:', error);
      });
  }, []); // Empty dependency array ensures this effect runs only once, after initial render

  //https://react.dev/learn/rendering-lists
  //https://en.wikipedia.org/wiki/Map_(higher-order_function)
  return (
    <div>
      <h2>Book Database</h2>
      <table>
        <thead>
          <tr><th>Author</th><th>Title</th><th>Published</th><th>Pages</th><th>Language</th></tr>
        </thead>
        <tbody>
          {
            data.map((row) => (
              <tr key={JSON.stringify(row)}>
                <td>{row.author}</td>
                <td>{row.title}</td>
                <td>{row.published}</td>
                <td>{row.pages}</td>
                <td>{row.language}</td>
              </tr>
            ))
          }
        </tbody>
      </table>
    </div>
  );
};

const book = ReactDOM.createRoot(document.getElementById('books'));
book.render(<BookComponent />);
*/

Run the server to see the example project.
```
npm start
```
Go to http://localhost:3000/books

Resources

https://db-engines.com/en/ranking_trend

https://node-postgres.com/guides/upgrading