pgroll

https://pgroll.com/docs/latest
Admin
pgroll is a migration tool for PostgreSQL that enables zero-downtime schema changes with instant...
Tokens:21,762
Snippets:344
Trust Score:6.9
Update:4 months ago
Show doc for...
Tokens:
Raw
### Install from source

Source: https://pgroll.com/docs/latest/installation

Command to install pgroll from source using Go.

```go
go install github.com/xataio/pgroll@latest
```

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

### Start migration command

Source: https://pgroll.com/docs/latest/tutorial

Command to start the pgroll migration defined in the YAML file.

```bash
pgroll start 03_add_is_active_column.yaml
```

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

### Go client example

Source: https://pgroll.com/docs/latest/guides/clientapps

Example of setting the search_path in a Go client application after establishing a connection.

```go
db, err := sql.Open("postgres", "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable")
if err != nil {
    return nil, err
}

searchPath := "public_02_add_assignee_column"
log.Printf("Setting search path to %q", searchPath)
_, err = db.Exec(fmt.Sprintf("SET search_path = %s", pq.QuoteIdentifier(searchPath)))
if err != nil {
    return nil, fmt.Errorf("failed to set search path: %s", err)
}
```

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

### Start and complete a migration in one command

Source: https://pgroll.com/docs/latest/cli/start

Starts and immediately completes the migration, equivalent to running `pgroll start` followed by `pgroll complete`.

```bash
$ pgroll start sql/03_add_column.yaml --complete

```

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

### Node.js client example

Source: https://pgroll.com/docs/latest/guides/clientapps

Example of setting the search_path in a Node.js client application.

```javascript
const { Client } = require("pg");

const client = new Client({
  user: "postgres",
  host: "localhost",
  database: "postgres",
  password: "postgres",
  port: 5432,
});

client.connect();

client.query("SET search_path TO public_02_add_assignee_column", (err, res) => {
  if (err) {
    console.error("Error setting search_path", err.stack);
  } else {
    console.log("search_path set successfully");
    // Perform your database operations here
  }

  // End the connection
  client.end();
});
```

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

### Python client example

Source: https://pgroll.com/docs/latest/guides/clientapps

Example of setting the search_path in a Python client application using psycopg2.

```python
import psycopg2

with psycopg2.connect('dbname=postgres user=postgres password=postgres options=-c search_path=public_02_add_assignee_column') as conn:
    pass
```

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

### Create one table

Source: https://pgroll.com/docs/latest/operations/create_table

Example of creating a single table.

```yaml
operations:
  - create_table:
      name: products
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
          unique: true
        - name: price
          type: decimal(10,2)
```

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

### Start the migration

Source: https://pgroll.com/docs/latest/tutorial

Command to initiate the pgroll migration using the specified YAML file.

```bash
pgroll start 02_user_description_set_nullable.yaml

```

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

### CI/CD pipeline example

Source: https://pgroll.com/docs/latest/guides/clientapps

Command to get the latest schema version for use in CI/CD pipelines.

```bash
$ pgroll latest schema
public_02_add_assignee_column

```

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

### Create one table (3)

Source: https://pgroll.com/docs/latest/operations/create_table

Example of creating a single table.

```yaml
operations:
  - create_table:
      name: posts
      columns:
        - name: id
          type: serial
          pk: true
        - name: title
          type: varchar(255)
        - name: user_id
          type: integer
          nullable: true
```

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

### Start a Postgres instance in Docker

Source: https://pgroll.com/docs/latest/tutorial

This command starts a new PostgreSQL instance in a Docker container, making it accessible on port 5432.

```bash
docker run --rm --name for-pgroll -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:18

```

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

### Install with Homebrew

Source: https://pgroll.com/docs/latest/installation

Commands to install pgroll using Homebrew on macOS or Linux.

```bash
# macOS or Linux
brew tap xataio/pgroll
brew install pgroll
```

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

### Database Example

Source: https://pgroll.com/docs/latest/cli/latest-migration

Prints the latest migration name in the target database.

```bash
$ pgroll latest migration

```

```bash
55_add_primary_key_constraint_to_table

```

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

### Start a migration

Source: https://pgroll.com/docs/latest/cli/start

Starts the migration defined in the specified YAML file.

```bash
$ pgroll start sql/03_add_column.yaml

```

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

### Command

Source: https://pgroll.com/docs/latest/cli/complete

This command completes the most recently started migration.

```bash
$ pgroll complete

```

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

### Go client example for setting search_path

Source: https://pgroll.com/docs/latest/tutorial

This Go code snippet demonstrates how a client application can set the `search_path` to a specific migration version after establishing a connection to a PostgreSQL database.

```Go
db, err := sql.Open("postgres", "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable")
if err != nil {
    return nil, err
}

searchPath := "02_user_description_set_nullable"
_, err = db.Exec(fmt.Sprintf("SET search_path = %s", pq.QuoteIdentifier(searchPath)))
if err != nil {
    return nil, fmt.Errorf("failed to set search path: %s", err)
}
```

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

### Status output example

Source: https://pgroll.com/docs/latest/cli/status

Example of the JSON output when a migration is complete.

```json
{
  "Schema": "public",
  "Version": "27_drop_unique_constraint",
  "Status": "Complete"
}

```

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

### Example Output: Database

Source: https://pgroll.com/docs/latest/cli/latest-schema

Example output showing the latest schema version in the target database.

```bash
public_55_add_primary_key_constraint_to_table

```

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

### Local Example

Source: https://pgroll.com/docs/latest/cli/latest-migration

