DEV Community: Evan Shortiss

Next.js authentication using Clerk, Drizzle ORM, and Neon

Evan Shortiss — Mon, 01 Apr 2024 18:11:47 +0000

Building an effective authentication and authorization system into your application is as equally fraught with challenges as managing database infrastructure. As the old wisdom says, never “roll your own” regarding authentication and authorization.

In addition to the effort required to develop the login, logout, and social provider integration, you must consider compliance with standards such as SOC 2 and HIPAA, multi-factor authentication, and user management.

Using Clerk enables you to seamlessly add advanced authentication and user management features to your application and comply with data security regulations by storing personally identifiable information in their compliant infrastructure.

In this article, you’ll learn how to use Clerk to add authentication to a Next.js application and how to use Clerk’s Next.js helpers to obtain user details in your components. You’ll also learn to use Drizzle ORM to store non-PII data in Neon’s serverless Postgres.

A complete example application with instructions for local development and deployment on Vercel can be found on GitHub at evanshortiss/neon-clerk-drizzle-nextjs. A live preview of the sample application is hosted on Vercel; try it out at neon-clerk-drizzle-nextjs.vercel.app and vote for your favorite periodic element!

Solution Architecture

Before diving into the implementation, let’s review the high-level architecture of this application.

Vercel will provide serverless hosting for your Next.js application. The Next.js application will direct your users to Clerk for authentication. Clerk will redirect users to your application once they’ve completed an authentication flow using a supported provider such as Google or Discord OAuth.

Once a user is signed in, they can access protected pages and routes in your application, and you can read their session data as needed to obtain the user ID to query or associate data with the authenticated user in your Neon Postgres database.

Sign Up to Neon and Configure Postgres

Sign up for Neon and create a project. This project will contain the Postgres database and a user_messages table that you will use to follow along with the rest of this article.

Enter a project name.
Use the default database name of neondb.
Choose the region closest to the location where your application will be deployed.
Click the Create project button.

You’ll instantly be provided with a connection string you can use to connect to your serverless Postgres database.

Sign Up to Clerk and Configure Application Sign In

Visit dashboard.clerk.com and sign up, or sign in if you’re an existing user. Create a new application and enable some of the available sign-in options. You can see that I’ve enabled Discord and Google as sign-in options for my application.

Once the application has been created on Clerk, you’ll find your API keys on the Home screen of your application in the Clerk dashboard. Specifically, you’ll need the API keys listed in the Next.js section soon!

Using Neon’s Serverless Driver with Next.js and Drizzle ORM

Create a Next.js Project and Install Dependencies

Create a Next.js application in your development environment. This requires Node.js v18 or newer to be installed in your development environment. This article assumes you use the following options when creating your Next.js application using create-next-app.

npx create-next-app@14.1 neon-clerk-next \
--typescript \
--eslint \
--tailwind \
--use-npm \
--app \
--src-dir \
--import-alias "@/*"

Once your Next.js application has been created, change to the project directory in your terminal, then add the Neon serverless driver and Drizzle ORM to your project’s dependencies using npm or your preferred package manager.

npm install @neondatabase/serverless drizzle-orm

Create a Schema and Database Connection

Create a file named src/app/db/schema.ts and define a user_messages schema using the types provided by Drizzle ORM.

// file: src/app/db/schema.ts
import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';

/**
 * This table stores quotes submitted by users.
 */
export const UserMessages = pgTable('user_messages', {
  // This will be the user ID provided by Clerk
  user_id: text('user_id').primaryKey().notNull(),
  createTs: timestamp('create_ts').defaultNow().notNull(),
  message: text('message').notNull()
});

To use Neon’s serverless driver with Drizzle ORM, create a file named src/app/db/index.ts and add the following code. Exporting the Drizzle instance from a file means it’s created on application startup and exported as a singleton that other modules can import and reuse to execute typesafe SQL queries against your Postgres database hosted by Neon.

// file: src/app/db/index.ts
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 }
});

Next, create a file named .env.local in the root of the Next.js project directory and add your database URL from the Neon Console as an environment variable named DATABASE_URL.

# Copy this from your project dashboard on https://console.neon.tech
DATABASE_URL=postgresql://user:pass@ep-adj-noun-12345.us-east-2.aws.neon.tech/mydatabase?sslmode=require"

Start your Application

Use the npm run dev command to start your Next.js application in development mode and confirm it’s available at http://localhost:3000. You won’t be prompted to authenticate since you haven’t added the Clerk middleware to your application yet – you’ll take care of that soon.

Generate and Apply Database Migrations using Drizzle Kit

Before using Drizzle ORM to perform database operations in your application, you’ll need to generate and apply schema migrations to your database. Drizzle Kit streamlines this process by detecting changes in your schema.ts file and generating the necessary SQL migrations to apply to your database.

To get started, add Drizzle Kit and dotenv to your project’s development dependencies.

npm i drizzle-kit dotenv -D

Next, create a file named drizzle.config.ts at the root of your repository and add the following configuration.

import type { Config } from 'drizzle-kit';
import * as dotenv from 'dotenv';

// Read the .env file if it exists, or a file specified by the
// dotenv_config_path parameter that's passed to Node.js
dotenv.config();

if (!process.env.DATABASE_URL) throw new Error('DATABASE_URL not found in environment');

export default {
  schema: './src/app/db/schema.ts',
  out: './src/app/db/migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL,
  },
  strict: true,
} satisfies Config;

Now you can use the generate command from the Drizzle Kit CLI to generate migration files. Run the following command to generate migrations for your user schema.

npx drizzle-kit generate:pg -- dotenv_config_path='.env.local'

A new src/app/db/migrations folder will be created, and an SQL migration file will be created that contains a CREATE TABLE statement for the user_messages table. You can read more about migrations in the Drizzle documentation.

Use the push command from the Drizzle Kit CLI to apply the migrations to your Postgres database on Neon. The following command runs the push command from Drizzle Kit and sets the config path for dotenv to load the DATABASE_URL from your .env.local file.

npx drizzle-kit push:pg -- dotenv_config_path='.env.local'

Visit the Tables view in the Neon Console, and your new user_messages table will be listed once the push command has finished.

