Setting up Next.js with DrizzleORM and NeonBD

In the second part of our tutorial series on building a full-stack game store with Next.js. In this segment, we shift our focus to the nitty-gritty of database design.

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

A well-structured database is paramount to our application's functionality, and our choice is NeonDB, a serverless Postgres database.

We'll be using Drizzle ORM, an Object-Relational Mapping tool, to seamlessly manage data. This step is pivotal, as it establishes the core of our game store's data architecture, ensuring efficient data handling, optimization, and scalability as we continue to expand our game catalog.

Setting up the project

We start by setting up a new Next.js application using the npx create-next-app command. This sets up a boilerplate next application that's all we need to start building our backend and connection to our database.

The next step is to install the project dependencies that include drizzle and the NeonDB serverless driver.

npm i drizzle-orm @neondatabase/serverless
npm i -D drizzle-kit

As an optional step, we can configure our package.json file to easily run our migrations and to quickly launch the drizzle studio using the following commands

• npm run db:push
• npm run db:generate
• npm run db:studio

  "scripts": {
    "db:push": "drizzle-kit push:pg",
    "db:studio": "drizzle-kit studio",
    "db:generate": "drizzle-kit generate:pg",
    "dev": "next dev",
    "build": "next build",
    "start": "next start",
    "lint": "next lint"
  }

Connecting to NeonDB

To connect to our database, we first need to head to NeonDB's website and create a free account, this gives you access to a Postgres database. All we need from the dashboard is to copy the connection string and use it in our .env.local file.

Below is an example for how our .env file may look like.

DRIZZLE_DATABASE_URL=

DB_HOST=
DB_USER=
DB_PASSWORD=
DB_DATABASE=

Next step is to create a configuration for drizzle to specify our schema location and some other parameters

import { Config } from "drizzle-kit";
import dotenv from "dotenv";

dotenv.config({ path: ".env.local" });

export default {
  schema: "./src/db/**/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    host: process.env.DB_HOST!,
    user: process.env.DB_USER!,
    password: process.env.DB_PASSWORD!,
    database: process.env.DB_DATABASE!,
    ssl: true,
  },
} satisfies Config;

And for the final step we initialize our client, to do this lets create a db folder and inside index.ts we add the following code.

In my example I have a schema in my /user/schema.ts, this approach allows us to add a schema file for each entity.

import { neon, neonConfig } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'
import * as userSchema from './user/schema'


neonConfig.fetchConnectionCache = true

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

export { db }

Creating the users entity

Entities represent the tables that we're going to have in our database, we create a users table with Drizzle ORM by exporting a pgTable function that takes the name of the table and an object containing its attributes.

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  authId: varchar('auth_id', { length: 256 }),
  email: varchar('email', { length: 256 }),
  profilePictureUrl: varchar('profile_picture_url', { length: 256 }),
  username: varchar('username', { length: 256 }),
  firstName: varchar('first_name', { length: 256 }),
  LastName: varchar('last_name', { length: 256 }),
  createdAt: timestamp('created_at', { mode: 'date' }).defaultNow(),
})

To apply the changes to our schema, all we need to do is to run the scripts

• npm run db:generate
• npm run db:push

Generating types

Drizzle autogenerates typescript types from tables we can do this by inferring the user type

export type User = typeof users.$inferSelect
export type CreateUser = typeof users.$inferInsert

Creating relations

One to many relationships

Relationships play a crucial role in relational databases, enabling us to establish connections between data by referencing related information from different tables. A one-to-many relationship is established when a record in one table can reference one and only one record from another table. In our example, we have a Media table that references a Games table, which means that each media entry can be linked to only one game. To implement this, we create a foreign key in the Media table and define a one-to-many relationship.

export const media = pgTable("media", {
  id: serial("media_id").primaryKey().notNull(),
  mediaUrl: varchar("media_url", { length: 256 }).notNull(),
  createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
  mediaType: mediaType("media_type"),
  gameId: integer("game_id").references(() => games.id),
});

export const mediaRelations = relations(media, ({ one }) => ({
  gameId: one(games, {
    fields: [media.gameId],
    references: [games.id],
  }),
}));

Many to many relationships

Much like the previous example, many-to-many relationships operate similarly to one-to-many relationships but with a subtle distinction. Instead of directly connecting tables, we introduce an intermediary or "join" table that stores a record for each relationship between media and games.

In this scenario, when multiple media items can be associated with multiple games, it's impractical to establish direct links between the two tables. Therefore, we employ a bridge table, often referred to as a junction table, which acts as an intermediary, recording these many-to-many associations. This junction table contains entries that specify which media is connected to which games, allowing for a flexible and versatile way to manage complex relationships between data entities. This approach is particularly useful in scenarios like tracking which users have access to various courses, or which products are available in multiple stores. The many-to-many relationship, with its bridge table, provides an elegant solution for modeling these intricate connections.

export const categoriesRelations = relations(categories, ({ many }) => ({
  games: many(gamesToCategories),
}));

export const gamesToCategories = pgTable(
  "games_to_categories",
  {
    gameId: integer("game_id")
      .notNull()
      .references(() => games.id),
    categoryId: integer("category_id")
      .notNull()
      .references(() => categories.id),
  },
  (t) => ({
    pk: primaryKey(t.gameId, t.categoryId),
  })
);

In summary, our tutorial on Drizzle ORM with Neon DB has equipped you with the skills to work with databases effectively. Now, it's time to dive in, practice, and build data-driven applications with confidence. Happy coding!