Hello, in this article we will try to answer question how to create api in Node.js with PostgreSQL? Programming is not my vertical expertise, i will try my best to demonstrate.

How to create API in Node.js with Postgresql

You need to download and install Node.js runtime first. If you are planning an enterprise or going live project I would suggest you to download and install LTS version of the runtime.

In bottom of this article added two chapters to describe APIs and Node.Js runtime.

Initiate The Project

Node.js projects can be managed by the package manager called npm. To initiate a new project you can issue npm init command:

PS C:\Users\010999862\NodeWork\PgApi> npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sensible defaults.

See `npm help init` for definitive documentation on these fields
and exactly what they do.

Use `npm install <pkg>` afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
package name: (pgapi)
version: (1.0.0)
description: Node js api example with PostgreSQL
entry point: (index.js)
test command:
git repository:
keywords:
author: Yiğit Özdemir
license: (ISC) MIT
About to write to C:\Users\010999862\NodeWork\PgApi\package.json:

{
  "name": "pgapi",
  "version": "1.0.0",
  "description": "Node js api example with PostgreSQL",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "Yiğit Özdemir",
  "license": "MIT"
}


Is this OK? (yes) yes
npm notice
npm notice New major version of npm available! 9.6.7 -> 10.2.5
npm notice Changelog: https://github.com/npm/cli/releases/tag/v10.2.5
npm notice Run npm install -g npm@10.2.5 to update!
npm notice

Install Required Packages

After initiating out project we will need to install 2 packages. First one is express which is a web framework (a really simple, performant and useful web framework) to handle web requests.

PS C:\Users\010999862\NodeWork\PgApi> npm install express --save

added 62 packages, and audited 63 packages in 2s

11 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

Other npm package we need is pg, it is a PostgreSQL database client to consume database.

PS C:\Users\010999862\NodeWork\PgApi> npm install pg --save

added 16 packages, and audited 79 packages in 3s

11 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

Create Sample Node.Js Application

Let’s create a sample NodeJs application, it is a first step to create an API.

var express = require('express');
var pg = require('pg');


var app = express();

var pgConfig = {
    user: 'postgres',
    password: 'passw0rd',
    host: 'localhost',
    port: 5432
};


app.get('/', (req, res) => {
    var pgClient = new pg.Client(pgConfig);
    pgClient.connect()
    .then((client) => {
        pgClient.end();
        res.send('This is a NodeJs application');
    }).catch(e => {
        res.send(e.stack);
    });
    
});

app.get('/version', (req, res) => {

});


app.listen(3000, () => {
    console.log("Application started");
});

Run application with node index.js command.

When you navigate http://localhost:3000 address, you will see:

That shows, your application is running.

Connect PostgreSQL and Query Version

app.get, app.post, app.put (or router.get, router.post etc) etc is methods to receieve HTTP requests and response to them.

app.get('/version', (req, res) => {
    var pgClient = new pg.Client(pgConfig);

    pgClient.connect()
    .then(() => {
        pgClient.query('SELECT VERSION() as version')
        .then(result => {
            res.send(result.rows[0]);
        })
        .catch(err => {
            res.send(err.stack);
        })
        return;
    }).catch(e => {
        console.log("connection error");
        res.send(e.stack);
        return;
    });

});

If you navigate to http://localhost:3000/verison you can see version information json. Modern browsers format them by default and displays them in a beautified way.

Cities API

If you want to have an API endpoint that shows cities, you can query it with client and send back to the requester in json format:

app.get('/cities', (req, res) => {
    var pgClient = new pg.Client(pgConfig);

    pgClient.connect()
    .then(() => {
        pgClient.query('SELECT * FROM cities')
        .then(result => {
            res.send(result.rows);
        })
        .catch(err => {
            res.send(err.stack);
        })
        return;
    }).catch(e => {
        console.log("connection error");
        res.send(e.stack);
        return;
    });
});

As you can see, it returns list of cities in JSON format.

To Sum Up

Of course REST API development is not simple as connecting to a database, querying and sending back. Authentication and authorization, data security, integration points, design, scaling, CI/CD… All of them are related topic with REST APIs.

In this article I just tried to explaing how to create api in node.js with postgresql topic. My example is pretty simple but i tried to explain as much as i can do.

Feel free to share your thoughts, ask questions, or provide feedback in the comments section below. Your input is valuable and can spark insightful discussions!

What is an API

Application Programming Interface.

The term “API” stands for Application Programming Interface, which serves as a bridge between different software applications, allowing them to communicate with each other. In the context of web development and software engineering, an API is a crucial component that enables developers to access the functionality of a particular platform or service, such as WordPress.

When you are developing an external application to interact with WordPress, leveraging WordPress APIs becomes essential. These APIs provide a set of rules and protocols that dictate how different software components should interact, enabling seamless integration and interoperability. In essence, an API can be thought of as a set of tools and protocols that developers utilize to build applications that can effectively communicate and interact with WordPress.

In summary, an API is a fundamental element in modern software development, empowering developers to create dynamic and interconnected applications that can leverage the capabilities of platforms like WordPress while offering enhanced functionality and user experiences.

What is Node.JS

“Node.js is a runtime environment that allows you to run JavaScript on the server side. It uses the V8 JavaScript engine, the same engine that powers Google Chrome, to execute code. This means that you can use JavaScript not only for client-side scripting, but also for server-side scripting. Node.js is commonly used to build scalable network applications and has a large ecosystem of libraries and packages available through npm, the Node Package Manager. Its event-driven, non-blocking I/O model makes it efficient and lightweight, which is particularly advantageous for real-time applications such as chat, streaming, and gaming platforms.”

I hope this provides a more comprehensive understanding of Node.js.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch