### Create Index with node-pg-migrate

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/indexes.md

Examples demonstrating various ways to create indexes, including single/multiple columns, sorting, and operator classes.

```ts
pgm.createIndex('table', 'column');
//expected output: CREATE INDEX ON "table" ("column")
```

```ts
pgm.createIndex('table', ['col1', 'col2']);
//expected output: CREATE INDEX ON "table" ("col1", "col2")
```

```ts
pgm.createIndex('table', [
  { name: 'col1', sort: 'ASC' },
  { name: 'col2', sort: 'DESC' },
]);
//expected output: CREATE INDEX ON "table" ("col1" ASC, "col2" DESC)
```

```ts
pgm.createIndex('table', [
  { name: 'col1', opclass: { schema: 'schema', name: 'opclass' }, sort: 'ASC' },
]);
//expected output: CREATE INDEX ON "table" ("col1" "schema"."opclass" ASC)
```

--------------------------------

### Install node-pg-migrate

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/getting-started.md

Add node-pg-migrate as a development dependency using your preferred package manager.

```sh
$ npm add -D node-pg-migrate
```

```sh
$ pnpm add -D node-pg-migrate
```

```sh
$ yarn add -D node-pg-migrate
```

```sh
$ bun add -D node-pg-migrate
```

--------------------------------

### Create PostgreSQL Extension

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/extensions.md

Installs one or more PostgreSQL extensions. You can specify options like `ifNotExists` to prevent errors if the extension is already installed, and `schema` to define where the extension's objects should be placed.

