tessl/npm-payloadcms--db-postgres

The officially supported PostgreSQL database adapter for Payload CMS with Drizzle ORM integration

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview

Eval results

Files

Drizzle ORM Integration

Name: tessl/npm-payloadcms--db-postgres
Author: tessl

Complete Drizzle ORM functionality re-exported by @payloadcms/db-postgres for direct database operations, schema definitions, and raw SQL queries. This provides full access to Drizzle's type-safe query builder and schema definition capabilities.

Import Paths

The package re-exports Drizzle functionality through multiple entry points:

@payloadcms/db-postgres/drizzle - Core Drizzle ORM functionality
@payloadcms/db-postgres/drizzle/pg-core - PostgreSQL-specific schema definitions
@payloadcms/db-postgres/drizzle/node-postgres - Node.js PostgreSQL driver integration
@payloadcms/db-postgres/drizzle/relations - Database relationship definitions

Capabilities

Core Drizzle Exports

All core Drizzle ORM functionality for building type-safe queries and handling database operations.

// SQL template literal for raw queries
const sql: SQL;

// Comparison operators
function eq<T>(left: T, right: T): SQLWrapper;
function ne<T>(left: T, right: T): SQLWrapper;
function gt<T>(left: T, right: T): SQLWrapper;
function gte<T>(left: T, right: T): SQLWrapper;
function lt<T>(left: T, right: T): SQLWrapper;
function lte<T>(left: T, right: T): SQLWrapper;

// Logical operators
function and(...conditions: SQLWrapper[]): SQLWrapper;
function or(...conditions: SQLWrapper[]): SQLWrapper;
function not(condition: SQLWrapper): SQLWrapper;

// Pattern matching
function like(column: any, pattern: string): SQLWrapper;
function ilike(column: any, pattern: string): SQLWrapper;

// Array operations
function inArray<T>(column: T, values: T[]): SQLWrapper;
function notInArray<T>(column: T, values: T[]): SQLWrapper;

// Null checks
function isNull(column: any): SQLWrapper;
function isNotNull(column: any): SQLWrapper;

// Aggregation functions
function count(column?: any): SQLWrapper;
function sum(column: any): SQLWrapper;
function avg(column: any): SQLWrapper;
function min(column: any): SQLWrapper;
function max(column: any): SQLWrapper;

// Utility functions
function placeholder(name: string): SQLWrapper;
function param(value: any): SQLWrapper;

Usage Examples:

import { sql, eq, and, or, gt, count } from '@payloadcms/db-postgres/drizzle';

// Raw SQL queries
const result = await db.execute(sql`
  SELECT * FROM users 
  WHERE created_at > ${new Date('2023-01-01')}
`);

// Type-safe queries with operators
const users = await db
  .select()
  .from(usersTable)
  .where(
    and(
      eq(usersTable.active, true),
      gt(usersTable.age, 18),
      or(
        eq(usersTable.role, 'admin'),
        eq(usersTable.role, 'user')
      )
    )
  );

// Aggregation queries
const userCount = await db
  .select({ count: count() })
  .from(usersTable)
  .where(eq(usersTable.active, true));

PostgreSQL Schema Definitions

PostgreSQL-specific column types, table definitions, and schema utilities.

// Table definition
function pgTable(
  name: string, 
  columns: Record<string, AnyPgColumn>
): PgTable;

// Column types
function text(name?: string): PgText;
function varchar(name?: string, config?: { length: number }): PgVarchar;
function char(name?: string, config?: { length: number }): PgChar;
function integer(name?: string): PgInteger;
function bigint(name?: string, mode: 'number' | 'bigint'): PgBigint;
function serial(name?: string): PgSerial;
function bigserial(name?: string, mode: 'number' | 'bigint'): PgBigSerial;
function boolean(name?: string): PgBoolean;
function date(name?: string): PgDate;
function timestamp(
  name?: string, 
  config?: { withTimezone?: boolean; precision?: number }
): PgTimestamp;
function time(
  name?: string,
  config?: { withTimezone?: boolean; precision?: number }
): PgTime;
function json<T>(name?: string): PgJson<T>;
function jsonb<T>(name?: string): PgJsonb<T>;
function uuid(name?: string): PgUuid;
function numeric(
  name?: string,
  config?: { precision?: number; scale?: number }
): PgNumeric;
function real(name?: string): PgReal;
function doublePrecision(name?: string): PgDoublePrecision;

// Array types
function array<T>(baseColumn: T): PgArray<T>;

// Enum types
function pgEnum<T extends string>(
  name: string, 
  values: readonly [T, ...T[]]
): PgEnum<T>;

// Schema definition
function pgSchema(name: string): PgSchema;

// Column constraints
function primaryKey(...columns: AnyPgColumn[]): PrimaryKeyBuilder;
function foreignKey(config: ForeignKeyConfig): ForeignKeyBuilder;
function unique(...columns: AnyPgColumn[]): UniqueConstraintBuilder;
function check(name: string, value: SQL): CheckConstraintBuilder;
function index(name?: string): IndexBuilder;

Usage Examples:

import { 
  pgTable, 
  text, 
  integer, 
  timestamp, 
  boolean, 
  uuid, 
  pgEnum,
  pgSchema 
} from '@payloadcms/db-postgres/drizzle/pg-core';

// Define enum
const roleEnum = pgEnum('role', ['admin', 'user', 'guest']);

// Define schema
const mySchema = pgSchema('my_app');

// Define table
const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  age: integer('age'),
  role: roleEnum('role').default('user'),
  active: boolean('active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Table with schema
const posts = mySchema.table('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: uuid('author_id').references(() => users.id),
  published: boolean('published').default(false),
});

Node.js PostgreSQL Driver Integration

Drizzle integration with the node-postgres driver for connection management and query execution.

// Database instance creation
function drizzle<T>(config: DrizzleConfig<T>): NodePgDatabase<T>;

interface DrizzleConfig<T> {
  /** pg.Pool or pg.Client instance */
  client: Pool | Client;
  
  /** Schema definitions */
  schema?: T;
  
  /** Query logger */
  logger?: boolean | DrizzleLogger;
}

interface DrizzleLogger {
  logQuery(query: string, params: unknown[]): void;
}

// Connection types
interface Pool {
  connect(): Promise<PoolClient>;
  end(): Promise<void>;
  query(text: string, params?: any[]): Promise<QueryResult>;
}

interface Client {
  connect(): Promise<void>;
  end(): Promise<void>;
  query(text: string, params?: any[]): Promise<QueryResult>;
}

Usage Examples:

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

// Create connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Create Drizzle instance
const db = drizzle({
  client: pool,
  schema: { users, posts },
  logger: true, // Enable query logging
});

// Use the database
const allUsers = await db.select().from(users);
const userWithPosts = await db.query.users.findFirst({
  with: {
    posts: true,
  },
});

Database Relationships

Relationship definitions for expressing table relationships and enabling relational queries.

// Relationship definition functions
function relations<T>(
  table: T,
  relations: RelationConfig<T>
): Relations<T>;

function one<T>(
  table: T,
  config?: RelationConfig
): OneRelation<T>;

function many<T>(
  table: T,
  config?: RelationConfig
): ManyRelation<T>;

interface RelationConfig {
  fields?: AnyColumn[];
  references?: AnyColumn[];
  relationName?: string;
}

Usage Examples:

import { relations, one, many } from '@payloadcms/db-postgres/drizzle/relations';

// Define relationships
const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  profile: one(profiles),
}));

const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
  category: one(categories, {
    fields: [posts.categoryId],
    references: [categories.id],
  }),
}));

// Use relationships in queries
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        category: true,
      },
    },
    profile: true,
  },
});

Additional PostgreSQL Utilities

PostgreSQL-specific utilities and functions.

// Geometry column for PostGIS
function geometryColumn(
  name: string,
  type: 'POINT' | 'LINESTRING' | 'POLYGON' | 'MULTIPOINT' | 'MULTILINESTRING' | 'MULTIPOLYGON',
  srid?: number
): PgGeometry;

// Custom column types
function interval(name?: string): PgInterval;
function bytea(name?: string): PgBytea;
function inet(name?: string): PgInet;
function cidr(name?: string): PgCidr;
function macaddr(name?: string): PgMacaddr;

// Advanced features
function generated<T>(
  expression: SQL,
  config?: { stored?: boolean }
): GeneratedColumn<T>;

Usage Examples:

import { geometryColumn } from '@payloadcms/db-postgres';
import { pgTable, text, timestamp, uuid } from '@payloadcms/db-postgres/drizzle/pg-core';

// Table with PostGIS geometry
const locations = pgTable('locations', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  point: geometryColumn('point', 'POINT', 4326),
  createdAt: timestamp('created_at').defaultNow(),
});

// Query spatial data
const nearbyLocations = await db.execute(sql`
  SELECT * FROM locations 
  WHERE ST_DWithin(point, ST_MakePoint(${longitude}, ${latitude})::geometry, 1000)
`);

Integration with Payload Adapter

The Drizzle integration works seamlessly with the Payload PostgreSQL adapter:

import { postgresAdapter } from '@payloadcms/db-postgres';
import { sql, eq } from '@payloadcms/db-postgres/drizzle';

export default buildConfig({
  db: postgresAdapter({
    pool: { connectionString: process.env.DATABASE_URI },
  }),
  
  // Access Drizzle instance in hooks
  hooks: {
    afterOperation: [
      async ({ operation, result, req }) => {
        // Direct database access with Drizzle
        const { payload } = req;
        const db = payload.db.drizzle;
        
        // Execute custom queries
        await db.execute(sql`
          INSERT INTO audit_log (operation, result, timestamp)
          VALUES (${operation}, ${JSON.stringify(result)}, NOW())
        `);
      },
    ],
  },
});

Install with Tessl CLI

npx tessl i tessl/npm-payloadcms--db-postgres

docs

core-adapter.md

drizzle-integration.md

index.md

migration-utils.md

tile.json

tessl/npm-payloadcms--db-postgres

drizzle-integration.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Drizzle ORM Integration

Import Paths

Capabilities

Core Drizzle Exports

PostgreSQL Schema Definitions

Node.js PostgreSQL Driver Integration

Database Relationships

Additional PostgreSQL Utilities

Integration with Payload Adapter

drizzle-integration.mddocs/