Changelog
Copy
View as Markdown

2 min read 4 likes

You can now run SQL on Managed Postgres in one command

Vincent Charlebois

Give fly mpg connect a statement or a file, and it will run it and exit when it’s done.

Until now, fly mpg connect only opened an interactive psql session. To run a query from a script or your agent, you had to start fly mpg proxy in the background, get credentials and then run your own psql.

When we tested coding agents on some routine Managed Postgres (MPG) jobs, we noticed they kept typing fly mpg connect <CLUSTER_ID> -c '...' even though -c didn’t exist. That was one more reason to add it.

As of flyctl v0.5.9, you can run a statement with -c or a file with -f:

fly mpg connect <CLUSTER_ID> -c 'select count(*) from orders'
fly mpg connect <CLUSTER_ID> -f migrate.sql
fly mpg connect <CLUSTER_ID> -c 'select * from orders' -- --csv -t

What comes after -- is passed to psql, like --csv -t in the example above.

If you’re looking for your <CLUSTER_ID>, you’ll find it with fly mpg list.

With -c or -f, flyctl’s messages go to stderr while stdout contains only psql’s output. This allows you to capture a value in a variable.

count=$(fly mpg connect <CLUSTER_ID> -c 'select count(*) from orders' -- -At)

In the example above, -At tells psql to print only the value, without the column header or the table formatting.

fly will exit 0 when your SQL succeeds and 1 when it fails. With either flag, psql stops at its first error. With -f, the statements that ran before the error will remain applied. If you would rather keep nothing on failure, wrap your file in BEGIN and COMMIT.

Here are a few things to know:

  • When using -c or -f, you won’t be prompted to pick a database or a user. You’ll need to explicitly pass --database or --username to pick specific targets. Without these flags, you get the cluster’s default database and user.
  • You can repeat -c or repeat -f, but you can’t mix these two in the same call.
  • fly mpg connect uses the same fixed local port as fly mpg proxy. This means only one can run at a time, as a second process will fail with address already in use. To keep a proxy running while you use fly mpg connect, you’ll want to start it on another port with fly mpg proxy -p <PORT>.

For more examples, run fly mpg connect --help or read Running SQL without a session.

If your agent still stumbles, please tell us. We’d like to hear what didn’t work and what can still be improved.