How to Retrieve Multiple Rows with Prisma

Prisma is a powerful ORM for Node.js that simplifies database interactions. Here, we will walk through how to retrieve multiple rows from a database using Prisma's findMany function.

Setting up the Function to Get Multiple Rows

To retrieve multiple rows from a table, we use Prisma’s findMany method. In this example, we are fetching a list of tours from the database. The function will handle different cases where we either fetch all tours or filter them based on a search term.

Basic Setup

Here’s a basic function to fetch all tours:

 
export const getAllTours = async (searchTerm = null) => {
    let tours;
    
    if (!searchTerm) {
        // Fetch all tours ordered by city in ascending order
        tours = await prisma.tour.findMany({
            orderBy: {
                city: 'asc'
            }
        });
    } else {
        // Fetch tours that match the search term in either city or country
        tours = await prisma.tour.findMany({
            where: {
                OR: [
                    { city: { contains: searchTerm } },
                    { country: { contains: searchTerm } }
                ]
            },
            orderBy: {
                city: 'asc'
            }
        });
    }
    
    return tours;
};
            

In this code:

• prisma.tour.findMany() is used to retrieve multiple rows from the tour table.
• orderBy sorts the results by city in ascending order.
• When a searchTerm is provided, we filter the results by either the city or country fields using Prisma’s contains option for partial matches.

Handling Conditions

The function has two distinct paths:

1. If no search term is provided, it retrieves all tours ordered by city.
2. If a search term is present, it filters the results based on whether the city or country contains the search term.

Optimizing Queries

For large datasets, it’s important to limit the number of rows fetched or implement pagination. Prisma’s findMany method allows you to add take and skip options to control the number of records returned and pagination.

const tours = await prisma.tour.findMany({
    where: {
        OR: [
            { city: { contains: searchTerm } },
            { country: { contains: searchTerm } }
        ]
    },
    orderBy: {
        city: 'asc'
    },
    take: 10, // Limit to 10 results
    skip: 0   // Skip the first 0 records (useful for pagination)
});
            

Conclusion

Using Prisma’s findMany method makes it easy to retrieve multiple rows from your database. The ability to filter and sort data with simple queries makes Prisma an efficient tool for database operations in Node.js applications. Try out these examples in your project to streamline your database queries!