view release on metacpan or  search on metacpan

lib/sqitchtutorial-clickhouse.pod  view on Meta::CPAN

command:

  > sqitch verify 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  Verifying db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
    * users .. ok
  Verify successful

Looks good! If you want to make sure that the verify script correctly dies if
the table doesn't exist, temporarily change the table name in the script to
something that doesn't exist, something like:

  SELECT nickname, password, fullname, mastodon, created_at
    FROM users_nonesuch
   WHERE false;

Then L<C<verify>|sqitch-verify> again:

  > sqitch verify 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  Verifying db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
    * users .. Received exception from server (version 25.8.2):
  Code: 60. DB::Exception: Received from clickhouse:9000. DB::Exception: Unknown table expression identifier 'users_nonesuch' in scope SELECT nickname, password, fullname, mastodon FROM users_nonesuch WHERE false. (UNKNOWN_TABLE)
  (query: -- Verify flipr:users on clickhouse

  SELECT nickname, password, fullname, mastodon
    FROM users_nonesuch
   WHERE false;)
  # Verify script "verify/users.sql" failed.
  not ok

  Verify Summary Report
  ---------------------
  Changes: 1
  Errors:  1
  Verify failed

ClickHouse is kind enough to tell us what the problem is. Don't forget to
change the table name back before continuing!

=head2 Status, Revert, Log, Repeat

For purely informational purposes, we can always see how a deployment was
recorded via the L<C<status>|sqitch-status> command, which reads the registry
tables from the database:

  > sqitch status 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  # On database db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
  # Project:  flipr
  # Change:   02916d6d5dca9faef8f2bb5822c53de8b7ae16bc
  # Name:     users
  # Deployed: 2025-09-19 14:32:08 -0400
  # By:       Marge N. O’Vera <marge@example.com>
  # 
  Nothing to deploy (up-to-date)

Let's make sure that we can revert the change:

  > sqitch revert 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  Revert all changes from db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse? [Yes]
    - users .. ok

The L<C<revert>|sqitch-revert> command first prompts to make sure that we
really do want to revert. This is to prevent unnecessary accidents. You can
pass the C<-y> option to disable the prompt. Also, notice the C<-> before the
change name in the output, which reinforces that the change is being
I<removed> from the database. And now the schema should be gone:

  > clickhouse client -d flipr_test -q 'show tables'

And the status message should reflect as much:

  > sqitch status 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  # On database db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
  No changes deployed

Of course, since nothing is deployed, the L<C<verify>|sqitch-verify> command
has nothing to verify:

  > sqitch verify 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  Verifying db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
  No changes deployed

However, we still have a record that the change happened, visible via the
L<C<log>|sqitch-log> command:

  > sqitch log 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  Revert 02916d6d5dca9faef8f2bb5822c53de8b7ae16bc
  Name:      users
  Committer: Marge N. O’Vera <marge@example.com>
  Date:      2025-09-19 14:33:23 -0400
  
      Creates table to track our users.
  
  Deploy 02916d6d5dca9faef8f2bb5822c53de8b7ae16bc
  Name:      users
  Committer: Marge N. O’Vera <marge@example.com>
  Date:      2025-09-19 14:32:08 -0400
  
      Creates table to track our users.

Note that the actions we took are shown in reverse chronological order, with
the revert first and then the deploy.

Cool. Now let's commit it.

  > git add .
  > git commit -m 'Add users table.'
  [main 414672a] Add users table.
   4 files changed, 18 insertions(+)
   create mode 100644 deploy/users.sql
   create mode 100644 revert/users.sql
   create mode 100644 verify/users.sql

And then deploy again. This time, let's use the C<--verify> option, so that
the C<verify> script is applied when the change is deployed:

  > sqitch deploy --verify 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  Deploying changes to db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
    + users .. ok

And now the schema should be back:

  > clickhouse client -d flipr_test -q 'show tables'
  users