Add Authentication to your Next.js Application using Clerk

The Next.js documentation provides a comprehensive overview of authentication, session management, and authorization with sample code. This requires writing a non-trivial amount of code and middleware. Since you’re using Clerk, it will handle this complexity for you!

To start, install Clerk’s Next.js package and add it to your Next.js project’s dependencies.

npm install @clerk/nextjs

Update your layout.tsx file to use the ClerkProvider and UserButton from the @clerk/nextjs package. The ClerkProvider provides access to the current session and user context. The UserButton renders a component that shows the user’s profile picture, provides access to account management, and a sign-out button.

export default function RootLayout({
  children,
}: Readonly<{
  children: React.ReactNode;
}>) {
  return (
    <ClerkProvider>
      <html lang="en">
        <body className={inter.className}>
          <div className="p-4 bg-white">
            <UserButton showName={true}></UserButton>
          </div>
          {children}
        </body>
      </html>
    </ClerkProvider>
  );
}

Next, create a file named src/middleware.ts, and add the authMiddleware provided by Clerk as shown in the following snippet.

import { authMiddleware } from "@clerk/nextjs";

export default authMiddleware({
  // Routes that should be accessible without signing in can
  // be defined as strings in this array, e.g, your home page
  publicRoutes: []
});

export const config = {
  // Protects all routes, including api/trpc:
  // https://clerk.com/docs/references/nextjs/auth-middleware
  matcher: ["/((?!.+\\.[\\w]+$|_next).*)", "/", "/(api|trpc)(.*)"],
};

Lastly, add the API keys provided in Next.js format on the Clerk dashboard to your .env.local file.

# Copy these from https://dashboard.clerk.com/
NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY=pk_test_youruniquevalue
CLERK_SECRET_KEY=sk_test_youruniquevalue

Start your application using the npm run dev command, and visit http://localhost:3000 in your web browser. If you’re prompted to sign in to your application, everything is going as planned!

Store Data in Postgres associated with a Clerk User ID

The final step in the process is to add some interactivity to your application. You will let users store their favorite quote in your Neon Postgres database. This will involve defining Next.js Server Actions and a Server Component that uses them.

Start by creating a file named src/app/actions.ts and add the following code to define createUserMessage and deleteUserMessage actions. Both actions obtain the user ID from the current session using Clerk’s currentUser helper.

'use server';

import { currentUser } from '@clerk/nextjs';
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('/');
}

Add the following code to src/app/page.tsx to create a minimalist web page that uses your actions. This enables users to create or delete a quote associated with their user ID. If you recall the schema you defined earlier using Drizzle ORM, it uses the user ID as the primary key. This means each user can store a single quote.

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();

  const ui = existingMessage ? (
    <div className="w-2/3 text-center">
      <h1 className="text-3xl">{existingMessage.message}</h1>
      <form action={deleteUserMessage} className="w-full rounded px-8 pt-6 pb-8 mb-4">
        <div className="w-full text-center">
          <input type="submit" value={"Delete Quote"} className="bg-[#00E699] transition-colors hover:bg-[#00e5BF] text-gray-800 font-semibold py-2 px-4 rounded focus:outline-none cursor-pointer"/>
        </div>
      </form>
    </div>
  ) : (
    <form action={createUserMessage} className="shadow-md w-2/3 rounded px-8">
      <div className="mb-6">
        <input type="text" name="message" placeholder="Mistakes are the portals of discovery - James Joyce" className="text-center appearance-none border rounded w-full p-3 text-gray-700 leading-tight focus:outline-none" />
      </div>
      <div className="w-full text-center">
        <input type="submit" value={"Save Message"} className="bg-[#00E699] cursor-pointer transition-colors hover:bg-[#00e5BF] text-gray-800 font-semibold py-2 px-4 rounded focus:outline-none"/>
      </div>
    </form>
  );



  return (
    <main className="flex -mt-16 min-h-screen flex-col align-center justify-center items-center px-24">
      <h2 className="text-2xl pb-6 text-gray-400">{existingMessage ? 'Your quote is wonderful...' : 'Save an inspiring quote for yourself...'}</h2>
      {ui}
    </main>
  );
}

Start your application using npm run dev, visit http://localhost:3000, and sign in using one of your chosen providers. You will be redirected to your application after signing in. The application displays a web page that contains an input field where you can submit a quote to associate with your user account and a lovely header with your username and profile picture powered by Clerk’s UserButton component.

Type in your favorite quote, for example, “To infinity and beyond!” by Buzz Lightyear. Click the Save Quote button or press Enter to save your quote. When you visit http://localhost:3000 in the future, the quote associated with your user ID will be displayed.

Conclusion

Congratulations, you’ve built a Next.js application that integrates with Clerk for user management and authentication and uses Neon’s serverless Postgres as its database.

Now that you’ve got an application up and running, you should visit Clerk’s Next.js documentation to learn how to deploy your application to production. You can use Neon’s Vercel Integration to manage development and preview database branches when you deploy your Next.js application on Vercel, which uses Neon’s serverless Postgres.

We would love to get your feedback. Follow us on X, join us on Discord, and let us know how we can help you build the next generation of applications.

Build a Real-time Materialized View from Postgres Changes using Confluent’s ksqlDB

Evan Shortiss — Wed, 28 Feb 2024 17:33:42 +0000

Neon’s support for Postgres’ logical replication features opens up a variety of interesting use cases, including for real-time streaming architectures based on change data capture. We previously demonstrated how to use Debezium to fan-out changes from Postgres by using Redis as a message broker.

Today we’ll explore how you can leverage the Apache Kafka and Kafka Connect ecosystem to capture and process changes from your Neon Postgres database. Specifically, you’ll learn how to stream changes from Postgres to Apache Kafka and process those changes using ksqlDB to create a materialized view that updates in response to database changes.

It’s possible to run Apache Kafka, Kafka Connect, and ksqlDB on your infrastructure; however, this guide will be using Confluent Cloud to host these components so we can focus on enabling data streaming and building a materialized view instead of managing infrastructure.

Why Apache Kafka with Postgres for Materialized Views?

