Docs

Integrate Neon Postgres with Clerk

Integrate Neon with Clerk

You will learn the following:

  • Use Clerk to authenticate access to your application backed by Neon.

Before you start

This tutorial demonstrates how to integrate Neon Postgres with Clerk in a Next.js application, using drizzle-orm for interacting with the database. The tutorial guides you through setting up a simple application that enables users to add, view, and delete messages. If a user has an existing message, they can view and delete it; otherwise, they can add a new message.

Create a new Next.js project

  1. Create a new Next.js project using the following command:
    terminal
    npx create-next-app clerk-neon-example --typescript --eslint --tailwind --use-npm --no-src-dir --app --import-alias "@/*"
  2. Navigate to the project directory and install the required dependencies:
    terminal
    cd clerk-neon-example
npm install @neondatabase/serverless dotenv
npm install drizzle-orm --legacy-peer-deps
npm install -D drizzle-kit

Integrate the Next.js Clerk SDK

Follow the Next.js quickstart to integrate the Next.js Clerk SDK into your application.

Configure the Clerk middleware

By default, clerkMiddleware() will not protect any routes. All routes are public and you must opt-in to protection for routes. For this tutorial, protect your entire application and ensure that only authenticated users can it.

In your middleware.ts file, update the code with the following configuration:

middleware.ts
import { clerkMiddleware } from '@clerk/nextjs/server';

export default clerkMiddleware((auth) => {
  auth().protect();
});

export const config = {
  matcher: ['/((?!.*\\..*|_next).*)', '/', '/(api|trpc)(.*)'],
};

Set your environment variables

You must add the Neon connection string to your project's environment variables.

  1. Navigate to the Neon console.
  2. In the navigation sidebar, select Dashboard.
  3. In the Connection Details section, find your Neon database connection string.
  4. Add the connection string to your .env.local file.

The final result should resemble the following:

.env.local
DATABASE_URL=NEON_DB_CONNECTION_STRING
NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY=YOUR_PUBLISHABLE_KEY
CLERK_SECRET_KEY=YOUR_SECRET_KEY

Set up the application schema

Create a schema for the database. The schema will include a table called user_messages with the colomns user_id, create_ts, and message.

  1. Inside the app/ directory, create a db folder.
  2. Inside the db/ folder, create a schema.ts file and an index.ts file.
  3. Use the tabs below to find the example code for the schema and index files.
app/db/schema.ts
import { pgTable, text, timestamp } from "drizzle-orm/pg-core";

export const UserMessages = pgTable("user_messages", {
  user_id: text("user_id").primaryKey().notNull(),
  createTs: timestamp("create_ts").defaultNow().notNull(),
  message: text("message").notNull(),
});
app/db/index.ts
import "dotenv/config";
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import { UserMessages } from "./schema";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL must be a Neon postgres connection string");
}

const sql = neon(process.env.DATABASE_URL);
export const db = drizzle(sql, {
  schema: { UserMessages },
});

Generate and run database migrations

Use the drizzle-kit package to generate and run database migrations.

  1. At the root of your project, create a drizzle.config.ts and add the following configuration:
    drizzle.config.ts
    import { defineConfig } from "drizzle-kit";
import * as dotenv from "dotenv";

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

export default defineConfig({
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  schema: "./app/db/schema.ts",
  out: "./migrations",
});
  2. Generate and run the database migrations by running the following commands:
    terminal
    npx drizzle-kit generate
npx drizzle-kit migrate

Create the UI of the home page

Add the following code to the app/page.tsx file to create the UI of the home page:

app/page.tsx
import { createUserMessage, deleteUserMessage } from "./actions";
import { db } from "./db";
import { currentUser } from "@clerk/nextjs/server";

async function getUserMessage() {
  const user = await currentUser();
  if (!user) throw new Error("User not found");
  return db.query.UserMessages.findFirst({
    where: (messages, { eq }) => eq(messages.user_id, user.id),
  });
}

export default async function Home() {
  const existingMessage = await getUserMessage();

  return (
    <main className="flex flex-col items-center justify-center min-h-screen">
      <h1 className="text-3xl font-bold mb-8">Neon + Clerk Example</h1>
      {existingMessage ? (
        <div className="text-center">
          <p className="text-xl mb-4">{existingMessage.message}</p>
          <form action={deleteUserMessage}>
            <button type="submit" className="bg-red-500 text-white px-4 py-2 rounded">
              Delete Message
            </button>
          </form>
        </div>
      ) : (
        <form action={createUserMessage} className="flex flex-col items-center">
          <input type="text" name="message" placeholder="Enter a message" className="border border-gray-300 rounded px-4 py-2 mb-4 w-64" />
          <button type="submit" className="bg-blue-500 text-white px-4 py-2 rounded">
            Save Message
          </button>
        </form>
      )}
    </main>
  );
}

Handle user interactions

Create server actions to handle the form submissions and database interactions.

  1. At the root of your project, create an actions.ts file.
  2. Paste the following code example:
    app/actions.ts
    "use server";

import { currentUser } from "@clerk/nextjs/server";
import { UserMessages } from "./db/schema";
import { db } from "./db";
import { redirect } from "next/navigation";
import { eq } from "drizzle-orm";

export async function createUserMessage(formData: FormData) {
  const user = await currentUser();
  if (!user) throw new Error("User not found");

  const message = formData.get("message") as string;
  await db.insert(UserMessages).values({
    user_id: user.id,
    message,
  });
  redirect("/");
}

export async function deleteUserMessage() {
  const user = await currentUser();
  if (!user) throw new Error("User not found");

  await db.delete(UserMessages).where(eq(UserMessages.user_id, user.id));
  redirect("/");
}

Run the application

Run your application and open http://localhost:3000 in your browser. Sign in with Clerk and interact with the application to add and delete user messages.

Feedback

What did you think of this content?
Edit this page on GitHub

Last updated on