Next.js, Drizzle ORM, PostgreSQL and Vercel Tutorial

GitHub: https://github.com/fullstackbook/nextjs-drizzle-orm-postgresql-vercel-tutorial

Learn how to integrate Drizzle ORM with Next.js, PostgreSQL and Vercel.

Step 1: Create a Next.js app

npx create-next-app@latest

Step 2: Cleanup

/app/page.tsx

export default function Home() {
  return <div>Home</div>;
}

/app/global.css

@tailwind base;
@tailwind components;
@tailwind utilities;

Step 3: Deploy to Vercel

npx vercel

Step 4: Create a Postgres Database In Vercel

  • Go to Vercel Dashboard
  • Go to project
  • Go to storage
  • Create a postgres database

In local terminal, run:

vercel env pull .env.local

Step 5: Install @vercel/postgres, Drizzle, and Drizzle Kit

npm i drizzle-orm
npm i @vercel/postgres
npm i -D drizzle-kit

Step 6: Create Drizzle Config

/drizzle.config.ts

import "@/lib/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./lib/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.POSTGRES_URL! + "?sslmode=require",
  },
  verbose: true,
  strict: true,
});

Step 7: Create the Schema

/lib/schema.ts

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

export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    name: text("name").notNull(),
    email: text("email").notNull(),
    image: text("image").notNull(),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
  },
  (users) => {
    return {
      uniqueIdx: uniqueIndex("unique_idx").on(users.email),
    };
  }
);

Step 8: Create configuration file for loading environment variables

/lib/config.ts

import { loadEnvConfig } from "@next/env";

const projectDir = process.cwd();
loadEnvConfig(projectDir);

Step 9: Create DB connection and helpers

/lib/db.ts

import "@/lib/config";
import { drizzle } from "drizzle-orm/vercel-postgres";
import { sql } from "@vercel/postgres";
import { users } from "./schema";
import * as schema from "./schema";

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

export const getUsers = async () => {
  const selectResult = await db.select().from(users);
  console.log("Results", selectResult);
  return selectResult;
};

export type NewUser = typeof users.$inferInsert;

export const insertUser = async (user: NewUser) => {
  return db.insert(users).values(user).returning();
};

export const getUsers2 = async () => {
  const result = await db.query.users.findMany();
  return result;
};

Step 10: Generate the sql migration file

npx drizzle-kit generate:pg

You may see an error like this:

Transforming const to the configured target environment ("es5") is not supported yet

The work around is to change the target from es5 to es6 in tsconfig.json.

{
  "compilerOptions": {
    "target": "es6"
  }
}

After running the generate command, you should see a drizzle folder created.

Step 11: Create the migrate script

/scripts/migrate.ts

import { migrate } from "drizzle-orm/vercel-postgres/migrator";
import { db } from "@/lib/db";

async function main() {
  await migrate(db, { migrationsFolder: "./drizzle" });
}

main();

Step 12: Run the migrate script

npx tsx scripts/migrate.ts

You should see some files generated in the drizzle folder.

Step 13: Create and run a seed script

/scripts/seed.ts

import { NewUser, insertUser } from "@/lib/db";

async function main() {
  const newUser: NewUser = {
    email: "foo@example.com",
    image: "some image url",
    name: "foo",
  };
  const res = await insertUser(newUser);
  console.log("insert user success", res);
  process.exit();
}

main();

Run the seed script.

npx tsx scripts/seed.ts

Step 14: Update Home Page to display data

import { getUsers, getUsers2 } from "@/lib/db";

export default async function Home() {
  const data = await getUsers();
  const data2 = await getUsers2();

  return (
    <div>
      <div>sql-like: {JSON.stringify(data)}</div>
      <div>relational: {JSON.stringify(data2)}</div>
    </div>
  );
}

Step 15: Update the schema

Add a field to the users schema in /lib/schema.ts. For example: password: text("password"),

Step 16: Try the push command

The push command syncs the drizzle schema definition schema.ts with the database.

The push command is useful for rapid prototyping.

The generate + migrate commands are useful if you want to keep track of database migrations.

npx drizzle-kit push:pg

Step 17: Add commands to package.json

Add the commands to package.json for future use.

{
  "scripts": {
    "generate": "drizzle-kit generate:pg",
    "migrate": "npx tsx scripts/migrate.ts",
    "seed": "npx tsx scripts/seed.ts",
    "push": "drizzle-kit push:pg"
  }
}

Conclusion

Drizzle has two main ways to update database schema:

  1. Generate and migrate - for projects that require migration files.
  2. Push - for prototyping or if project doesn't require migration files.

Drizzle has two main ways to query data:

  1. SQL-like - for SQL-like query builder.
  2. Relational - for ORM-like syntax for fetching data that always outputs 1 SQL query.

Reference