### Example Graphile Migrate Configuration

Source: https://github.com/graphile/migrate/blob/main/README.md

This JSON5 configuration demonstrates setting pgSettings, placeholders, and defining actions for afterReset, afterAllMigrations, and afterCurrent stages. It relies on environment variables for database credentials.

```json
{
  "pgSettings": {
    "search_path": "app_public,app_private,app_hidden,public"
  },
  "placeholders": {
    ":DATABASE_AUTHENTICATOR": "!ENV",
    ":DATABASE_VISITOR": "!ENV"
  },
  "afterReset": [
    "afterReset.sql",
    {
      "_": "command",
      "command": "DATABASE_URL=\"$$GM_DBURL\" npx --no-install graphile-worker --schema-only"
    }
  ],
  "afterAllMigrations": [
    {
      "_": "command",
      "command": "pg_dump --schema-only --no-owner --exclude-schema=graphile_migrate --file=data/schema.sql \"$$GM_DBURL\""
    }
  ],
  "afterCurrent": ["afterCurrent.sql"]
}
```

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

### SQL Commands for Database Setup

Source: https://github.com/graphile/migrate/blob/main/CONTRIBUTING.md

Execute these SQL commands to create a role and a database for testing Graphile Migrate. The role 'gmtestuser' is created with login privileges and a password, and the database 'graphile_migrate_test' is owned by this role.

```sql
create role gmtestuser with login password 'gmtestpass';
create database graphile_migrate_test owner gmtestuser;
```

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

### Create Databases and Roles

Source: https://github.com/graphile/migrate/blob/main/README.md

Use these bash commands to create a database role and two databases (main and shadow) for your project. Ensure you have PostgreSQL installed and running.

```bash
createuser --pwprompt dbowner
createdb myapp --owner=dbowner
createdb myapp_shadow --owner=dbowner
```

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

### Multifile Migration Example

Source: https://github.com/graphile/migrate/blob/main/FORMATS.md

Demonstrates the use of --! split comments to divide a migration into multiple files. Each split is separated by a newline, and empty files result in two newlines due to the trim and trail rule.

```sql
--! split: 001.sql
select 1;

--! split: 002-empty.sql


--! split: 003.sql
select 3;

```

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

### Idempotent SQL Migration Example

Source: https://github.com/graphile/migrate/blob/main/README.md

Use `CREATE OR REPLACE FUNCTION` and `DROP TABLE IF EXISTS` for idempotent SQL migrations. This ensures scripts can be run multiple times without errors.

```sql
create or replace function...
drop table if exists ...
drop trigger if exists ...
```

```sql
-- undo
drop table if exists people;

-- redo
create table people (
  id serial primary key,
  name text
);
```

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

### Run Migration with Root Privileges

Source: https://context7.com/graphile/migrate/llms.txt

Execute a migration file using root or superuser privileges. This is necessary for operations that require elevated permissions, such as installing extensions.

```bash
graphile-migrate run migrations/install_extensions.sql --root
```

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

### Run Migration Against Root Database

Source: https://context7.com/graphile/migrate/llms.txt

Execute a migration file against a specified root database, such as template1. This is useful for initial setup or configuration tasks.

```bash
graphile-migrate run migrations/create_roles.sql --rootDatabase
```

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

### SQL function definition

Source: https://github.com/graphile/migrate/blob/main/README.md

Example SQL function to be included in a migration.

```sql
create or replace function myfunction(a int, b int)
returns int as $$
  select a + b;
$$ language sql stable;
```

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

### Start Migration Watch Mode Programmatically

Source: https://context7.com/graphile/migrate/llms.txt

Enable watch mode for custom development tooling using the watch() function. This allows for automatic re-migration on file changes.

