API Route in Next.js 14 for Database Updates with Supabase

Let's explore how to set up an API route using Next.js 14 that interacts with a database using Supabase. This setup involves creating a backend API route in a Next.js application to handle POST requests for updating a database and retrieving the status of these updates. We will go through the entire process step-by-step, from initializing the Supabase client to defining the API route. Note that I am using the app router in this example.

Prerequisites

To follow along with this tutorial, you should have:

Node.js installed on your system.
Basic knowledge of JavaScript and Next.js.
A Supabase account, which you can set up at Supabase.

Step 1: Setting Up Your Next.js Project

If you haven't already created a Next.js project, you can start by setting one up using the following commands:

bash

npx create-next-app@latest my-next-app
cd my-next-app

This sets up a new Next.js project in the my-next-app directory.

Step 2: Adding Supabase to the Project

Install the Supabase JS Client:

Run the following command to install the Supabase client:
bash
```
npm install @supabase/supabase-js
```
Configure Environment Variables:

Create a .env.local file in the root of your project and add the following environment variables:
```
NEXT_PUBLIC_SUPABASE_URL=your_supabase_url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_supabase_anon_key
```
Replace your_supabase_url and your_supabase_anon_key with the actual values from your Supabase project settings.

Step 3: Creating the Supabase Client

Create a file named supabaseClient.js in the root directory and add the following code to initialize the Supabase client:

javascript

import { createClient } from "@supabase/supabase-js";

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY;

const supabase = createClient(supabaseUrl, supabaseAnonKey);

export default supabase;

Step 4: Writing the Database Update Function

In the lib directory, create a file named update-database.mjs and add the following function:

javascript

import supabase from "../supabaseClient";

export async function updateDatabase() {
  const { data, error } = await supabase
    .from("_update_database")
    .insert([{ update: new Date().toISOString() }]);

  if (error) {
    throw new Error(`Failed to update database: ${error.message}`);
  }

  return data;
}

This function inserts the current date and time into a table called _update_database.

the schema for the _update_database table can be:

id (int8): Unique identifier for each record
update (Timestamp): Timestamp of the update

Step 5: Creating the API Route

Create a folder named api within the app directory and add a file named route.ts. Add the following code to route.ts handle POST requests:

javascript

import { updateDatabase } from "@/lib/update-database.mjs";

export default async function handler(req, res) {
  if (req.method === "POST") {
    try {
      const data = await updateDatabase();
      res.status(200).json({
        message: "Request processed successfully",
        data: data,
      });
    } catch (error) {
      console.error("Error:", error);
      res.status(500).json({ message: "Error in processing your request" });
    }
  } else {
    res
      .status(405)
      .json({ message: "This endpoint only accepts POST requests" });
  }
}

Step 6: Testing the Setup

To test your API, you can use tools like Postman or simply use curl:

bash

curl -X POST http://localhost:3000/api/update-database

You should see a response indicating the request was processed successfully or an error message.