Welcome to the world of building RESTful APIs with Node.js and PostgreSQL! In this guide, we'll take you on an exciting journey, where we'll transform your Node.js app into a robust, scalable RESTful API using PostgreSQL as our database. Let’s dive in and have some fun along the way!

Let’s start by setting up a new Node.js project.

mkdir node-postgres-api
cd node-postgres-api
npm init -y

We need a few packages to get our project up and running.

npm install express pg body-parser

Setting Up PostgreSQL Fire up your PostgreSQL server and create a new database.

CREATE DATABASE node_postgres_api;

Creating the Database and Tables Connect to your new database and create a simple users table.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

Setting up an Express Server Create a new file named server.js and set up a basic Express server.

const express = require('express');
const bodyParser = require('body-parser');
const app = express();
const port = 3000;

app.use(bodyParser.json());

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

Connecting to PostgreSQL Now, let’s connect our server to the PostgreSQL database. Create a file named db.js.

const { Pool } = require('pg');
const pool = new Pool({
  user: 'your_postgres_username',
  host: 'localhost',
  database: 'node_postgres_api',
  password: 'your_postgres_password',
  port: 5432,
});
module.exports = pool;

5.Creating API Endpoints

Let’s add some endpoints to our API. Update your server.js file.

const express = require('express');
const bodyParser = require('body-parser');
const pool = require('./db');
const app = express();
const port = 3000;

app.use(bodyParser.json());

// Get all users
app.get('/users', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM users');
    res.json(result.rows);
  } catch (err) {
    console.error(err.message);
  }
});

// Get user by ID
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.message);
  }
});

// Create new user
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.message);
  }
});

// Update user
app.put('/users/:id', async (req, res) => {
  const { id } = req.params;
  const { name, email } = req.body;
  try {
    await pool.query('UPDATE users SET name = $1, email = $2 WHERE id = $3', [name, email, id]);
    res.json('User updated successfully');
  } catch (err) {
    console.error(err.message);
  }
});

// Delete user
app.delete('/users/:id', async (req, res) => {
  const { id } = req.params;
  try {
    await pool.query('DELETE FROM users WHERE id = $1', [id]);
    res.json('User deleted successfully');
  } catch (err) {
    console.error(err.message);
  }
});

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

Congratulations! You’ve built a RESTful API with Node.js and PostgreSQL. Now you can expand this foundation with more features and complexity as needed. Happy coding!

Feel free to leave your thoughts or questions in the comments below. Let's keep the conversation going and make coding fun together!

Step-by-Step Guide to Creating RESTful APIs with Node.js and PostgreSQL