You can now run SQL on Managed Postgres in one command

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.

Cool! I’d love to be able to connect by cluster name instead of ID. E.g. fly mpt connect myorg-prod instead of fly mpg connect v8018f...

That would be nice to type, but there’s a catch: a cluster name isn’t unique. Even inside one org, you can have several clusters with the same name.

That said, there could be a way for us to support names. Then, when two clusters match, it could list their names and IDs so you can tell them apart. Curious if that would work for you or if you would expect something else.

Until then, fly mpg connect with no ID gives you a picker that shows the names.