Postgres is a mature and battle-tested database solution that supports materialized views, so why do we need messaging infrastructure like Apache Kafka to process events and create a materialized view? We already explained the importance of avoiding the dual-write problem when integrating your application with message brokers, so let’s focus on the data streaming and performance concerns instead.

As a reminder, a materialized view stores the result of a query at a specific point in time. Let’s take a look at an example. Imagine you have a write-heavy application that involves two tables represented by the following SQL:

CREATE TABLE players (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE player_scores (
    id SERIAL PRIMARY KEY,
    score DECIMAL(10, 2) NOT NULL,
    player_id INTEGER REFERENCES players(id),
    CONSTRAINT fk_player
        FOREIGN KEY(player_id)
        REFERENCES players(id)
        ON DELETE CASCADE
);

This database contains a players table to store player information and a player_scores table to track their scores. You might be required to create a leaderboard table that keeps track of each player’s total score (using a SUM aggregate function), retain a history of these changes, and notify subscribers about changes to the leaderboard in real-time.

Using a materialized view is one option for keeping track of the total scores. The following SQL would create a materialized view to achieve this functionality in Postgres:

CREATE MATERIALIZED VIEW player_total_scores AS
SELECT
    p.id AS player_id,
    p.name,
    COALESCE(SUM(s.score), 0) AS total_score
FROM
    players p
LEFT JOIN
    player_scores s ON p.id = s.player_id
GROUP BY
    p.id;

Keeping the view current requires issuing a REFRESH MATERIALIZED VIEW query after each insert to the player_scores table. This could have significant performance implications, doesn’t retain leaderboard history, and you still need to stream the changes to downstream subscribers reliably unless you want them to poll the database for changes.

A more scalable and flexible approach involves a microservices architecture that uses change data capture with logical replication to stream player data and score events to an Apache Kafka cluster for processing, as outlined in the following architecture diagram.

An Apache Kafka cluster is a collection of brokers (or nodes) that enable parallel processing of records by downstream subscribers such as ksqlDB. Data is organized into topics in Apache Kafka, and topics are split into partitions that are replicated across the brokers in the cluster to enable high availability. The beauty of using Apache Kafka and Kafka Connect is that connectors can source events from one system and sink them to other systems, including back to Postgres, if you want! You could even place Kafka in front of Postgres to insert score events into the database in a controlled manner.

Using ksqlDB with Apache Kafka enables you to process database change events stored in topics, perform aggregation operations, and keep the results of those operations in another Kafka topic to retain leaderboard history.

Get Started with Neon and Logical Replication

To get started, sign up for Neon and create a project. This project will contain the Postgres database that holds the players and player_scores tables:

Enter a project name.
Use default database name neondb.
Choose the region closest to your location.
Click Create project.

Next, visit the Beta section of the Project settings screen and enable logical replication. Visit our documentation to view a complete set of logical replication guides.

Use the SQL Editor in the Neon console to create two tables in the neondb database: one to hold player information and another to hold score records for players. Each row in player_scores contains a foreign key referencing a player by their ID.

CREATE TABLE players (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE player_scores (
    id SERIAL PRIMARY KEY,
    score DECIMAL(10, 2) NOT NULL,
    player_id INTEGER REFERENCES players(id),
    CONSTRAINT fk_player
        FOREIGN KEY(player_id)
        REFERENCES players(id)
        ON DELETE CASCADE
);

Create a publication for these two tables. The publication defines what operations on those tables are replicated to other Postgres instances or subscribers. You’ll deploy a Debezium connector on Confluent’s cloud that uses this publication to observe changes in the specified tables:

CREATE PUBLICATION confluent_publication FOR TABLE players, player_scores;

Create a logical replication slot to retain and stream changes in the write-ahead log (WAL) to subscribers. The Debezium connector on Confluent’s cloud will use this slot to consume relevant changes from the WAL:

SELECT pg_create_logical_replication_slot('debezium', 'pgoutput');

Use the Roles section of the Neon console to create a new role named confluent_cdc. Be sure to save the password for the role someplace safe since it will only be displayed once. With the role in place, grant it permissions on the public schema using the SQL Editor :

GRANT USAGE ON SCHEMA public TO confluent_cdc;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO confluent_cdc;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO confluent_cdc;

Now, you’ve got everything in place to start consuming changes from your players and player_scores tables in your Neon Postgres database.

Get Started with Apache Kafka and Debezium on Confluent Cloud

This guide assumes you’re new to Confluent Cloud. If you’re an existing user, you can modify the steps to integrate with your existing environments and Apache Kafka cluster(s).

Sign into https://confluent.cloud and follow the onboarding flow to create a basic Apache Kafka cluster. Choose the region that’s closest to your Neon Postgres database region.

Once your cluster has been provisioned, click on it in the Environments screen, then select the Connectors view from the side menu on the next page.

Apache Kafka on Confluent supports a plethora of connectors. Many of these are based on the various open-source Kafka Connect connectors. Find and select the Postgres CDC Source connector in the list. This connector is based on the Debezium project we wrote about in our fan-out using Debezium and Upstash Redis article.

On the Add Postgres CDC Source connector screen:

Select Global Access.
Click the Generate API key & download button to generate an API key and secret.
Click Continue.

Next, configure the connection between the connector and your Postgres database on Neon:

Database name: neondb
Database server name: neon
SSL mode: require
Database hostname: Find this on the Neon console. Refer to our documentation.
Database port: 5432
Database username: confluent_cdc
Database password: This is the password for the confluent_cdc role you created earlier.
Click Continue.

Configure the connector properties. The first of these is the Kafka record key and value formats. Select the following options:

Output Kafka record value format: JSON_SR
Output Kafka record key format: JSON_SR

The JSON_SR option causes change event record schemas to be registered in your Confluent Cloud environment’s Schema Registry. You can think of the schemas as type information for records in your Kafka topics. These are essential to working with the change data event records, as you’ll see shortly.

Expand the advanced configuration and set the following options:

Slot name: debezium
Publication auto create mode: disabled
Publication name: confluent_publication
Tables included: public.players,public.player_scores

Click Continue , accept the default values for sizing and tasks, and give your connector a name. Once finished, your connector will be shown on the Connectors screen. Confirm that it’s marked as Running and not in an error state. If the connector reports an error, check the configuration properties for correctness.

Confirm Change Data Capture is Working

Use the SQL Editor in the Neon console to insert some players and scores into your tables using the following SQL statements:

INSERT INTO players (name) VALUES ('Mario'), ('Peach'), ('Bowser'), ('Luigi'), ('Yoshi');

INSERT INTO player_scores (player_id, score) VALUES
(1, 0.31),
(3, 0.16),
(4, 0.24),
(5, 0.56),
(1, 0.19),
(2, 0.34),
(3, 0.49),
(5, 0.71);

Return to the Confluent Cloud console and select the Topics item from the side menu. You will see two topics that correspond to your database tables.

Click on either of the topics, then use the Messages tab to view database change events captured by the Debezium connector and streamed to the topic. Each message in Kafka contains a key and value; in this case, these are the database row ID and row contents.

Apache Kafka uses partitions to increase parallelism and replicates partitions across multiple nodes to increase durability. Since Kafka partitions are an ordered immutable sequence of messages, the offset represents the message position in its partition. Topics in a production Kafka environment can be split into 100 or more partitions if necessary, to enable parallel processing by as many consumers as there are partitions.

Next, confirm that schemas have been registered for your change records. Select the Schema Registry from the bottom left of the side menu in the Confluent Cloud console, and confirm that schemas have been registered for the records in your topics.

If you click on the schema entries with a “key” suffix, you notice that the schema simply contains an id property. This property corresponds to the id or primary key of the database row. The schema entries with a “value” suffix correspond to the backing table’s schema.

Create a Materialized View using ksqlDB

With schemas and topics containing messages in place, you can use ksqlDB to create a materialized view that updates in response to database changes.

Select ksqlDB in the side menu for your cluster to provision a new ksqlDB instance with Global access enabled, and use the default values for sizing and configuration. The provisioning process can take a couple of minutes, so be patient.

Select your ksqlDB cluster once it’s ready, then navigate to the Streams tab and click Import topics as streams to import your player_scores and players topics as streams in ksqlDB. Creating a stream from your topic allows you to perform operations such as joins or aggregations on the data contained in the underlying topic, as you’ll see in a moment. Click Import to create the streams.

Now, use the Editor tab in the ksqlDB cluster UI to create a table named player_scores. The table will store an aggregation of your system’s latest state, i.e. a materialized view. In your case, it’ll represent the sum of the score events for each player. Paste the following query into the Editor and click Run query.

CREATE TABLE player_scores AS
    SELECT player_id, SUM(score) as total_score
    FROM NEONPUBLICPLAYER_SCORES
    GROUP BY player_id
    EMIT CHANGES;

This creates a table in ksqlDB that is continuously updated in response to events in the NEONPUBLICPLAYER_SCORES stream. The table will contain a row for each player with their unique ID and the sum of all associated score events.

Confirm the table is working as expected by selecting PLAYER_SCORES under the Tables heading and clicking Query table. A list of records that contain the sum of player scores will be displayed.

Return to the Neon console and insert more data into the player_scores table. The materialized view will automatically update within a few seconds to reflect the new total_score for each player.

The materialized view can be consumed by interacting with the ksqlDB REST API. Visit the API Keys in your cluster’s UI to create an API key to authenticate against the ksqlDB REST API, and use the settings tab in your ksqlDB cluster UI to find the cluster’s hostname.

You can use the following cURL command to get a stream of changes from the table in your terminal:

curl --http1.1 -X "POST" "https://$KSQLDB_HOSTNAME/query" \
-H "Accept: application/vnd.ksql.v1+json" \
--basic --user "$API_KEY:$API_SECRET" \
-d $'{
"ksql": "SELECT * FROM PLAYER_SCORES EMIT CHANGES;",
"streamsProperties": {}
}'

This command will establish a persistent connection that streams a header followed by changes as they occur in the table in real-time. You can confirm this by using the SQL Editor in the Neon console to insert more data into the player_scores table and observing the updated total scores being streamed into your terminal:

[{"header":{"queryId":"transient_PLAYER_SCORES_7047340794163641810","schema":"`PLAYER_ID` INTEGER, `TOTAL_SCORE` DECIMAL(10, 2)"}},
{"row":{"columns":[1,4.50]}},
{"row":{"columns":[2,5.04]}},
{"row":{"columns":[3,5.85]}},
{"row":{"columns":[4,6.12]}},
{"row":{"columns":[5,11.43]}},
{"row":{"columns":[1,5.00]}},
{"row":{"columns":[4,6.80]}},
{"row":{"columns":[5,12.70]}},
{"row":{"columns":[1,5.82]}},
{"row":{"columns":[4,6.92]}}'

This same stream of events over HTTP can be integrated into your application to enable real-time updates in a UI or to update other components in your application architecture.

Conclusion

Neon’s support for Postgres’ logical replication enables change data capture, and streaming database changes to Apache Kafka for real-time processing with ksqlDB to create enriched data streams and materialized views using SQL syntax. If you’re looking for a Postgres database, sign up and try Neon for free. Join us in our Discord server to share your experiences, suggestions, and challenges.

Fan-out from Postgres with Change Data Capture using Debezium and Upstash Redis

Evan Shortiss — Wed, 14 Feb 2024 23:32:41 +0000

Neon now has beta support for Postgres Logical Replication. This enables teams to use Change Data Capture (CDC) to observe database changes – such as INSERT and UPDATE operations – and stream these changes to downstream systems.

We previously wrote about the benefits of CDC and how it enables Event-Driven Architecture (EDA). An EDA facilitates the implementation of messaging patterns, such as fan-out, with your Neon Postgres database at the heart of the system.

Implementing a fan-out pattern enables you to create applications composed of loosely coupled components. Downstream consumers can work individually or as groups to asynchronously process database events to update other parts of your system in real-time.

For example, a user sending a message in your application might necessitate sending a push notification to other users. If the message is written to the database and, in turn, a message broker, the broker can facilitate fan-out in a one-to-many fashion to downstream consumers, one of which is repsonsible for delivery to mobile devices. But how exactly do you stream changes from Postgres to a message broker, and why can’t the application layer simply handle this task? Keep reading to find out.

This guide will show you how to use Debezium Server with Neon’s serverless Postgres, and Redis streams provided by Upstash, to enable message fan-out with at-least-once delivery semantics. A repository with a sample Node.js consumer for the data produced by Debezium to Redis streams is available on GitHub at evanshortiss/neon-debezium-redis-cdc.

The Dual Write Problem

Before we dive into the solution, let’s understand why it’s recommended to use a CDC platform like Debezium to stream changes from Postgres to a message broker.

Imagine a scenario where you must insert or update a record in Postgres and notify downstream systems of the write operation in real-time. The delivery requirements for such a system are typically at-least-once, i.e., you need guaranteed delivery to the event consumers. A naive approach to this problem involves dual writes.

A dual write occurs when you alter data in two systems without ensuring consistency. The following pseudocode provides a simple illustration of the dual-write problem:

async function insertAndPublishMessage (data) {
  await postgres.query(
    SQL`
      INSERT INTO messages (from, to, content)
      VALUES (${data.from},${data.to},${data.content})
    `
  )

  await broker.publish('message.insert', data)
}

In a perfect world, this code persists data in Postgres, and once successful, it publishes an event to a message broker such as Redis streams, Apache Kafka, or Amazon Kinesis. This broker facilitates a fan-out pattern, as shown in the following architecture diagram.

If the database transaction fails, the prior code will throw an error and not publish a message to the message broker. However, if the database transaction succeeds but the message broker is down, you could end up with an inconsistent state because the downstream consumers are unaware of the database change(s). Changing the order of operations doesn’t help since you might inform downstream consumers of a change that fails to be committed to the database.

Maybe you’re thinking that Postgres’ nifty LISTEN and NOTIFY capabilities offer a solution to this problem; however, those provide an at-most-once delivery mechanism, meaning sent events will be lost if downstream listeners are briefly disconnected from Postgres.

Using Debezium to Avoid Dual Writes

Debezium consumes changes from Postgres’ write-ahead log (WAL) via a logical replication slot and streams these changes to messaging infrastructure in real-time. By consuming the WAL, only committed changes to the database are processed by Debezium and reliably forwarded to downstream message brokers, thus avoiding the dual write problem. This is illustrated in the following architectural diagram:

Debezium tracks its progress through the WAL by storing its current WAL offset. This means you can safely restart Debezium, and it will resume streaming change events from where it last left off. If the downstream message broker is unavailable, Debezium will retry sending messages until the broker returns online.

Get Started with Neon and Logical Replication

To start using Debezium with Neon’s serverless Postgres, sign up for Neon and create a project.

Once you have a project, enable logical replication:

Select your project in the Neon console.
On the Neon Dashboard, navigate to Settings > Beta.
Click the Enable button.
Confirm that you understand the changes and click Enable again.

That’s it! A message will appear stating that logical replication is enabled for your project.

Before moving to the next section, create a table and add some data using the SQL Editor in the Neon console by running the following SQL statements:

CREATE TABLE playing_with_neon(id SERIAL PRIMARY KEY, name TEXT NOT NULL, value REAL);

INSERT INTO playing_with_neon (name, value)
VALUES 
  ('Mario', random()),
  ('Peach', random()),
  ('Bowser', random()),
  ('Luigi', random()),
  ('Yoshi', random());

Configure Postgres as a Debezium Data Source

To begin, create a workspace folder in your development environment, and within it, create another folder to store your Debezium configuration.

mkdir -p debezium-neon-redis/debezium

cd debezium-neon-redis

Debezium uses a properties file to store the necessary configuration. Create a file named application.properties inside the debezium/ folder, and add the following configuration to define Postgres as a data source:

# PostgreSQL source connector properties
debezium.source.database.hostname=${PGHOST}
debezium.source.database.port=${PGPORT:5432}
debezium.source.database.user=${PGUSER}
debezium.source.database.password=${PGPASSWORD}
debezium.source.database.dbname=${PGDATABASE}
debezium.source.database.server.name=tutorial
debezium.source.snapshot.mode=initial
debezium.source.plugin.name=pgoutput
debezium.source.connector.class=io.debezium.connector.postgresql.PostgresConnector
debezium.source.schema.whitelist=public
table.include.list=playing_with_neon

This configuration instructs Debezium to:

Connect to Postgres using the connection details defined in the PG environment variables.
Accept payloads in pgoutput format. Neon also supports wal2json.
Subscribe to changes in the playing_with_neon table.
Perform a table snapshot to inform downstream consumers of the initial state. This is useful if you’re adding Debezium to an existing application that has tables that already contain data and you’d like to process the existing rows and not just new rows.

Next, add the following lines to the application.properties. Comments are provided to explain what these configuration properties do:

# Enable human readable logs
quarkus.log.console.json=${JSON_FMT_LOG:false}

# Exclude the DB schema from event payloads to keep them lean
debezium.format.schemas.enable=false

# Keep track of the WAL offsets to safely resume on restarts
debezium.source.offset.storage.file.filename=data/offsets.dat
debezium.source.offset.flush.interval.ms=0

Nice work! You’ve enabled logical replication for a Neon serverless Postgres database and created a Debezium Server configuration to use it as a data source.

Configure Upstash Redis as a Debezium Data Sink

When Debezium captures database changes, it needs to stream them to a destination known as a “sink”. You’ll be using a Redis instance provided by Upstash as a sink; however, Debezium supports various sinks, meaning you can stream data to your preferred messaging infrastructure.

Provision a Redis instance by visiting console.upstash.com, choosing Redis, and clicking the Create Database button. Enter the following parameters when creating your Redis instance:

Name: neon-debezium
Type: Regional
Region: Select the region closest to your Neon Postgres database.
TLS (SSL) Enabled: Yes
Eviction: Yes

The form will resemble the following screenshot:

Add the following entries to the application.properties to define Redis as your data sink:

debezium.sink.type=redis
database.user=${REDIS_USER}
debezium.sink.redis.address=${REDIS_ADDRESS}
debezium.sink.redis.password=${REDIS_PASSWORD}
debezium.sink.redis.wait.retry.enabled=true
debezium.sink.redis.ssl.enabled=true

This configuration will send change events to a Redis stream data structure in your Upstash Redis instance. In typical Redis fashion, all data is stored under a key. The key name will be composed of the “debezium” prefix followed by the database schema and table name, resulting in debezium.public.playing_with_neon in this particular example.

Check the Redis documentation for a detailed description of all supported configuration properties for the Redis sink.

Start a Debezium Server Container

Your application.properties references environment variables. It’s time to create a .env file to store those variables to pass them into the Debezium Server container. You can also run Debezium on your host machine using Java and by downloading a Debezium Server distribution, but Docker and Podman allow you to test different versions quickly without affecting your host environment.

Visit the Dashboard for your project in the Neon Console, and select the Parameters only option in the Connection Details pane. Copy the values displayed and paste them into a .env file in the debezium-neon-redis folder you created earlier, removing the surrounding single quotes.

You’ll also need to define the Redis environment variables expected by the application.properties. These are displayed in your Redis instance’s Details section on the Upstash Console.

Your .env file should resemble the following example:

# Do not wrap the variable values in quotes. Docker doesn't like that...
PGHOST=ep-adj-noun-a1b2c3.us-west-2.aws.neon.tech
PGDATABASE=neondb
PGUSER=yourusername
PGPASSWORD=yourpassword

REDIS_ADDRESS=usw1-random-animal-12345.upstash.io:33324
REDIS_USER=default
REDIS_PASSWORD=upstashredispassword

Before continuing, confirm your folder structure matches this sample:

debezium-neon-redis
├── .env
├── debezium
│ └── application.properties

Now start a Debezium Server container using this command from within the debezium-neon-redis directory:

docker run --rm \
--name debezium-server \
--env-file .env \
-v $PWD/debezium:/debezium/conf \
debezium/server:2.5.1.Final

Debezium Server will start and print a series of logs. The logs include information about the initial snapshot being performed and should end with a line stating that Debezium is Searching for WAL resume position.

Viewing Changes in Redis and Consuming them with Node.js

Return to the Neon SQL Editor in your project and run the following INSERT statement:

INSERT INTO playing_with_neon (name, value) VALUES ('Yoshi', random());

Debezium Sever should log a message resembling First LSN 'LSN{0/2159960}' received. This confirms that Debezium is successfully consuming the WAL!

To confirm data is being sent to Redis:

Return to the console.upstash.com.
Select your Redis instance.
Navigate to the Data Browser tab.
Set the filter to All Types or Stream, and search for debezium*.

You should see a key named debezium.public.playing_with_neon. This stream contains the 5 initial rows you inserted in your database with matching timestamps. The new row you inserted a moment ago will also be there.

You can now use a Redis client and the XREADGROUP command to have consumer groups process messages in parallel as they arrive to the stream. Messages should be acknowledged using the XACK command, since this enables you to restart consumers and have them resume processing where they last left off.

A simple Node.js program to process messages as part of a consumer group would be similar to this, but with better error handling in a production codebase, of course. A complete example can be found in this repository on GitHub.

import * as redis from 'redis'

const REDIS_STREAMS_KEY = 'debezium.public.playing_with_neon'
const REDIS_GROUP_ID = 'pwn-lr'

// Remember to change this value for each replica of this
// program that you're running. It must be stable value
// across restarts!
const REDIS_CONSUMER_ID = 'consumer-0'

async function main () {
  const client = redis.createClient({
    name: 'lr-demo-client',
    url: 'redis-stack:6379'
  })

  // The ID of '0' here makes sure the group consumes messages from
  // the beginning of the stream. Use '$' if you want to ignore old
  // messages that were created prior to the group's creation time
  await client.xGroupCreate(REDIS_STREAMS_KEY, REDIS_GROUP_ID, '0');

  readFromStream()

  async function readFromStream() {
    const res = await client.xReadGroup(
      REDIS_GROUP_ID, REDIS_CONSUMER_ID,
      { key: REDIS_STREAMS_KEY, id: '&gt;' },
      { BLOCK: 0, COUNT: 10 }
    )

    if (res) {
      for (const { id, message } of res[0].messages) {
        // Add message processing logic here...

        // ...then ack the message!
        await client.xAck(REDIS_STREAMS_KEY, REDIS_GROUP_ID, id)
      }
    }

    // Immediately queue the next xReadGroup call
    setImmediate(readFromStream)
  }
}

main()

Conclusion

Neon’s support for Postres’ Logical Replication enables development teams to leverage Event-Driven Architectures to create robust, real-time applications with Postgres at the core. Change Data Capture platforms and technologies like Debezium provide low-code open-source solutions to reliably stream changes from Postgres to messaging systems such as Redis for further processing and analysis. If you’re looking for a Postgres database, sign up and try Neon for free. Join us in our Discord server to share your experiences, suggestions, and challenges.

Using Neon’s Auto-Suspend with Long-Running Applications

Evan Shortiss — Wed, 24 Jan 2024 23:27:01 +0000

We’re Neon, a cloud-native serverless Postgres solution. With Neon, your Postgres databases and environments are just one click away. You can still benefit from serverless Postgres if your application isn’t serverless. Try using Neon’s serverless Postgres with your long-running applications today.

We refer to Neon as serverless Postgres because it can auto-suspend your database when it’s not actively serving queries and autoscale when it is. Neon’s serverless Postgres pairs perfectly with applications deployed in serverless environments such as Cloudflare Workers or Vercel, thanks to our serverless driver and support for SQL over HTTP. Your database should be able to scale to zero when it’s not processing queries – just like your serverless applications. This can result in savings not just for production workloads but also for development and staging environments.

If you have a traditional long-running application, sometimes called “serverfull”, and you’re interested in trying Neon, you’ll be glad to know that Neon is compatible with those applications too. After all, Neon is just Postgres. These application servers, usually running MVC-type frameworks like Ruby on Rails and Django, can take advantage of Neon’s auto-suspend to reduce database costs and utilization during off-peak times, just like serverless applications. In the case of a long-running application, auto-suspend will inevitably sever any connections between your application and the database. When your application attempts to reconnect and issue new queries, Neon will restart your Postgres database to serve them.

This post will illustrate configuring your Postgres driver to handle auto-suspend using node-postgres, how to improve performance using client-side pooling, and how to put it all together in an application that uses HTMX, Fastify, and Drizzle ORM. Not a Node.js developer? Don’t stop reading! The concepts discussed in this post apply to other Postgres drivers and runtimes.

Understanding Neon’s Auto-Suspend Feature

How does auto-suspend work anyway? Let’s look into it so you can better configure your applications and environments to handle auto-suspend gracefully. Doing so will enable you to take advantage of cost savings and make your application more resilient to connection errors.

As the name suggests, Neon’s auto-suspend feature will suspend database instances if no activity has been detected within the configured auto-suspend window for a given compute. Auto-suspend works even if clients are connected to the database, but only under certain circumstances. Since Neon is open-source, you can see exactly how this feature works by looking at files such as compute_tools/src/monitor.rs in the neondatabase/neon repository on GitHub.

At the time of writing, auto-suspend is triggered when the following conditions are true:

No activity has been detected in a time period larger than the auto-suspend window.
No WAL senders are active. In other words, you’re not using Logical Replication.
Autovacuum is not currently running.

You can test the impact of auto-suspend on a long-lived application that lacks error handling and reconnect logic using the following code.

// filename: http-server.pg-client.ts

import { createServer } from 'http'
import { Client } from 'pg'

let client

async function getVersion () {
  if (!client) {
    client = new Client({ connectionString: process.env.DATABASE_URL })
    await client.connect()
  }

  return (await client.query('SELECT version()')).rows[0].version;
}

createServer(async function (req, res) {
  const version = await getVersion()

  res.write(version)
  res.end()
}).listen(8080)

This Node.js application will work fine so long as it receives consistent traffic. The consistent traffic would prevent Neon’s auto-suspend from being triggered for the database specified by DATABASE_URL. If auto-suspend were to suspend the database, this program would exit due to an unhandled client error event. Better handling of the connection lifecycle is necessary to make this application more resilient.

Managing Connections with Client-Side Pooling

The prior example’s getVersion() code could be modified to open and close a database connection for each query. This would address the concerns around connection loss but could also introduce the following issues:

Add tens or hundreds of milliseconds of latency overhead per request.
Exhaust Postgres’ connection limits.

Neon’s pooler endpoint (based on PgBouncer) and our serverless driver can provide a workaround for these issues and are especially important for serverless application architectures where many instances of your application will open connections to the database. However, you may want to continue using your existing database driver with Neon, or the limitations of PgBouncer might pose a problem for your long-running application.

Using a client-side connection pool with your existing Postgres driver can:

Control the number of open connections by reusing existing connections.
Avoid the latency overhead of opening and closing connections for each query.
Manage connection lifecycle on your behalf and gracefully handle Neon’s auto-suspend.

Switching to a client-side pool is often a trivial code change, as shown by the following code sample.

// filename: http-server.pg-pool.ts

import { createServer } from 'http'
import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  idleTimeoutMillis: 60 * 1000,
  max: 20
})