```typescript
import { watch, Settings } from "graphile-migrate";

const settings: Settings = {
  connectionString: process.env.DATABASE_URL,
  shadowConnectionString: process.env.SHADOW_DATABASE_URL,
  migrationsFolder: "./migrations",
};

// Start watching for migration changes
async function startDevelopment() {
  await watch(settings, false, false); // (settings, once, shadow)
  console.log("Watching for migration migration changes...");
}

startDevelopment();
```

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

### Initialize Graphile Migrate Project

Source: https://github.com/graphile/migrate/blob/main/README.md

Run this command after setting up your environment variables to initialize a new Graphile Migrate project. This sets up the necessary configuration and directory structure.

```bash
graphile-migrate init
```

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

### Initialize Graphile Migrate Project

Source: https://context7.com/graphile/migrate/llms.txt

Initializes a new Graphile Migrate project. Use `--folder` to create migrations in a directory, or `--config` to specify a custom configuration file path.

```bash
graphile-migrate init
```

```bash
graphile-migrate init --folder
```

```bash
graphile-migrate init --config ./config/.gmrc
```

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

### Compile SQL with Shadow Database Placeholders

Source: https://context7.com/graphile/migrate/llms.txt

Use this command to compile SQL files, supporting shadow database placeholders. Ensure the migrations folder is correctly specified.

```bash
graphile-migrate compile migrations/current.sql --shadow
```

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

### Docker Compose for Graphile Migrate Tests

Source: https://github.com/graphile/migrate/blob/main/CONTRIBUTING.md

Use this docker-compose.yml file to set up a PostgreSQL database for running Graphile Migrate tests. Ensure your local user account has the necessary permissions for database connections.

```yaml
version: "3"

services:
  graphile_migrate_postgres:
    container_name: graphile_migrate_postgres
    environment:
      POSTGRES_USER: ${USER}
      POSTGRES_HOST_AUTH_METHOD: trust
    image: "postgres:12.4"
    ports:
      - 5432:5432
    restart: always
    volumes:
      - graphile_migrate_data:/var/lib/postgresql/data

volumes:
  graphile_migrate_data:
    driver: local
```

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

### Run Migration Seed File

Source: https://context7.com/graphile/migrate/llms.txt

Execute a SQL migration file for seeding data. This command compiles and runs the specified SQL file against the database.

```bash
graphile-migrate run migrations/seed.sql
```

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

### Set Database Environment Variables

Source: https://github.com/graphile/migrate/blob/main/README.md

Export the necessary database connection URLs as environment variables. DATABASE_URL is for the main application, SHADOW_DATABASE_URL for development commands, and ROOT_DATABASE_URL for database management tasks like resetting.

```bash
export DATABASE_URL="postgres://dbowner:password@localhost/myapp"
export SHADOW_DATABASE_URL="postgres://dbowner:password@localhost/myapp_shadow"

export ROOT_DATABASE_URL="postgres://postgres:postgres@localhost/postgres"
```

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

### Run Migrations

Source: https://context7.com/graphile/migrate/llms.txt

Applies un-executed committed migrations to the database. Use `--shadow` to run against a shadow database for testing, or `--forceActions` to execute actions even if no migrations are pending.

```bash
graphile-migrate migrate
```

```bash
graphile-migrate migrate --shadow
```

```bash
graphile-migrate migrate --forceActions
```

```bash
graphile-migrate migrate --config ./my-project/.gmrc
```

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

### Graphile-Migrate Configuration (.gmrc)

Source: https://context7.com/graphile/migrate/llms.txt

Configure database connections, placeholders, and lifecycle actions using a JSON5 file. Connection strings can be set directly or via environment variables.

