Published on

Use Drizzle with Neon in Next.js

Authors
  • avatar
    Name
    Shelton Ma
    Twitter

1. Setup PostgreSQL with Neon

Sign In: neon, create a project on Neon

2. Configure Drizzle ORM

1. Documentation

Drizzle ORM Documentation for Neon

2. alias scripts

// package.json
"scripts": {
  "db:generate": "npx drizzle-kit generate",
  "db:migrate": "npx drizzle-kit migrate",
  "db:studio": "npx drizzle-kit studio"
},

3. Setup: Get Started with Drizzle and PostgreSQL

  1. File Structure

    📦 <project root>
        ├ 📂 drizzle
        ├ 📂 src
        │   ├ 📂 db
        │   │  └ 📜 schema.ts
        │   └ 📜 index.ts
        ├ 📜 .env
        ├ 📜 drizzle.config.ts
        ├ 📜 package.json
        └ 📜 tsconfig.json
    
  2. Install Dependencies

    pnpm add drizzle-orm pg dotenv
    pnpm add -D drizzle-kit tsx @types/pg
    
    pnpm add @neondatabase/serverless
    
  3. Set Up Environment Variables in .env.local DATABASE_URL=

  4. Connect Drizzle ORM to the Database src/db/drizzle.ts

    import { neon } from "@neondatabase/serverless";
    import { drizzle } from "drizzle-orm/neon-http";
    
    const sql = neon(process.env.DATABASE_URL!);
    export const db = drizzle({ client: sql });
    
  5. Create tables in src/db/schema.ts.

    import { integer, text, pgTable } from "drizzle-orm/pg";
    
    export const users = pgTable("users", {
      id: integer("id").primaryKey().autoIncrement(),
      name: text("name"),
      email: text("email").unique().notNull(),
    });
    
  6. Setup Drizzle config file drizzle.config.ts

    import { defineConfig } from "drizzle-kit";
    
    export default defineConfig({
      schema: "./src/db/schema.ts",
      out: "./drizzle",
      driver: "pg",
      dbCredentials: {
        connectionString: process.env.DATABASE_URL!,
      },
    });
    
  7. Applying changes to the database

    npx drizzle-kit generate # pnpm run db:generate
    npx drizzle-kit migrate # pnpm run db:migrate
    npx drizzle-kit studio # pnpm run db:studio
    

Drizzle pagination

import { auth } from "@/auth";
import { db } from "@/db/drizzle";
import { documents } from "@/db/schema";
import { AnyColumn, asc, count, desc, eq } from "drizzle-orm";
import { NextRequest, NextResponse } from "next/server";

export async function GET(req: NextRequest) {
  const session = await auth();
  if (!session?.user?.id) {
    return NextResponse.json({ message: "Unauthorized" }, { status: 401 });
  }
  const page = req.nextUrl.searchParams.get("page");
  const pageSize = req.nextUrl.searchParams.get("pagesize");
  const pageNumber = page ? Number(page) : 1;
  const pageSizeNumber = pageSize ? Number(pageSize) : 10;
  const sort = req.nextUrl.searchParams.get("sort");
  const sortOrder = req.nextUrl.searchParams.get("sortOrder");
  const sortBy =
    sort && sort in documents ? (sort as keyof typeof documents) : "updatedAt";
  const sortOrderDirection = sortOrder ? sortOrder : "desc";
  const column = documents[sortBy] ?? documents.updatedAt;
  const Documents = await db
    .select()
    .from(documents)
    .where(eq(documents.ownerId, session?.user?.id))
    .orderBy(
      sortOrderDirection === "asc"
        ? asc(column as AnyColumn)
        : desc(column as AnyColumn)
    )
    .limit(pageSizeNumber)
    .offset((pageNumber - 1) * pageSizeNumber);

  const totalDocuments = await db
    .select({ count: count() })
    .from(documents)
    .where(eq(documents.ownerId, session?.user?.id));

  return NextResponse.json(
    {
      documents: Documents,
      totalPages: Math.ceil(Number(totalDocuments[0].count) / pageSizeNumber),
      totalDocuments: Number(totalDocuments[0].count),
      pageSize: pageSizeNumber,
      currentPage: pageNumber,
    },
    { status: 200 }
  );
}