// Log connection loss errors, but do not terminate the program. The pool will create
// new connections that will start the Neon postgres compute to run future queries
// if the compute endpoiint is idle
pool.on('error', (err) =&gt; {
  console.log(`pg:pool error ${err.message}. open connections ${pool.totalCount}`)
})

createServer(async function (req, res) {
 // The pool will run this query once a connection is available
  const version = (await pool.query('SELECT version()')).rows[0].version

  res.write(version)
  res.end()
}).listen(8080)

The connection pool requires minimal code to handle the loss of connectivity with a Neon Postgres compute that has auto-suspend enabled. When your application needs to run queries at some point in the future, the pool will establish one or more new connections with your database, causing it to start up.

Moreover, reusing connections in the pool can significantly boost your application’s throughput. Performing a benchmark using Apache Bench reveals the following performance metrics in favor of the codebase that uses a connection pool:

	Total Time (100 requests)	Average Latency	P99 Response Time	Req/Sec
Pooled	0.27 seconds	46ms	370ms	370
Unpooled	3.786 seconds	678ms	764ms	26.4

You can test it yourself using the following command to issue 100 requests with a concurrency of 20 at the application:

ab -n 100 -c 20 'http://127.0.0.1:8080/'

Note: Results will depend on hardware resources, connection quality, pool size, proximity to your Neon Postgres database region, assigned Neon compute resources, and other factors.