```json5
{
  // Database connections (prefer environment variables)
  // "connectionString": "postgres://user:pass@localhost/myapp",
  // "shadowConnectionString": "postgres://user:pass@localhost/myapp_shadow",
  // "rootConnectionString": "postgres://postgres:postgres@localhost/postgres",

  // PostgreSQL session settings applied before migrations
  "pgSettings": {
    "search_path": "app_public,app_private,public"
  },

  // Placeholder substitutions for SQL files
  "placeholders": {
    ":DATABASE_VISITOR": "!ENV",  // Loads from process.env.DATABASE_VISITOR
    ":GRAPHILE_WORKER_SCHEMA": "graphile_worker"
  },

  // Actions executed after database reset
  "afterReset": [
    // Run SQL file with root privileges (note the ! prefix)
    "!install_extensions.sql",
    // Run SQL file as normal user
    "afterReset.sql",
    // Run shell command
    {
      "_": "command",
      "command": "npx graphile-worker --schema-only"
    }
  ],

  // Actions after all migrations complete
  "afterAllMigrations": [
    {
      "_": "command",
      "shadow": true,  // Only run on shadow database
      "command": "pg_dump --schema-only --no-owner --file=schema.sql \"$GM_DBURL\""
    }
  ],

  // Actions after current migration runs (watch mode)
  "afterCurrent": [
    "afterCurrent.sql"
  ],

  // Custom blank migration content
  "blankMigrationContent": "-- Write your migration here\n",

  // Migrations folder location (default: ./migrations)
  "migrationsFolder": "./migrations"
}
```

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

### Compile SQL from Stdin

Source: https://context7.com/graphile/migrate/llms.txt

Compile SQL files by piping their content to the graphile-migrate compile command. This is useful for dynamic SQL generation.

```bash
cat migrations/current.sql | graphile-migrate compile
```

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

### Run Migration on Shadow Database

Source: https://context7.com/graphile/migrate/llms.txt

Execute a migration file against the shadow database. This is useful for testing migrations without affecting the production database.

```bash
graphile-migrate run migrations/seed.sql --shadow
```

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

### Shorthand SQL Action

Source: https://github.com/graphile/migrate/blob/main/README.md

Use the '!' prefix as a shorthand to execute a SQL file with superuser privileges.

```json
"afterReset": [ "!install_extensions.sql" ]
```

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

### Run Current Migration

Source: https://context7.com/graphile/migrate/llms.txt

Executes all committed migrations plus the current migration. Use `--shadow` to target the shadow database or `--forceActions` to ensure all actions are run.

```bash
graphile-migrate current
```

```bash
graphile-migrate current --shadow
```

```bash
graphile-migrate current --forceActions
```

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

### Idempotent Migration: Create/Replace Schemas and Tables

Source: https://context7.com/graphile/migrate/llms.txt

Write idempotent SQL migrations using `DROP IF EXISTS` and `CREATE OR REPLACE` for schemas and tables. This ensures migrations can be run multiple times without errors.

```sql
-- migrations/current.sql

-- Create or replace schemas
DROP SCHEMA IF EXISTS app_public CASCADE;
CREATE SCHEMA app_public;

-- Create tables with CASCADE to handle dependencies
DROP TABLE IF EXISTS app_public.users CASCADE;
CREATE TABLE app_public.users (
  id serial PRIMARY KEY,
  username text NOT NULL UNIQUE,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Add columns idempotently
ALTER TABLE app_public.users DROP COLUMN IF EXISTS avatar_url CASCADE;
ALTER TABLE app_public.users ADD COLUMN avatar_url text;

-- Create or replace functions
CREATE OR REPLACE FUNCTION app_public.current_user_id()
RETURNS int AS $$
  SELECT nullif(current_setting('jwt.claims.user_id', true), '')::int;
$$ LANGUAGE sql STABLE;

-- Use anonymous blocks for complex conditional logic
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'app_public'
      AND table_name = 'users'
      AND column_name = 'name'
  ) THEN
    ALTER TABLE app_public.users RENAME COLUMN name TO username;
  END IF;
END$$;

-- Row-level security
ALTER TABLE app_public.users ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS select_own ON app_public.users;
CREATE POLICY select_own ON app_public.users
  FOR SELECT USING (id = app_public.current_user_id());
```

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

### Source Environment Variables and Watch for Changes