Prints the latest migration name from a local migration directory.

```bash
$ pgroll latest migration --local examples/

```

```bash
55_add_primary_key_constraint_to_table

```

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

### Control backfill process

Source: https://pgroll.com/docs/latest/cli/start

Demonstrates controlling the backfill process with batch size and delay options.

```bash
$ pgroll migrate examples/ --backfill-batch-size 500 --backfill-batch-delay 100ms

```

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

### Create Table with Version Schema

Source: https://pgroll.com/docs/latest/operations/create_table

Example of creating a 'products' table with an 'id' and 'name' column, and setting the version_schema to 'with_version_schema'.

```yaml
version_schema: with_version_schema
operations:
  - create_table:
      name: products
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
          unique: true
```

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

### Create one table (2)

Source: https://pgroll.com/docs/latest/operations/create_table

Example of creating a single table with a generated identity column.

```yaml
operations:
  - create_table:
      name: fruits
      columns:
        - name: id
          type: bigint
          pk: true
          generated:
            identity:
              user_specified_values: BY DEFAULT
        - name: name
          type: varchar(255)
          unique: true
        - name: price
          type: decimal(10,2)
```

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

### Example: Local

Source: https://pgroll.com/docs/latest/cli/latest-schema

Prints the latest schema version from a local migration directory.

```bash
$ pgroll latest schema --local examples/

```

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

### Set replica identity example

Source: https://pgroll.com/docs/latest/operations/set_replica_identity

An example of setting the replica identity for the 'fruits' table using an index.

```yaml
operations:
  - set_replica_identity:
      table: fruits
      identity:
        type: index
        index: fruits_pkey
```

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

### Drop a table example

Source: https://pgroll.com/docs/latest/operations/drop_table

An example of how to drop the 'products' table within a migration.

```yaml
operations:
  - drop_table:
      name: products
```

```json
{
  "operations": [
    {
      "drop_table": {
        "name": "products"
      }
    }
  ]
}
```

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

### Create multiple tables

Source: https://pgroll.com/docs/latest/operations/create_table

Example of creating multiple tables, where each table is a separate operation in the migration.

```yaml
operations:
  - create_table:
      name: customers
      columns:
        - name: id
          type: integer
          pk: true
        - name: name
          type: varchar(255)
          unique: true
        - name: credit_card
          type: text
          nullable: true
  - create_table:
      name: bills
      columns:
        - name: id
          type: integer
          pk: true
        - name: date
          type: time with time zone
        - name: quantity
          type: integer
  - create_table:
      name: sellers
      columns:
        - name: name
          type: varchar(255)
          pk: true
        - name: zip
          type: integer
          pk: true
        - name: description
          type: varchar(255)
          nullable: true
```

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

### Update migrations in the migrations folder

Source: https://pgroll.com/docs/latest/cli/update

Example for updating migrations in the `migrations` folder.

```bash
$ pgroll update migrations

```

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

### Migrate Command Example

Source: https://pgroll.com/docs/latest/cli/migrate

Applies migrations from a specified directory to the target database. If `--complete` is passed, the final migration is completed; otherwise, it's left active.

```bash
$ pgroll migrate examples/

```

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

### Status output example for a specific schema

Source: https://pgroll.com/docs/latest/cli/status

Example of the JSON output when viewing the status of a different schema.

```json
{
  "Schema": "schema_a",
  "Version": "01_create_tables",
  "Status": "Complete"
}

```

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

### Example: Database

Source: https://pgroll.com/docs/latest/cli/latest-schema

Prints the latest schema version in the target database.

```bash
$ pgroll latest schema

```

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

### Example migrations directory structure

Source: https://pgroll.com/docs/latest/cli/pull

This shows the typical structure of migration files after running the pull command.

```bash
$ ls migrations/

01_create_tables.yaml
02_create_another_table.yaml
03_add_column_to_products.yaml
04_rename_table.yaml
05_sql.yaml
06_add_column_to_sql_table.yaml
...

```

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

### Add a PRIMARY KEY constraint

Source: https://pgroll.com/docs/latest/operations/create_constraint

Example of adding a primary key constraint to the 'tasks' table, specifying the 'id' column. This example also includes the 'create_table' operation for context.

```yaml
operations:
  - create_table:
      name: tasks
      columns:
        - name: id
          type: serial
        - name: title
          type: varchar(255)
          nullable: false
        - name: description
          type: varchar(255)
        - name: deadline
          type: time with time zone
  - create_constraint:
      name: tasks_pkey
      table: tasks
      columns: [id]
      type: primary_key
      up:
        id: id
      down:
        id: id
```

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

### Create a baseline with JSON format

Source: https://pgroll.com/docs/latest/cli/baseline

Example of creating a baseline migration with the JSON format.

```bash
pgroll baseline 01_initial_schema ./migrations --json
```

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

### Drizzle SQL migration conversion

Source: https://pgroll.com/docs/latest/guides/orms

Example of generating SQL with drizzle-kit and then converting it using pgroll convert.

```bash
drizzle-kit generate --dialect postgresql --schema=./src/schema.ts --name=init
pgroll convert 0000_init.sql
{
  "operations": [
      "create_table": {
        "name": "employees",
        "colunms:" [
          {
            "name": "name",
            "type": "varchar(100)"
          },
          {
            "name": "joined",
            "type": "timestamp with time zone"
          },
          {
            "name": "email",
            "type": "varchar(254)"
          }
        ]
      }
    ]
  }
}

```

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

### Create a table with a raw SQL migration

Source: https://pgroll.com/docs/latest/operations/raw_sql

