Add PostgreSQL to Express Server

datasource.ts

import pg from "pg";

import { item, user } from "./models";
import { CONFIG } from "./pgenv";

const to_item = (itemRow: pg.QueryResult<any>): item => {
  return {
    id: parseInt(itemRow["id"]),
    task: itemRow["task"],
    priority: parseInt(itemRow["priority"]),
    date: new Date(itemRow["date"]),
  };
};

const to_user = (userRow: pg.QueryResult<any>): user => {
  return {
    id: parseInt(userRow["id"]),
    first_name: userRow["first_name"],
    last_name: userRow["last_name"],
  };
};

const execute = async (query: string): Promise<pg.QueryResult> => {
  const client = new pg.Client(CONFIG);
  await client.connect();

  const result = await client.query(query);
  await client.end;
  return result;
};

export const get_item = async (id: number): Promise<item> => {
  const query = `SELECT * from item where id=${id}`;
  return execute(query).then((res) => {
    if (res.rowCount == 0) {
      throw new Error(`No item was found with id: ${id}`);
    }
    const itemRow = res.rows[0];
    return to_item(itemRow);
  });
};

export const get_user = async (id: number): Promise<user> => {
  const query = `SELECT * FROM "user" where id=${id}`;
  return execute(query).then((res) => {
    if (res.rowCount == 0) {
      throw new Error(`No user was found with id: ${id}`);
    }
    const userRow = res.rows[0];
    return to_user(userRow);
  });
};

export const add_item = (item: item): Promise<number> => {
  const query = `INSERT INTO item (task, priority, date) VALUES ('${item.task}', ${item.priority}, '${item.date}') RETURNING id`;
  return execute(query).then((res) => {
    if (res.rowCount == 0) {
      throw new Error(`Cannot add item ${item}`);
    }
    return res.rows[0]["id"];
  });
};

export const add_user = (user: user): Promise<number> => {
  const query = `INSERT INTO "user" (first_name, last_name) VALUES ('${user.first_name}', '${user.last_name}') RETURNING id`;
  return execute(query).then((res) => {
    if (res.rowCount == 0) {
      throw new Error(`Cannot add user ${user}`);
    }
    return res.rows[0]["id"];
  });
};

export const update_item = (item: item, id: number): Promise<void> => {
  const query = `UPDATE item SET task='${item.task}', priority=${item.priority}, date ='${item.date}' WHERE id=${id}`;
  return execute(query).then((res) => {
    if (res.rowCount == 0) {
      throw new Error(`Cannot update item ${item}`);
    }
  });
};

export const update_user = (user: user, id: number): Promise<void> => {
  const query = `UPDATE "user" SET first_name='${user.first_name}', last_name='${user.last_name}' WHERE id=${id}`;
  return execute(query).then((res) => {
    if (res.rowCount == 0) {
      throw new Error(`Cannot update user ${user}`);
    }
  });
};

init.sql

-- Create Table item
CREATE IF NOT EXISTS TABLE item (
    id NUMERIC PRIMARY KEY,
    task VARCHAR(250) NOT NULL,
    priority NUMERIC NOT NULL,
    date TIMESTAMP DEFAULT NOW()
);
-- Insert initial set of items
INSERT INTO item (id, task, priority, date)
VALUES (1, 'Prepare Coffee', 1, '2021-05-01'),
    (2, 'Boil Eggs', 2, '2021-05-01'),
    (3, 'Buy Milk', 3, '2021-05-01'),
    ON CONFLICT DO NOTHING;
-- Create Table user
CREATE TABLE "user" (
    id NUMERIC PRIMARY KEY,
    first_name VARCHAR(250) NOT NULL,
    last_name NUMERIC NOT NULL
);
-- Insert initial set of users
INSERT INTO 'user' (id, first_name, last_name)
VALUES (1, 'John', 'Doe'),
    (2, 'Brad', 'Gabson'),
    (3, 'Allen', 'Ray'),
    ON CONFLICT DO NOTHING;

items.ts

import express from "express";
import { add_item, get_item, update_item } from "./datasource";

export const router = express.Router();

// GET Method
router.get("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    const item = await get_item(id);
    if (!item) {
      res.send(`Cannot find item with id: ${id}`);
    } else {
      res.send(item);
    }
  } catch (error) {
    res.send(error.message);
  }
});

// POST Method
router.post("/", async (req, res) => {
  try {
    const item = req.body;
    const id = await add_item(item);
    res.send(`Added item with id: ${id}`);
  } catch (error) {
    res.send(error.messsage);
  }
});

// PUT Method
router.put("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    const item = req.body;
    await update_item(item, id);
    res.send(`Updated item with id: ${id}`);
  } catch (error) {
    res.send(error.message);
  }
});

models.ts

export interface item {
  id: number;
  task: string;
  priority: number;
  date: Date;
}

export interface user {
  id: number;
  first_name: string;
  last_name: string;
}

users.ts

import express from "express";
import { add_user, get_user, update_user } from "./datasource";
export const router = express.Router();

// GET Method
router.get("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    const item = await get_user(id);
    if (!item) {
      res.send(`Cannot find user with id: ${id}`);
    } else {
      res.send(item);
    }
  } catch (error) {
    res.send(error.message);
  }
});

// POST Method
router.post("/", async (req, res) => {
  try {
    const user = req.body;
    const id = await add_user(user);
    res.send(`Added user with id: ${id}`);
  } catch (error) {
    res.send(error.message);
  }
});

// PUT Method
router.put("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    const user = req.body;
    await update_user(user, id);
    res.send(`Updated user with id: ${id}`);
  } catch (error) {
    res.send(error.message);
  }
});