Aditya Chukka

Posted on Jun 3, 2021

Add PostgreSQL to Express Server

#node #express #postgres #typescript

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()
);

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;

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

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;

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

Define an interface for user

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

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

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

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",
};

⚠️ 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);
  });
};

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"]),
    };
};

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"];
  });
};

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

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

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"],
    };
};

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"];
  });
};

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

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";

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

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

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

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";

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

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

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

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"
  },
..

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

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
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
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

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
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
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

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.

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site

Top comments (2)

Rohit Gupta • Jun 3 '21

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.

Aditya Chukka • Jun 3 '21

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.

DEV Community