An example of a raw SQL migration used to create a 'users' table.

```yaml
operations:
  - sql:
      up: CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)
      down: DROP TABLE users
```

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

### Example of creating a table with a unique constraint

Source: https://pgroll.com/docs/latest/guides/orms

This shows how an ORM might generate separate statements for table creation and unique constraint, and how to consolidate them into a single 'create_table' operation with a 'constraints' list.

```json
{
  "create_table": {
    "name": "employees",
    "columns": [
      {
        "name": "email",
        "varchar(254)"
      }
    ]
  }
},
{
  "create_constraint": {
    "name": "my_unique_email",
    "type": "unique",
    "columns": ["email"]
    "up": {
      "email": "TODO"
    }
  }
}
```

```json
{
  "create_table": {
    "name": "employees",
    "columns": [
      {
        "name": "email",
        "varchar(254)"
      }
    ],
    "constraints": [
      {
        "name": "my_unique_email",
        "type": "unique",
        "columns": ["email"]
      }
    ]
  }
}
```

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

### Create a baseline with default YAML format

Source: https://pgroll.com/docs/latest/cli/baseline

Example of creating a baseline migration with the default YAML format.

```bash
pgroll baseline 01_initial_schema ./migrations
```

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

### Run a SQL migration on migration complete

Source: https://pgroll.com/docs/latest/operations/raw_sql

An example demonstrating a raw SQL migration that runs its 'up' expression upon migration completion.

```yaml
operations:
  - sql:
      up: ALTER TABLE people ADD COLUMN birth_date timestamp
      onComplete: true
```

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

### Change column type

Source: https://pgroll.com/docs/latest/operations/alter_column/change_type

Example of changing the type of the 'rating' column on the 'reviews' table.

```yaml
operations:
  - alter_column:
      table: reviews
      column: rating
      type: integer
      up: CAST(rating AS integer)
      down: CAST(rating AS text)
```

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

### Drizzle Employee Schema

Source: https://pgroll.com/docs/latest/guides/orms

An example Drizzle schema definition for employees.

```typescript
import { pgTable, timestamp, varchar } from 'drizzle-orm/pg-core';

export const employees = pgTable('employees', {
  name: varchar({ length: 100 }),
  joined: timestamp({ withTimezone: true }),
  email: varchar({ length: 254 })
});

```

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

### Create a baseline without confirmation prompt

Source: https://pgroll.com/docs/latest/cli/baseline

Example of creating a baseline migration without a confirmation prompt.

```bash
pgroll baseline 01_initial_schema ./migrations --yes
```

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

### Django SQL migration conversion

Source: https://pgroll.com/docs/latest/guides/orms

Example of piping Django's sqlmigrate output to pgroll convert to generate a pgroll migration.

```bash
manage.py sqlmigrate my_app 0000 | pgroll convert
{
  "operations": [
    {
      "create_table": {
        "name": "employees",
        "colunms:" [
          {
            "name": "name",
            "type": "varchar(100)"
          },
          {
            "name": "joined",
            "type": "timestamp with time zone"
          },
          {
            "name": "email",
            "type": "varchar(254)"
          }
        ]
      }
    }
  ]
}

```

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

### Alembic SQL migration conversion

Source: https://pgroll.com/docs/latest/guides/orms

Example of piping Alembic's SQL output to pgroll convert to generate a pgroll migration.

```bash
$ alembic update {revision} --sql | pgroll convert
{
  "operations": [
    {
      "create_table": {
        "name": "employees",
        "colunms:" [
          {
            "name": "name",
            "type": "varchar(100)"
          },
          {
            "name": "joined",
            "type": "timestamp with time zone"
          },
          {
            "name": "email",
            "type": "varchar(254)"
          }
        ]
      }
    }
  ]
}

```

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

### Create Table Operation (Default Version Schema Name)

Source: https://pgroll.com/docs/latest/operations

Example of a create_table operation where the version schema name defaults to the migration filename.

```yaml
operations:
  - create_table:
      name: items
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
```

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

### Add a UNIQUE constraint

Source: https://pgroll.com/docs/latest/operations/create_constraint

Example of adding a multi-column unique constraint on the 'tickets' table, specifying columns 'sellers_name' and 'sellers_zip'.

```yaml
operations:
  - create_constraint:
      type: unique
      table: tickets
      name: unique_zip_name
      columns:
        - sellers_name
        - sellers_zip
      up:
        sellers_name: sellers_name
        sellers_zip: sellers_zip
      down:
        sellers_name: sellers_name
        sellers_zip: sellers_zip
```

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

### Add a FOREIGN KEY constraint

Source: https://pgroll.com/docs/latest/operations/create_constraint

Example of adding a multi-column foreign key constraint to the 'tickets' table, referencing the 'sellers' table. The 'up' SQL expressions do not perform data transformation in this case.

```yaml
operations:
  - create_constraint:
      type: foreign_key
      table: tickets
      name: fk_sellers
      columns:
        - sellers_name
        - sellers_zip
      references:
        table: sellers
        columns:
          - name
          - zip
      up:
        sellers_name: sellers_name
        sellers_zip: sellers_zip
      down:
        sellers_name: sellers_name
        sellers_zip: sellers_zip
```

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

### Create Table Operation (Overridden Version Schema Name)

Source: https://pgroll.com/docs/latest/operations

Example of a create_table operation where the version schema name is explicitly set using the 'version_schema' field.

```yaml
version_schema: my_version_schema
operations:
  - create_table:
      name: items
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
```

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

