REST API for PostgreSQL with Node.js¶

This tutorial demonstrates how to set up a system that allows you to query a PostgreSQL database deployed on UCloud by using NGINX as a reverse proxy for a Node.js application. This setup is particularly useful for creating a web API that interacts with your database securely and efficiently.

Overview: System Architecture¶

This system architecture is designed to query a PostgreSQL database through a RESTful API developed in Node.js, with NGINX functioning as a reverse proxy. This configuration not only enhances security but also improves the efficiency of interactions with the database. The architecture comprises the following components:

NGINX Server: Serves as the frontline interface for incoming HTTP requests. It is configured to act as a reverse proxy, forwarding requests to the Node.js application based on predefined routing rules, thereby abstracting the backend details from the client.
Node.js Application: Hosted behind NGINX, this application implements a RESTful API that handles HTTP requests by interacting with the PostgreSQL database. It processes requests, performs CRUD operations on the database, and returns responses back to the client through NGINX.
PostgreSQL Database: The data storage solution that stores, retrieves, and manages the data accessed and manipulated by the Node.js application. PostgreSQL offers a robust, scalable, and secure database environment for handling complex queries and large datasets.

This configuration ensures that the API is both accessible and secure, providing a streamlined interface for database operations. It is ideal for scenarios requiring database interaction from web applications, mobile apps, or external services.

Database Initialization: PostgreSQL Setup¶

First, start a new PostgreSQL database server on UCloud. In this example we use the default settings for database name, user and password.

From the job terminal window access the PostgreSQL command line interface:

$ psql

Tip

psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

ucloud=#

Here's how to set up a sample table and insert some data:

Create a sample table:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Insert sample data:

INSERT INTO products (name, price) VALUES
('Product 1', 10.50),
('Product 2', 15.75);

NGINX as Gateway: Configuring Access¶

Start a new NGINX web server on UCloud with the optional parameters:

Connect to other jobs: Select the Job ID of the running PostgreSQL database server and specify a hostname.
Configure custom links to your application: Add a public URL which will be used to connect to the database.

In the following we will use the configuration parameters:

PostgreSQL hostname: postgres-server
Public link: app-mypgapi.cloud.sdu.dk

API Crafting: Developing a RESTful Service with Node.js.¶

Open a terminal window inside the running NGINX instance and create a simple Node.js application to serve as an API for querying the database.

Initialize the project¶

Navigate to the project directory and initialize a new Node.js project:

$ mkdir pg-api && cd pg-api
$ npm init -y

Install dependencies¶

Install express for creating the server and pg for connecting to the PostgreSQL database:

$ npm install express pg

Implement the API¶

Create an app.js file with the following content to set up routes for accessing the database:

const express = require('express');
const { Pool } = require('pg');
const app = express();

const pool = new Pool({
   user: 'ucloud',
   host: 'postgres-server',
   database: 'ucloud',
   password: 'ucloud',
   port: 5432,
});

app.get('/products', async (req, res) => {
   const { rows } = await pool.query('SELECT * FROM products');
   res.json(rows);
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => console.log(`Server running on port ${PORT}`));

The app is served by default on port 3000.

Note

The connection string to the PostgreSQL database contains the hostname (in this case postgres-server) defined when we submitted the NGINX instance. The database name and access credentials are set equal to the default values.

Run the application¶

Start the Node.js application with:

$ nohup node app &

The application runs in background.

NGINX Adjustment: API Proxy Setup¶

To allow external access to the API, configure NGINX to proxy requests to the Node.js application.

Edit NGINX configuration: Modify /etc/nginx/conf.d/default.conf with the following server block:

# /etc/nginx/conf.d/default.conf
server {

  listen 8080;
  server_name localhost;

  location /api/ {
     # Strip the /api prefix before the request is forwarded to the Node.js app
     rewrite ^/api/(.*)$ /$1 break;
     proxy_pass http://localhost:3000;
     proxy_http_version 1.1;
     proxy_set_header Connection "";
     proxy_buffering off;
     proxy_set_header Host $host;
     proxy_set_header X-Real-IP $remote_addr;
     proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
     proxy_set_header X-Forwarded-Proto $scheme;
  }
}

Reload NGINX: Validate your configuration and reload NGINX to apply the changes:
```
$ nginx -t
$ nginx -s reload
```

Testing: API Fuctionality Verification¶

Finally, open a terminal window on your local host and test accessing your API through NGINX:

$ curl https://app-mypgapi.cloud.sdu.dk/api/products

Tip

[{"id":1,"name":"Product 1","price":"10.50"},{"id":2,"name":"Product 2","price":"15.75"}]

You should see a JSON response containing the data from your products table like in the example above.

Summary: System Overview¶

We have set up a Node.js application to query a PostgreSQL database deployed on UCloud and configured NGINX to securely proxy requests to this application. This approach offers a scalable and secure method to expose your database operations as a web API.