Source: https://github.com/graphile/migrate/blob/main/README.md

Source your environment variables from a .env file to easily load them. Then, run 'graphile-migrate watch' to automatically apply changes in your current migration.

```bash
. ./.env
graphile-migrate watch
```

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

### Compile SQL Migration File

Source: https://context7.com/graphile/migrate/llms.txt

Compiles a SQL file by resolving includes and replacing placeholders, outputting the result to STDOUT. Useful for debugging or generating final SQL.

```bash
graphile-migrate compile migrations/current.sql
```

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

### Dump Existing Database Schema with pg_dump

Source: https://github.com/graphile/migrate/blob/main/README.md

Use `pg_dump` to create a schema-only dump of your existing production database. This dump will serve as the initial state for Graphile Migrate. Ensure to specify the output file and connection string.

```bash
pg_dump --schema-only --no-owner --file=migrations/initial_schema.sql "postgres://..."
```

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

### Build Graphile Migrate Docker Image

Source: https://github.com/graphile/migrate/blob/main/docs/docker/README.md

Build the Docker image for Graphile Migrate, specifying Node.js and PostgreSQL versions. This command is run from the repository root.

```bash
docker build -t graphile-migrate docs/docker \
    --build-arg NODEJS_VERSION=14 --build-arg POSTGRES_VERSION=12
```

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

### Configure afterReset Action in .gmrc

Source: https://github.com/graphile/migrate/blob/main/README.md

Configure the `.gmrc` file to automatically apply the `initial_schema.sql` after a database reset. This ensures new or reset databases are initialized correctly before migrations are applied.

```json
"afterReset": [ "initial_schema.sql" ]
```

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

### Include External SQL Files in Migrations

Source: https://context7.com/graphile/migrate/llms.txt

Organize reusable SQL logic into separate files and include them in your main migration scripts using the --!include directive.

```sql
-- migrations/current.sql
--!include functions/current_user_id.sql
--!include functions/authenticate.sql

-- Rest of migration...
DROP TABLE IF EXISTS app_public.sessions CASCADE;
CREATE TABLE app_public.sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id int NOT NULL REFERENCES app_public.users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);
```

```sql
-- migrations/fixtures/functions/authenticate.sql
CREATE OR REPLACE FUNCTION app_public.authenticate(
  email text,
  password text
) RETURNS app_public.users AS $$
DECLARE
  v_user app_public.users;
BEGIN
  SELECT * INTO v_user
  FROM app_public.users u
  WHERE u.email = authenticate.email;

  IF v_user.password_hash = crypt(password, v_user.password_hash) THEN
    RETURN v_user;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql STRICT SECURITY DEFINER;
```

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

### Compile and Run SQL Files Programmatically

Source: https://context7.com/graphile/migrate/llms.txt

Use the compile() function to process SQL files with placeholder replacement, or the run() function to execute arbitrary SQL against the database. This is useful for seeding or complex SQL operations.

```typescript
import { compile, run, Settings } from "graphile-migrate";
import * as fs from "fs";

const settings: Settings = {
  connectionString: process.env.DATABASE_URL,
  placeholders: {
    ":APP_SCHEMA": "app_public",
  },
};

// Compile a SQL file with placeholder replacement
async function compileSql() {
  const rawSql = fs.readFileSync("./migrations/current.sql", "utf8");
  const compiled = await compile(settings, rawSql, { shadow: false });
  console.log(compiled);
}

// Execute a SQL file against the database
async function seedDatabase() {
  const seedSql = fs.readFileSync("./seeds/initial-data.sql", "utf8");
  const rows = await run(settings, seedSql, "seeds/initial-data.sql", {
    shadow: false,
    root: false,
  });
  console.log("Seed completed, affected rows:", rows);
}
```

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

### Watch for Migration Changes

Source: https://context7.com/graphile/migrate/llms.txt