Real-World Example with HTMX, Fastify, and Drizzle

Putting these pieces together, let’s look at how this knowledge can be applied to an application built using HTMX, Fastify, and Drizzle ORM. The source code for the application is available in evanshortiss/neon-and-long-running-apps on GitHub.

The application includes code that gracefully handles Neon’s auto-suspend. This ensures that it reconnects to the Neon Postgres database when new requests come in but tolerates losing connectivity to the database during periods of inactivity.

The primary endpoint for this application uses the following code to fetch elements of the periodic table from the database and render them as an HTML page:

server.get('/', async (req, reply) =&gt; {
  const db = await req.drizzle()
  const elements = await db.select().from(Elements)

  return reply.view('/views/index', { elements });
});

The code defines an index (GET /) HTTP endpoint on the Fastify server that:

Invokes a custom Fastify plugin (req.drizzle()) to obtain a Drizzle instance.
Uses the Drizzle instance to query the database for all elements.
Renders and returns an HTML page containing the elements to a client.

HTMX’s hx-delete enables user interaction to delete database elements and dynamically update the page’s HTML in response.

When benchmarked using the same Apache Bench command as before, hosting a single instance of this Node.js application on a lightweight dual-core VM produces the following results in favor of connection pooling.

	Total Time (100 requests)	Average Latency	P99 Response Time	Req/Sec
