Node.js – Create a Node.js API Using TypeScript (Part 2 – PostgreSQL)

In this tutorial, we would connect to PostgreSQL and write the function to perform CRUD operations using TypeScript.

1. GET Request for List of Countries
2. Integrate the Routing into the Controller
3. GET Country by Id
4. Make a POST Request
5. Make an UPDATE
6. DELETE A Country

1. GET Request for List of Countries

Step 1 – Inside the src folder, create a file named connection.ts. This would contain the connection parameters for PostgreSQL. The content is given below:

import pgPromise from 'pg-promise';
const pg = pgPromise({});
export const db = pg("postgres://postgres:password@localhost:5432/fleetdb");

Step 2 – Install pg-promise using npm

Step 3 – Create a file inside the services folder named country-service.ts. This file would contain the functions that executes SQL statements to perform GET, POST, PUT and DELETE on the PostgreSQL database. For now, we write only the function to retrieve list of countries

import {db} from '../connection'

export const getCountries = async () => {
    return await db.query('SELECT * FROM Country')
}

Step 4 – Create a file country-controller inside the controllers folder and here, you write the code to call the getCountries function in the country-service and send it as a response to the the REST API GET request.

import {Request, Response} from 'express';
import * as countryService from '../services/country-service'

const getCountries = async (req: Request, res: Response) => {
    countryService.getCountries().then(
        (countries) => {
            res.send(countries);
        }
    )
};

export default {getCountries}

Step 5 – You now need to modify the app-routes file to include the /countries route. The content would now be:

import express from 'express'
import controller from '../controllers/home-controller'
import countryController from '../controllers/country-controller'

const router = express.Router();

router.get('/home', controller.getHome);
router.get('/countries', countryController.getCountries)

export = router;

A this point, you can start the application and visit the /countries route. You’ll sure to get list of countries as response.

2. Integrate Routing into the Controller

You notice that we have a separate file for route and controller. Now we would integrate the two and therefore, we’ll not need the app-routes.ts file. To to this follow the steps:

Step 1 – Delete the routes directory. This is because we would now have to specify the routes in the controller files just like in Spring Boot!

Step 2 – Modify the home-controller.ts file to include router like so.

const router = express.Router();

router.get('/home', (req: Request, res: Response) => {
    return res.send(service.goHome());
})

export default router

Step 3 – Also modify the country-controller to include router like this:

const router = express.Router();

router.get('/countries', (req: Request, res: Response) => {
    countryService.getCountries().then(
        (countries) => {
            res.send(countries);
        }
    );
});

export default router

Step 4 – You now need to modify the server.ts file to now use the routes defined in the controller files. The server.ts file would now be:

import http from 'http'
import express, {Express} from 'express'
import * as homeController from './controllers/home-controller'
import * as countryController from './controllers/country-controller'

const router: Express = express();

router.use('',
    homeController.default,
    countryController.default
)

const httpServer = http.createServer(router)

httpServer.listen(6060, ()=>{
    console.log(`Server is running at port 6060`);
})

You can now test the application once again to make sure everything works.

3. Get Countries by Id

Now we want to see how we can pass a url parameter across to the controller.

Step 1 – Create a method getCountryById in the country-service.ts file

export async function getCountryById(id: number) {
    return await db.any('SELECT * FROM Country WHERE id = $1', [id])
}

Step 2 – Create  function in the country-controller like this:

router.get('/countries/:id', (req: Request, res: Response) => {
    countryService.getCountryById(parseInt(req.params.id)).then(
        (country) => {
            res.send(country);
        }
    );
});

At this point, you can test the application to and access the url /countries/1.

In the next part, we would write the methods to perform POST, PUT and DELETE operation.