Applies committed migrations and then watches the current migration file for changes, re-executing it on save. Use `--shadow` to apply to the shadow database, or `--once` to run the current migration and exit.

```bash
graphile-migrate watch
```

```bash
graphile-migrate watch --shadow
```

```bash
graphile-migrate watch --once
```

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

### Command Action Spec Object

Source: https://github.com/graphile/migrate/blob/main/README.md

Specify a shell command to be executed during the migration process. Environment variables like GM_DBURL are available.

```json
{
  "_": "command",
  "command": "npx --no-install graphile-worker --once"
}
```

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

### Reset Database

Source: https://context7.com/graphile/migrate/llms.txt

Drops and recreates the database, then applies all committed migrations. This is a destructive operation and requires the `--erase` flag for confirmation. Use `--shadow` to target the shadow database.

```bash
graphile-migrate reset --erase
```

```bash
graphile-migrate reset --shadow --erase
```

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

### Run Pending Migrations Programmatically

Source: https://context7.com/graphile/migrate/llms.txt

Integrate Graphile Migrate into your build tools or custom scripts by using the migrate() function. Ensure your database connection details and migration folder are correctly configured in the settings.

```typescript
import { migrate, Settings } from "graphile-migrate";

const settings: Settings = {
  connectionString: process.env.DATABASE_URL,
  shadowConnectionString: process.env.SHADOW_DATABASE_URL,
  rootConnectionString: process.env.ROOT_DATABASE_URL,
  migrationsFolder: "./migrations",
  pgSettings: {
    search_path: "app_public,app_private,public",
  },
};

// Run pending migrations
async function runMigrations() {
  try {
    await migrate(settings);
    console.log("Migrations completed successfully");
  } catch (error) {
    console.error("Migration failed:", error);
    process.exit(1);
  }
}

runMigrations();
```

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

### Idempotent Schema and Table Creation

Source: https://github.com/graphile/migrate/blob/main/docs/idempotent-examples.md

Use `DROP ... IF EXISTS` and `CREATE` for idempotent schema and table operations. This ensures tables are created only if they don't exist and dropped if they do, preventing errors on reruns.

```sql
-- Create a schema
DROP SCHEMA IF EXISTS app CASCADE;
CREATE SCHEMA app;
```

```sql
-- Create a table
DROP TABLE IF EXISTS foo CASCADE;
CREATE TABLE foo ...;
```

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

### SQL Action Spec Object

Source: https://github.com/graphile/migrate/blob/main/README.md

Define a SQL action to execute a specific SQL file against the database. The `root: true` option runs the file with superuser privileges.

```json
{
  "_": "sql",
  "file": "install_extensions.sql",
  "root": false
}
```

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

### Exporting .gmrc.js Configuration

Source: https://github.com/graphile/migrate/blob/main/README.md

Use this syntax to export your graphile-migrate configuration from a .gmrc.js file.

```javascript
module.exports = {

```

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

### Include external SQL file in current migration

Source: https://github.com/graphile/migrate/blob/main/README.md

Use the `--!include` directive to embed the content of an external SQL file into the current migration. The included file's content will be replaced at the directive's location.

```sql
--!include functions/myfunction.sql
drop policy if exists access_by_numbers on mytable;
create policy access_by_numbers on mytable for update using (myfunction(4, 2) < 42);
```

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

### Resulting SQL after inclusion

Source: https://github.com/graphile/migrate/blob/main/README.md

This is the SQL that will be executed after the `--!include` directive has been processed, showing the embedded content.

```sql
--! Included functions/myfunction.sql
create or replace function myfunction(a int, b int)
returns int as $$
  select a + b;
$$ language sql stable;
--! EndIncluded functions/myfunction.sql
drop policy if exists access_by_numbers on mytable;
create policy access_by_numbers on mytable for update using (myfunction(4, 2) < 42);
```

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

### Idempotent Function Creation/Replacement

Source: https://github.com/graphile/migrate/blob/main/docs/idempotent-examples.md

