queries.js

const Pool = require('pg').Pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'postgres',
  password: 'Keyin2021',
  port: 5432,
})


const getUsers = (request, response) => {
   const id = parseInt(request.query.name)

  pool.query('SELECT * FROM test_20220908.users ORDER BY id ASC', (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })
}

const getUserById = (request, response) => {
  const id = parseInt(request.params.id)

  console.log("Id = " + id)

  pool.query('SELECT * FROM test_20220908.users WHERE id = $1', [id], (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })
}

const findUsersBySearch = (request, response) => {
  const id = parseInt(request.query.id)
  const name = request.query.name

  var sqlStmt = 'SELECT * FROM test_20220908.users where '


  if (!name == false) {
    sqlStmt += ("name = '" + name + "' ")
    console.log("adding name = " + name)
  }

  if (!Number.isNaN(id)) {
    sqlStmt += ("AND id = " + id)
    console.log("Id = " + id)
  }

  console.log(sqlStmt)

  pool.query(sqlStmt, (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })

}

const createUser = (request, response) => {
  const { id, name, course_id } = request.body

  pool.query('INSERT INTO test_20220908.users (id, name, course_id) VALUES ($1, $2, $3)', [id, name, course_id], (error, results) => {
    if (error) {
      throw error
    }
    response.status(201).send(`User added with ID: ${results.insertId}`)
  })
}

const updateUser = (request, response) => {
  const id = parseInt(request.params.id)
  const { name, course_id } = request.body

  pool.query(
    'UPDATE test_20220908.users SET name = $1, course_id = $2 WHERE id = $3',
    [name, course_id, id],
    (error, results) => {
      if (error) {
        throw error
      }
      response.status(200).send(`User modified with ID: ${id}`)
    }
  )
}

const deleteUser = (request, response) => {
  const id = parseInt(request.params.id)

  pool.query('DELETE FROM test_20220908.users WHERE id = $1', [id], (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).send(`User deleted with ID: ${id}`)
  })
}

module.exports = {
    getUsers,
    getUserById,
    findUsersBySearch,
    createUser,
    updateUser,
    deleteUser,
}