pgroll

https://github.com/xataio/pgroll
Admin
pgroll is an open-source command-line tool that enables safe, reversible, and zero-downtime schema...
Tokens:23,183
Snippets:284
Trust Score:9.1
License:Apache-2.0
Update:2 months ago
Show doc for...
Tokens:
Raw
### Start a migration

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/start.mdx

Initiates a migration defined in the specified YAML file.

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

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

### Install pgroll from source

Source: https://github.com/xataio/pgroll/blob/main/docs/install-pgroll.md

Requires Go 1.24 or later to be installed on the system.

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

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

### Start Postgres in Docker

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Use this command to spin up a temporary Postgres instance for testing migrations.

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

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

### View Definition Output

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Example SQL definitions for migration views.

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

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

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

### Start a migration

Source: https://github.com/xataio/pgroll/blob/main/README.md

Applies the migration operations and creates a new schema version.

```sh
pgroll --postgres-url postgres://user:password@host:port/dbname start initial_migration.json
```

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

### Start and complete a migration

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/start.mdx

Starts and immediately completes a migration. Use only when no applications are running against the old schema.

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

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

### Start pgroll Migration

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Executes the migration defined in the specified YAML file.

```bash
pgroll start 02_user_description_set_nullable.yaml
```

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

### Install pgroll via Homebrew

Source: https://github.com/xataio/pgroll/blob/main/docs/install-pgroll.md

Use this method on macOS or Linux systems with Homebrew installed.

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

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

### Create a table migration

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/README.md

Example of a migration that creates an 'items' table, resulting in a version schema named after the filename.

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

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

### Run pgroll complete command

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/complete.mdx

Executes the completion of the most recently started migration.

```bash
$ pgroll complete
```

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

### ORM generated SQL for column addition

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/orms.mdx

Example of verbose SQL statements produced by an ORM when adding a new column with a default value.

```sql
ALTER TABLE "adults" ADD COLUMN "age" smallint DEFAULT 18 NULL CHECK ("age" >= 18);
ALTER TABLE "adults" ALTER COLUMN "level" DROP DEFAULT;

UPDATE adults SET age = 18;

ALTER TABLE adults ALTER COLUMN age SET NOT NULL;

UPDATE "adults" SET "age" = 18 WHERE "age" IS NULL;
SET CONSTRAINTS ALL IMMEDIATE;

ALTER TABLE "adults" ALTER COLUMN "age" SET NOT NULL;
```

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

### Run the create command

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/create.mdx

Initiate the interactive migration creation process.

```bash
$ pgroll create
```

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

### Run baseline command

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/baseline.mdx

The base command requires a version name and a target directory for the migration file.

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

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

### Create baseline with JSON format

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/baseline.mdx

Uses the --json flag to output the placeholder migration file in JSON format.

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

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

### Insert Sample Data

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Populates the users table with 100,000 rows for testing.

```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;
```

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

### Run migrations

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/migrate.mdx

Applies all unapplied migrations from the specified directory to the target database.

```bash
$ pgroll migrate examples/
```

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

### set_replica_identity

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/set_replica_identity.mdx

The set_replica_identity operation updates the replica identity configuration for a specified table. It is applied directly to the underlying table upon migration start.

```APIDOC
## set_replica_identity

### Description
Sets the replica identity for a table. This operation is deprecated and will be removed in a future release; use raw SQL instead.

### Structure
```json
{
  "set_replica_identity": {
    "table": "name of the table",
    "identity": {
      "type": "full | default | nothing | index",
      "index": "name of the index, if type is 'index'"
    }
  }
}
```
```

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

### Command Usage

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-migration.mdx

Basic syntax for the latest migration command.

```bash
pgroll latest migration [flags]
```

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

### Create baseline with default YAML format

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/baseline.mdx

Generates a baseline migration file in the specified directory using the default YAML format.

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

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

### Initialize pgroll CLI

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/init.mdx

Run this command to create the required internal schema in your database.

```bash
$ pgroll init
```

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

### Apply Migration

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Executes the migration file and completes the process in one step.

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

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

### Configure Raw SQL with onComplete Flag

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/raw_sql.mdx

Configuration for running SQL operations during the migration completion phase, which is incompatible with down expressions.

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

```json
{
  "sql": {
    "up": "SQL expression",
    "onComplete": true
  }
}
```

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

### Create one table

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Basic syntax for creating a single table.

```yaml
<ExampleSnippet example="02_create_another_table.yaml" languange="yaml" />
```

```yaml
<ExampleSnippet example="20_create_posts_table.yaml" languange="yaml" />
```

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

### List database schemas

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Displays the current list of schemas and their owners.

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

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

### Set search_path in Go

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/clientapps.mdx

Configures the search_path after establishing a database connection using the database/sql package.

```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)
}
```

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

### Define a migration file

Source: https://github.com/xataio/pgroll/blob/main/README.md

JSON configuration for defining schema operations like creating a new table.

```json
{
  "name": "initial_migration",
  "operations": [
    {
      "create_table": {
        "name": "customers",
        "columns": [
          {
            "name": "id",
            "type": "integer",
            "pk": true
          },
          {
            "name": "name",
            "type": "varchar(255)",
            "unique": true
          },
          {
            "name": "bio",
            "type": "text",
            "nullable": true
          }
        ]
      }
    }
  ]
}
```

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

### Initialize pgroll

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Configures the internal state storage within the target Postgres database.

```bash
pgroll init
```

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

### Use Arbitrary Connection String

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-url.mdx

Generates a connection URL using a provided connection string as the base, while still referencing the target database for the latest schema version.

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

```text
postgresql://someotheruser:someotherpass@example.com:5432/mydb?options=-c%20search_path%3Dpublic_00n_some_migration
```

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

### Equivalent SQL DDL

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

The standard SQL representation of the table creation migration.

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

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

### Inspect Database Schemas

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

List all schemas in the database to identify migration-specific schemas.

```sql
\dn
```

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

### Retrieve latest schema from local directory

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-schema.mdx

Uses the --local flag to point the command to a specific directory containing migration files.

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

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

### Configure backfill parameters

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/migrate.mdx

Controls the batch size and delay for data backfilling operations to manage performance impact.

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

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

### Set search_path in Go client

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Configures the PostgreSQL search_path after establishing a database connection to target a specific migration version.

```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)
}
```

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

### Describe Table Schema

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Displays the current schema of the users table.

```sql
DESCRIBE users
```

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

### View pgroll convert help

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/orms.mdx

Displays usage information and available flags for the pgroll convert command.

```sh
$ 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
```

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

### Initialize pgroll in the database

Source: https://github.com/xataio/pgroll/blob/main/README.md

Creates internal tables required to track schema versions and history.

```sh
pgroll --postgres-url postgres://user:password@host:port/dbname init
```

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

### Use Global Connection String

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-url.mdx

Generates a connection URL using the global pgroll connection string as the base.

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

```text
postgresql://user:pass@example.com:5432/mydb?options=-c%20search_path%3Dpublic_00n_some_migration
```

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

### Create baseline without confirmation prompt

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/baseline.mdx

Uses the --yes flag to bypass the interactive confirmation prompt.

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

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

### Baseline an existing database

Source: https://github.com/xataio/pgroll/blob/main/README.md

Imports the current structure of an already populated database into pgroll.

```sh
pgroll --postgres-url postgres://user:password@host:port/dbname baseline 000_existing_schema ./migrations/
```

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

### Check schema version view

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Commands to inspect specific schema versions and their underlying views.

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

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

```sql
\d+ public_03_add_is_active_column.user
```

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

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

### List generated migration files

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/pull.mdx

Displays the contents of the migrations directory after a successful pull operation.

```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
...
```

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

### create_table

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Defines the structure for creating a new table, including column specifications and table-level constraints.

```APIDOC
## create_table

### Description
Creates a new table in the database with specified columns and constraints.

### Request Body
- **name** (string) - Required - Name of the new table
- **columns** (array) - Optional - List of column definitions
- **constraints** (array) - Optional - List of table-level constraints

### Column Definition
- **name** (string) - Column name
- **type** (string) - Postgres type
- **comment** (string) - Postgres comment for the column
- **nullable** (boolean) - Nullable status
- **unique** (boolean) - Unique status
- **pk** (boolean) - Primary key status
- **default** (string) - Default value
- **check** (object) - Check constraint details
- **generated** (object) - Generated column details
- **references** (object) - Foreign key reference details

### Constraint Definition
- **name** (string) - Constraint name
- **type** (string) - Constraint type
- **columns** (array) - List of columns
- **check** (string) - Condition of CHECK constraint
- **nulls_not_distinct** (boolean) - Nulls not distinct status
- **deferrable** (boolean) - Deferrable status
- **initially_deferred** (boolean) - Initially deferred status
- **no_inherit** (boolean) - No inherit status
- **references** (object) - Foreign key reference details
- **index_parameters** (object) - Index parameters
- **exclude** (object) - Exclude constraint details
```

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