Use `CREATE OR REPLACE FUNCTION` for idempotent function updates. This command will create the function if it doesn't exist or replace it if it does, ensuring the latest version is always applied.

```sql
-- Change the body or flags of a function
CREATE OR REPLACE FUNCTION ...;
```

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

### Commit Current Migration

Source: https://context7.com/graphile/migrate/llms.txt

Validates the current migration against the shadow database, commits it to the `committed/` folder with a hash signature, and resets the current migration. Use `-m` to add a descriptive message.

```bash
graphile-migrate commit
```

```bash
graphile-migrate commit -m "Add users table and authentication functions"
```

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

### Idempotent SQL for Function Creation

Source: https://github.com/graphile/migrate/blob/main/README.md

To prevent drift, ensure SQL statements for creating functions are idempotent by including a `DROP FUNCTION IF EXISTS` clause before the `CREATE FUNCTION` statement. This makes the script safe to run multiple times.

```sql
-- Revision 1
drop function if exists rnd();

create function rnd() returns int as $$
  select 4;
$$ language sql stable;
```

```sql
-- Revision 2
drop function if exists rnd();
drop function if exists get_random_number();

create function get_random_number() returns int as $$
  select 4;
$$ language sql stable;
```

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

### Check Migration Status

Source: https://context7.com/graphile/migrate/llms.txt

Reports the current migration status. Exit codes indicate pending work (1 for uncommitted, 2 for non-empty current, 3 for both). Use `--skipDatabase` to check only the current migration file without a database connection.

```bash
graphile-migrate status
```

```bash
graphile-migrate status --skipDatabase
```

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

### Check Migration Status Programmatically

Source: https://context7.com/graphile/migrate/llms.txt

Query the status of your database migrations using the status() function, which is useful for CI/CD pipelines. It returns information about pending migrations and the current migration's commit status.

```typescript
import { status, Settings } from "graphile-migrate";

const settings: Settings = {
  connectionString: process.env.DATABASE_URL,
  shadowConnectionString: process.env.SHADOW_DATABASE_URL,
};

async function checkStatus() {
  const result = await status(settings, { skipDatabase: false });

  if (result.remainingMigrations && result.remainingMigrations.length > 0) {
    console.log("Pending migrations:", result.remainingMigrations);
  }

  if (result.hasCurrentMigration) {
    console.log("Current migration has uncommitted changes");
  }

  return result;
}

checkStatus();
```

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

### Force LF Line Endings for Migrations

Source: https://github.com/graphile/migrate/blob/main/README.md

Configure Git to use LF line endings for SQL migration files to prevent hash verification failures across different operating systems.

```gitattributes
migrations/committed/*.sql text eol=lf
migrations/current.sql text eol=lf
```

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

### Use Placeholders for Environment-Specific Values

Source: https://context7.com/graphile/migrate/llms.txt

Utilize placeholders in your SQL migrations to inject environment-specific values, such as database roles or schema names, without modifying committed files. These are replaced from .gmrc or environment variables.

```sql
-- migrations/current.sql
-- :DATABASE_VISITOR is replaced with the actual value from .gmrc or env

GRANT USAGE ON SCHEMA app_public TO :DATABASE_VISITOR;
GRANT SELECT ON app_public.users TO :DATABASE_VISITOR;

-- Built-in placeholders
COMMENT ON DATABASE :DATABASE_NAME IS 'Main application database';
ALTER DATABASE :DATABASE_NAME OWNER TO :DATABASE_OWNER;
```

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

### Allow Editing Committed Migrations

Source: https://github.com/graphile/migrate/blob/main/README.md

Use the `--! AllowInvalidHash` comment to bypass Graphile Migrate's cryptographic hashing checks for committed migrations. This is a rare use case and should be avoided if possible.

```sql
--! AllowInvalidHash
```

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

### Idempotent Function Signature Change

