Try building Express + PostgreSQL + Sequelize with Docker [Part 2]

Last time

https://qiita.com/rockguitar67/items/b644a63b3e39cc1fcaea

Yes it is continued.

③ Settings around DB

There is a file that has DB settings in the Express environment inside the container. The access information to the DB set in docker-compose.yml is specified here.

db_client.js

module.exports = {
    pg_client: function () {
        const { Client } = require('pg')

        const client = new Client({
            user: process.env.DB_USER,
            host: process.env.DB_HOST,
            port: process.env.DB_PORT,
            database: process.env.DB_NAME
        })
        return client
    },
  };

I think that you can still access the DB even at present. Let's take a look at index.js in routes!

Write how it behaves when you access'URL /'with GET. By reading sb_client.js earlier, it is possible to connect to the DB.

routes/index.js

router.get('/', function (req, res, next) {
  const client = require("../db_client").pg_client()

  client.connect()
     .then(() => console.log("success!!"))
     .then(() => client.query("select * from chat order by timestamp desc"))
     .then(function (results) {
      console.table(results.rows)
       res.render('index', { result: results.rows })
     })

However, if you look at the 6th line, the DB process is called by the SQL statement. This is still good for studying SQL, but if you prepare multiple tables and perform processing such as relations that connect them, use ORM, which is easier.

④ Utilization of ORM

Object-relational mapping (O / RM, ORM) is a programming technique that transforms incompatible data between databases and object-oriented programming languages. See wikipedia

Leverage Sequelize as an ORM when using Node.js.

Terminal

# npm install -g sequelize sequelize-cli

# sequelize-cli init

At this point, the config.json and model directories will be created in the working directory.

config

json:./config/config.json

{
  "development": {
    "username": "postgres",
    "password": null,
    "database": "mydatabase",
    "host": "database",
    "dialect": "postgres",
    "operatorsAliases": false
  },

It seems that it is common to specify 127.0.0.1 for host in config.json, but in the case of Docker, at the time of migration, you may get an error saying that you can not access the DB. Therefore, DB_HOST (= database) set in docker-compose.yml is used as it is.

Decide the table and column information to be created for the time being, and create a DB!

Terminal

# sequelize-cli model:generate --name user --attributes firstName:string,lastName:string,email:string

# sequelize-cli db:migrate

migration

When the migration is complete, the DB column information will be displayed in the migration folder.

javascript:./migration/date-create-user.js

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('users');
  }
};

model

When the migration is completed, the DB column information entered in Model will be displayed. If you can confirm this, it is completed.

javascript:./model/user.js

user.init({
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING,
    email: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'user',
  });

model / index.js and model / user.js created this time are edited by relation settings and associations, but at this point there is no touch.

User registration → User display sequence

You can easily create it with the user registration app.

https://github.com/LostGeneration1999/Express_PostgreSQL_ORM

From the form User: Kyuji Fujikawa was entered and displayed.

Reference article

https://qiita.com/markusveeyola/items/64875c9507d5fa32884e

Access to PostgreSQL https://qiita.com/yusuke-ka/items/448843020c0406363ba5

ORM related https://qiita.com/izszzz/items/31d448c501d24d31c846

ORM related https://qiita.com/KoKeCross/items/144949ba03e5138fc6d5