CtrlK
BlogDocsLog inGet started
Tessl Logo

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

migration-utils.mddocs/

Migration Utilities

Migration helper functions for upgrading PostgreSQL database schemas from older versions of Payload CMS. These utilities handle schema transformation and data migration between major versions.

Capabilities

Postgres V2 to V3 Migration

Migrates PostgreSQL database schemas from Payload CMS v2 format to v3 format, handling schema changes, relationship structures, and data transformations.

/**
 * Migrates PostgreSQL database from Payload v2 to v3 format
 * @param args - Migration arguments containing database and payload instances
 * @returns Promise that resolves when migration is complete
 */
function migratePostgresV2toV3(args: MigrateUpArgs): Promise<void>;

Usage Examples:

import { migratePostgresV2toV3 } from '@payloadcms/db-postgres/migration-utils';

// In a custom migration file
export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
  // Run the v2 to v3 migration
  await migratePostgresV2toV3({ db, payload, req });
  
  // Additional custom migration logic
  console.log('Migration from v2 to v3 completed');
}
// Using in production migrations configuration
import { postgresAdapter } from '@payloadcms/db-postgres';
import { migratePostgresV2toV3 } from '@payloadcms/db-postgres/migration-utils';

export default buildConfig({
  db: postgresAdapter({
    pool: { connectionString: process.env.DATABASE_URI },
    prodMigrations: [
      {
        name: 'v2-to-v3-migration',
        up: migratePostgresV2toV3,
        down: async ({ db, payload, req }) => {
          throw new Error('Downgrade from v3 to v2 not supported');
        },
      },
    ],
  }),
});

Migration Process

The v2 to v3 migration handles several key transformations:

Schema Changes

  • Updates table structures to match v3 requirements
  • Modifies column types and constraints
  • Adjusts index definitions
  • Updates foreign key relationships

Relationship Updates

  • Converts v2 relationship format to v3 structure
  • Updates join tables and reference columns
  • Preserves relationship data integrity

Data Transformation

  • Converts field formats between versions
  • Updates JSON structures in database
  • Handles locale and version data migration
  • Preserves all existing content

Version Control

  • Maintains version history during migration
  • Updates version metadata format
  • Ensures draft/published status consistency

Migration Arguments

interface MigrateUpArgs {
  /** The Postgres Drizzle instance for executing SQL directly within the current transaction */
  db: PostgresDB;
  
  /** Payload CMS instance for Local API methods */
  payload: Payload;
  
  /** PayloadRequest object containing the current transaction */
  req: PayloadRequest;
}

interface MigrateDownArgs {
  /** The Postgres Drizzle instance for executing SQL directly within the current transaction */
  db: PostgresDB;
  
  /** Payload CMS instance for Local API methods */
  payload: Payload;
  
  /** PayloadRequest object containing the current transaction */
  req: PayloadRequest;
}

Error Handling

The migration utility includes comprehensive error handling:

  • Schema Validation: Verifies v2 schema structure before migration
  • Data Integrity: Ensures no data loss during transformation
  • Rollback Support: Provides transaction-based rollback on failure
  • Progress Logging: Detailed logging of migration progress
  • Conflict Resolution: Handles naming conflicts and duplicate data

Best Practices

Pre-Migration Steps

  1. Backup Database: Always create a full database backup before migration
  2. Test Environment: Run migration in staging environment first
  3. Schema Analysis: Review current schema for custom modifications
  4. Downtime Planning: Plan for application downtime during migration

During Migration

  1. Monitor Progress: Watch migration logs for any warnings or errors
  2. Resource Monitoring: Ensure adequate database resources
  3. Connection Management: Verify stable database connections
  4. Data Validation: Spot-check critical data during migration

Post-Migration Steps

  1. Schema Verification: Confirm v3 schema structure is correct
  2. Data Integrity: Validate that all data migrated successfully
  3. Application Testing: Test application functionality with new schema
  4. Performance Check: Monitor query performance after migration
// Example comprehensive migration setup
import { postgresAdapter } from '@payloadcms/db-postgres';
import { migratePostgresV2toV3 } from '@payloadcms/db-postgres/migration-utils';

export default buildConfig({
  db: postgresAdapter({
    pool: { 
      connectionString: process.env.DATABASE_URI,
      max: 20, // Ensure adequate connections for migration
    },
    logger: console, // Enable logging for migration monitoring
    prodMigrations: [
      {
        name: 'payload-v2-to-v3',
        up: async ({ db, payload, req }) => {
          console.log('Starting Payload v2 to v3 migration...');
          
          try {
            await migratePostgresV2toV3({ db, payload, req });
            console.log('Migration completed successfully');
          } catch (error) {
            console.error('Migration failed:', error);
            throw error; // This will trigger rollback
          }
        },
        down: async ({ db, payload, req }) => {
          throw new Error('Downgrade from v3 to v2 is not supported');
        },
      },
    ],
  }),
});

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