Pooled	0.304 seconds	52ms	60ms	329
Unpooled	3.76 seconds	672ms	755ms	26.6

Note: The Node.js application was hosted in SFO. The Neon Postgres database was hosted in the US-West region and had 1 shared vCPU and 1 GB of RAM. The pg.Pool size was set to 20.

Conclusion

Neon’s serverless Postgres can be used with both traditional long-running applications and serverless architectures. If you have long-running applications implementing robust connection handling, you can use Neon’s auto-suspend feature to reduce your database bill, especially for non-production environments. Sign up to try Neon with your existing applications, and join us on Discord to share your experiences, suggestions, and challenges with us.

Auth setup with Neon, Keycloak and Koyeb

Evan Shortiss — Fri, 15 Dec 2023 19:15:41 +0000

Keycloak is an open-source identity and access management solution that centralizes authentication and authorization management. It provides features such as single sign-on (SSO), two-factor authentication, social login, and user federation with LDAP or Active Directory user federation. Various identity protocols such as Open ID Connect, SAML 2.0, and OAuth 2.0 are supported, which makes integrating Keycloak with new and existing applications easy.

Whether you’re a novice getting started with Keycloak or a veteran user, you’ll need a database to support your Keycloak deployment in production. Since Keycloak has first-class support for Postgres, this guide will outline a minimal setup to use Keycloak with Neon’s Postgres. All configurations and code in this guide can be found in the neondatabase/keycloak-example repository on GitHub.

