Querying data with Next.js and drizzle ORM

In the third part of our tutorial series on building a full-stack game store with Next.js. In this segment, we shift our focus to querying data with the Drizzle ORM.

Click the button below to view the full app and all of its related tutorials.

Drizzle ORM and Next.js are a potent pairing that offers a robust approach to server-side data querying. This blog is your guide to understanding and mastering the art of data retrieval using these technologies.

Whether you're a seasoned developer or just starting your journey, this blog will unravel the intricacies of data querying with Drizzle ORM and Next.js. We'll dive deep into concepts, best practices, and practical examples to equip you with the knowledge you need to harness your data effectively.

Starting

Kicking off from the end of the previous tutorial, we have by now successfully set up a connection, a schema for our Neon database along with the Drizzle client.

neonConfig.fetchConnectionCache = true

const schema = {
  ...userSchema,
  ...gameSchema,
  ...platfomsSchema,
  ...mediaSchema,
}
const sql = neon(process.env.DRIZZLE_DATABASE_URL!)
const db = drizzle(sql, { schema })

export { db }
// const result = await db.select().from(...);

We are now all set to query data on the server using Next.js and our Drizzle client

Data querying with Drizzle

Drizzle closely resembles SQL syntax for data querying. To retrieve data from a table, all you need to do is invoke the select.from function and provide it with the table object as a parameter.

const result = await db.select().from(...);

Just like SQL you can then specify different filters:

await db
	.select()
	.from(games)
	.where(eq(games.id, 10))

Applying pagination

Drizzle offers convenient functions for data pagination, enabling you to control the number of items displayed per page and skip over a specific number of records. To achieve this, you can make use of the limit and offset functions. In our context, the limit corresponds to the page size, defining how many items appear on each page, while the offset signifies the page number, determining where your pagination begins. These functions simplify the process of breaking down large datasets into manageable segments, enhancing the user experience and optimizing data retrieval.

await db
	.select()
	.from(games)
	.where(eq(games.id, 10))
    .limit(10)
    .offset(10)

Selecting related data

Using Drizzle Queries

The primary way in SQL and drizzle to fetch data that has multiple relations is using joins. However, Drizzle provides a more convenient way to select data using queries.

 db.query.libraryItems.findMany({
          where: eq(libraryItems.userId, user.id),
          with: {
            game: true,
          },
          limit: 10,
          offset: 10,
        })

Using Joins

Another way to query data in drizzle is using joins, this provides more control with nested filters and the overall shape of the data returned.

//Using joins

  const {data} = await db
    .select({ games })
    .from(gamesToCategories)
    .leftJoin(games, eq(gamesToCategories.gameId, games.id))
    .leftJoin(categories, eq(gamesToCategories.categoryId, categories.id))
    .where(and(...conditions))
    .limit(4)

In this instance, our selection is based on the joining table, rather than the games table itself. We perform a join operation that combines both the games and categories tables to retrieve games along with their corresponding categories. This approach allows us to obtain a comprehensive view of games and their associated categories, enhancing the depth of information we can access.

Final thoughts

In conclusion, mastering data querying with Drizzle ORM and Next.js unlocks a world of possibilities for developers. The ability to precisely retrieve and manipulate data empowers us to build more efficient and dynamic web applications.