{"id":261,"date":"2022-03-09T11:50:38","date_gmt":"2022-03-09T11:50:38","guid":{"rendered":"https:\/\/www.kindsonthegenius.com\/nodejs\/?p=261"},"modified":"2022-03-10T09:42:19","modified_gmt":"2022-03-10T09:42:19","slug":"node-js-create-a-node-js-api-using-typescript-part-2-postgresql","status":"publish","type":"post","link":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/","title":{"rendered":"Node.js – Create a Node.js API and TypeScript (Part 2 – PostgreSQL)"},"content":{"rendered":"

In this tutorial, we would connect to PostgreSQL and write the function to perform CRUD operations using TypeScript.<\/p>\n

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

     <\/p>\n

    1. GET Request for List of Countries<\/strong><\/h4>\n

    Step 1<\/strong> – Inside the src folder, create a file named connection.ts<\/strong>. This would contain the connection parameters for PostgreSQL. The content is given below:<\/p>\n

    import<\/span> pgPromise from 'pg-promise'<\/span>;\r\nconst<\/span> pg =<\/span> pgPromise({});\r\nexport<\/span> const<\/span> db =<\/span> pg(\"postgres:\/\/postgres:password@localhost:5432\/fleetdb\"<\/span>);\r\n<\/pre>\n
     <\/p>\n
    Step 2<\/strong> – Install pg-promise<\/strong><\/em> using npm<\/p>\n
    Step 3<\/strong> – Create a file inside the services folder named country-service.ts<\/strong>. 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<\/p>\n
    import<\/span> {db} from '..\/connection'<\/span>\r\n\r\nexport<\/span> const<\/span> getCountries =<\/span> async () =><\/span> {\r\n    return<\/span> await db.query('SELECT * FROM Country'<\/span>)\r\n}\r\n<\/pre>\n
     <\/p>\n
    Step 4<\/strong> – 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.<\/p>\n
    import<\/span> {Request, Response} from 'express'<\/span>;\r\nimport<\/span> *<\/span> as<\/span> countryService from '..\/services\/country-service'<\/span>\r\n\r\nconst<\/span> getCountries =<\/span> async (req: Request<\/span>, res: Response<\/span>) =><\/span> {\r\n    countryService.getCountries().then(\r\n        (countries) =><\/span> {\r\n            res.send(countries);\r\n        }\r\n    )\r\n};\r\n\r\nexport<\/span> default<\/span> {getCountries}\r\n<\/pre>\n
     <\/p>\n
    Step 5<\/strong> – You now need to modify the app-routes file to include the \/countries route. The content would now be:<\/p>\n
    import<\/span> express from 'express'<\/span>\r\nimport<\/span> controller from '..\/controllers\/home-controller'<\/span>\r\nimport<\/span> countryController from '..\/controllers\/country-controller'<\/span>\r\n\r\nconst<\/span> router =<\/span> express.Router();\r\n\r\nrouter.get('\/home'<\/span>, controller.getHome);\r\nrouter.get('\/countries'<\/span>, countryController.getCountries)\r\n\r\nexport<\/span> =<\/span> router;\r\n<\/pre>\n
     <\/p>\n
    A this point, you can start the application and visit the \/countries route. You’ll sure to get list of countries as response.<\/p>\n
     <\/p>\n
    2. Integrate Routing into the Controller<\/strong><\/h4>\n
    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:<\/p>\n
    Step 1<\/strong> – Delete the routes directory. This is because we would now have to specify the routes in the controller files just like in Spring Boot!<\/p>\n
    Step 2<\/strong> – Modify the home-controller.ts<\/strong><\/em> file to include router like so.<\/p>\n
    const<\/span> router =<\/span> express.Router();\r\n\r\nrouter.get('\/home'<\/span>, (req: Request<\/span>, res: Response<\/span>) =><\/span> {\r\n    return<\/span> res.send(service.goHome());\r\n})\r\n\r\nexport<\/span> default<\/span> router\r\n<\/pre>\n
     <\/p>\n
    Step 3<\/strong> – Also modify the country-controller to include router like this:<\/p>\n
    const<\/span> router =<\/span> express.Router();\r\n\r\nrouter.get('\/countries'<\/span>, (req: Request<\/span>, res: Response<\/span>) =><\/span> {\r\n    countryService.getCountries().then(\r\n        (countries) =><\/span> {\r\n            res.send(countries);\r\n        }\r\n    );\r\n});\r\n\r\nexport<\/span> default<\/span> router\r\n<\/pre>\n
     <\/p>\n
    Step 4<\/strong> – 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:<\/p>\n
    import<\/span> http from 'http'<\/span>\r\nimport<\/span> express, {Express} from 'express'<\/span>\r\nimport<\/span> *<\/span> as<\/span> homeController from '.\/controllers\/home-controller'<\/span>\r\nimport<\/span> *<\/span> as<\/span> countryController from '.\/controllers\/country-controller'<\/span>\r\n\r\nconst<\/span> router: Express<\/span> =<\/span> express();\r\n\r\nrouter.use(''<\/span>,\r\n    homeController.default<\/span>,\r\n    countryController.default<\/span>\r\n)\r\n\r\nconst<\/span> httpServer =<\/span> http.createServer(router)\r\n\r\nhttpServer.listen(6060<\/span>, ()=><\/span>{\r\n    console.log(`<\/span>Server is running at port 6060<\/span>`<\/span>);\r\n})\r\n<\/pre>\n
    You can now test the application once again to make sure everything works.<\/p>\n
     <\/p>\n
    3. Get Countries by Id<\/strong><\/h4>\n
    Now we want to see how we can pass a url parameter across to the controller.<\/p>\n
    Step 1<\/strong> – Create a method getCountryById in the country-service.ts file<\/p>\n
    export<\/span> async function<\/span> getCountryById(id: number<\/span>) {\r\n    return<\/span> await db.any('SELECT * FROM Country WHERE id = $1'<\/span>, [id])\r\n}\r\n<\/pre>\n
     <\/p>\n
    Step 2<\/strong> – Create\u00a0 function in the country-controller like this:<\/p>\n
    router.get('\/countries\/:id'<\/span>, (req: Request<\/span>, res: Response<\/span>) =><\/span> {\r\n    countryService.getCountryById(parseInt<\/span>(req.params.id)).then(\r\n        (country) =><\/span> {\r\n            res.send(country);\r\n        }\r\n    );\r\n});\r\n<\/pre>\n
     <\/p>\n
    At this point, you can test the application to and access the url \/countries\/1.<\/p>\n
    In the next part, we would write the methods to perform POST, PUT and DELETE operation.<\/p>\n","protected":false},"excerpt":{"rendered":"
    In this tutorial, we would connect to PostgreSQL and write the function to perform CRUD operations using TypeScript. GET Request for List of Countries Integrate … <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[32,11,31],"class_list":["post-261","post","type-post","status-publish","format-standard","hentry","category-node-js-tutorials","tag-postgresql","tag-rest-api","tag-typescript"],"yoast_head":"\nNode.js - Create a Node.js API and TypeScript (Part 2 - PostgreSQL) - Node.js Tutorials<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will create a REST API in Node.js and write the methods to get list of countries and getById\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Node.js - Create a Node.js API and TypeScript (Part 2 - PostgreSQL) - Node.js Tutorials\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will create a REST API in Node.js and write the methods to get list of countries and getById\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Node.js Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2022-03-09T11:50:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-03-10T09:42:19+00:00\" \/>\n<meta name=\"author\" content=\"kindsonthegenius\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"kindsonthegenius\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/\"},\"author\":{\"name\":\"kindsonthegenius\",\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/#\\\/schema\\\/person\\\/7f8fc5792578d2ff54003fcebe6c46b5\"},\"headline\":\"Node.js – Create a Node.js API and TypeScript (Part 2 – PostgreSQL)\",\"datePublished\":\"2022-03-09T11:50:38+00:00\",\"dateModified\":\"2022-03-10T09:42:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/\"},\"wordCount\":429,\"commentCount\":0,\"keywords\":[\"PostgreSQL\",\"REST API\",\"TypeScript\"],\"articleSection\":[\"Node.js Tutorials\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/\",\"name\":\"Node.js - Create a Node.js API and TypeScript (Part 2 - PostgreSQL) - Node.js Tutorials\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/#website\"},\"datePublished\":\"2022-03-09T11:50:38+00:00\",\"dateModified\":\"2022-03-10T09:42:19+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/#\\\/schema\\\/person\\\/7f8fc5792578d2ff54003fcebe6c46b5\"},\"description\":\"In this tutorial, you will create a REST API in Node.js and write the methods to get list of countries and getById\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Node.js – Create a Node.js API and TypeScript (Part 2 – PostgreSQL)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/#website\",\"url\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/\",\"name\":\"Node.js Tutorials\",\"description\":\"Best Node.js Tutorials\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/#\\\/schema\\\/person\\\/7f8fc5792578d2ff54003fcebe6c46b5\",\"name\":\"kindsonthegenius\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b9d710de456c3d85e5614c3a6992fa3d527425e2ab32b8bd5d85bfbaa235004b?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b9d710de456c3d85e5614c3a6992fa3d527425e2ab32b8bd5d85bfbaa235004b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b9d710de456c3d85e5614c3a6992fa3d527425e2ab32b8bd5d85bfbaa235004b?s=96&d=mm&r=g\",\"caption\":\"kindsonthegenius\"},\"url\":\"https:\\\/\\\/www.kindsonthegenius.com\\\/nodejs\\\/author\\\/kindsonthegenius-3\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Node.js - Create a Node.js API and TypeScript (Part 2 - PostgreSQL) - Node.js Tutorials","description":"In this tutorial, you will create a REST API in Node.js and write the methods to get list of countries and getById","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Node.js - Create a Node.js API and TypeScript (Part 2 - PostgreSQL) - Node.js Tutorials","og_description":"In this tutorial, you will create a REST API in Node.js and write the methods to get list of countries and getById","og_url":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/","og_site_name":"Node.js Tutorials","article_published_time":"2022-03-09T11:50:38+00:00","article_modified_time":"2022-03-10T09:42:19+00:00","author":"kindsonthegenius","twitter_card":"summary_large_image","twitter_misc":{"Written by":"kindsonthegenius","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/"},"author":{"name":"kindsonthegenius","@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/#\/schema\/person\/7f8fc5792578d2ff54003fcebe6c46b5"},"headline":"Node.js – Create a Node.js API and TypeScript (Part 2 – PostgreSQL)","datePublished":"2022-03-09T11:50:38+00:00","dateModified":"2022-03-10T09:42:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/"},"wordCount":429,"commentCount":0,"keywords":["PostgreSQL","REST API","TypeScript"],"articleSection":["Node.js Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/","url":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/","name":"Node.js - Create a Node.js API and TypeScript (Part 2 - PostgreSQL) - Node.js Tutorials","isPartOf":{"@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/#website"},"datePublished":"2022-03-09T11:50:38+00:00","dateModified":"2022-03-10T09:42:19+00:00","author":{"@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/#\/schema\/person\/7f8fc5792578d2ff54003fcebe6c46b5"},"description":"In this tutorial, you will create a REST API in Node.js and write the methods to get list of countries and getById","breadcrumb":{"@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/node-js-create-a-node-js-api-using-typescript-part-2-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.kindsonthegenius.com\/nodejs\/"},{"@type":"ListItem","position":2,"name":"Node.js – Create a Node.js API and TypeScript (Part 2 – PostgreSQL)"}]},{"@type":"WebSite","@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/#website","url":"https:\/\/www.kindsonthegenius.com\/nodejs\/","name":"Node.js Tutorials","description":"Best Node.js Tutorials","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.kindsonthegenius.com\/nodejs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.kindsonthegenius.com\/nodejs\/#\/schema\/person\/7f8fc5792578d2ff54003fcebe6c46b5","name":"kindsonthegenius","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/b9d710de456c3d85e5614c3a6992fa3d527425e2ab32b8bd5d85bfbaa235004b?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/b9d710de456c3d85e5614c3a6992fa3d527425e2ab32b8bd5d85bfbaa235004b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b9d710de456c3d85e5614c3a6992fa3d527425e2ab32b8bd5d85bfbaa235004b?s=96&d=mm&r=g","caption":"kindsonthegenius"},"url":"https:\/\/www.kindsonthegenius.com\/nodejs\/author\/kindsonthegenius-3\/"}]}},"_links":{"self":[{"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/posts\/261","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/comments?post=261"}],"version-history":[{"count":9,"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/posts\/261\/revisions"}],"predecessor-version":[{"id":277,"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/posts\/261\/revisions\/277"}],"wp:attachment":[{"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/media?parent=261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/categories?post=261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/nodejs\/wp-json\/wp\/v2\/tags?post=261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}