### Retrieve Latest Migration from Database

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-migration.mdx

Prints the latest migration name from the target database.

```bash
$ pgroll latest migration
```

```text
55_add_primary_key_constraint_to_table
```

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

### Generate empty migrations

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/create.mdx

Create empty migration files using specific formats and names without interactive prompts.

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

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

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

### Inspect Migration Views

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Examine the structure of views within specific migration schemas.

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

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

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

### Create table with version schema

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Configures the version_schema field during table creation.

```yaml
<ExampleSnippet example="56_with_version_schema.yaml" languange="yaml" />
```

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

### Inspect table structure

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Shows the column definitions for the users table including temporary pgroll columns.

```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>      |
+-----------------------+------------------------+-----------------------------------------------------------------+----------+--------------+-------------+
```

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

### Query Table Data

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Retrieves the first 10 rows from the users table to verify migration progress.

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

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

### Retrieve Latest Migration from Local Directory

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-migration.mdx

Prints the latest migration name from a specified local directory using the --local flag.

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

```text
55_add_primary_key_constraint_to_table
```

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

### Check default schema status

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/status.mdx

Displays the migration status for the public schema.

```bash
$ pgroll status
```

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

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

### Create table with column defaults

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Sets default values for columns, ensuring string literals are properly quoted.

```yaml
<ExampleSnippet example="28_different_defaults.yaml" languange="yaml" />
```

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

### Define Migration Up SQL

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Specifies the SQL used to populate the new column during the backfill process.

```yaml
up: "SELECT CASE WHEN description IS NULL THEN 'description for ' || name ELSE description END"
```

```json
"up": "SELECT CASE WHEN description IS NULL THEN 'description for ' || name ELSE description END",
```

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

### Pull migrations to a directory

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/pull.mdx

Executes the pull command to write schema history as YAML files to the target directory.

```bash
$ pgroll pull migrations/
```

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

### Create multiple tables

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Defines multiple table creation operations within a single migration.

```yaml
<ExampleSnippet example="01_create_tables.yaml" languange="yaml" />
```

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

### Configure client search path

Source: https://github.com/xataio/pgroll/blob/main/README.md

Directs client applications to use the new schema version created by pgroll.

```sql
SET search_path TO 'public_initial_migration';
```

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

### Convert SQL file to pgroll migration

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/convert.mdx

Translates SQL statements from a file into a pgroll migration output to stdout.

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

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

### Create table with foreign key

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Defines a foreign key constraint directly on a column.

```yaml
<ExampleSnippet example="19_create_orders_table.yaml" languange="yaml" />
```

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

### Define Raw SQL Migration Structure

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/raw_sql.mdx

Basic structure for defining up and down SQL expressions in YAML or JSON formats.

```yaml
sql:
  up: SQL expression
  down: SQL expression
```

```json
{
  "sql": {
    "up": "SQL expression",
    "down": "SQL expression"
  }
}
```

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

### Command Usage

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-url.mdx

Basic syntax for the pgroll latest url command.

```bash
pgroll latest url [connection-string] [flags]
```

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

### Set search_path in Python

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/clientapps.mdx

Sets the search_path via connection options when using the psycopg2 library.

```python
import psycopg2

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

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

### Verify rollback results

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Displays the state of schemas and tables after a rollback has been performed.

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

```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_index

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_index.mdx

Defines a new index on a specified table with options for columns, sorting, uniqueness, and storage parameters.

```APIDOC
## create_index

### Description
Creates a new index on a set of columns within a table.

### Request Body
- **table** (string) - Required - Name of table on which to define the index
- **name** (string) - Required - Index name
- **columns** (array) - Required - List of columns to index
- **predicate** (string) - Optional - Conditional expression for defining a partial index
- **storage_parameters** (string) - Optional - Comma-separated list of storage parameters
- **unique** (boolean) - Optional - Set to true to create a unique index
- **method** (string) - Optional - Index method (btree, hash, gist, spgist, gin, brin)
```

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

### Retrieve latest schema from database

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/latest-schema.mdx

Executes the command against the target database to identify the current schema version.

```bash
$ pgroll latest schema
```

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

### Update migration files command

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/update.mdx

Updates migration files in the specified folder to the latest schema version.

```bash
$ pgroll update {migrations-folder}
```

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

### View employee table records after constraint migration

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/updown.mdx

Displays the employee table records after applying the capitalized_name constraint.

