sq diff compares metadata, or row data, for sources, or individual tables.
sq diff takes as arguments either a pair of sources (“source diff”), or
a pair of tables (“table diff”). For our examples, we’ll use a pair of
sq diffis in beta release. There is still work to be done with performance, scalability, and testing. It is likely that the implementation will change based upon user feedback. If you find a bug, please open an issue. General feedback can be left on the diff discussion.
Use flags to specify the elements you want to compare. The available elements are:
--overview: source metadata, without schema (source diff only)
--dbprops: database/server properties (source diff only)
--schema: schema structure, for database or individual table
--counts: show row counts when using
--data: row data values
--all: all of the above
Note that you can combine flags:
# Longhand $ sq diff @sakila/staging @sakila/prod --overview --dbprops --schema # Shorthand $ sq diff @sakila/staging @sakila/prod -OBS
For table diff, the default behavior is to diff table schema and row counts. Table row data is not compared.
# Diff the "address" table in staging vs prod. $ sq diff @sakila/staging.address @sakila/prod.address
In the example above, we see that the row counts differ, and also that
the table structure is different: the column
For source diff, the default behavior is to diff the source overview, schema, and table row counts. Table row data is not compared.
$ sq diff @sakila/staging @sakila/prod
To compare row data, use the
# Diff the rows of the "actor" table in staging vs prod. $ sq diff @sakila/staging.actor @sakila/prod.actor --data
--datais used with source diff, all rows in all tables (and views) are compared. Obviously this may take some time to complete.
-f) flag with
--data to specify the row data output format.
The default is
text. The available formats are:
You can change the
sq config set diff.data.format.
--formatflag only works in conjunction with row data diff (
--data). Metadata diff (e.g.
--schema) is currently always output in YAML.
-S) to compare only schema/structure. This applies both
to source diff and table diff.
# Compare the structure of every table/view in staging vs prod. $ sq diff @sakila/staging @sakila/prod --schema
-N) in conjunction with
--schema to also see row counts.
# Show schema for each table, and row counts. $ sq diff @sakila/staging @sakila/prod --schema --counts # Shorthand $ sq diff @sakila/staging @sakila/prod -SC
-O) to diff high-level source metadata. This flag applies
only to source diff. It compares
the source definitions (handle, driver, location), as well as some high-level
information about the database (product, version, etc.).
$ sq diff @sakila/staging @sakila/prod --overview
-B) to diff database/server properties. Applies only to source diff.
$ sq diff @sakila/staging @sakila/prod --dbprops
-a) to diff every element in both sources. Use with caution with
$ sq diff @sakila/staging @sakila/prod --all
You can control the number of surrounding lines using the
The default is
# Don't show any surrounding lines $ sq diff @sakila/staging.actor @sakila/prod.actor --data -U0 # Show 5 surrounding lines $ sq diff @sakila/staging.actor @sakila/prod.actor --data -U5
You can set the default number of lines
sq config set diff.lines.
--unifiedflag could easily have been named
--linesor such, but we stick with
--unifiedfor alignment with the familiar