Create a Neon project and Keycloak Database

Each Neon project includes a single Postgres instance that contains a neondb database by default. Still, it’s best to create a dedicated keycloak database to store Keycloak’s data.

To create a project, visit the Neon console. If it’s your first time visiting the console you should be prompted to create a new project as shown. If you already have a project you’d like to use, select it from the projects list.

Create the Keycloak database by visiting the SQL Editor section of your project, and running the following query:

CREATE DATABASE keycloak;

You can verify that the keycloak database was created by viewing the Databases section of the Neon console or running the SELECT datname FROM pg_database; query in the SQL Editor.

Configure a Keycloak Database User

Instead of accessing your new keycloak database using the default user with the neon_superuser role, creating a new user named keycloak_admin is best. This new user will have administrative privileges limited to the keycloak database.

Use the Neon SQL Editor to input the commands to create the user and assign permissions:

Visit the SQL Editor in your project.
Select the keycloak database using the dropdown menu in the top-right.

Issue the following command to create a new user named keycloak_admin, making sure to replace the password with a value that has 60 bits of entropy, per our documentation:

/* Create a strong password per: https://neon.tech/docs/manage/roles */
CREATE USER keycloak_admin WITH PASSWORD 'r3plac3_th1s';

/* Assign permissions to the `keycloak_admin` user */
GRANT ALL ON SCHEMA public TO keycloak_admin;