Source: https://github.com/graphile/migrate/blob/main/docs/idempotent-examples.md

Handle function signature changes idempotently by dropping the existing function if it exists and then creating a new one with the desired signature. This avoids conflicts if the function already exists with a different signature.

```sql
-- Change a function signature (arguments, return type, etc)
DROP FUNCTION IF EXISTS ... CASCADE;
CREATE OR REPLACE FUNCTION ...
```

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

### Uncommit Latest Migration

Source: https://context7.com/graphile/migrate/llms.txt

Moves the latest committed migration back to the current migration file for editing. Use with caution, as it's only safe before the migration has been applied to other databases.

```bash
graphile-migrate uncommit
```

```bash
graphile-migrate commit -m "Updated migration message"
```

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

### Idempotent Column Rename using Anonymous Block

Source: https://github.com/graphile/migrate/blob/main/docs/idempotent-examples.md

Implement custom idempotency for commands like `rename` by using an anonymous `DO` block. This checks for the column's existence before attempting to rename it, preventing errors.

```sql
do $$
begin
    /* if column `username` exists on users table */
    if exists(
        select 1
            from information_schema.columns
            where table_schema = 'public'
            and table_name = 'users'
            and column_name = 'username'
    ) then
        /* rename the column to `name` */
        alter table users
            rename column username to name;
    end if;
end$$;
```

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

### Idempotent Column Addition

Source: https://github.com/graphile/migrate/blob/main/docs/idempotent-examples.md

Add columns idempotently by dropping them if they exist before adding. This prevents errors if the column has already been added in a previous migration run.

```sql
-- Add a column to the end of the table
ALTER TABLE foo DROP COLUMN IF EXISTS bar CASCADE;
ALTER TABLE foo ADD COLUMN bar ...;
```

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

### Disable Transaction for Migration

Source: https://github.com/graphile/migrate/blob/main/README.md

Use the `--! no-transaction` comment at the top of a migration file to disable wrapping it in a transaction. This is necessary for certain operations like augmenting ENUM types. Ensure the migration contains exactly one statement when disabling transactions.

```sql
--! no-transaction
ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'Admin';
```

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

### Idempotent Enum Value Rename using Anonymous Block

Source: https://github.com/graphile/migrate/blob/main/docs/idempotent-examples.md

Achieve idempotency for renaming enum values by checking for the enum value's existence within an anonymous `DO` block. This prevents errors if the value has already been renamed.

```sql
do $$
begin
    /* if `PENDING` exists in purchase_status enum */
    if exists(
        select 1
            from pg_catalog.pg_enum as enum_value
        inner join pg_catalog.pg_type as custom_type
            on custom_type.oid = enum_value.enumtypid
        where typname = 'purchase_status'
            and enumlabel = 'PENDING'
    ) then
        /* rename the enum value to `PURCHASE_PENDING` */
        alter type app_public.purchase_status rename value 'PENDING' to 'PURCHASE_PENDING';
    end if;
end$$;
```

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

### Disable Transactions for Specific Operations

Source: https://context7.com/graphile/migrate/llms.txt

Use the `--! no-transaction` directive for SQL statements that cannot be executed within a transaction, such as adding values to an existing enum type.

```sql
--! no-transaction
ALTER TYPE app_public.user_role ADD VALUE IF NOT EXISTS 'MODERATOR';
```

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

### Idempotent Column Modification

Source: https://github.com/graphile/migrate/blob/main/docs/idempotent-examples.md

Modify columns idempotently. Use `ALTER COLUMN ... SET NOT NULL` to ensure a column is not null, and `ALTER COLUMN ... TYPE ... USING ...` to change a column's type, handling potential existing data.

```sql
-- Make a column NOT NULL
ALTER TABLE foo ALTER COLUMN bar SET NOT NULL;
```

```sql
-- Alter a column type
ALTER TABLE foo ALTER COLUMN bar TYPE int USING bar::int;
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.