Running sqlx-cli on a read-only database currently fails

[manifest]

I have found these related issues/pull requests

#4106

Description

It’s common to apply migrations with sqlx migrate run on every deploy as an initial stage before starting an application.

In a Kubernetes environment, we apply migrations from within the initial container before starting an application container. In some maintenance scenarios, such as updating the version of a PostgreSQL server, we switch our application to a read-only PostgreSQL replica. That makes the application unable to serve write requests, but it is still able to serve read requests. The problem arises if the application gets restarted. It won’t be able to connect to the database server because sqlx migrate run fails with the following error:

error: while executing migrations: error returned from database: cannot execute CREATE TABLE in a read-only transaction

Currently, we use the workaround

#!/bin/bash

if [[ ! "${DATABASE_URL}" ]]; then echo "DATABASE_URL is required." 1>&2; exit 1; fi

READ_ONLY_MODE=$(psql --tuples-only -c 'SHOW transaction_read_only;' "${DATABASE_URL}" | xargs)

if [[ "${READ_ONLY_MODE}" == "on" ]]; then
    echo "The database is in the read-only mode."
else
    cargo sqlx migrate run
fi

Prefered solution

It seems reasonable to integrate similar functionality into sqlx-cli – skipping running migrations on a read-only database, because it removes boilerplate and dependency on another database client (i.e. psql).

The error above arises because of the way how sqlx-cli ensures the migration table exists.

Instead of attempting to create a table, we can just test for any record within it with the simple SELECT 1 FROM {table_name} and then create a table if necessary. That is standard SQL, so can serve as a general solution for any database supported by sqlx.

Is this a breaking change? Why or why not?

No. It enables the execution of sqlx-cli against a read-only database without altering its functionality.