Create a table under a non exsting schema

[althaf004]

Please provide a sample json on how to specify schemas with tables.

[andrew-farries]

Hi @althaf004 👋

There is currently no way to create a new schema with a pgroll migration. Migrations are expected to run in an already-existing schema.

Once the schema exists, the schema in which the migration should run is specified with the --schema flag, eg:

pgroll start examples/01_create_tables.json --schema my_schema_name

This will ensure that the tables created by the migration are created under my_schema_name.

[althaf004]

if I have 15 custom database schemas, and there are changes needed in all the tables within these schemas, do I have to create 15 different JSON files and execute each JSON file separately for each schema?

i would recommend to have a flag in json, so that a single file can be used for entire migration.

[andrew-farries]

If the migration is the same for each schema you only need one migration file. For example to run a migration called 01_create_tables.json in each of the 15 schema you could run:

#!/bin/bash

# Define an array of schema names
declare -a schemas=(
  "schema1"
  "schema2"
  # and so on for each of the schema names
)

# Loop through each schema name and invoke pgroll
for schema in "${schemas[@]}"; do
  echo "Running pgroll with --schema=$schema"
  pgroll start 01_create_tables.json --schema="$schema"
done

The script runs the same migration file in each of the 15 schema.

[althaf004]

hey, But migration scripts should not be same for each schemas, it could differ from schema to schema.
Say

• schema A needs to have a table created.
• schema B needs to have an alter column

is it possible to have the above statements in a single json and run it.

[andrew-farries]

In that case you would need different migration files for each schema.

If you wanted to automate it you could use a script like:

#!/usr/bin/env bash

# Declare an associative array to map schema names to migration files
declare -A schema_to_json=(
  ["schema1"]="examples/01_create_tables.json"
  ["schema2"]="examples/02_create_another_table.json"
  # and so on for each schema
)

# Run the migration file for each entry in the associative array
for schema in "${!schema_to_json[@]}"; do
  json_file="${schema_to_json[$schema]}"
  pgroll start "$json_file" --schema="$schema"
done