- Published on
Use Drizzle with Neon in Next.js
- Authors
- Name
- Shelton Ma
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"
},
Get Started with Drizzle and PostgreSQL
3. Setup:File Structure
📦 <project root> ├ 📂 drizzle ├ 📂 src │ ├ 📂 db │ │ └ 📜 schema.ts │ └ 📜 index.ts ├ 📜 .env ├ 📜 drizzle.config.ts ├ 📜 package.json └ 📜 tsconfig.json
Install Dependencies
pnpm add drizzle-orm pg dotenv pnpm add -D drizzle-kit tsx @types/pg pnpm add @neondatabase/serverless
Set Up Environment Variables
in .env.local DATABASE_URL=
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 });
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(), });
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!, }, });
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 }
);
}