DEV Community

loading...

Add PostgreSQL to Express Server

Aditya Chukka
・9 min read

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

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

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

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

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

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.

Discussion (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 Author

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.