How to check if query returned no rows

[DragonOsman]

Hi. I want to know how I can check if my query returned no rows.

I'm using MySQL Server version 9.1 with Next.js 15. I'm trying to build a personal library app, intended to be app where users can log in to manage a list of books they've read.

This is my code for an API route to add a book; first it tries to query the database to see if the book is already in there, and if it isn't, it adds it. Otherwise, it redirects to update route. What I know is how I can properly determine if the book is there or not by checking if the query returned any rows or not.

import connectionPool from "../../../lib/db";
import { NextRequest, NextResponse } from "next/server";
import { redirect } from "next/navigation";
import { currentUser } from "@clerk/nextjs/server";
import { QueryResult } from "mysql2";

export const POST = async (req: NextRequest) => {
  const user = await currentUser();
  if (!user) {
    return NextResponse.json({ status: 401, message: "Please log in first" });
  }

  // Check if book is already in database
  let rows: QueryResult = [];
  const { title, synopsis, author, isbn, publication_date, readerIds } = await req.json();
  const conn = await connectionPool.getConnection();
  try {
    [rows] = await conn.connection.query("SELECT * FROM books (title, isbn) WHERE title = ? AND isbn = ?",
      [title, isbn]
    );
  } catch (err) {
    console.error(err);
  }


  // If it isn't, insert it
  if (!rows) {
    const [result] = await connectionPool.query("INSERT INTO books (title, synopsis, author, isbn, publication_date, reader_ids) VALUES (?, ?, ?, ?, ?, ?)",
      [title, synopsis, author, isbn, publication_date, readerIds]
    );
    return NextResponse.json({status: 201, success: true, result});
  } else {
    // Otherwise, redirect user to update route to edit the database
    // entry and insert their ID into the reader_ids array
    redirect("/api/books/update");
  }
};

If I'm doing it wrong, what's the correct way? Do I need to write the code to add the book inside the catch block and keep the redirect function where it is? Or what?

[wellwelwel]

Hi, @DragonOsman 🙋🏻‍♂️

Based on your title, you can just check the length of rows, for example:

const isEmpty = rows.length === 0;

Now based in your example, a common/classic way is to check if there is a data in the database, is to count the rows directly in the query, for example:

SELECT COUNT(*) AS `total` FROM `books` WHERE `title` = ? AND `isbn` = ?;

It will return an array like:

rows[0].total; // number

There is also the EXISTS operator, that can also make sense.