integrate-neon-with-drizzle-orm

name: Integrate Neon with Drizzle ORM description: Use this skill when user asks to integrate Neon (serverless Postgres) with Drizzle ORM.

Neon and Drizzle Integration Guidelines

Overview

This guide covers the specific integration patterns, configurations, and optimizations for using Drizzle ORM with Neon Postgres. Follow these guidelines to ensure efficient, secure, and robust database operations in serverless and traditional environments.

Dependencies

For Neon with Drizzle ORM integration, include these specific dependencies. The ws package is required for persistent WebSocket connections in Node.js environments older than v22.

bun install drizzle-orm @neondatabase/serverless ws
bun install -D drizzle-kit dotenv @types/ws

Neon Connection String

Always use the Neon connection string format and store it in an environment file (.env, .env.local).

DATABASE_URL="postgresql://[user]:[password]@[neon_hostname]/[dbname]?sslmode=require&channel_binding=require"

Connection Setup: Choosing the Right Driver Adapter

Neon's serverless driver offers two connection methods: HTTP and WebSocket. Drizzle has a specific adapter for each.

1. HTTP Adapter (Recommended for Serverless/Edge)

This method is ideal for short-lived, stateless environments like Vercel Edge Functions or AWS Lambda. It uses fetch for each query, resulting in very low latency for single operations.

• Use the neon client from @neondatabase/serverless.
• Use the drizzle adapter from drizzle-orm/neon-http.

// src/db.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import { config } from "dotenv";

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

if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL is not defined');
}

const sql = neon(process.env.DATABASE_URL);
export const db = drizzle(sql);

2. WebSocket Adapter (for node-postgres compatibility)

This method is suitable for long-running applications (e.g., a standard Node.js server) or when you need support for interactive transactions. It maintains a persistent WebSocket connection.

• Use the Pool client from @neondatabase/serverless.
• Use the drizzle adapter from drizzle-orm/neon-serverless.
• Configure the WebSocket constructor for Node.js environments older than v22.

// src/db.ts
import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import { config } from "dotenv";
import ws from 'ws';

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

if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL is not defined');
}

// Required for Node.js < v22
neonConfig.webSocketConstructor = ws;

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

Drizzle Config for Neon

Configure drizzle.config.ts to manage your schema and migrations. Neon is fully Postgres-compatible, so the dialect is postgresql.

// drizzle.config.ts
import { config } from 'dotenv';
import { defineConfig } from "drizzle-kit";

config({ path: '.env.local' }); // Use .env.local for local dev

export default defineConfig({
  schema: "./src/schema.ts",
  out: "./drizzle", // Or your preferred migrations folder
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  }
});

Migrations with Drizzle Kit

drizzle-kit is used to generate and apply schema changes to your Neon database.

1. Generate Migrations

After changing your schema in src/schema.ts, generate a new migration file.

bunx drizzle-kit generate

This command reads your drizzle.config.ts, compares your schema to the database state, and creates SQL files in your output directory (./drizzle).

2. Apply Migrations

You can apply migrations via the command line or programmatically.

Command Line:

bunx drizzle-kit migrate

Schema Considerations for Neon

Standard Postgres Schema

Define your schema using Postgres-specific types from drizzle-orm/pg-core.

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

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

// Export types for type safety
export type User = typeof usersTable.$inferSelect;
export type NewUser = typeof usersTable.$inferInsert;

// Example posts table for relationship demonstrations
export const postsTable = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  userId: integer('user_id').notNull().references(() => usersTable.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export type Post = typeof postsTable.$inferSelect;
export type NewPost = typeof postsTable.$inferInsert;

Neon-Specific Query Optimizations

Efficient Queries for Serverless

Optimize for Neon's serverless environment:

• Keep connections short-lived
• Use prepared statements for repeated queries
• Batch operations when possible

// Example of optimized query for Neon
import { db } from '../db';
import { sql } from 'drizzle-orm';
import { usersTable } from '../schema';

export async function batchInsertUsers(users: NewUser[]) {
  // More efficient than multiple individual inserts on Neon
  return db.insert(usersTable).values(users).returning();
}

// For complex queries, use prepared statements
export const getUsersByRolePrepared = db.select()
  .from(usersTable)
  .where(sql`${usersTable.role} = $1`)
  .prepare('get_users_by_role');

// Usage: getUsersByRolePrepared.execute(['admin'])

Transaction Handling with Neon

Neon supports transactions through Drizzle:

import { db } from '../db';
import { usersTable, postsTable } from '../schema';

export async function createUserWithPosts(user: NewUser, posts: NewPost[]) {
  return await db.transaction(async (tx) => {
    const [newUser] = await tx.insert(usersTable).values(user).returning();

    if (posts.length > 0) {
      await tx.insert(postsTable).values(
        posts.map(post => ({
          ...post,
          userId: newUser.id
        }))
      );
    }

    return newUser;
  });
}

Working with Neon Branches

A key feature of Neon is database branching. You can create isolated copies of your database for development, testing, or preview environments. Manage connections to these branches using environment variables.

Here is a common pattern for setting up your database client to connect to different branches based on the environment:

// Using different Neon branches with environment variables
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

// For multi-branch setup
const getBranchUrl = () => {
  const env = process.env.NODE_ENV;
  if (env === 'development') {
    return process.env.DEV_DATABASE_URL;
  } else if (env === 'test') {
    return process.env.TEST_DATABASE_URL;
  }
  return process.env.DATABASE_URL;
};

const sql = neon(getBranchUrl()!);
export const db = drizzle({ client: sql });

Neon-Specific Error Handling

Handle Neon-specific connection issues:

import { db } from '../db';
import { usersTable } from '../schema';

export async function safeNeonOperation<T>(operation: () => Promise<T>): Promise<T> {
  try {
    return await operation();
  } catch (error: any) {
    // Handle Neon-specific error codes
    if (error.message?.includes('connection pool timeout')) {
      console.error('Neon connection pool timeout');
      // Handle appropriately
    }

    // Re-throw for other handling
    throw error;
  }
}

// Usage
export async function getUserSafely(id: number) {
  return safeNeonOperation(() =>
    db.select().from(usersTable).where(eq(usersTable.id, id))
  );
}

Best Practices for Neon with Drizzle

1. Connection Management

   • Keep connection times short for serverless functions
   • Use connection pooling for high traffic applications

2. Neon Features

   • Utilize Neon branching for development and testing
   • Consider Neon's auto-scaling for database design

3. Query Optimization

   • Batch operations when possible
   • Use prepared statements for repeated queries
   • Optimize complex joins to minimize data transfer

4. Schema Design

   • Leverage Postgres-specific features supported by Neon
   • Use appropriate indexes for your query patterns
   • Consider Neon's performance characteristics for large tables