eugene trace

The eugene trace command will actually execute your SQL script in a transaction. If you have PostgreSQL installed, eugene trace can set up a temporary database server for you, and run through all the SQL scripts you give it to trace them. If you prefer to use your own database server, you can give provide eugene trace with connection information and it will roll back scripts by default, in this case.

PostgreSQL has a number of interesting features that can help eugene trace look for dangerous patterns while executing SQL statements in a transaction. eugene trace will look at the data types of every column, it will discover new indexes and constraints, and it will discover when database objects get moved to a new location on disk -- that is, table or index rewrites.

Since eugene trace has so much information, it is much less likely to trigger false positives than eugene lint, but it is also slower and requires a live database connection.

eugene trace can catch broad categories of dangerous patterns -- sometimes, it will discover a table rewrite that eugene lint can not detect, but it may not be able to tell you about why that table rewrite happened. eugene trace will discover all indexes and constraints created in a transaction, even if they were implicitly created. If you need to tell eugene trace that you know a statement to be safe, you can tell it to ignore a lint by adding a comment to your SQL script, see ignores.

If you want to run eugene trace in CI, or as a pre-commit hook, you can use --git-diff=main or -gmain to trace files that are new/unstaged, or have changes in them since main. eugene trace will still run all the scripts, but will only check the ones that have changed.


$ eugene help trace
Trace effects by running statements from SQL migration script

`eugene trace` will set up a temporary postgres server for tracing, unless disabled.

Reads $PGPASS for password to postgres, if ~/.pgpass is not found.

`eugene trace` exits with failure if any problems are detected.

Usage: eugene trace [OPTIONS] [paths]...

          Path to SQL migration scripts, directories, or '-' to read from stdin

  -v, --var <PLACEHOLDERS>
          Provide name=value for replacing ${name} with value in the SQL script
          Can be used multiple times to provide more placeholders.

  -i, --ignore <IGNORED_HINTS>
          Ignore the hints with these IDs, use `eugene hints` to see available hints
          Can be used multiple times: `-i E3 -i E4`
          Or comment your SQL statement like this:
          `-- eugene-ignore: E3, E4`
          alter table foo add column bar json;
          This will ignore hints E3 and E4 for this statement only.

  -f, --format <FORMAT>
          Output format, plain, json or markdown
          [default: plain]
          [possible values: json, markdown, md, plain]

  -a, --accept-failures
          Exit successfully even if problems are detected.
          Will still fail for syntax errors in the SQL script.

      --sort-mode <SORT_MODE>
          Sort mode for script discovery, auto, name or none
          This is used to order scripts when a path is a directory, or many paths are provided.
          `auto` will sort by versions or sequence numbers.
          `auto` requires all files to have the same naming scheme, either flyway-style or leading sequence numbers.
          `name` will sort lexically by name.
          [default: auto]
          [possible values: auto, name, none]

  -s, --skip-summary
          Skip the summary section for markdown output

  -g, --git-diff <GIT_DIFF>
          Filter out discovered scripts that are have not been changed since this git ref
          Pass a git ref, like a commit hash, tag, or branch name.

          Disable creation of temporary postgres server for tracing
          By default, trace will create a postgres server in a temporary directory
          This relies on having `initdb` and `pg_ctl` in PATH, which eugene images have.
          Eugene deletes the temporary database cluster when done tracing.

  -o, --postgres-options <POSTGRES_OPTIONS>
          Portgres options to pass to the temporary postgres server
          Example: `eugene trace -o "-c fsync=off -c log_statement=all"`
          [default: ]

      --initdb <INITDB_OPTIONS>
          Initdb options to pass when creating the temporary postgres server
          Example: `eugene trace --initdb "--encoding=UTF8"`
          Supply it more than once to add multiple options.

  -U, --user <USER>
          Username to use for connecting to postgres
          [default: postgres]

  -d, --database <DATABASE>
          Database to connect to
          [default: postgres]

  -H, --host <HOST>
          Host to connect to
          [default: localhost]

  -p, --port <PORT>
          Port to connect to
          [default: 5432]

  -c, --commit
          Commit at the end of the transaction.
          Commit is always enabled for the temporary server, otherwise rollback is default.

  -e, --extra
          Show locks that are normally not in conflict with application code

  -h, --help
          Print help (see a summary with '-h')