```sql
 name  | nick |     email      | salary  |                 bio
-------+------+----------------+---------+-------------------------------------
 Alice | al   | al@company.com |  5000.5 | hi, i am al
 Bob   | rob  |                |  5400.5 | i am bob aka rob. i love gardening.
 Carol | cat  |                | 6500.75 |
```

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

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

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Defines a table schema with mixed nullability constraints.

```yaml
<ExampleSnippet example="14_add_reviews_table.yaml" languange="yaml" />
```

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

### Check specific schema status

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/status.mdx

Uses the --schema flag to inspect the migration status of a non-default schema.

```bash
$ pgroll status --schema schema_a
```

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

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

### Create table with comment

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Adds a descriptive comment to the table definition.

```yaml
<ExampleSnippet example="12_create_employees_table.yaml" languange="yaml" />
```

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

### Set search_path in Node.js

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/clientapps.mdx

Executes a SET command to update the search_path after connecting with the pg client.

```js
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();
});
```

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

### List Migration Triggers

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Display the triggers created by pgroll for data synchronization.

```sql
_pgroll_trigger_users__pgroll_new_description BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION _pgroll_trigger_users__pgroll_new_description>
_pgroll_trigger_users_description BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION _pgroll_trigger_users_description()
```

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

### Query users table

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Retrieves specific user records to verify data state after migration.

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

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

### Convert Alembic migrations to pgroll

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/orms.mdx

Pipes SQL output from Alembic's offline mode directly into the pgroll converter.

```sh
$ 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)"
          }
        ]
      }
    }
  ]
}
```

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

### Define a custom SQL function for migrations

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/updown.mdx

Create a reusable SQL function to perform complex data transformations during migration steps.

```sql
CREATE OR REPLACE FUNCTION random_bio(integer)
    RETURNS TEXT AS
    $$
        SELECT array_to_string(
            ARRAY (
                SELECT substring(
                    'abcdefghijklmnopqrstuvwxyz'
                    FROM (ceil(random()*26))::int FOR 1
                )
                FROM generate_series(1, $1)
            ),
            ''
)
$$
LANGUAGE sql VOLATILE;
```

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

### View employee table schema

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/updown.mdx

Displays the structure of the employee table in PostgreSQL.

```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)
```

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

### Define index structure

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_index.mdx

Schema definitions for creating an index using YAML or JSON formats.

```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
```

```json
{
  "create_index": {
    "table": "name of table on which to define the index",
    "name": "index name",
    "columns": [
      {
        "column": "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"
  }
}
```

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

### Create table with table level foreign key constraints

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Defines foreign key constraints at the table level.

```yaml
<ExampleSnippet example="51_create_table_with_table_foreign_key_constraint.yaml" languange="yaml" />
```

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

### Validate a migration file

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/validate.mdx

Use this command to verify the structure and content of a pgroll migration file before execution.

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

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

### Create table structure

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Defines the top-level structure for a create table operation.

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

```json
{
  "create_table": {
    "name": "name of new table",
    "columns": [...],
    "constraints": [...]
  }
}
```

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

### Set Postgres search path

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Configures the session to target a specific schema version.

```sql
SET search_path = 'public_01_create_users_table'
```

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

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

### Define up and down SQL expressions

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_constraint.mdx

Required SQL expressions for each column covered by the constraint.

```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
```

```json
{
  "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",
  }
}
```

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

### Restrict to single migration

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/migrate.mdx

Fails the command if more than one unapplied migration is detected, ensuring only one migration is applied at a time.

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

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

### Define Table Migration

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Migration definition to create a users table, provided in both YAML and JSON formats.

```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
```

```json
{
  "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
          }
        ]
      }
    }
  ]
}
```

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

### Convert SQL from stdin

Source: https://github.com/xataio/pgroll/blob/main/docs/cli/convert.mdx

Reads SQL statements directly from standard input when no file path is provided.

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

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

### View employee table records after type conversion

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/updown.mdx

Displays the employee table records after converting the salary column to integer.

```sql
 name  | nick |     email      | salary |                 bio
-------+------+----------------+--------+-------------------------------------
 Alice | al   | al@company.com |   5000 | hi, i am al
 Bob   | rob  |                |   5400 | i am bob aka rob. i love gardening.
 Carol | cat  |                |   6500 |
```

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

### Complete a migration

Source: https://github.com/xataio/pgroll/blob/main/README.md

Finalizes the migration and removes the old schema version.

```sh
pgroll --postgres-url postgres://user:password@host:port/dbname complete
```

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

### Verbose pgroll migration from ORM

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/orms.mdx