Deploy Keycloak on Koyeb

Keycloak can be deployed on a machine with a JDK installed, or from a container image using Docker or Podman. Setting up infrastructure manually can be quite a chore, so instead I’ll show you how to deploy Keycloak on Koyeb with valid SSL certificates. If they support deploying container images, you can replace Koyeb with your preferred hosting provider.

To get started, sign up for an account at koyeb.com. Next, click this link to start deploying Keycloak on Koyeb from a template with some preconfigured values.

Set the following parameters for the deployment:

Instance type: Eco is adequate for testing.
Region: Choose the region closest to your Neon Postges database’s region.
Configure the following environment variables:
1. KC_DB_URL: Replace the NEON_HOSTNAME with your Neon database’s hostname from the Connection Details on Neon’s console, e.g ep-broken-hill-12345.us-east-2.aws.neon.tech.
2. KC_DB_USERNAME: keycloak_admin.
3. KC_DB_PASSWORD: The password you set for keycloak_admin.
4. KC_HOSTNAME: This must match the hostname you define when deploying the service on Koyeb. The hostname is listed at the bottom of the deploy page and is a combination of the App name and your organization name, e.g keycloak-your-org.koyeb.app.
5. KEYCLOAK_ADMIN_PASSWORD: This will be the password you use to log in as the admin user and perform administrative actions in Keycloak

Click the Deploy button and you’ll be directed to a screen where you can monitor the status of your deployment.

Scroll down to the bottom of this page and wait for the logs to print a line stating that Keycloak has started. It will resemble the following example:

Keycloak 23.0.1 on JVM (powered by Quarkus 3.2.9.Final) started in 33.009s. Listening on: http://0.0.0.0:8080

Create a Keycloak Realm and Client

Keycloak uses the concept of realms to create a self-contained space where you manage users, roles, and authentication settings for one or more client applications. The master realm is created by default and is used to manage other realms. A realm contains one or more clients representing applications, e.g., a Next.js application deployed on a specific domain.

Return to your Koyeb Apps list and click on the hostname underneath your Keycloak service to visit the Keycloak welcome page. Click the link to access the Administration Console and log in using the username and password you set using KEYCLOAK_ADMIN and KEYCLOAK_ADMIN_PASSWORD when first deploying Keycloak.

The master realm will be selected by default. Use the dropdown in the top left corner to click the Create realm button. Upload the example quickstart.json realm file from the neondatabase/keycloak-example repository to create a realm.

Once the realm is created you can see a sample user named alice, and a client named nextjs-application within the Users and Clients sections.

By querying some tables, you can confirm that Keycloak is using your Neon Postgres database. For example:

Visit the SQL Editor in your project on the Neon console.
Run the query SELECT * from user_entity;.

The user named alice defined in the sample realm JSON should be listed in the output.

Connecting a Next.js Application to Keycloak

The repository associated with this blog post at neondatabase/keycloak-example includes a Next.js application that uses NextAuth.js to integrate with Keycloak to perform user authentication.

Clone the repository, install the dependencies, and create a .env.local file:

git clone git@github.com:neondatabase/keycloak-example.git

cd keycloak-example/next-auth-example

cp .env.local.example .env.local

npm i

Edit the .env.local file so that the hostname of the KEYCLOAK_ISSUER points to your Keycloak instance, and generate an AUTH_SECRET using the instructions in the file. Start the application using the npm run dev command, and visit http://localhost:3000 to interact with it.

Click the Sign In button in the top right corner to initiate the OAuth-based login flow using Keycloak. You can sign in using the username alice and password alice. Once logged in you can visit the different pages in the application and view the user’s session data.

Conclusion and Next Steps

Now you know how to use Neon as your Postgres storage provider for Keycloak. Consider crafting an optimized Keycloak container for production deployment to decrease startup time, and refer to the Keycloak database configuration guide for information on connection pooling and other database configuration properties. You can also experiment with Neon’s branching and point-in-time restore capability to discover how to restore Keycloak data to an earlier version if needed.