Creating Custom APIs with PostgreSQL

This tutorial will guide you through the process of creating custom APIs to interact with a PostgreSQL database. You will learn how to set up a Node.js server, connect to PostgreSQL, and create API endpoints for various operations.

1. Prerequisites

Before you begin, make sure you have the following:

• PostgreSQL installed and running on your machine or server.
• Basic understanding of PostgreSQL and SQL queries.
• Basic knowledge of web development and RESTful APIs.
• A web server or application server to host the API (e.g., Node.js with Express).

2. Setting Up the Environment

We will use Node.js with the Express framework to create our API. Follow these steps to set up the environment:

1. Install Node.js from the official website: https://nodejs.org/
2. Create a new directory for your project and navigate to it in your terminal:

mkdir custom-api
cd custom-api

3. Initialize a new Node.js project:

npm init -y

4. Install the required dependencies:

npm install express pg

3. Creating the API

Follow these steps to create the API:

1. Create a new file named index.js in your project directory.
2. Open index.js and add the following code to set up the Express server and connect to PostgreSQL:

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

const app = express();
const port = 3000;

// PostgreSQL connection pool
const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});

// Middleware to parse JSON bodies
app.use(express.json());

// Routes
app.get('/users', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM users');
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

app.get('/users/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

app.post('/users', async (req, res) => {
  const { name, email } = req.body;
  try {
    const result = await pool.query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [name, email]
    );
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

app.put('/users/:id', async (req, res) => {
  const { id } = req.params;
  const { name, email } = req.body;
  try {
    const result = await pool.query(
      'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
      [name, email, id]
    );
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

app.delete('/users/:id', async (req, res) => {
  const { id } = req.params;
  try {
    await pool.query('DELETE FROM users WHERE id = $1', [id]);
    res.send('User deleted');
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}/`);
});
                

4. Testing the API

To test the API, you can use a tool like Postman or cURL. Here are some example requests:

4.1. Retrieving All Users

GET http://localhost:3000/users
            

4.2. Retrieving a Single User

GET http://localhost:3000/users/1
            

4.3. Creating a New User

POST http://localhost:3000/users
Content-Type: application/json

{
  "name": "John Doe",
  "email": "john.doe@example.com"
}
            

4.4. Updating a User

PUT http://localhost:3000/users/1
Content-Type: application/json

{
  "name": "Jane Doe",
  "email": "jane.doe@example.com"
}
            

4.5. Deleting a User

DELETE http://localhost:3000/users/1
            

5. Conclusion

In this tutorial, we covered the basics of creating a custom API to interact with PostgreSQL. We set up an Express server, connected to a PostgreSQL database, created endpoints for various CRUD operations, and tested the API. This setup can be extended to include more complex operations and integrations based on your specific requirements.