DEV Community

Aditya Chukka
Aditya Chukka

Posted on

3 1

Add PostgreSQL to Express Server

postgres_express

In this section, we will add postgres database connection to node server written in express.

Dependencies

  • An Express Node Server running locally or in a remote server.

Please check our earlier post on routing requests on a node server

  • A PostgreSQL database running locally or in a remote server.

If you are feeling ambitious, please refer to this commit for source code

Database Setup

  • Download and install latest version of PostgreSQL

    At the time of writing this post, the latest version if 13.3

  • Create a database initialization script init.sql

  • Create table for item

-- 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()
);
Enter fullscreen mode Exit fullscreen mode
  • Add an initial set of items
-- 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;
Enter fullscreen mode Exit fullscreen mode
  • Create table for user
-- Create Table user
CREATE TABLE "user" (
    id NUMERIC PRIMARY KEY,
    first_name VARCHAR(250) NOT NULL,
    last_name NUMERIC NOT NULL
);
Enter fullscreen mode Exit fullscreen mode
  • Add initial set of users
-- 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;
Enter fullscreen mode Exit fullscreen mode
  • Stitching all above parts together we get 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;
view raw init.sql hosted with ❤ by GitHub

Define Models

Before we store the actual resource, we need to define them

  • Create a module for models models.ts
  • Define an interface for item
export interface item {
  id: number;
  task: string;
  priority: number;
  date: Date;
}
Enter fullscreen mode Exit fullscreen mode
  • Define an interface for user
export interface user {
  id: number;
  first_name: string;
  last_name: string;
}
Enter fullscreen mode Exit fullscreen mode
  • Your completed models.ts should look like below
export interface item {
id: number;
task: string;
priority: number;
date: Date;
}
export interface user {
id: number;
first_name: string;
last_name: string;
}
view raw models.ts hosted with ❤ by GitHub

Connect to PostgreSQL

Install node-pg module

npm install pg
Enter fullscreen mode Exit fullscreen mode

At this point we are ready to interact with our data storage. Let's create a module for interacting with postgres database, datasource.ts

Execute query on database

Create a method (execute) to interact with postgreSQL given a query

const execute = async (query: string): Promise<pg.QueryResult> => {
  const client = new pg.Client(CONFIG);
  await client.connect();
  // It is not a good practice to create a client for every single request. Have a single instance of client per service

  const result = await client.query(query);
  await client.end;
  return result;
};
Enter fullscreen mode Exit fullscreen mode
  • In this method, we initialize a client to postgres database using config
    • An example config should look like this
export const CONFIG = {
    host: "127.0.0.1",
    user: "postgres",
    port: 5433,
    ssl: false,
    database: "postgres",
    password: "letskeepitreal",
};
Enter fullscreen mode Exit fullscreen mode

⚠️ Please do not commit your config to git or any other code hosting service

  • Once the client is initialized, we execute the query passed as input
  • Disconnect the client
  • Finally return the result of executed query.

Q: Why are we using Promises ?
We use Promise for three major reasons

  • pg.Client operations are asynchronous
    • Makes the code easy to follow
    • Provides better control than callbacks

Fetch items from database

Add a method to fetch item(s) based on id

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);
  });
};
Enter fullscreen mode Exit fullscreen mode
  • In this method, we select records from item table that match the given id
  • If there are no such items, we throw an exception
  • Else pick the first item row from the resulting database rows
  • Finally convert the database item row into our item interface using to_item method
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"]),
    };
};
Enter fullscreen mode Exit fullscreen mode

Insert items into database

Create a method to add a given item into postgres database

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"];
  });
};
Enter fullscreen mode Exit fullscreen mode
  • In this method, we prepare the insert query that returns the id of newly inserted record
  • Next execute the query we prepared
  • If there is no change is rowCount, i.e no records are inserted, we throw an Error.
  • Else, return the id of the item we inserted

Update items in database

Create a method to update an item with given id in the database

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}`);
    }
  });
};
Enter fullscreen mode Exit fullscreen mode
  • In this method, we prepare the update query based on the item and item we receive
  • Next execute the query we prepared
  • If there is no change is rowCount, i.e no records are updated , we throw an Error.

Fetch users from database

Add a method to fetch users based on id

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);
    });
  };
Enter fullscreen mode Exit fullscreen mode
  • In the above method, we select records from user table that match the given id
  • If there are no such users, we throw an exception
  • Else, pick the first item from the resulting database rows
  • Finally convert the database item row into our user interface using to_user method
const to_user = (userRow: pg.QueryResult<any>): user => {
    return {
        id: parseInt(userRow["id"]),
        first_name: userRow["first_name"],
        last_name: userRow["last_name"],
    };
};
Enter fullscreen mode Exit fullscreen mode

Insert users into database

Create a method to add a given user into postgres database

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"];
  });
};
Enter fullscreen mode Exit fullscreen mode
  • In this method, we prepare the insert query that returns the id of newly inserted record
  • Next execute the query we prepared
  • If there is no change is rowCount, i.e no records are inserted, we throw an Error.
  • Else, return the id of the user we inserted

Update users in database

Create a method to update an user with given id in the database

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}`);
    }
  });
};
Enter fullscreen mode Exit fullscreen mode
  • In this method, we prepare the update query based on the item and item we receive
  • Next execute the query we prepared
  • If there is no change is rowCount, i.e no records are updated , we throw an Error.

Combining all the operations together our datasource.ts module should look like below

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}`);
}
});
};
view raw datasource.ts hosted with ❤ by GitHub

Connect datasource to items router

In this section, we will connect HTTP endpoints in user router with relevant methods in data source

  • Import methods from data source
import { add_item, get_item, update_item } from "./datasource";
Enter fullscreen mode Exit fullscreen mode

GET Request

// 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);
  }
});
Enter fullscreen mode Exit fullscreen mode
  • Call get_item based on id we receive from request parameters
  • If there is an item, send the item as response
  • If there is no item, send an message Cannot find item ...
  • If there is an error, send the error message

POST Request

// 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);
  }
});
Enter fullscreen mode Exit fullscreen mode
  • Call add_item based on the item we receive from request body
  • If there is an error, send the error message as response
  • Else, return the id as response

PUT Request

// 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);
  }
});
Enter fullscreen mode Exit fullscreen mode
  • Call update_item based on the item we receive from request body and id we receive in request parameters
  • If there is an error, send the error message as response
  • Else, return the id as response

Combining all requests and response, our updated items router should look below

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);
}
});
view raw items.ts hosted with ❤ by GitHub

Connect datasource to users router

In this section, we will connect HTTP endpoints in user router with relevant methods in data source

  • Import methods from data source
import { add_user, get_user, update_user } from "./datasource";
Enter fullscreen mode Exit fullscreen mode

GET Request

// 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);
  }
});
Enter fullscreen mode Exit fullscreen mode
  • Call get_user based on id we receive from request parameters
  • If there is an item, send the item as response
  • If there is no item, send an message Cannot find user ...
  • If there is an error, send the error message

POST Request

// 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);
  }
});
Enter fullscreen mode Exit fullscreen mode
  • Call add_user based on the user we receive from request body
  • If there is an error, send the error message as response
  • Else, return the id as response

PUT Request

// 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);
  }
});

Enter fullscreen mode Exit fullscreen mode
  • Call update_user based on the user we receive from request body and id we receive in request parameters
  • If there is an error, send the error message as response
  • Else, return the id as response

Combining all requests and response, our updated users router should look below

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);
}
});
view raw users.ts hosted with ❤ by GitHub

Now we are ready to run our server

Running the server

  • If you are using node to run your server, use the flag --es-module-specifier-resolution=node
  • This tells node to import modules without extensions .js or .mjs
  • Update your start script in package.json
  "scripts": {
    "start": "tsc && node --es-module-specifier-resolution=node dist/index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
..
Enter fullscreen mode Exit fullscreen mode
  • Run your server by using the command npm start
  • You should see a message similar to below on your terminal
$ npm start

> tdsvc@0.1.0 start D:\Workspace\blog-series\tdsvc
> tsc && node --es-module-specifier-resolution=node dist/index.js  

server is listening on port: 3000
Enter fullscreen mode Exit fullscreen mode

Testing the requests

  • Run the server
  • Open postman app or any other REST Client

Requests on item

  • Send a GET request to read an item

    • Method: GET
    • URL: http://localhost:3000/tdsvc/item/1
    • You should see a response as shown below GET_ITEM
  • Send a POST request to write an item

    • Method: POST
    • URL: http://localhost:3000/tdsvc/item/
    • Request Body with json format
      {
        "id": 4,
        "task": "Buy Corn",
        "priority": 4,
        "date": "2021-05-03"
      }
    
    • You should see a response as shown below POST_ITEM
  • Send a PUT request to update an item

    • Method: PUT
    • URL: http://localhost:3000/tdsvc/item/4
    • Request Body with json format
       {
         "id": 4,
         "task": "Buy Corn Chips",
         "priority": 4,
         "date": "2021-05-04"
       }
    
    • You should see a response as shown below PUT_ITEM

Requests on user

  • Send a GET request to read an user

    • Method: GET
    • URL: http://localhost:3000/tdsvc/user/1
    • You should see a response as shown below GET_USER
  • Send a POST request to write an user

    • Method: POST
    • URL: http://localhost:3000/tdsvc/user/
    • Request Body with json format
      {
        "id": -1,
        "first_name": "Jeremey",
        "last_name": "Solo"
      }
    
    • You should see a response as shown below POST_USER
  • Send a PUT request to update an user

    • Method: PUT
    • URL: http://localhost:3000/tdsvc/user/1
    • Request Body with json format
       {
         "id": 4,
         "first_name": "Jeremy",
         "last_name": "Solo"
       }
    
    • You should see a response as shown below PUT_USER

Please check the full code in this commit

❤️ Congratulations 👏, you have successfully added PostgreSQL to your node server running on Express.

Thanks for reading through the entire article. Please reach out with questions, comments and/or feedback.

Top comments (2)

Collapse
 
heyrohit profile image
Rohit Gupta

This is amazing. Keep up.
My one cent though, for query execution, every time the method is called, a new instance of pg client is created. I guess this should be a global variable that should connect from a available pool of connections to execute.

Collapse
 
achukka profile image
Aditya Chukka

Hi Rohit,
Thanks for the suggestion. I agree, kept it simple for the sake of post, but in PRODUCTION, you should have a single instance of client per a single instance of service.