X
Xata•3w ago
November

CLI/programmatic migrations for pg-enabled dbs

Hey all, I'm looking to try and test out xata, and want to be able to do some migrations programmatically So far, it looks like re-using my existing Flyway migrations is out (I can't just point it to the xata instance unless it's a dedicated instance), so I figured that pgroll CLI would be the way to go I know that pgroll is already built-in to the UI itself for managing, and prior questions seem to point to the UI (https://discord.com/channels/996791218879086662/1268594196474757121), but I was wondering if there was a way to get programmatic or externally-driven migrations working with xata- trying with: 
pgroll init --postgres-url <my xata connection string>
pgroll init --postgres-url <my xata connection string>
results in: 
ERROR   Failed to initialize pgroll: pq: unsupported statement [CREATE FUNCTION] on regular level, see https://xata.io/sql-support for the support matrix
Error: pq: unsupported statement [CREATE FUNCTION] on regular level, see https://xata.io/sql-support for the support matrix
ERROR   Failed to initialize pgroll: pq: unsupported statement [CREATE FUNCTION] on regular level, see https://xata.io/sql-support for the support matrix
Error: pq: unsupported statement [CREATE FUNCTION] on regular level, see https://xata.io/sql-support for the support matrix
1 Reply
cmck
cmck•3w ago
Hi @November, thanks for reaching out. As per the thread you linked pgroll is already built into Xata. You can use the Xata CLI with xata migrate to manage migrations in your Xata database. 
USAGE
  $ xata migrate [--no-input] [--profile <profile-name>]

COMMON FLAGS
  --no-input                Will not prompt interactively for missing values
  --profile=<profile-name>  Profile name to use

DESCRIPTION
  Execute multi-schema migrations with complete, rollback, start, list, status commands.

COMMANDS
  migrate complete  Complete an active migration
  migrate list      List migrations for a database branch
  migrate rollback  Rollback an active migration
  migrate start     Start a new migration
  migrate status    Get the status of the last pgroll migration
USAGE
  $ xata migrate [--no-input] [--profile <profile-name>]

COMMON FLAGS
  --no-input                Will not prompt interactively for missing values
  --profile=<profile-name>  Profile name to use

DESCRIPTION
  Execute multi-schema migrations with complete, rollback, start, list, status commands.

COMMANDS
  migrate complete  Complete an active migration
  migrate list      List migrations for a database branch
  migrate rollback  Rollback an active migration
  migrate start     Start a new migration
  migrate status    Get the status of the last pgroll migration
As per the error you observed, you won't be able to use pgroll or external migration tools on a Xata shared cluster due to the restricted permissions. It might be possible to use some tools with a dedicated cluster, which has less restrictions, but I would need to confirm this and, at the end of the day, the recommendation is to use xata migrate
Connect to Postgres
Learn how to connect to Postgres directly via the wire protocol

Did you find this page helpful?