Introduction to node-pg-migrate

node-pg-migrate is a handy tool for managing PostgreSQL database migrations in Node.js applications. It offers a robust API to create, alter, and manage database schemas.

Getting Started

  npm install node-pg-migrate

Basic Usage

To create a migration, use the following command:

  npx node-pg-migrate create your_migration_name

Useful API Methods

createTable

Create a new table with the specified columns:

  exports.up = pgm => {
    pgm.createTable('users', {
      id: 'id',
      name: { type: 'varchar(100)', notNull: true },
      email: { type: 'varchar(100)', notNull: true, unique: true },
      created_at: { type: 'timestamp', default: pgm.func('current_timestamp') }
    });
  };

dropTable

Drop an existing table:

  exports.down = pgm => {
    pgm.dropTable('users');
  };

addColumns

Add columns to an existing table:

  exports.up = pgm => {
    pgm.addColumns('users', {
      age: { type: 'integer' },
      address: { type: 'varchar(1000)' }
    });
  };

dropColumns

Remove columns from an existing table:

  exports.down = pgm => {
    pgm.dropColumns('users', ['age', 'address']);
  };

createIndex

Create an index on specified columns:

  exports.up = pgm => {
    pgm.createIndex('users', 'email');
  };

dropIndex

Remove an index from specified columns:

  exports.down = pgm => {
    pgm.dropIndex('users', 'email');
  };

renameTable

Rename an existing table:

  exports.up = pgm => {
    pgm.renameTable('users', 'members');
  };

renameColumn

Rename a column in an existing table:

  exports.up = pgm => {
    pgm.renameColumn('users', 'name', 'full_name');
  };

Application Example

Below is a simple Node.js application to manage a users table using node-pg-migrate APIs:

  const { Pool } = require('pg');
  const { migrate } = require('node-pg-migrate');

  const pool = new Pool({
    user: 'your_db_user',
    host: 'localhost',
    database: 'your_db_name',
    password: 'your_db_password',
    port: 5432,
  });

  async function runMigrations() {
    await migrate({
      databaseUrl: 'postgres://your_db_user:your_db_password@localhost/your_db_name',
      dir: 'migrations',
      direction: 'up',
      migrationsTable: 'pgmigrations',
    });
    console.log('Migrations ran successfully');
  }

  runMigrations().then(() => pool.end());

In the example above, we created a new PostgreSQL connection using pg module and ran the migrations with node-pg-migrate.

For more advanced usage and configuration, refer to the full node-pg-migrate documentation.

Hash: 70630bd8b434862cd1a2a4a6db10b522d253ff256ce7f067ee486f4a968ebd97