```APIDOC
## POST /api/extensions/create

### Description
Creates one or more PostgreSQL extensions.

### Method
POST

### Endpoint
/api/extensions/create

### Parameters
#### Request Body
- **extension** (string or array[string]) - Required - Name(s) of extensions to install
- **options** (object) - Optional - Configuration options for extension creation
  - **ifNotExists** (boolean) - Optional - Install extension only if it does not exist (default: false)
  - **schema** (string) - Optional - The name of the schema in which to install the extension's objects

### Request Example
```json
{
  "extension": "uuid-ossp",
  "options": {
    "ifNotExists": true,
    "schema": "extensions"
  }
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message of extension creation.

#### Response Example
```json
{
  "message": "Extension 'uuid-ossp' created successfully."
}
```
```

--------------------------------

### Add pg dependency

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/getting-started.md

Install the 'pg' library as a direct or dev dependency using your preferred package manager.

```sh
$ npm add pg
```

```sh
$ pnpm add pg
```

```sh
$ yarn add pg
```

```sh
$ bun add pg
```

--------------------------------

### Example: Use Grouped SQL Loader

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migration-loading-strategies.md

Enables grouping of `*.up.sql` and `*.down.sql` files into a single migration unit. This configuration normalizes migration IDs and supports switching between single `.sql` files and split `.up/.down` files without altering the `migrationsTable`.

```typescript
import { runner } from 'node-pg-migrate';

await runner({
  databaseUrl: process.env.DATABASE_URL!,
  dir: 'migrations',
  direction: 'up',
  migrationsTable: 'pgmigrations',
  migrationLoaderStrategies: [{ extensions: ['.sql'], loader: 'sql' }],
});
```

--------------------------------

### Example: Custom Loader Function

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migration-loading-strategies.md

Provides a custom loader function directly for specific file extensions. This allows for bespoke migration file processing logic.

```typescript
import type { MigrationLoader } from 'node-pg-migrate';
import { runner } from 'node-pg-migrate';

const customLoader: MigrationLoader = async (filePaths) => {
  // map files to migration units
  return [];
};

await runner({
  databaseUrl: process.env.DATABASE_URL!,
  dir: 'migrations',
  direction: 'up',
  migrationsTable: 'pgmigrations',
  migrationLoaderStrategies: [
    { extensions: ['.sql'], loader: 'sql' },
    { extensions: ['.mjs'], loader: customLoader },
  ],
});
```

--------------------------------

### Set PGSSLMODE environment variable for SSL connection

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/faq/troubleshooting.md

To establish an SSL connection to your database, set the PGSSLMODE environment variable to a value other than 'disable'. This example uses 'require'.

```bash
PGSSLMODE=require node-pg-migrate up
```

--------------------------------

### Create Initial Migration

Source: https://github.com/salsita/node-pg-migrate/blob/main/README.md

Create a new migration file using the npm script. This file will contain the SQL commands to set up initial tables.

```javascript
export const up = (pgm) => {
  pgm.createTable('users', {
    id: 'id',
    name: { type: 'varchar(1000)', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createTable('posts', {
    id: 'id',
    userId: {
      type: 'integer',
      notNull: true,
      references: '"users"',
      onDelete: 'CASCADE',
    },
    body: { type: 'text', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createIndex('posts', 'userId');
};
```

--------------------------------

### SQL Generated by Shorthands

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/index.md

This SQL demonstrates how the defined shorthands are expanded into a `CREATE TABLE` statement.

```sql
CREATE TABLE "test" ("id" uuid PRIMARY KEY, "createdAt" timestamp DEFAULT current_timestamp NOT NULL);
```

--------------------------------

### Create first migration (JavaScript)

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/getting-started.md

Define the 'up' and 'down' functions to create 'users' and 'posts' tables and an index on 'posts.userId'.

```js
exports.up = (pgm) => {
  pgm.createTable('users', {
    id: 'id',
    name: { type: 'varchar(1000)', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createTable('posts', {
    id: 'id',
    userId: {
      type: 'integer',
      notNull: true,
      references: '"users"',
      onDelete: 'CASCADE',
    },
    body: { type: 'text', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createIndex('posts', 'userId');
};

exports.down = (pgm) => {};
```

--------------------------------

### Run All Up Migrations

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Executes all pending 'up' migrations from the current database state.

```bash
node-pg-migrate up
```

--------------------------------

### Run migrations using package managers

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/faq/typescript.md

Execute the configured migration script using various package managers.

```bash
npm run migrate
```

```bash
pnpm run migrate
```

```bash
yarn migrate
```

```bash
bun run migrate
```

--------------------------------

### Create Migration Command

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Creates a new migration file. The name provided will have a timestamp prepended, and spaces/underscores will be replaced with dashes.

```bash
node-pg-migrate create {migration-name}
```

--------------------------------

### Run database queries with pgm.db

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/misc.md

Executes queries using the same database connection as the current migration process.

```javascript
pgm.db.query
pgm.db.select
```

--------------------------------

### Apply Migrations

Source: https://github.com/salsita/node-pg-migrate/blob/main/README.md

Apply all pending migrations to the database. Ensure the DATABASE_URL environment variable is set.

```bash
DATABASE_URL=postgres://test:test@localhost:5432/test npm run migrate up
```

--------------------------------

### Configure SSL certificates via JSON for advanced settings

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/faq/troubleshooting.md

For advanced SSL configurations, including setting specific certificates, use a JSON configuration file. Refer to the node-postgres documentation for details on SSL configuration.

```bash
node-pg-migrate --config /path/to/your/config.json up
```

--------------------------------

### Run N Up Migrations

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Executes a specified number (N) of pending 'up' migrations from the current database state.

```bash
node-pg-migrate up {N}
```

--------------------------------

### Database Connection via JSON Properties

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Configure the database connection by specifying individual properties like user, password, host, port, and database name in a JSON configuration file.

```jsonc
// config/default.json
{
  "db": {
    "user": "postgres",
    "password": "",
    "host": "localhost",
    "port": 5432,
    "database": "database",
  },
}
```

--------------------------------

### Configuration Options

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/api.md

These options can be passed to the node-pg-migrate CLI or its programmatic API to customize its behavior.

```APIDOC
## Configuration Options

### `verbose`

- **Type**: `boolean`
- **Description**: Print all debug messages like DB queries run. If you switch it on, it will disable the `logger.debug` method.

### `decamelize`

- **Type**: `boolean`
- **Description**: Runs [`decamelize`](https://github.com/salsita/node-pg-migrate/blob/main/src/utils/decamelize.ts) on table/column/etc. names.

### `migrationLoaderStrategies`

- **Type**: `MigrationLoaderStrategy[]`
- **Description**: Allows custom loading strategies based on file extensions. If omitted, the default behavior is used. See [Migration Loading Strategies](migration-loading-strategies).

## MigrationLoaderStrategy Interface

```ts
export interface MigrationLoaderStrategy {
  // File extensions handled by this strategy.
  extensions: string[];

  /**
   * Loader that handles conversion of file paths to migration units.
   *
   * @param filePaths - The file paths to load migrations from.
   * @returns The migration units.
   */
  loader: MigrationLoader | 'default' | 'legacySql' | 'sql';
}
```

## MigrationUnit Interface

```ts
export interface MigrationUnit {
  // The unique identifier for the migration unit. Represents the significant part of the file name used for tracking which migrations have been performed.
  id: string;

  // File paths that are part of the migration unit.
  filePaths: string[];

  // The migration builder actions that are contained within the migration files.
  actions: MigrationBuilderActions;
}
```
```

--------------------------------

### Create Sequence

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/sequences.md

Creates a new sequence with specified options.

```APIDOC
## POST /api/sequences

### Description
Creates a new sequence in the database.

### Method
POST

### Endpoint
/api/sequences

### Parameters
#### Request Body
- **sequence_name** (string) - Required - The name of the new sequence.
- **options** (object) - Optional - Configuration options for the sequence.
  - **temporary** (boolean) - Optional - Adds the `TEMPORARY` clause.
  - **ifNotExists** (boolean) - Optional - Adds the `IF NOT EXISTS` clause.
  - **type** (string) - Optional - Type of the sequence.
  - **increment** (number) - Optional - Sets the increment value of the sequence.
  - **minvalue** (number | null | false) - Optional - Sets the minimum value of the sequence or `NO MINVALUE`.
  - **maxvalue** (number | null | false) - Optional - Sets the maximum value of the sequence or `NO MAXVALUE`.
  - **start** (number) - Optional - Sets the starting value of the sequence.
  - **cache** (number) - Optional - Sets how many sequence numbers should be pre-allocated.
  - **cycle** (boolean) - Optional - Adds `CYCLE` or `NO CYCLE` clause.
  - **owner** (string | null | false) - Optional - Sets the owner of the sequence or no owner.

### Request Example
```json
{
  "sequence_name": "my_sequence",
  "options": {
    "temporary": false,
    "ifNotExists": true,
    "increment": 1,
    "start": 100,
    "cache": 10,
    "cycle": false
  }
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message of sequence creation.

#### Response Example
```json
{
  "message": "Sequence 'my_sequence' created successfully."
}
```
```

--------------------------------

### Append ?ssl=true to DATABASE_URL for SSL connection

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/faq/troubleshooting.md

Alternatively, you can enable SSL for your database connection by appending '?ssl=true' to your DATABASE_URL.

```bash
DATABASE_URL=postgres://user:password@host:port/database?ssl=true node-pg-migrate up
```

--------------------------------

### Database Connection via JSON URL

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Configure the database connection using a URL string within a JSON configuration file.

```jsonc
// config/default.json
{
  "db": {
    "url": "postgres://postgres:password@localhost:5432/database",
  },
}
```

--------------------------------

### Configure package.json scripts

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/getting-started.md

Add a 'migrate' script to your package.json for easy command execution. Use '-j ts' for TypeScript projects.

```jsonc
{
  "scripts": {
    // ..
    "migrate": "node-pg-migrate", // [!code ++]
  },
}
```

```jsonc
{
  "scripts": {
    // ..
    "migrate": "node-pg-migrate -j ts", // [!code ++]
  },
}
```

--------------------------------

### Apply Environment-Specific Configuration

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Command to apply a specific environment configuration (e.g., 'prod') from a JSON configuration file using the --config-value option.

```bash
node-pg-migrate --config-file=migrations.config.js --config-value=prod
```

--------------------------------

### Create first migration (TypeScript)

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/getting-started.md

Define the 'up' and 'down' functions to create 'users' and 'posts' tables and an index on 'posts.userId'.

```ts
import { MigrationBuilder } from 'node-pg-migrate';

export const up = (pgm: MigrationBuilder) => {
  pgm.createTable('users', {
    id: 'id',
    name: { type: 'varchar(1000)', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createTable('posts', {
    id: 'id',
    userId: {
      type: 'integer',
      notNull: true,
      references: '"users"',
      onDelete: 'CASCADE',
    },
    body: { type: 'text', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createIndex('posts', 'userId');
};

export async function down(pgm: MigrationBuilder): Promise<void> {}
```

--------------------------------

### Creating Tables with Schemas

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/index.md

Specify a schema when creating tables by passing an object with `schema` and `name` keys to `pgm.createTable`.

```javascript
pgm.createTable(
  { schema: 'my_schema', name: 'my_table_name' },
  { id: 'serial' }
);
```

--------------------------------

### Legacy SQL Migration Markers

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migration-loading-strategies.md

Demonstrates the marker comments used in legacy SQL migrations for separating 'Up' and 'Down' migration sections. If no markers are present, the entire file is treated as an 'up' migration.

```sql
-- Up Migration

-- Down Migration
```

--------------------------------

### pgm.sql

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/misc.md

Executes a raw SQL query with optional mustache-style templating.

```APIDOC
## pgm.sql

### Description
Runs raw SQL with optional basic mustache templating.

### Parameters
- **sql** (string) - Required - SQL query to run
- **args** (object) - Optional - Key/value pairs of arguments to replace in the SQL query
```

--------------------------------

### pgm.db

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/misc.md

Executes database queries using the same connection as the migration.

```APIDOC
## pgm.db.query and pgm.db.select

### Description
Allows running database queries using the same connection the migration is currently running on. Returns a promise with the query result or returned rows.
```

--------------------------------

### Add columns using type shorthand

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/columns.md

Demonstrates the shorthand syntax for adding a column by specifying only the type.

```ts
pgm.addColumns('myTable', { age: 'integer' });
```

```ts
pgm.addColumns('myTable', { age: { type: 'integer' } });
```

--------------------------------

### Configure tsconfig-paths for Jiti

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Enable Jiti's TypeScript/JavaScript path resolution for migration files. Set to true to auto-discover tsconfig.json, or provide a specific path.

```bash
`tsconfig-paths` | | `false` | Enable [`jiti`](https://github.com/unjs/jiti) tsconfig paths resolution when loading TS/JS migration files. Pass `true` to auto-discover the nearest `tsconfig.json`, or a path to a specific `tsconfig.json` (e.g. `--tsconfig-paths ./tsconfig.json`)
```

--------------------------------

### Execute raw SQL with pgm.sql

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/misc.md

Executes a raw SQL string, supporting basic mustache-style templating for dynamic values.

```javascript
pgm.sql( sql )
```

--------------------------------

### Configure migration script in package.json

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/faq/typescript.md

Add the -j ts flag to the migration command to enable TypeScript support.

```jsonc
{
  "scripts": {
    // ..
    "migrate": "node-pg-migrate -j ts", // [!code ++]
  },
}
```

--------------------------------

### Data Types & Convenience Shorthand

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/columns.md

Explains how to use PostgreSQL data types and shorthand notations for common column definitions.

```APIDOC
## Data types & Convenience Shorthand

Data type strings will be passed through directly to postgres, so write types as you would if you were writing the queries by hand.

**There are some aliases on types to make things more foolproof:** _(int, string, float, double, datetime, bool)_

**There is a shorthand to pass only the type instead of an option object:**

```ts
pgm.addColumns('myTable', { age: 'integer' });
```

Equivalent to:

```ts
pgm.addColumns('myTable', { age: { type: 'integer' } });
```

**There is a shorthand for normal auto-increment IDs:**

```ts
pgm.addColumns('myTable', { id: 'id' });
```

Equivalent to:

```ts
pgm.addColumns('myTable', { id: { type: 'serial', primaryKey: true } });
```
```

--------------------------------

### JSON Configuration Options

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Define various node-pg-migrate settings using a JSON configuration object. This includes schema names, migration directories, and file naming conventions.

```jsonc
{
  "schema": "public",
  "create-schema": false,
  "database-url-var": "SECRET_DB_URL",
  "migrations-dir": "migrations",
  "use-glob": false,
  "migrations-schema": "public",
  "create-migrations-schema": false,
  "migrations-table": "pgmigrations",
  "migration-filename-format": "utc",
  "migration-file-language": "js",
  "ignore-pattern": "/SKIP$/",
  "template-file-name": "templates/new-migration.js",
  "check-order": true,
  "verbose": true,
  "decamelize": false,
  "tsconfig-paths": "./tsconfig.json"
}
```

--------------------------------

### Add column to existing table (JavaScript)

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/getting-started.md

Use pgm.addColumns to add a 'lead' column of type text to the 'posts' table.

```js
export const up = (pgm) => {
  pgm.addColumns('posts', {
    lead: { type: 'text', notNull: true },
  });
};
```

--------------------------------

### Basic Migration Structure

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/index.md

A standard migration file includes `up` and `down` functions to define database schema changes. `pgm` is a helper object for migration operations.

```javascript
export const shorthands = undefined;

export const up = function up(pgm) {};

export const down = function down(pgm) {};
```

--------------------------------

### Database Connection via Environment Variable

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Set the DATABASE_URL environment variable to specify the database connection string when running the node-pg-migrate command.

```bash
DATABASE_URL=postgres://postgres@localhost/database node-pg-migrate
```

--------------------------------

### createDomain

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/domains.md

Creates a new domain in the database.

```APIDOC
## createDomain

### Description
Creates a new domain in the database.

### Parameters
- **domain_name** (Name) - Required - Name of the new domain
- **type** (string) - Required - Type of the new domain
- **options** (object) - Required - Configuration options including default, collation, notNull, check, and constraintName.
```

--------------------------------

### Using .env file with --envPath

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

If a .env file exists and is not in the current directory, use the --envPath option to specify its location for loading environment variables.

```bash
node-pg-migrate --envPath path/to/.env up
```

--------------------------------

### Set SSL Mode for Database Connection

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Configure SSL for your PostgreSQL connection by setting the PGSSLMODE environment variable. Use values other than 'disable' from the libpq documentation.

```bash
For SSL connection to DB you can set `PGSSLMODE` environment variable to value
from [list](https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNECT-SSLMODE) other
than `disable`.
e.g. `PGSSLMODE=require node-pg-migrate up` ([pg](https://github.com/brianc/node-postgres/blob/main/CHANGELOG.md#v260)
will take it into account)
```

--------------------------------

### pgm.createRole

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/roles.md

Creates a new database role with specified options.

```APIDOC
## pgm.createRole(role_name, role_options)

### Description
Creates a new PostgreSQL role. Refer to the [PostgreSQL documentation](http://www.postgresql.org/docs/current/static/sql-createrole.html) for more details.

### Parameters
- **role_name** (Name) - Required - Name of the new role.
- **role_options** (object) - Required - Configuration options including superuser, createdb, createrole, inherit, login, replication, bypassrls, limit, password, encrypted, valid, inRole, role, and admin.
```

--------------------------------

### createSchema

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/schemas.md

Creates a new schema in the database.

```APIDOC
## createSchema

### Description
Creates a new schema in the database.

### Parameters
- **schema_name** (string) - Required - Name of the new schema
- **schema_options** (object) - Optional - Configuration options
  - **ifNotExists** (boolean) - Optional - Adds IF NOT EXISTS clause
  - **authorization** (string) - Optional - Alternative user to own new schema
```

--------------------------------

### Environment-Specific JSON Configuration

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Configure different settings for various environments (e.g., dev, prod) within a single JSON configuration file. Use the --config-value option to select the active environment.

```jsonc
{
  "dev": {
    "migrations-schema": "public",
    "verbose": true,
  },
  "prod": {
    "migrations-schema": "myapp",
  },
}
```

--------------------------------

### Use --no-reject-unauthorized for self-signed SSL certificates

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/faq/troubleshooting.md

When using pg v8 or later, SSL connections might reject self-signed certificates by default. Use the --no-reject-unauthorized CLI option to accept them. This option should not be combined with '?ssl=true'.

```bash
node-pg-migrate --no-reject-unauthorized up
```

--------------------------------

### pgm.func

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/misc.md

Inserts a raw string into the migration without escaping it.

```APIDOC
## pgm.func

### Description
Inserts a raw string that is not escaped. Useful for column definitions like `pgm.func('CURRENT_TIMESTAMP')`.

### Parameters
- **sql** (string) - Required - String to not be escaped
```

--------------------------------

### Create Index API

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/indexes.md

API for creating new indexes on a table.

```APIDOC
## POST /api/indexes

### Description
Creates a new index on a specified table with given columns and options.

### Method
POST

### Endpoint
/api/indexes

### Parameters
#### Request Body
- **tablename** (string) - Required - The name of the table to alter.
- **columns** (string or array[string]) - Required - The columns to add to the index, with optional operator class and sort order.
- **options** (object) - Optional - Configuration for the index.
  - **name** (string) - Optional - The name for the index. If not provided, one will be inferred.
  - **unique** (boolean) - Optional - Set to true for a unique index.
  - **where** (string) - Optional - A raw SQL string for the WHERE clause of the index.
  - **concurrently** (boolean) - Optional - Create the index concurrently.
  - **ifNotExists** (boolean) - Optional - If true, do not throw an error if the index already exists.
  - **method** (string) - Optional - The index method (e.g., 'btree', 'hash', 'gist', 'spgist', 'gin').
  - **include** (string or array[string]) - Optional - Columns to include in the index.
  - **nulls** (string) - Optional - Specifies handling of NULL values ('distinct' or 'not distinct'), applicable only for unique indexes.

### Request Example
```json
{
  "tablename": "users",
  "columns": ["email"],
  "options": {
    "unique": true,
    "name": "idx_users_email_unique"
  }
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message of index creation.

#### Response Example
```json
{
  "message": "Index 'idx_users_email_unique' created successfully on table 'users'."
}
```
```

--------------------------------

### Create Table API

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/tables.md

API for creating a new table in the database.

```APIDOC
## POST /pgm/createTable

### Description
Creates a new table in the database.

### Method
POST

### Endpoint
/pgm/createTable

### Parameters
#### Request Body
- **tablename** (Name) - Required - The name for the new table.
- **columns** (object) - Required - Column definitions for the new table. See [column definitions section](columns.md#column-definitions).
- **options** (object) - Optional - Table options.

### Options (within request body)
- **temporary** (boolean) - Optional - Defaults to `false`.
- **ifNotExists** (boolean) - Optional - Defaults to `false`.
- **inherits** (Name) - Optional - Table(s) to inherit from.
- **constraints** (object) - Optional - Table constraints. See `expression` of [add constraint](constraints.md#pgmaddconstraint-tablename-constraint_name-expression-).
- **like** (Name or object) - Optional - Table(s) to inherit from or object with `table` and `options` keys.
  - **like.including** (string or array[string]) - Optional - Specifies what to include when using `like`. Possible values: 'COMMENTS', 'CONSTRAINTS', 'DEFAULTS', 'IDENTITY', 'INDEXES', 'STATISTICS', 'STORAGE', 'ALL'.
  - **like.excluding** (string or array[string]) - Optional - Specifies what to exclude when using `like`. Possible values: 'COMMENTS', 'CONSTRAINTS', 'DEFAULTS', 'IDENTITY', 'INDEXES', 'STATISTICS', 'STORAGE', 'ALL'.
- **comment** (string) - Optional - Adds a comment on the table.
- **unlogged** (boolean) - Optional - Defaults to `false`.

### Request Example
```json
{
  "tablename": "users",
  "columns": {
    "id": "SERIAL PRIMARY KEY",
    "name": "VARCHAR(100) NOT NULL",
    "email": "VARCHAR(255) UNIQUE"
  },
  "options": {
    "ifNotExists": true,
    "comment": "Stores user information"
  }
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message.

#### Response Example
```json
{
  "message": "Table 'users' created successfully."
}
```
```

--------------------------------

### Generated SQL for Tables with Schemas

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/index.md

This SQL shows the output of creating a table within a specific schema.

```sql
CREATE TABLE "my_schema"."my_table_name" (
 "id" serial
);
```

--------------------------------

### Custom JSON Configuration File

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Specify a custom JSON file for database connection configuration. The format is the same as the 'db' entry in a standard config file.

```jsonc
// path/to/config.json
{
  "user": "postgres",
  "password": "",
  "host": "localhost",
  "port": 5432,
  "database": "database",
}
```

--------------------------------

### Defining Column Type Shorthands

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/index.md

Use the `shorthands` export to define custom types that can be reused in column definitions, simplifying migration files.

```javascript
export const shorthands = {
  id: { type: 'uuid', primaryKey: true },
  createdAt: {
    type: 'timestamp',
    notNull: true,
    default: new PgLiteral('current_timestamp'),
  },
};
```

--------------------------------

### POST /pgm.addColumns

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/columns.md

Adds one or more columns to an existing table in the database.

```APIDOC
## POST /pgm.addColumns

### Description
Add columns to an existing table.

### Method
POST

### Endpoint
`/pgm.addColumns`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **tablename** (string) - Required - The name of the table to add columns to.
- **new_columns** (object) - Required - An object where keys are column names and values are column definitions.
- **options** (object) - Optional - Additional options for the operation.

### Request Example
```json
{
  "tablename": "myTable",
  "new_columns": {
    "email": { "type": "string", "notNull": true },
    "age": { "type": "integer", "default": 0 }
  },
  "options": {}
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message.

#### Response Example
```json
{
  "message": "Columns added successfully."
}
```
```

--------------------------------

### Redo Last N Migrations

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Re-executes the last N migrations by running their respective 'down' migrations followed by their 'up' migrations.

```bash
node-pg-migrate redo {N}
```

--------------------------------

### Create Materialized View

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/mViews.md

Creates a new materialized view with specified options and definition.

```APIDOC
## POST /salsita/node-pg-migrate

### Description
Creates a new materialized view.

### Method
POST

### Endpoint
/salsita/node-pg-migrate

### Parameters
#### Path Parameters
- **viewName** (string) - Required - name of the new materialized view
- **options** (object) - Optional - Configuration options for the materialized view.
  - **ifNotExists** (boolean) - Optional - adds `IF NOT EXISTS` clause
  - **columns** (string or array) - Optional - use if you want to name columns differently then inferred from definition
  - **tablespace** (string) - Optional - specifies the tablespace
  - **storageParameters** (object) - Optional - key value pairs of Storage Parameters
  - **data** (boolean) - Optional - default `undefined`
- **definition** (string) - Required - SQL of SELECT statement

### Request Example
```json
{
  "viewName": "my_materialized_view",
  "options": {
    "ifNotExists": true,
    "columns": ["col1", "col2"],
    "tablespace": "pg_default",
    "storageParameters": {
      "fillfactor": 80
    },
    "data": true
  },
  "definition": "SELECT id, name FROM users"
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message of view creation.
```

--------------------------------

### Redo Last Migration

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Re-executes the last migration by first running its 'down' migration and then its 'up' migration.

```bash
node-pg-migrate redo
```

--------------------------------

### Run Single Down Migration

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Reverts the last executed 'up' migration by running a single 'down' migration.

```bash
node-pg-migrate down
```

--------------------------------

### pgm.createFunction

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/functions.md

Creates a new PostgreSQL function.

```APIDOC
## pgm.createFunction

### Description
Creates a new function in the database.

### Parameters
- **function_name** (Name) - Required - Name of the new function
- **function_params** (array[string]|array[object]) - Required - Parameters of the new function
- **function_options** (object) - Optional - Configuration options including returns, language, replace, window, behavior, security, onNull, and parallel
- **definition** (string) - Required - The body definition of the function
```

--------------------------------

### MigrationLoaderStrategy Interface

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/api.md

Defines a custom strategy for loading database migrations based on file extensions. Use this to handle custom migration file formats.

```typescript
export interface MigrationLoaderStrategy {
  // File extensions handled by this strategy.
  extensions: string[];

  /**
   * Loader that handles conversion of file paths to migration units.
   *
   * @param filePaths - The file paths to load migrations from.
   * @returns The migration units.
   */
  loader: MigrationLoader | 'default' | 'legacySql' | 'sql';
}
```

--------------------------------

### Create View API

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/views.md

API for creating new views in the database. Supports various options for view definition.

```APIDOC
## POST /api/views

### Description
Creates a new view in the database.

### Method
POST

### Endpoint
/api/views

### Parameters
#### Request Body
- **viewName** (string) - Required - The name of the new view.
- **definition** (string) - Required - The SQL SELECT statement defining the view.
- **options** (object) - Optional - Configuration options for the view.
  - **temporary** (boolean) - Optional - Adds the `TEMPORARY` clause.
  - **replace** (boolean) - Optional - Adds the `OR REPLACE` clause.
  - **recursive** (boolean) - Optional - Adds the `RECURSIVE` clause.
  - **columns** (string or array) - Optional - Specifies column names if different from inferred names.
  - **checkOption** (string) - Optional - Sets the check option to `CASCADED` or `LOCAL`.
  - **viewOptions** (object) - Optional - Key-value pairs for PostgreSQL view options.

### Request Example
```json
{
  "viewName": "my_view",
  "definition": "SELECT id, name FROM users WHERE active = true",
  "options": {
    "temporary": false,
    "replace": true,
    "checkOption": "LOCAL"
  }
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message of view creation.

#### Response Example
```json
{
  "message": "View 'my_view' created successfully."
}
```
```

--------------------------------

### Add column to existing table (TypeScript)

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/getting-started.md

Use pgm.addColumns to add a 'lead' column of type text to the 'posts' table.

```ts
import { MigrationBuilder } from 'node-pg-migrate';

export const up = (pgm: MigrationBuilder) => {
  pgm.addColumns('posts', {
    lead: { type: 'text', notNull: true },
  });
};
```

--------------------------------

### Create Policy API

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/policies.md

This operation allows you to create a new policy for a specified table in PostgreSQL.

```APIDOC
## POST /pgm/createPolicy

### Description
Creates a new policy for a specified table.

### Method
POST

### Endpoint
/pgm/createPolicy

### Parameters
#### Request Body
- **tableName** (Name) - Required - The name of the table to alter.
- **policyName** (string) - Required - The name of the new policy.
- **options** (object) - Required - Options for the policy.
  - **command** (string) - Optional - `ALL`, `SELECT`, `INSERT`, `UPDATE`, or `DELETE`.
  - **role** (string or array) - Optional - The role(s) to which the policy is to be applied.
  - **using** (string) - Optional - SQL conditional expression for visibility check.
  - **check** (string) - Optional - SQL conditional expression for insert/update check.

### Request Example
```json
{
  "tableName": "users",
  "policyName": "active_users_policy",
  "options": {
    "command": "SELECT",
    "role": "app_user",
    "using": "is_active = true"
  }
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message of policy creation.

#### Response Example
```json
{
  "message": "Policy 'active_users_policy' created successfully on table 'users'."
}
```
```

--------------------------------

### pgm.createOperatorFamily

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/operators.md

Creates a new PostgreSQL operator family.

```APIDOC
## pgm.createOperatorFamily

### Description
Creates a new PostgreSQL operator family.

### Method
pgm.createOperatorFamily

### Arguments
#### Path Parameters
- **operator_family_name** (Name) - Required - name of the new operator family
- **index_method** (string) - Required - name of the index method of operator family
```

--------------------------------

### Drop PostgreSQL Extension

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/extensions.md

Uninstalls one or more PostgreSQL extensions. Options include `ifExists` to avoid errors if the extension is not present, and `cascade` to remove dependent objects.

```APIDOC
## DELETE /api/extensions/drop

### Description
Drops one or more PostgreSQL extensions.

### Method
DELETE

### Endpoint
/api/extensions/drop

### Parameters
#### Request Body
- **extension** (string or array[string]) - Required - Name(s) of extensions to drop
- **drop_options** (object) - Optional - Configuration options for dropping the extension
  - **ifExists** (boolean) - Optional - Drops extension only if it exists
  - **cascade** (boolean) - Optional - Drops also dependent objects

### Request Example
```json
{
  "extension": "uuid-ossp",
  "drop_options": {
    "ifExists": true,
    "cascade": true
  }
}
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message of extension drop.

#### Response Example
```json
{
  "message": "Extension 'uuid-ossp' dropped successfully."
}
```
```

--------------------------------

### Table Alteration Arguments and Options

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/tables.md

This section describes the arguments and options used when altering tables with node-pg-migrate.

```APIDOC
## Table Alteration

### Description
This endpoint is used to alter existing tables in the database.

### Method
POST (or other relevant method based on context, assuming a command-line interface or API call)

### Endpoint
/salsita/node-pg-migrate/alter-table

### Parameters
#### Path Parameters
- **tablename** (Name) - Required - The name of the table to alter.

#### Query Parameters
None specified.

#### Request Body
- **options** (object) - Required - An object containing options for altering the table.
  - **levelSecurity** (string) - Optional - Sets the level of security. Allowed values: `DISABLE`, `ENABLE`, `FORCE`, or `NO FORCE`.
  - **unlogged** (boolean) - Optional - If true, sets the table as UNLOGGED.
```

--------------------------------

### Handling Asynchronous Operations in Migrations

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/index.md

Migrations can perform asynchronous operations by accepting a `run` callback or by returning a Promise from the `up` or `down` function.

```javascript
export const up = function up(pgm, run) {
  doSomethingAsync(function () {
    run();
  });
};
```

```javascript
export const up = function (pgm) {
  return new Promise((resolve) => {
    // doSomethingAsync
    resolve();
  });
};
```

```javascript
export const up = async (pgm) => {
  // doSomethingAsync
};
```

--------------------------------

### Add auto-increment ID column

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/columns.md

Demonstrates the shorthand syntax for creating a primary key serial ID column.

```ts
pgm.addColumns('myTable', { id: 'id' });
```

```ts
pgm.addColumns('myTable', { id: { type: 'serial', primaryKey: true } });
```

--------------------------------

### createOperatorClass

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/operators.md

Creates a new PostgreSQL operator class.

```APIDOC
## pgm.createOperatorClass(operator_class_name, type, index_method, operator_list, options)

### Description
Creates a new operator class.

### Parameters
#### Arguments
- **operator_class_name** (Name) - Required - name of the new operator class
- **type** (string) - Required - data type of the new operator class
- **index_method** (string) - Required - name of the index method of operator class
- **operator_list** (array) - Required - list of operator objects
- **options** (object) - Required - Configuration options

#### Options
- **default** (boolean) - Optional - adds DEFAULT clause
- **family** (string) - Optional - type of left argument
```

--------------------------------

### Column Definitions

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/columns.md

Defines the available options for specifying columns when creating or altering tables.

```APIDOC
## Column Definitions

The `createTable` and `addColumns` methods both take a `columns` argument that specifies column names and options. It is an object (key/value) where each key is the name of the column, and the value is another object that defines the options for the column.

| Option                        | Type                                  | Description                                                                                  |
| ----------------------------- | ------------------------------------- | -------------------------------------------------------------------------------------------- |
| `type`                        | `string`                              | Data type (use normal postgres types)                                                        |
| `collation`                   | `string`                              | Collation of data type                                                                       |
| `unique`                      | `boolean`                             | Set to true to add a unique constraint on this column                                        |
| `primaryKey`                  | `boolean`                             | Set to true to make this column the primary key                                              |
| `notNull`                     | `boolean`                             | Set to true to make this column not null                                                     |
| `default`                     | `string`                              | Adds DEFAULT clause for column. Accepts null, a literal value, or a `pgm.func()` expression. |
| `check`                       | `string`                              | SQL for a check constraint for this column                                                   |
| `references`                  | [Name](/migrations/#type) or `string` | A table name that this column is a foreign key to                                            |
| `referencesConstraintName`    | `string`                              | Name of the created constraint                                                               |
| `referencesConstraintComment` | `string`                              | Comment on the created constraint                                                            |
| `onDelete`                    | `string`                              | Adds ON DELETE constraint for a reference column                                             |
| `onUpdate`                    | `string`                              | Adds ON UPDATE constraint for a reference column                                             |
| `match`                       | `string`                              | `FULL` or `SIMPLE`                                                                           |
| `deferrable`                  | `boolean`                             | Flag for deferrable column constraint                                                        |
| `deferred`                    | `boolean`                             | Flag for initially deferred deferrable column constraint                                     |
| `comment`                     | `string`                              | Adds comment on column                                                                       |
| `expressionGenerated`         | `string`                              | Expression to compute column value                                                           |
| `sequenceGenerated`           | `object`                              | Creates identity column see [sequence options section](sequences.md#sequence-options)        |
| `precedence`                  | `string`                              | `ALWAYS` or `BY DEFAULT`                                                                     |
```

--------------------------------

### MigrationUnit Interface

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/api.md

Represents a single database migration, including its identifier, associated file paths, and the actions to be performed. This interface is used internally to manage migration execution.

```typescript
export interface MigrationUnit {
  // The unique identifier for the migration unit. Represents the significant part of the file name used for tracking which migrations have been performed.
  id: string;

  // File paths that are part of the migration unit.
  filePaths: string[];

  // The migration builder actions that are contained within the migration files.
  actions: MigrationBuilderActions;
}
```

--------------------------------

### Run N Down Migrations

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/cli.md

Reverts the last N executed 'up' migrations by running N 'down' migrations.

```bash
node-pg-migrate down {N}
```

--------------------------------

### createOperator

Source: https://github.com/salsita/node-pg-migrate/blob/main/docs/src/migrations/operators.md

Creates a new PostgreSQL operator.

```APIDOC
## pgm.createOperator(operator_name, options)

### Description
Creates a new operator in the database.

### Parameters
#### Arguments
- **operator_name** (Name) - Required - name of the new operator
- **options** (object) - Required - Configuration options

#### Options
- **procedure** (Name) - Optional - name of procedure performing operation
- **left** (Name) - Optional - type of left argument
- **right** (Name) - Optional - type of right argument
- **commutator** (Name) - Optional - name of commutative operator
- **negator** (Name) - Optional - name of negating operator
- **restrict** (Name) - Optional - name of restriction procedure
- **join** (Name) - Optional - name of join procedure
- **hashes** (boolean) - Optional - adds HASHES clause
- **merges** (boolean) - Optional - adds MERGES clause
```