Tutorial: Create a Headless API System with Next.js and PostgreSQL

What is Next.js?

Next.js is a popular open-source React framework developed by Vercel. It enables developers to build server-side rendering (SSR) and static web applications with ease. Next.js comes with a variety of built-in features such as:

• File-based routing: Simplifies the creation of routes within the application.
• Server-side rendering: Enhances SEO and performance by rendering pages on the server.
• Static site generation: Allows pre-rendering of pages at build time.
• API routes: Facilitates the creation of API endpoints without the need for an additional backend framework.

What is PostgreSQL?

PostgreSQL is an advanced, open-source relational database management system (RDBMS). It is known for its reliability, feature-richness, and performance.

PostgreSQL supports a wide range of data types and provides robust querying capabilities, making it a popular choice for many applications. Key features of PostgreSQL include:

• ACID compliance: Ensures data integrity and reliability.
• Support for JSON and JSONB: Facilitates the storage and manipulation of JSON data.
• Extensible and customizable: Allows users to define their own data types, operators, and functions.
• Strong security features: Provides various authentication methods and data encryption.

What is a Headless System?

A headless system is an architecture where the backend (or the "head") is decoupled from the frontend. In a headless setup, the backend only provides data through APIs (Application Programming Interfaces), and the frontend can be built using any technology or framework that can consume these APIs. This separation offers several benefits:

Building a headless API system with Next.js and PostgreSQL combines the best of both worlds: a powerful, modern framework for your backend and frontend, and a reliable, feature-rich database.

This setup not only enhances performance and scalability but also provides the flexibility needed to future-proof your applications. In the next sections, we will dive into the step-by-step process of setting up this system, creating CRUD operations, and connecting your Next.js application to a PostgreSQL database.

Step 1: Setup Next.js Project

First, create a new Next.js project. Open your terminal and run:

npx create-next-app@latest nextjs-api-postgres
cd nextjs-api-postgres

Step 2: Install Dependencies

Next, install the necessary dependencies. You will need pg for PostgreSQL and next-connect for handling API routes.

npm install pg next-connect

Step 3: Setup PostgreSQL Connection

Create a new file to handle the PostgreSQL connection. Create a folder named lib and inside it, create a file named db.js.

mkdir lib
touch lib/db.js

In lib/db.js, add the following code to establish the connection:

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

const pool = new Pool({
  user: 'your_db_user',
  host: 'your_db_host',
  database: 'your_db_name',
  password: 'your_db_password',
  port: 5432,
});

module.exports = {
  query: (text, params) => pool.query(text, params),
};

Replace your_db_user, your_db_host, your_db_name, and your_db_password with your actual PostgreSQL credentials.

Step 4: Create API Route

Now, create an API route. Inside the pages/api directory, create a new file named items.js.

touch pages/api/items.js

In pages/api/items.js, add the following code:

import nc from 'next-connect';
import db from '../../lib/db';

const handler = nc()
  .get(async (req, res) => {
    try {
      const result = await db.query('SELECT * FROM items');
      res.json(result.rows);
    } catch (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  })
  .post(async (req, res) => {
    const { name, description } = req.body;
    try {
      const result = await db.query(
        'INSERT INTO items (name, description) VALUES ($1, $2) RETURNING *',
        [name, description]
      );
      res.status(201).json(result.rows[0]);
    } catch (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  });

export default handler;

Step 5: Setup PostgreSQL Database

Make sure you have a PostgreSQL database set up with a table named items. You can create the table using the following SQL command:

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  description TEXT
);

Step 6: Run the Project

Finally, run your Next.js project:

npm run dev

Step 7: Test the API

You can test the API endpoints using a tool like Postman or cURL:

• GET request to /api/items to fetch all items.
• POST request to /api/items with a JSON body to create a new item:

{
  "name": "Sample Item",
  "description": "This is a sample item description."
}

This setup provides a basic API headless system using Next.js and PostgreSQL. You can extend it by adding more routes, implementing authentication, or improving error handling as needed.

To add full CRUD (Create, Read, Update, Delete) functionality to the API, you can extend the existing items.js file in the pages/api directory. Here’s how you can do it:

Step 8: Extend the API Routes

Update pages/api/items.js to handle all CRUD operations:

import nc from 'next-connect';
import db from '../../lib/db';

const handler = nc()
  .get(async (req, res) => {
    try {
      const result = await db.query('SELECT * FROM items');
      res.json(result.rows);
    } catch (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  })
  .post(async (req, res) => {
    const { name, description } = req.body;
    try {
      const result = await db.query(
        'INSERT INTO items (name, description) VALUES ($1, $2) RETURNING *',
        [name, description]
      );
      res.status(201).json(result.rows[0]);
    } catch (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  });

export default handler;

Next, create a new file pages/api/items/[id].js to handle the Read, Update, and Delete operations for a single item:

touch pages/api/items/[id].js

In pages/api/items/[id].js, add the following code:

import nc from 'next-connect';
import db from '../../../lib/db';

const handler = nc()
  .get(async (req, res) => {
    const { id } = req.query;
    try {
      const result = await db.query('SELECT * FROM items WHERE id = $1', [id]);
      if (result.rows.length === 0) {
        return res.status(404).json({ error: 'Item not found' });
      }
      res.json(result.rows[0]);
    } catch (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  })
  .put(async (req, res) => {
    const { id } = req.query;
    const { name, description } = req.body;
    try {
      const result = await db.query(
        'UPDATE items SET name = $1, description = $2 WHERE id = $3 RETURNING *',
        [name, description, id]
      );
      if (result.rows.length === 0) {
        return res.status(404).json({ error: 'Item not found' });
      }
      res.json(result.rows[0]);
    } catch (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  })
  .delete(async (req, res) => {
    const { id } = req.query;
    try {
      const result = await db.query('DELETE FROM items WHERE id = $1 RETURNING *', [id]);
      if (result.rows.length === 0) {
        return res.status(404).json({ error: 'Item not found' });
      }
      res.json({ message: 'Item deleted successfully' });
    } catch (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    }
  });

export default handler;

Step 9: Test the CRUD API

You can test the following API endpoints using a tool like Postman or cURL:

• GET request to /api/items to fetch all items.
• POST request to /api/items with a JSON body to create a new item:

{
  "name": "Sample Item",
  "description": "This is a sample item description."
}

• GET request to /api/items/[id] to fetch a single item by its ID.
• PUT request to /api/items/[id] with a JSON body to update an item:

{
  "name": "Updated Item",
  "description": "This is an updated item description."
}

• DELETE request to /api/items/[id] to delete an item by its ID.

Example SQL Queries for Testing

You can use the following SQL queries to create and manage the items table in your PostgreSQL database:

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  description TEXT
);

INSERT INTO items (name, description) VALUES ('Sample Item 1', 'Description for sample item 1');
INSERT INTO items (name, description) VALUES ('Sample Item 2', 'Description for sample item 2');

This setup provides a complete CRUD API using Next.js and PostgreSQL. You can extend it further by adding validation, authentication, and other features as needed.