### Drop multi-column constraint structure with multiple columns

Source: https://pgroll.com/docs/latest/operations/drop_multi_column_constraint

Example structure for dropping a multi-column constraint covering multiple columns, specifying up and down SQL expressions for each.

```yaml
up:
  a: up SQL expression for column a
  b: up SQL expression for column b
down:
  a: down SQL expression for column a
  b: down SQL expression for column b
```

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

### Add a CHECK constraint

Source: https://pgroll.com/docs/latest/operations/create_constraint

Example of adding a check constraint on 'sellers_name' and 'sellers_zip' fields of the 'ticket' table. The 'up' SQL expression includes data migration logic to ensure compliance with the new constraint.

```yaml
operations:
  - create_constraint:
      type: check
      table: tickets
      name: check_zip_name
      columns:
        - sellers_name
        - sellers_zip
      check: sellers_name = 'alice' OR sellers_zip > 0
      up:
        sellers_name: sellers_name
        sellers_zip: SELECT CASE WHEN sellers_name != 'alice' AND sellers_zip <= 0 THEN 123 WHEN sellers_name != 'alice' THEN sellers_zip ELSE sellers_zip END
      down:
        sellers_name: sellers_name
        sellers_zip: sellers_zip
```

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

### Database Schemas

Source: https://pgroll.com/docs/latest/tutorial

Output showing the schemas present in the database after the migration step.

```text
Copy Code
You should see something like this:
```
+-----------------------------------------+-------------------+
| Name                                    | Owner             |
+-----------------------------------------+-------------------+-------------------+
| pgroll                                  | postgres          |
| public                                  | pg_database_owner |
| public_01_create_users_table            | postgres          |
| public_02_user_description_set_nullable | postgres          |
+-----------------------------------------+-------------------+

```
```

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

### List Schemas

Source: https://pgroll.com/docs/latest/tutorial

Command to list all schemas in the database.

```bash
Copy Code
\dn

```

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

### Create an index with storage parameters

Source: https://pgroll.com/docs/latest/operations/create_index

Set storage parameters and index method.

```yaml
operations:
  - create_index:
      name: idx_fruits_name
      table: fruits
      columns:
        - column: "name"
      method: hash
      storage_parameters: fillfactor = 70
```

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

### Rename a CHECK constraint

Source: https://pgroll.com/docs/latest/operations/rename_constraint

Example of renaming a CHECK constraint.

```yaml
operations:
  - rename_constraint:
      table: people
      from: name_length
      to: name_length_check
```

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

### Apply the first migration

Source: https://pgroll.com/docs/latest/tutorial

This command applies the 'create_users_table' migration to the database. The --complete flag applies and finalizes the migration in one step.

```bash
pgroll start sql/01_create_users_table.yaml --complete

```

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

### Describe New Schema View

Source: https://pgroll.com/docs/latest/tutorial

Command to describe the users view in the new schema.

```sql
Copy Code
\d+ public_02_user_description_set_nullable.users

```

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

### Initialize pgroll

Source: https://pgroll.com/docs/latest/tutorial

This command initializes pgroll in the target Postgres database, configuring its internal state store.

```bash
pgroll init

```

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

### List schemas command

Source: https://pgroll.com/docs/latest/tutorial

Command to list the available schemas, showing the newly created schema for the migration.

```bash
\dn
```

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

### Complete the migration

Source: https://pgroll.com/docs/latest/tutorial

Command to complete the current pgroll migration.

```bash
pgroll complete

```

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

### Describe Old Schema View

Source: https://pgroll.com/docs/latest/tutorial

Command to describe the users view in the old schema.

```sql
Copy Code
\d+ public_01_create_users_table.users

```

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

### List schemas

Source: https://pgroll.com/docs/latest/tutorial

Command to list the current schemas in the database after migration completion.

```sql
\dn

```

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

### Create a `btree` index

Source: https://pgroll.com/docs/latest/operations/create_index

Create a `btree` index on the `name` column in the `fruits` table.

```yaml
operations:
  - create_index:
      name: idx_fruits_name
      table: fruits
      columns:
        - column: "name"
```

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

### Create a table with column defaults

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with different DEFAULT values.

```yaml
operations:
  - create_table:
      name: items
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
          default: '''unnamed'''
        - name: price
          type: decimal(10,2)
          default: "0.00"
        - name: created_at
          type: timestamptz
          default: now()
```

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

### Drop a CHECK constraint

Source: https://pgroll.com/docs/latest/operations/drop_multi_column_constraint

Example of dropping a CHECK constraint named 'check_zip_name' from the 'tickets' table.

```yaml
operations:
  - drop_multicolumn_constraint:
      table: tickets
      name: check_zip_name
      up:
        sellers_name: sellers_name
        sellers_zip: sellers_zip
      down:
        sellers_name: sellers_name
        sellers_zip: sellers_zip
```

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

### Remove `NOT NULL` from a column

Source: https://pgroll.com/docs/latest/operations/alter_column/drop_not_null_constraint

Example of removing a `NOT NULL` constraint from the `title` column in the `posts` table.

```yaml
operations:
  - alter_column:
      table: posts
      column: title
      nullable: true
      up: title
      down: SELECT CASE WHEN title IS NULL THEN 'placeholder title' ELSE title END
```

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

### Create a table with nullable and non-nullable columns

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with a mix of nullable and non-nullable columns.

```yaml
operations:
  - create_table:
      name: reviews
      columns:
        - name: id
          type: serial
          pk: true
        - name: username
          type: text
        - name: product
          type: text
        - name: review
          type: text
          nullable: true
```

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

### Remove a comment from a column

Source: https://pgroll.com/docs/latest/operations/alter_column/change_comment

Example demonstrating how to remove a comment from a column by setting the 'comment' field to null.

```yaml
operations:
  - alter_column:
      table: events
      column: name
      comment: null
      up: name
      down: name
```

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

### Generate empty JSON migrations

Source: https://pgroll.com/docs/latest/cli/create

Generates empty JSON migration files.

```bash
pgroll create --name my_migration --empty --json

```

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

### Create users table migration (YAML)

Source: https://pgroll.com/docs/latest/tutorial

This YAML defines a migration to create a 'users' table with specified columns and constraints.

```yaml
operations:
  - create_table:
      name: users
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
          unique: true
        - name: description
          type: text
          nullable: true

```

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

### Rollback Scenario

Source: https://pgroll.com/docs/latest/tutorial

Illustrates a scenario where a rollback is necessary due to an unremoved original schema version.

```text
Since the original schema version, `02_user_description_set_nullable`, was never removed, existing client applications remain unaware of the migration and subsequent rollback.
```

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

### Generate empty YAML migrations

Source: https://pgroll.com/docs/latest/cli/create

Generates empty YAML migration files.

```bash
pgroll create --name my_migration --empty

```

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

### Create migration files interactively

Source: https://pgroll.com/docs/latest/cli/create

This command helps you generate `pgroll` migrations interactively, and saves the file to disk in YAML format.

```bash
$ pgroll create

```

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

### Create an index with custom operator class

Source: https://pgroll.com/docs/latest/operations/create_index

Create an index with a custom operator class.

```yaml
operations:
  - create_index:
      name: idx_fruits_custom_opclass
      table: fruits
      columns:
        - column: "id"
          opclass:
            name: int8_ops
```

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

### Query new schema users table

Source: https://pgroll.com/docs/latest/tutorial

SQL query to show the 'users' table in the new schema, exposing the new column with its temporary name.

```sql
 SELECT users.id,
    users.name,
    users.description,
    users._pgroll_new_is_atcive AS is_atcive
   FROM users;
```

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

### Describe users table schema

Source: https://pgroll.com/docs/latest/tutorial

SQL command to display the schema of the users table, showing column details and modifiers.

```sql
DESCRIBE users

```

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

### View users table data

Source: https://pgroll.com/docs/latest/tutorial

SQL query to select and display the first 10 rows of the users table, including the new and temporary columns added by pgroll.

```sql
SELECT * FROM users ORDER BY id LIMIT 10

```

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

### Create a partial index

Source: https://pgroll.com/docs/latest/operations/create_index

Create a partial index on the `id` column in the `fruits` table.

```yaml
operations:
  - create_index:
      name: idx_fruits_id_gt_10
      table: fruits
      columns:
        - column: "id"
      predicate: id > 10
```

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

### Rollback command

Source: https://pgroll.com/docs/latest/tutorial

Command to initiate the rollback of the last pgroll migration.

```bash
pgroll rollback
```

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

### pgroll init command

Source: https://pgroll.com/docs/latest/cli/init

Initializes pgroll for first use.

```bash
$ pgroll init

```

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

### Alter many column properties including comment

Source: https://pgroll.com/docs/latest/operations/alter_column/change_comment

An example of an alter column migration that includes setting a comment along with other properties like type, default, nullable, unique, and check constraints.

```yaml
operations:
  - alter_column:
      table: events
      column: name
      type: text
      default: '''unknown event'''
      nullable: false
      comment: the full name of the event
      unique:
        name: events_event_name_unique
      check:
        name: event_name_length
        constraint: length(name) > 3
      up: SELECT CASE WHEN name IS NULL OR LENGTH(name) <= 3 THEN 'placeholder' ELSE name END
      down: event_name
```

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

### Users table schema with new column

Source: https://pgroll.com/docs/latest/tutorial

Shows the structure of the 'users' table in the new schema, including the temporary new column '_pgroll_new_is_atcive'.

```text
+-----------------------+------------------------+-----------------------------------------------------------------+----------+--------------+-------------+
| Column                | Type                   | Modifiers                                                       | Storage  | Stats target | Description |
|-----------------------+------------------------+-----------------------------------------------------------------+----------+--------------+-------------|
| id                    | integer                |  not null default nextval('_pgroll_new_users_id_seq'::regclass) | plain    | <null>       | <null>      |
| name                  | character varying(255) |  not null                                                       | extended | <null>       | <null>      |
| description           | text                   |  not null                                                       | extended | <null>       | <null>      |
| _pgroll_new_is_atcive | boolean                |  default true                                                   | plain    | <null>       | <null>      |
+-----------------------+------------------------+-----------------------------------------------------------------+----------+--------------+-------------+
```

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

### Set Search Path

Source: https://pgroll.com/docs/latest/tutorial

Command to set the search path to the old schema.

```sql
Copy Code
SET search_path = 'public_01_create_users_table'

```

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

### Create a table with a comment

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with a comment on the table and a comment on a column.

```yaml
operations:
  - create_table:
      name: employees
      comment: This is a comment for the employees table
      columns:
        - name: id
          type: serial
          pk: true
        - name: role
          type: varchar(255)
          comment: This is a comment for the role column
```

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

### pgroll convert --help

Source: https://pgroll.com/docs/latest/guides/orms

Help output for the pgroll convert subcommand, showing its usage and flags.