A multi-operation pgroll migration resulting from the ORM SQL statements shown previously.

```json
{
  "add_column": {
    "column": {
      "check": {
        "constraint": "age >= 18",
        "name": "age_check"
      },
      "default": "18",
      "name": "age",
      "nullable": true,
      "type": "smallint"
    },
    "table": "adults",
    "up": "TODO: Implement SQL data migration"
  }
},
{
  "alter_column": {
    "column": "age",
    "default": null,
    "table": "adults",
    "down": "TODO: Implement SQL data migration",
    "up": "TODO: Implement SQL data migration"
  }
},
{
  "sql": {
    "up": "update adults set age = 18"
  }
},
{
  "alter_column": {
    "column": "age",
    "nullable": false,
    "table": "adults",
    "up": "TODO: Implement SQL data migration"
    "down": "TODO: Implement SQL data migration",
  }
},
{
  "sql": {
    "up": "UPDATE \"adults\" SET \"age\" = 18 WHERE \"age\" IS NULL"
  }
},
{
  "sql": {
    "up": "SET CONSTRAINTS ALL IMMEDIATE"
  }
},
{
  "alter_column": {
    "column": "age",
    "nullable": false,
    "table": "adults",
    "up": "TODO: Implement SQL data migration"
    "down": "TODO: Implement SQL data migration",
  }
}
```

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

### Retrieve latest schema version

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/clientapps.mdx

Uses the pgroll CLI to identify the current schema version.

```bash
$ pgroll latest schema
public_02_add_assignee_column
```

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

### Create table with table level constraints

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Defines constraints at the table level rather than the column level.

```yaml
<ExampleSnippet example="50_create_table_with_table_constraint.yaml" languange="yaml" />
```

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

### Apply complex transformations with constraints

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/updown.mdx

Use custom SQL functions and conditional logic within a migration to enforce data quality and format constraints.

```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
```

```json
{
  "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"
        }
      }
    }
  ]
}
```

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

### Define a pgroll migration structure

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/README.md

Basic structure for migration files in YAML and JSON formats.

```yaml
version_schema: <version schema name>
operations: [...]
```

```json
{
  "version_schema": "<version schema name>",
  "operations": [...]
}
```

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

### Foreign key structure definition

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/alter_column/add_foreign_key.mdx

Defines the required fields for adding a foreign key constraint, including reference details and SQL migration expressions.

```yaml
alter_column:
  table: table name
  column: column name
  references:
    name: name of foreign key reference
    table: name of referenced table
    column: name of referenced column
    on_delete: ON DELETE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION
  up: SQL expression
  down: SQL expression
```

```json
{
  "alter_column": {
    "table": "table name",
    "column": "column name",
    "references": {
      "name": "name of foreign key reference",
      "table": "name of referenced table",
      "column": "name of referenced column",
      "on_delete": "ON DELETE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION"
    },
    "up": "SQL expression",
    "down": "SQL expression"
  }
}
```

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

### Define migration operation

Source: https://github.com/xataio/pgroll/blob/main/docs/tutorial.md

Defines a new column addition operation in YAML or JSON format.

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

```json
{
  "operations": [
    {
      "add_column": {
        "table": "users",
        "column": {
          "name": "is_atcive",
          "type": "boolean",
          "nullable": true,
          "default": "true"
        }
      }
    }
  ]
}
```

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

### Convert Django migrations to pgroll

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/orms.mdx

Defines a Django model and pipes the generated SQL migration script into the pgroll converter.

```python
class Employee():
    name = models.CharField(max_length=100)
    joined = models.DateTimeField()
    email = models.EmailField()
```

```sh
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)"
          }
        ]
      }
    }
  ]
}
```

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

### Create table with generated identity column

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Creates a table featuring a generated identity column.

```yaml
<ExampleSnippet example="08_create_fruits_table.yaml" languange="yaml" />
```

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

### Create table with CHECK constraint

Source: https://github.com/xataio/pgroll/blob/main/docs/operations/create_table.mdx

Applies a CHECK constraint to a specific column.

```yaml
<ExampleSnippet
  example="25_add_table_with_check_constraint.yaml"
  languange="yaml"
/>
```

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

### Convert Drizzle migrations to pgroll

Source: https://github.com/xataio/pgroll/blob/main/docs/guides/orms.mdx

Defines a Drizzle schema and uses the generated SQL file as input for the pgroll converter.

```ts
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 })
});
```

```sh
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)"
          }
        ]
      }
    ]
  }
}
```
Tokens:
Raw