@drizzle-adapter/node-postgres

PostgreSQL adapter implementation for the Drizzle Adapter ecosystem using the node-postgres (pg) driver.

Overview

The @drizzle-adapter/node-postgres package provides the PostgreSQL implementation for the Drizzle Adapter interface using the widely-adopted node-postgres driver. While you don't interact with this package directly (you use @drizzle-adapter/core instead), it enables PostgreSQL support in the Drizzle Adapter ecosystem.

Why node-postgres?

The node-postgres driver offers several advantages:

• Mature and Stable: Battle-tested in production for many years
• Native Connection Pooling: Built-in connection pool management
• Full Feature Support: Access to all PostgreSQL features
• Binary Data Support: Efficient binary data handling
• Prepared Statements: Automatic statement preparation and caching
• Type Parsing: Native handling of PostgreSQL data types
• SSL Support: Secure connections with full SSL configuration
• Active Maintenance: Regular updates and security patches

Installation

# Install both the core package and the PostgreSQL adapter
pnpm install @drizzle-adapter/core @drizzle-adapter/node-postgres

Important: Adapter Registration

For the adapter to work correctly with the DrizzleAdapterFactory, you must import it for its self-registration side effects:

// Import for side effects - adapter will self-register
import '@drizzle-adapter/node-postgres';

// Now you can use the factory
import { DrizzleAdapterFactory } from '@drizzle-adapter/core';

Usage

Configuration

import { DrizzleAdapterFactory, TypeDrizzleDatabaseConfig } from '@drizzle-adapter/core';

const config: TypeDrizzleDatabaseConfig = {
  DATABASE_DRIVER: 'node-postgres',
  DATABASE_URL: 'postgresql://user:password@localhost:5432/database'
};

const factory = new DrizzleAdapterFactory();
const adapter = factory.create(config);

Schema Definition

const dataTypes = adapter.getDataTypes();

const users = dataTypes.dbTable('users', {
  id: dataTypes.dbSerial('id').primaryKey(),
  name: dataTypes.dbText('name').notNull(),
  email: dataTypes.dbText('email').notNull().unique(),
  profile: dataTypes.dbJsonb('profile'),
  searchVector: dataTypes.dbTsVector('search_vector'),
  createdAt: dataTypes.dbTimestampTz('created_at').defaultNow()
});

const posts = dataTypes.dbTable('posts', {
  id: dataTypes.dbSerial('id').primaryKey(),
  userId: dataTypes.dbInteger('user_id')
    .references(() => users.id),
  title: dataTypes.dbText('title').notNull(),
  content: dataTypes.dbText('content').notNull(),
  tags: dataTypes.dbArray('tags', { type: 'text' }),
  metadata: dataTypes.dbJsonb('metadata'),
  published: dataTypes.dbBoolean('published').default(false),
  createdAt: dataTypes.dbTimestampTz('created_at').defaultNow()
});

Basic CRUD Operations

import { eq, and, or, desc, sql } from 'drizzle-orm';

const connection = await adapter.getConnection();
const client = connection.getClient();

try {
  // INSERT
  // Single insert with returning
  const [newUser] = await client
    .insert(users)
    .values({
      name: 'John Doe',
      email: '[email protected]',
      profile: { bio: 'Hello!' }
    })
    .returning();

  // Bulk insert
  await client
    .insert(posts)
    .values([
      {
        userId: newUser.id,
        title: 'First Post',
        content: 'Hello, world!',
        tags: ['hello', 'first']
      },
      {
        userId: newUser.id,
        title: 'Second Post',
        content: 'Another post',
        tags: ['second']
      }
    ]);

  // SELECT
  // Select all
  const allUsers = await client
    .select()
    .from(users);

  // Select with conditions
  const user = await client
    .select()
    .from(users)
    .where(eq(users.email, '[email protected]'));

  // Select with join and array operations
  const postsWithTags = await client
    .select({
      userName: users.name,
      postTitle: posts.title,
      tags: posts.tags
    })
    .from(posts)
    .leftJoin(users, eq(posts.userId, users.id))
    .where(sql`${posts.tags} && ARRAY['hello']::text[]`)
    .orderBy(desc(posts.createdAt));

  // Full-text search
  const searchResults = await client
    .select()
    .from(posts)
    .where(sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content}) @@ to_tsquery('english', 'hello')`)
    .orderBy(desc(posts.createdAt));

  // UPDATE
  // Update with JSONB operations
  await client
    .update(users)
    .set({
      profile: sql`${users.profile} || '{"title": "New Title"}'::jsonb`
    })
    .where(eq(users.id, newUser.id));

  // DELETE
  await client
    .delete(posts)
    .where(
      and(
        eq(posts.userId, newUser.id),
        eq(posts.published, false)
      )
    );

} finally {
  // Always close the connection
  await connection.disconnect();
}

Advanced Features

Transaction Support

const connection = await adapter.getConnection();
const client = connection.getClient();

try {
  await client.transaction(async (tx) => {
    const [user] = await tx
      .insert(users)
      .values({
        name: 'John Doe',
        email: '[email protected]'
      })
      .returning();

    await tx
      .insert(posts)
      .values({
        userId: user.id,
        title: 'My First Post',
        content: 'Hello, world!'
      });
  });
} finally {
  await connection.disconnect();
}

Full-Text Search

// Create search vector
await client.execute(sql`
  UPDATE ${posts}
  SET search_vector = to_tsvector('english', title || ' ' || content)
`);

// Create GiST index
await client.execute(sql`
  CREATE INDEX posts_search_idx ON ${posts} USING GiST (search_vector)
`);

// Search posts
const results = await client
  .select()
  .from(posts)
  .where(sql`${posts.searchVector} @@ to_tsquery('english', ${searchQuery})`)
  .orderBy(sql`ts_rank(${posts.searchVector}, to_tsquery('english', ${searchQuery})) DESC`);

Array Operations

// Find posts with specific tags
const taggedPosts = await client
  .select()
  .from(posts)
  .where(sql`${posts.tags} && ARRAY['typescript', 'postgresql']`);

// Add tag to existing tags
await client
  .update(posts)
  .set({
    tags: sql`array_append(${posts.tags}, 'new-tag')`
  })
  .where(eq(posts.id, postId));

Best Practices

1. Connection Management: Always use try/finally to ensure connections are closed
2. Transactions: Use transactions for related operations
3. Prepared Statements: Statements are automatically prepared and cached
4. Error Handling: Implement proper error handling and retries
5. Query Optimization: Use indexes and proper query planning

Contributing

We welcome contributions! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

Related Packages

• @drizzle-adapter/core - Core interfaces and types
• @drizzle-adapter/pg-core - Shared PostgreSQL functionality
• @drizzle-adapter/postgres-js - Alternative PostgreSQL adapter