Unhelpful error messages (first migration)

[TravisCardwell]

As detailed in my Codd Experiment blog entry, I experimented with first migrations in an attempt to find one that gets Codd to initialize the database (create the codd_schema.sql_migrations table) without creating the database itself. In doing so, I ran into a number of error messages that could probably be improved. These are recreated in the following log.

(0) Setup

Run a test database:

$ docker run \
    --detach \
    --name "pg_codd" \
    --publish "127.0.0.1:5432:5432" \
    --env POSTGRES_DB="postgres" \
    --env POSTGRES_USER="postgres" \
    --env POSTGRES_PASSWORD="password" \
    "postgres:15.2-alpine"

Create a test project:

$ mkdir /tmp/codd-errors
$ cd /tmp/codd-errors
$ mkdir bin config sql-migrations expected-schema
$ wget -O bin/codd \
    https://github.com/mzabani/codd/releases/download/v0.1.1/codd
$ chmod 0755 bin/codd

Configure settings in config/codd-dev.sh:

export CODD_CONNECTION=postgres://postgres:password@127.0.0.1/postgres
export CODD_EXPECTED_SCHEMA_DIR=expected-schema
export CODD_MIGRATION_DIRS=sql-migrations

Load settings in the current shell:

$ source config/codd-dev.sh

(1) First migration empty

Create an empty migration:

$ touch 0001-empty.sql

Attempting to add an empty migration results in an SqlError because the codd_schema.sql_migrations table does not yet exist.

$ ./bin/codd add 0001-empty.sql
[Error] Got SQL Error: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"codd_schema.sql_migrations\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

[Warn] Waiting 1000ms before next try

[Warn] Retrying

[Error] Got SQL Error: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"codd_schema.sql_migrations\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

[Warn] Waiting 2000ms before next try

[Warn] Retrying

codd: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"codd_schema.sql_migrations\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

It would probably be better to check if the table is in the database before attempting to use it. It would then be possible to provide a better error message.

(2) First migration only connection string

Create a first migration that only has a custom connection string:

$ echo "-- codd-connection: $CODD_CONNECTION" > 0001-conn.sql

Attempting to add this migration results in a connection string error, even though the connection string is valid.

$ ./bin/codd add 0001-conn.sql 2>&1 | fold -sw 78
Could not add migration: Connection string is not a valid libpq connection
string. A valid libpq connection string is either in the format
'postgres://username[:password]@host:port/database_name', with URI-encoded
(percent-encoded) components except for the host and bracket-surround IPv6
addresses, or in the keyword value pairs format, e.g. 'dbname=database_name
host=localhost user=postgres' with escaping for spaces, quotes or empty
values. More info at
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

The code has a check that the custom connection string is different from the default connection string, but we get the same error when the connection strings differ.

$ env CODD_CONNECTION=postgres://admin:password@127.0.0.1/postgres \
    ./bin/codd add 0001-conn.sql 2>&1 \
  | fold -sw 78
Could not add migration: Connection string is not a valid libpq connection
string. A valid libpq connection string is either in the format
'postgres://username[:password]@host:port/database_name', with URI-encoded
(percent-encoded) components except for the host and bracket-surround IPv6
addresses, or in the keyword value pairs format, e.g. 'dbname=database_name
host=localhost user=postgres' with escaping for spaces, quotes or empty
values. More info at
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

(3) First migration with connection string and SELECT

Copy the previous migration and add a SELECT statement:

$ cp 0001-conn.sql 0001-conn-select.sql
$ echo "SELECT 1;" >> 0001-conn-select.sql

Attempting to add this migration results in the same SqlError! It is notable that adding a statement changes the error message. If Codd requires that every migration have at least one statement, perhaps it should provide an error message to inform users of this when a migration has no statements.

$ ./bin/codd add 0001-conn-select.sql
[Error] Got SQL Error: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"codd_schema.sql_migrations\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

[Warn] Waiting 1000ms before next try

[Warn] Retrying

[Error] Got SQL Error: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"codd_schema.sql_migrations\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

[Warn] Waiting 2000ms before next try

[Warn] Retrying

codd: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"codd_schema.sql_migrations\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

(4) First migration only no transaction

Create a first migration that only configures no-txn:

$ echo "-- codd: no-txn" > 0001-notxn.sql

Attempting to add this migration results in a puzzling error message.

$ ./bin/codd add 0001-notxn.sql 2>&1 | fold -sw 78
Could not add migration: The options 'no-txn' are invalid. Valid options are
either 'in-txn' or 'no-txn'

(5) First migration with no transaction and connection string

Create a migration that specifies no transaction and a custom connection string:

$ cat 0001-notxn.sql 0001-conn.sql > 0001-notxn-conn.sql

Attempting to add this migration results in the connection string error.

$ ./bin/codd add 0001-notxn-conn.sql 2>&1 | fold -sw 78
Could not add migration: Connection string is not a valid libpq connection
string. A valid libpq connection string is either in the format
'postgres://username[:password]@host:port/database_name', with URI-encoded
(percent-encoded) components except for the host and bracket-surround IPv6
addresses, or in the keyword value pairs format, e.g. 'dbname=database_name
host=localhost user=postgres' with escaping for spaces, quotes or empty
values. More info at
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

[mzabani]

As usual, thanks for the detailed bug report, and sorry for the delay addressing this.

After #152, this is what will happen to error messages:

(1) First migration empty

$ codd add 0001-empty.sql 
Error detected: The migration seems to have no SQL statements

Tip: It looks like this is your first migration. Make sure either the target database of your default connection string already exists, or add a migration that creates your database with a custom connection string. Example:

    -- codd: no-txn
    -- codd-connection: dbname=postgres user=postgres host=localhost
    -- Make sure the connection string above works, or change it to one that works.
    CREATE DATABASE "codd-experiments" OWNER "codd_admin";
    -- Also make sure the DB above doesn't exist yet, and that the DB owner does.

- The migration above looks scary, but it's one of the rare few that will require anything other than plain SQL.
- If this is what you need, feel free to copy the migration above into a .sql file, modify it accordingly and add that as your first migration.
- If the above doesn't work, you want a more complete example or want to know more, make sure to read https://github.com/mzabani/codd/blob/master/docs/BOOTSTRAPPING.md for more on bootstrapping your database with codd.

(2) First migration only connection string

Exactly the same as (1).

(3) First migration with connection string and SELECT

Only the beginning of the error changes:

$ codd add 0001-conn-select.sql 
[Error] The earliest existing migration has no custom connection string or there are no migrations at all. Exiting.

ExitFailure 1

Tip: It looks like this is your first migration. Make sure either the target database of your default connection string already exists, or add a migration that creates your database with a custom connection string. Example:

    -- codd: no-txn
    -- codd-connection: dbname=postgres user=postgres host=localhost
    -- Make sure the connection string above works, or change it to one that works.
    CREATE DATABASE "codd-experiments" OWNER "codd_admin";
    -- Also make sure the DB above doesn't exist yet, and that the DB owner does.

- The migration above looks scary, but it's one of the rare few that will require anything other than plain SQL.
- If this is what you need, feel free to copy the migration above into a .sql file, modify it accordingly and add that as your first migration.
- If the above doesn't work, you want a more complete example or want to know more, make sure to read https://github.com/mzabani/codd/blob/master/docs/BOOTSTRAPPING.md for more on bootstrapping your database with codd.

(4) First migration only no transaction

Same as (1) and (2).

(5) First migration with no transaction and connection string

Same as (1), (2) and (4).