Next.js with Drizzle ORM: Database Integration Guide

npx create-next-app@latest my-app --typescript --tailwind --eslint
cd my-app
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg
# Create .env.local with DATABASE_URL=postgres://user:pass@localhost:5432/mydb

import { pgTable, serial, text, timestamp, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

import type { Config } from 'drizzle-kit';

export default {
  schema: './db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool, { schema });

import { db } from '@/db';
import { posts } from '@/db/schema';

export default async function HomePage() {
  const allPosts = await db.select().from(posts);
  return (
    <ul>
      {allPosts.map(post => (
        <li key={post.id}>{post.title}</li>
      ))}
    </ul>
  );
}

import { db } from '@/db';
import { posts, users } from '@/db/schema';
import { eq } from 'drizzle-orm';

export default async function PostPage({ params }: { params: { id: string } }) {
  const post = await db
    .select()
    .from(posts)
    .where(eq(posts.id, parseInt(params.id)))
    .innerJoin(users, eq(posts.authorId, users.id))
    .limit(1);
  
  if (!post.length) return <div>Not found</div>;
  return <div>{post[0].posts.title} by {post[0].users.name}</div>;
}

import { db } from '@/db';
import { posts } from '@/db/schema';
import { NextRequest, NextResponse } from 'next/server';

export async function POST(request: NextRequest) {
  const body = await request.json();
  const { title, content, authorId } = body;
  
  const newPost = await db.insert(posts).values({
    title,
    content,
    authorId,
  }).returning();
  
  return NextResponse.json(newPost[0], { status: 201 });
}

import { db } from '@/db';
import { users, posts } from '@/db/schema';
import { eq } from 'drizzle-orm';

export async function GET(request: NextRequest, { params }: { params: { id: string } }) {
  const userWithPosts = await db.query.users.findFirst({
    where: eq(users.id, parseInt(params.id)),
    with: {
      posts: true,
    },
  });
  return NextResponse.json(userWithPosts);
}

import { db } from '@/db';
import { posts } from '@/db/schema';
import { NextRequest, NextResponse } from 'next/server';

export async function GET() {
  try {
    const allPosts = await db.select().from(posts);
    return NextResponse.json(allPosts);
  } catch (error) {
    console.error('Database error:', error);
    return NextResponse.json(
      { error: 'Failed to fetch posts' },
      { status: 500 }
    );
  }
}

import { describe, it, expect, beforeAll } from 'vitest';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from '../db/schema';

const sqlite = new Database(':memory:');
const db = drizzle(sqlite, { schema });

beforeAll(async () => {
  // Run migrations or create tables
  db.run(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)`);
});

describe('Users', () => {
  it('should insert and retrieve a user', async () => {
    await db.insert(schema.users).values({ name: 'Test', email: 'test@test.com' });
    const users = await db.select().from(schema.users);
    expect(users.length).toBe(1);
  });
});

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool } from '@neondatabase/serverless';
import * as schema from './schema';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });