Add PostgreSQL to Express Server
Tags: node, express, postgres, typescript
Published: 2021-06-03 04:17:33

![postgres_express](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j3wm3ub5pkl0osc9qsit.png)

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

## Dependencies

- An [Express](https://expressjs.com/) Node Server running locally or in a remote server.

> Please check our earlier [post](https://dev.to/achukka/routing-requests-in-node-server-12gj) on routing requests on a node server

- A [PostgreSQL](https://www.postgresql.org/) database running locally or in a remote server.

> If you are feeling ambitious, please refer to this [commit](https://github.com/achukka/tdsvc/pull/2/commits/1683631e81ed93556d82ab822a36a44e06a2dc8b) for source code

## Database Setup

- Download and install latest version of [PostgreSQL](https://www.postgresql.org/download/)
> At the time of writing this post, the latest version if `13.3`

- Create a database initialization script `init.sql`

- Create table for `item`
```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()
);
```
- Add an initial set of `items`
```sql
-- 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`
```sql
-- 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`
```sql
-- 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` 

{% gist https://gist.github.com/achukka/df2ddda2a7978d130418033003f763e6 file=init.sql %}

## 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`
```javascript
export interface item {
  id: number;
  task: string;
  priority: number;
  date: Date;
}
```
- Define an interface for `user`
```javascript
export interface user {
  id: number;
  first_name: string;
  last_name: string;
}
```
- Your completed `models.ts` should look like below

{% gist https://gist.github.com/achukka/df2ddda2a7978d130418033003f763e6 file=models.ts %}

## Connect to PostgreSQL

Install [node-pg](https://node-postgres.com/) module
```bash
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
```javascript
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](https://node-postgres.com/api/client) to postgres database using config
  -  An example config should look like this
```javascript
export const CONFIG = {
    host: "127.0.0.1",
    user: "postgres",
    port: 5433,
    ssl: false,
    database: "postgres",
    password: "letskeepitreal",
};
```
> :warning: Please do not commit your config to git or any other code hosting service

- Once the client is initialized, we execute the [query](https://node-postgres.com/api/client#clientquery) passed as input 
- [Disconnect](https://node-postgres.com/api/client#clientend) the client
- Finally return the result of executed query.

**Q:** Why are we using Promises ?
We use `Promise` for three major reasons
  - [pg.Client](https://node-postgres.com/api/client#clientquery) 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`
```javascript
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

```javascript
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
```javascript
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
```javascript
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`
```javascript
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  
```javascript
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
```javascript
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
```javascript
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 
{% gist https://gist.github.com/achukka/df2ddda2a7978d130418033003f763e6 file=datasource.ts %}

## 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
```javascript
import { add_item, get_item, update_item } from "./datasource";
```

**GET Request**
```javascript
// 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**
```javascript
// 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**
```javascript
// 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 

{% gist https://gist.github.com/achukka/df2ddda2a7978d130418033003f763e6 file=items.ts %}


## 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
```javascript
import { add_user, get_user, update_user } from "./datasource";
```

**GET Request**
```javascript
// 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**
```javascript
// 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**
```javascript
// 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 

{% gist https://gist.github.com/achukka/df2ddda2a7978d130418033003f763e6 file=users.ts %}

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`
```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
```bash
$ 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](https://www.postman.com/) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ih3dq9e6uaw7efu2jhbr.PNG)
    
- Send a `POST` request to write an item
    - Method: `POST`
    - URL: `http://localhost:3000/tdsvc/item/`
    - Request `Body` with `json` format
      ```json
      {
        "id": 4,
        "task": "Buy Corn",
        "priority": 4,
        "date": "2021-05-03"
      }
      ```
    - You should see a response as shown below
      ![POST_ITEM](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7nx50rwp6q6ynl0xoo88.PNG)
 

- Send a `PUT` request to update an item
    - Method: `PUT`
    - URL: `http://localhost:3000/tdsvc/item/4`
    - Request `Body` with `json` format
       ```json
       {
         "id": 4,
         "task": "Buy Corn Chips",
         "priority": 4,
         "date": "2021-05-04"
       }
       ```
    - You should see a response as shown below
      ![PUT_ITEM](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ogxb05eiia2ome3uyif1.PNG)
 

### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2aitgc6oi8lfxvkxb4hc.PNG)
 

- Send a `POST` request to write an user
    - Method: `POST`
    - URL: `http://localhost:3000/tdsvc/user/`
    - Request `Body` with `json` format
      ```json
      {
        "id": -1,
        "first_name": "Jeremey",
        "last_name": "Solo"
      }
      ```
    - You should see a response as shown below
      ![POST_USER](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lfzkrz6mi14z1j50lkt0.PNG) 

- Send a `PUT` request to update an user
    - Method: `PUT`
    - URL: `http://localhost:3000/tdsvc/user/1`
    - Request `Body` with `json` format
       ```json
       {
         "id": 4,
         "first_name": "Jeremy",
         "last_name": "Solo"
       }
       ```
    - You should see a response as shown below
      ![PUT_USER](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n7d3a9v4tif7vpih1mhi.PNG)


Please check the full code in this [commit](https://github.com/achukka/tdsvc/pull/2/commits/1683631e81ed93556d82ab822a36a44e06a2dc8b)

❤️ 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.