```bash
$ pgroll convert --help
Convert SQL statements to a pgroll migration. The command can read SQL statements from stdin or a file

Usage:
  pgroll convert <path to file with migrations> [flags]
Flags:
  -h, --help          help for convert

```

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

### Create a unique index

Source: https://pgroll.com/docs/latest/operations/create_index

Create a unique index.

```yaml
operations:
  - create_index:
      name: idx_fruits_unique_name
      table: fruits
      columns:
        - column: "name"
      unique: true
```

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

### Create a table with a foreign key

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with a foreign key constraint defined on a column.

```yaml
operations:
  - create_table:
      name: orders
      columns:
        - name: id
          type: serial
          pk: true
        - name: user_id
          type: integer
          references:
            name: fk_users_id
            table: users
            column: id
        - name: quantity
          type: int
```

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

### Schemas after rollback

Source: https://pgroll.com/docs/latest/tutorial

Output showing the schemas after the rollback, indicating the removal of the migration-specific schema.

```text
+-----------------------------------------+-------------------+
| Name                                    | Owner             |
|-----------------------------------------+-------------------| 
| pgroll                                  | postgres          |
| public                                  | pg_database_owner |
| public_02_user_description_set_nullable | postgres          |
+-----------------------------------------+-------------------+
```

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

### Expected output after insertion

Source: https://pgroll.com/docs/latest/tutorial

This shows the expected output after inserting Alice and Bob into the users table.

```text
+--------+-------+---------------------+
| id     | name  | description         |
+--------+-------+---------------------+
| 100001 | Alice | this is Alice       |
| 100002 | Bob   | NULL                |
+--------+-------+---------------------+

```

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

### Create a table with table level foreign key constraints

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with table level foreign key constraints, including ON DELETE and ON UPDATE actions.

```yaml
operations:
  - create_table:
      name: phonebook
      columns:
        - name: id
          type: serial
        - name: provider_id
          type: serial
        - name: name
          type: varchar(255)
        - name: city
          type: varchar(255)
        - name: phone
          type: varchar(255)
      constraints:
        - name: phonebook_pk
          type: primary_key
          columns:
            - id
        - name: provider_fk
          type: foreign_key
          columns:
            - provider_id
          deferrable: false
          references:
            table: telephone_providers
            columns:
              - id
          on_delete: CASCADE
          on_update: CASCADE
          match_type: SIMPLE
        - name: unique_numbers
          type: unique
          columns:
            - phone
          index_parameters:
            include_columns:
              - name
        - name: name_must_be_present
          type: check
          check: length(name) > 0
```

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

### Create a table with multiple table level constraints

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with table level constraints including primary key, unique, and check constraints.

```yaml
operations:
  - create_table:
      name: telephone_providers
      columns:
        - name: id
          type: serial
        - name: name
          type: varchar(255)
        - name: tax_id
          type: varchar(255)
        - name: headquarters
          type: varchar(255)
      constraints:
        - name: provider_pk
          type: primary_key
          columns:
            - id
        - name: unique_tax_id
          type: unique
          columns:
            - tax_id
        - name: name_must_be_present
          type: check
          check: length(name) > 0
```

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

### Query old schema users table

Source: https://pgroll.com/docs/latest/tutorial

SQL query to show the 'users' table in the old schema, which does not include the new column.

```sql
 SELECT users.id,
    users.name,
    users.description
   FROM users;
```

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

### Add multiple columns

Source: https://pgroll.com/docs/latest/operations/add_column

Add three new columns to the `products` table. Each column addition is a separate operation:

```yaml
operations:
  - add_column:
      table: products
      up: UPPER(name)
      column:
        name: description
        type: varchar(255)
        nullable: true
  - add_column:
      table: products
      column:
        name: stock
        type: int
        nullable: false
        default: "100"
  - add_column:
      table: products
      up: name || '-category'
      column:
        name: category
        type: varchar(255)
        nullable: false
```

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

### Users table schema after rollback

Source: https://pgroll.com/docs/latest/tutorial

Shows the structure of the 'users' table after rollback, confirming the removal of the temporary new column.

```text
+-------------+------------------------+-----------------------------------------------------------------+----------+--------------+-------------+
| Column      | Type                   | Modifiers                                                       | Storage  | Stats target | Description |
|-------------+------------------------+-----------------------------------------------------------------+----------+--------------+-------------|
| id          | integer                |  not null default nextval('_pgroll_new_users_id_seq'::regclass) | plain    | <null>       | <null>      |
| name        | character varying(255) |  not null                                                       | extended | <null>       | <null>      |
| description | text                   |  not null                                                       | extended | <null>       | <null>      |
+-------------+------------------------+-----------------------------------------------------------------+----------+--------------+-------------+
```

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

### Create users table migration (SQL DDL)

Source: https://pgroll.com/docs/latest/tutorial

This SQL DDL statement is equivalent to the YAML migration definition for creating the 'users' table.

```sql
CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) UNIQUE NOT NULL,
  description TEXT
)

```

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

### Insert two new users

Source: https://pgroll.com/docs/latest/tutorial

This SQL statement inserts two new users into the 'users' table, one with a description and one without.

```sql
SELECT * FROM users WHERE name = 'Alice' or name = 'Bob'
```

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

### Create a table with exclusion constraint

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with an exclusion constraint.

```yaml
operations:
  - create_table:
      name: library
      columns:
        - name: id
          type: serial
        - name: returned
          type: timestamp
        - name: title
          type: text
        - name: summary
          type: text
      constraints:
        - name: rooms_pk
          type: primary_key
          columns:
            - id
        - name: forbid_duplicated_titles
          type: exclude
          exclude:
            index_method: btree
            elements: title WITH =
            predicate: title IS NOT NULL
```

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

### Insert users into the table

Source: https://pgroll.com/docs/latest/tutorial

This SQL statement inserts 100,000 users into the 'users' table, with roughly half having descriptions.

```sql
INSERT INTO users (name, description)
 SELECT
   'user_' || suffix,
   CASE
     WHEN random() < 0.5 THEN 'description for user_' || suffix
     ELSE NULL
   END
 FROM generate_series(1, 100000) AS suffix;

```

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

### Raw SQL Operation with onComplete Flag

Source: https://pgroll.com/docs/latest/operations/raw_sql

Shows how to use the 'onComplete' flag to run a raw SQL 'up' expression during the complete phase of a migration, noting its incompatibility with the 'down' expression.

```yaml
sql:
  up: SQL expression
  onComplete: true
```

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

### Insert Data

Source: https://pgroll.com/docs/latest/tutorial

Inserting data into the users table through the old schema view.

```sql
Copy Code
INSERT INTO users(name, description) VALUES ('Alice', 'this is Alice'), ('Bob', NULL)

```

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

### Create a table with a CHECK constraint

Source: https://pgroll.com/docs/latest/operations/create_table

Create a table with a CHECK constraint on one column.

```yaml
operations:
  - create_table:
      name: people
      columns:
        - name: id
          type: integer
          pk: true
        - name: name
          type: varchar(255)
          check:
            name: name_length
            constraint: length(name) > 3
```

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

### Users Table Schema

Source: https://pgroll.com/docs/latest/tutorial

Schema definition for the users table, including new columns and constraints.

```sql
Copy Code
The `_pgroll_new_description` column has a `NOT NULL` `CHECK` constraint, but the old `description` column is still nullable.
Why is the `NOT NULL` constraint on the new `_pgroll_new_description` column `NOT VALID`?
Defining the constraint as `NOT VALID` means that the `users` table will not be scanned to enforce the `NOT NULL` constraint for existing rows. This means the constraint can be added quickly without locking rows in the table. `pgroll` assumes that the `up` SQL provided by the user will ensure that no `NULL` values are written to the `_pgroll_new_description` column.
We'll talk about what the two triggers on the table do later.
For now, let's look at the schemas in the database:

```

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

### Set search path to the new schema version

Source: https://pgroll.com/docs/latest/tutorial

This command sets the search path to the new version of the schema, 'public_02_user_description_set_nullable'.

```sql
SET search_path = 'public_02_user_description_set_nullable'
```

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

### Expected output after schema update

Source: https://pgroll.com/docs/latest/tutorial

This shows the expected output for Alice and Bob after the trigger has rewritten the NULL value using the migration's 'up' SQL.

```text
+--------+-------+---------------------+
| id     | name  | description         |
+--------+-------+---------------------+
| 100001 | Alice | this is Alice       |
| 100002 | Bob   | description for Bob |
+--------+-------+---------------------+

```

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

### Abort on Multiple Unapplied Migrations

Source: https://pgroll.com/docs/latest/cli/migrate

Ensures that only a single migration is applied by failing if more than one unapplied migration is detected.

```bash
$ pgroll migrate examples/ --expect-one

```

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

### Add column with `up` SQL

Source: https://pgroll.com/docs/latest/operations/add_column

Add a new column to the `users` table and define `up` SQL to ensure that the new column is populated with a value when a row is added to the old version of the table:

```yaml
operations:
  - add_column:
      table: users
      up: UPPER(name)
      column:
        name: description
        type: varchar(255)
        nullable: true
```

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

### Convert SQL statements from stdin

Source: https://pgroll.com/docs/latest/cli/convert

Reads SQL statements from stdin and translates them into a pgroll migration, writing the output to stdout in YAML format.

```bash
$ cat 'CREATE TABLE my_table(name text);' | pgroll convert

```

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

### Add is_active column migration (YAML)

Source: https://pgroll.com/docs/latest/tutorial

This YAML defines a migration to add a new boolean column 'is_atcive' (with a typo) to the 'users' table.

```yaml
operations:
  - add_column:
      table: users
      column:
        name: is_atcive
        type: boolean
        nullable: true
        default: "true"
```

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

### Structure

Source: https://pgroll.com/docs/latest/operations/create_table

The structure for a create table operation in YAML or JSON format.

```yaml
create_table:
  name: name of new table
  columns: [...] 
  constraints: [...]
```

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

### Structure

Source: https://pgroll.com/docs/latest/operations/create_index

The structure of a create index operation in YAML/JSON format.

```yaml
create_index:
  table: name of table on which to define the index
  name: index name
  columns:
    column_name:
      collate: collation name
      sort: ASC | DESC
      nulls: FIRST | LAST
      opclass:
        name: operator_class_name
        params:
          - param1=val
          - param2=val
  predicate: conditional expression for defining a partial index
  storage_parameters: comma-separated list of storage parameters
  unique: true | false
  method: btree
```

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

### Column Definition

Source: https://pgroll.com/docs/latest/operations/create_table

The structure for defining a column within a create table operation.

```yaml
- name: column name
  type: postgres type
  comment: postgres comment for the column
  nullable: true|false
  unique: true|false
  pk: true|false
  default: default value
  check:
    name: name of check constraint
    constraint: constraint expression
    no_inherit: true|false
  generated:
    expression: expression for stored column
    identity:
      user_specified_values: user specified values can be used, can be ALWAYS and BY DEFAULT. Default is ALWAYS
      sequence_options: sequence options for identity columns
  references:
    name: name of foreign key constraint
    table: name of referenced table
    column: name of referenced column
    on_delete: ON DELETE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION
    on_update: ON UPDATE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION
    match_type: match type, can be SIMPLE or FULL. Default is SIMPLE
```

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

### pgroll migration for non-nullable description

Source: https://pgroll.com/docs/latest/tutorial

This YAML configuration defines a pgroll migration to alter the 'description' column in the 'users' table, making it non-nullable and providing SQL to handle existing NULL values.

```yaml
operations:
  - alter_column:
      table: users
      column: description
      nullable: false
      up: "SELECT CASE WHEN description IS NULL THEN 'description for ' || name ELSE description END"
      down: description
```

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

### Convert SQL migration file

Source: https://pgroll.com/docs/latest/cli/convert

Reads the SQL statements from a specified file and translates them into a pgroll migration, writing the output to stdout in YAML format.

```bash
$ pgroll convert /path/to/migration.sql

```

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

### New Schema View Definition

Source: https://pgroll.com/docs/latest/tutorial

The SQL query for the users view in the new schema, mapping the new column.

```sql
Copy Code
The output should contain something like this:
```
 SELECT users.id,
    users.name,
    users._pgroll_new_description AS description
   FROM users;
```
```

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

### Employee table schema

Source: https://pgroll.com/docs/latest/guides/updown

The schema of the employee table before migrations.

```sql
postgres=# \d employee
                                 Table "public.employee"
 Column |       Type       | Collation | Nullable |               Default
--------+------------------+-----------+----------+--------------------------------------
 id     | integer          |           | not null | nextval('employee_id_seq'::regclass)
 name   | text             |           | not null |
 nick   | text             |           | not null |
 email  | text             |           |          |
 salary | double precision |           | not null |
 bio    | text             |           |          |
Indexes:
    "employee_pkey" PRIMARY KEY, btree (id)
```

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

### Use the global pgroll connection string as the base

Source: https://pgroll.com/docs/latest/cli/latest-url

With no arguments, the command uses the global `--postgres-url` flag (or the `PGROLL_PG_URL` environment variable) as the connection string on which to set the `search_path`.

```bash
$ export PGROLL_PG_URL=postgresql://user:pass@example.com:5432/mydb
$ pgroll latest url
```

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

### Old Schema View Definition

Source: https://pgroll.com/docs/latest/tutorial

The SQL query for the users view in the old schema.

```sql
Copy Code
The output should contain something like this:
```
 SELECT users.id,
    users.name,
    users.description
   FROM users;
```
```

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

### Add check constraint for capitalized name

Source: https://pgroll.com/docs/latest/guides/updown

YAML configuration to add a check constraint ensuring names are capitalized.

```yaml
operations:
 - create_constraint:
    name: capitalized_name
    table: employee
    type: check
    columns:
     - name
    check: name = INITCAP(name)
    up:
     name: name
    down:
     name: name
```

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

### Add a check constraint for bio length with conditional logic

Source: https://pgroll.com/docs/latest/guides/updown

This snippet demonstrates adding a 'check' constraint to the 'bio' column. It uses a CASE statement to handle null bios, short bios (appending random characters), and long bios (truncating and adding '...').

```yaml
operations:
 - create_constraint:
    name: limited_required_bio
    table: employee
    type: check
    columns:
     - bio
    check: length(bio) > 15 AND length(bio) < 100
    up:
     bio: >
       CASE
         WHEN bio IS NULL THEN 'this employee did not provide a bio'
         WHEN length(bio) <= 15 THEN bio || SELECT random_bio(16-length(bio))
         WHEN length(bio) >= 100 THEN left(bio, 96) || '...'
         ELSE bio
       END
    down:
     bio: bio
```

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

### Structure

Source: https://pgroll.com/docs/latest/operations/alter_column/change_default

The structure for a change default operation in YAML format.

```yaml
alter_column:
  table: table name
  column: column name
  default: new default value | null
  up: SQL expression
  down: SQL expression
```

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

### Baseline command

Source: https://pgroll.com/docs/latest/cli/baseline

The command to create a baseline migration for an existing database schema.

```bash
pgroll baseline <version> <target directory>
```

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

### Constraint Definition

Source: https://pgroll.com/docs/latest/operations/create_table

The structure for defining a constraint within a create table operation.

```yaml
- name: constraint name
  type: constraint type
  columns: [list, of, columns]
  check: condition of CHECK constraint
  nulls_not_distinct: true|false
  deferrable: true|false
  initially_deferred: true|false
  no_inherit: true|false
  references:
    name: name of foreign key constraint
    table: name of referenced table
    columns: [list, of, referenced, columns]
    on_delete: ON DELETE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION
    on_delete_set_columns: [list of FKs to set, in on delete operation on SET NULL or SET DEFAULT]
    on_update: ON UPDATE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION
    match_type: match type, can be SIMPLE, FULL or PARTIAL. Default is SIMPLE
  index_parameters:
    tablespace: index_tablespace
    storage_parameters: parameter=value
    include_columns: [list, of, columns, included in index]
  exclude:
    index_method: name of the index method, e.g. btree
    elements: exclude elements
    predicate: WHERE clause of the exclude constraint
```

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

### Add column without `up` SQL

Source: https://pgroll.com/docs/latest/operations/add_column

Add a new column to the `reviews` table. There is no need for `up` SQL because the column has a default:

```yaml
operations:
  - add_column:
      table: reviews
      column:
        name: rating
        type: text
        default: "0"
```
Tokens:
Raw