Docs

Integrate Neon Postgres with Clerk

You will learn the following:

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

This tutorial demonstrates how to integrate Neon Postgres with Clerk in a Next.js application, using drizzle-orm and drizzle-kit to interact with the database. The tutorial guides you through setting up a simple application that enables users to add, view, and delete messages using Server Actions and Middleware with Clerk.

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
    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 Clerk Next.js SDK into your application.

Protect your application routes

To ensure that only authenticated users can access your application, modify clerkMiddleware to require authentication for every route.

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

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

export const config = {
  matcher: [
    // Skip Next.js internals and all static files, unless found in search params
    '/((?!_next|[^?]*\\.(?:html?|css|js(?!on)|jpe?g|webp|png|gif|svg|ttf|woff2?|ico|csv|docx?|xlsx?|zip|webmanifest)).*)',
    // Always run for API routes
    '/(api|trpc)(.*)',
  ],
}

Set your neon connection string

Add the Neon connection string to your project's environment variables. You can find the Neon connection string in the Neon console - see the Neon docs for more information.

Your environment variable file should have the following values:

.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 and database connection

  1. Inside the app/, create a db/ directory.

  2. Create a schema.ts file in the db/ directory that defines the database schema. The schema will include a table called user_messages with the columns user_id, create_ts, and message.The user_id column will be used to store the user's Clerk ID.

    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(),
    })
  3. Create an index.ts file in the db directory to set up the database connection.

    app/db/index.ts
    import { loadEnvConfig } from '@next/env'
    import { neon } from '@neondatabase/serverless'
    import { drizzle } from 'drizzle-orm/neon-http'
    import { UserMessages } from './schema'
    
    loadEnvConfig(process.cwd())
    
    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 },
    })

Push the schema to the database

  1. To load the schema into the database, create a drizzle.config.ts file at the root of your project and add the following configuration:

    drizzle.config.ts
    import { defineConfig } from 'drizzle-kit'
    import { loadEnvConfig } from '@next/env'
    
    loadEnvConfig(process.cwd())
    
    if (!process.env.DATABASE_URL) {
      throw new Error('DATABASE_URL must be a Neon postgres connection string')
    }
    
    export default defineConfig({
      dialect: 'postgresql',
      dbCredentials: {
        url: process.env.DATABASE_URL,
      },
      schema: './app/db/schema.ts',
    })
  2. Run the following command to push the schema to the database:

    terminal
    npx drizzle-kit push

Create Server Actions to handle user interactions

To handle form submissions for adding and deleting user messages, create two Server Actions in app/actions.ts. Use Clerk's auth() helper to obtain the user ID, which will be used to interact with the database.

app/actions.ts
'use server'

import { auth } from '@clerk/nextjs/server'
import { UserMessages } from './db/schema'
import { db } from './db'
import { eq } from 'drizzle-orm'

export async function createUserMessage(formData: FormData) {
  const { userId } = await auth()
  if (!userId) throw new Error('User not found')

  const message = formData.get('message') as string
  await db.insert(UserMessages).values({
    user_id: userId,
    message,
  })
}

export async function deleteUserMessage() {
  const { userId } = await auth()
  if (!userId) throw new Error('User not found')

  await db.delete(UserMessages).where(eq(UserMessages.user_id, userId))
}

Create the UI for the Home Page

In your app/page.tsx file, add the following code to create the UI for the home page. If a message exists, the user can view and delete it; otherwise, they can add a new message.

To retrieve the user's messages, use Clerk's auth() helper to obtain the user's ID. Then, use this ID to query the database for the user's messages.

To enable the user to delete or add a message, use the deleteUserMessage() and createUserMessage() actions created in the previous step.

app/page.tsx
import { createUserMessage, deleteUserMessage } from './actions'
import { db } from './db'
import { auth } from '@clerk/nextjs/server'

export default async function Home() {
  const { userId } = await auth()
  if (!userId) throw new Error('User not found')
  const existingMessage = await db.query.UserMessages.findFirst({
    where: (messages, { eq }) => eq(messages.user_id, userId),
  })

  return (
    <main>
      <h1>Neon + Clerk Example</h1>
      {existingMessage ? (
        <div>
          <p>{existingMessage.message}</p>
          <form action={deleteUserMessage}>
            <button>Delete Message</button>
          </form>
        </div>
      ) : (
        <form action={createUserMessage}>
          <input type="text" name="message" placeholder="Enter a message" />
          <button>Save Message</button>
        </form>
      )}
    </main>
  )
}

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?

Last updated on