sqitch...ckhouse(3pm)
💅 ok bestie, welcome
hey hi hello bestie!! the page you just opened is sqitchtutorial-clickhouse(3pm), straight out of the User Contributed Perl Documentation pile. it's a whole tutorial situation and I'm about to walk you through it like we're texting at 2am. buckle up. 🎀
🦋 NAME 🦋
sqitchtutorial-clickhouse — a tutorial introduction to Sqitch change management on ClickHouse. that's the whole vibe: learn Sqitch, but make it ClickHouse. 💖
✨ SYNOPSIS ✨
sqitch *periodt. that's the silhouette of literally every command you're about to run. 💅
🌸 DESCRIPTION 🌸
okay so here's the tea: this tutorial shows you how to build a sqitch-enabled ClickHouse project from the ground up, use a VCS to plan out your deployments, and work with other devs so your changes stay in sync and in the right order. no chaos. no "wait who pushed that". 🫶
We're starting from absolute scratch — a totally fictional antisocial networking site called Flipr. Every example in here uses Git as the VCS and ClickHouse as the storage engine, but ngl, for the most part you can slot in other VCSes and database engines wherever it makes sense. 🌈
- Git:
https://git-scm.com/ - ClickHouse:
https://clickhouse.com/clickhouse
If you'd rather manage a database with a different engine, there's a whole buffet of other tutorials waiting for you:
- PostgreSQL, Yugabyte, and CockroachDB Tutorial
- SQLite Tutorial
- Oracle Tutorial
- MySQL and MariaDB Tutorial
- Firebird Tutorial
- Vertica Tutorial
- Exasol Tutorial
- Snowflake Tutorial
- ClickHouse Tutorial
🔌 Connection Configuration 🔌
Sqitch needs ClickHouse v25.8 or higher, because that's the version that brings the lightweight updates and deletes it leans on. And if you upgraded an instance from something older — fr fr, go run SET compatibility = '25.8' (or higher). don't skip this. 😭
Also: Sqitch needs ODBC to connect to ClickHouse, so you've gotta make sure the ClickHouse ODBC driver (https://github.com/ClickHouse/clickhouse-odbc) is installed and properly configured. At its simplest, on Unix-like systems, you name the driver ClickHouse by adding this entry to odbcinst.ini — that file usually lives in /etc, /usr/etc, or /usr/local/etc:
[ClickHouse]
Description = Unicode ODBC Driver for ClickHouse
Driver = /opt/clickhouse/lib/libclickhouseodbcw.so👀 notice it's the Unicode driver, not the ANSI one — yes that matters. also, you may need to tweak that path depending on the version of the ODBC driver and where you installed it. Consult the docs for your ODBC driver manager — e.g. UnixODBC (https://www.unixodbc.org) or iODBC (https://www.iodbc.org) — for the details.
With that configured, your Sqitch target URIs can name the driver like so:
db:clickhouse://user:pass@host:port/dbname?Driver=ClickHouse⚡ Ports ⚡
On top of the ODBC connection Sqitch uses to maintain its registry tables, it also depends on the ClickHouse Client (https://clickhouse.com/docs/interfaces/cli) to actually run change scripts. So Sqitch needs both the HTTP and Native ports (https://clickhouse.com/docs/guides/sre/network-ports) to connect. 💀
The port you hand over as part of a URI decides which HTTP port ODBC uses. So if your ClickHouse server runs on a non-standard Native port — anything other than 9004, or 9440 for encrypted connections — you've gotta spell out the native port with the NativePort query parameter:
db:clickhouse://user:pass@host:port/dbname?Driver=ClickHouse?NativePort=9999🚀 Starting a New Project 🚀
Usually the very first move when you start a new project is to create a source code repository. So let's do exactly that with Git:
> mkdir flipr
> cd flipr
> git init .
Initialized empty Git repository in /flipr/.git/
> touch README.md
> git add .
> git commit -am 'Initialize project, add README.'
[main (root-commit) 046b8f5] Initialize project, add README.
1 file changed, 38 insertions(+)
create mode 100644 README.mdIf you're a Git girlie and wanna follow along with the history, the repository used in these examples is on GitHub ✨: https://github.com/sqitchers/sqitch-clickhouse-intro
Now that we've got a repo, let's get started with Sqitch. Every Sqitch project must have a name attached to it, and, optionally, a unique URI. We recommend including the URI, because it makes object identifiers more unique internally and stops a different project with the same name from deploying over yours. 👑
So let's specify one when we initialize Sqitch:
> sqitch init flipr --uri https://github.com/sqitchers/sqitch-clickhouse-intro/ --engine clickhouse
Created sqitch.conf
Created sqitch.plan
Created deploy/
Created revert/
Created verify/Let's have a little look-see at sqitch.conf:
> cat sqitch.conf
[core]
engine = clickhouse
# plan_file = sqitch.plan
# top_dir = .
# [engine "clickhouse"]
# target = db:clickhouse:
# registry = sqitch
# client = clickhouse-clientGood — it clocked that we're creating changes for the ClickHouse engine (thank you, --engine clickhouse) and saved it to the file. And it also wrote a commented-out [engine "clickhouse"] section with ClickHouse engine-specific settings, all commented out and ready to be edited as appropriate. 💖
By default, Sqitch reads sqitch.conf in the current directory for settings. But it also reads ~/.sqitch/sqitch.conf for user-specific settings. Since ClickHouse's clickhouse client is not in the path on my system, let's go ahead and tell it where to find the client on our computer — (don't bother with this if you're using the Docker image https://hub.docker.com/r/sqitch/sqitch/, because it uses the client inside the container, not on your host machine 🙈):
> sqitch config --user engine.clickhouse.client /opt/bin/clickhouseAnd let's also tell it who we are, since this data gets used across all our projects:
> sqitch config --user user.name 'Marge N. O’Vera'
> sqitch config --user user.email 'marge@example.com'Take a look at ~/.sqitch/sqitch.conf and you'll see this:
> cat ~/.sqitch/sqitch.conf
[engine "clickhouse"]
client = /opt/bin/clickhouse
[user]
name = Marge N. O’Vera
email = marge@example.comWhich means Sqitch should be able to find clickhouse for any project, and it'll always properly identify us when planning and committing changes. 🫶
Back to the repository. Have a look at the plan file, sqitch.plan:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-clickhouse-intro/Notice it holds the name and URI of the app we're building. Sqitch uses this data to manage cross-project dependencies. The %syntax-version pragma is always set by Sqitch, so it always knows how to parse the plan, even if the format changes down the line. 👑
Let's commit these changes and start creating the database changes:
> git add .
> git commit -am 'Initialize Sqitch configuration.'
[main d32b8ae] Initialize Sqitch configuration.
2 files changed, 12 insertions(+)
create mode 100644 sqitch.conf
create mode 100644 sqitch.plan✨ Our First Change ✨
Okay bestie, time to actually make something. Our app needs users (every app does, it's giving main character energy), so we're gonna create a table for them. Run this:
> sqitch add users -n 'Creates table to track our users.'
Created deploy/users.sql
Created revert/users.sql
Created verify/users.sql
Added "users" to sqitch.planThe add command adds a database change to the plan and then writes the deploy, revert, and verify scripts that represent that change. Three files, one plan entry, slay.
Now we edit those files. The deploy script's whole job is to create the table. By default, the deploy/users.sql file looks like this:
-- Deploy flipr:users to clickhouse
-- XXX Add DDLs here.What we want is to replace that XXX comment with the CREATE TABLE statement, like so:
-- Deploy flipr:users to clickhouse
CREATE TABLE users (
nickname VARCHAR(50) PRIMARY KEY,
password VARCHAR(512) NOT NULL,
fullname VARCHAR(512) NOT NULL,
mastodon VARCHAR(512) NOT NULL,
created_at DATETIME64(6, 'UTC') NOT NULL DEFAULT now64(6, 'UTC')
) ENGINE = MergeTree;The revert script's job is to precisely revert the change that the deploy script made, so we edit revert/users.sql to look like this:
-- Revert flipr:users from clickhouse
DROP TABLE users;Now we can try deploying this change. First, we need to create a database to deploy to. Assuming ClickHouse is running on localhost using the default user (as is provided by the ClickHouse Docker Image — that's https://hub.docker.com/_/clickhouse):
> clickhouse client --query 'CREATE DATABASE flipr_test'Then we tell Sqitch where to send the change, via a database URI (it's https://github.com/libwww-perl/uri-db/), again assuming the default user & database and an ODBC driver named ClickHouse (peep "Connection Configuration" for the deets):
> sqitch deploy 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
Adding registry tables to db:clickhouse://default@localhost/sqitch?Driver=ClickHouse
Deploying changes to db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
+ users .. okSo first, Sqitch created the registry database and the tables it uses to track database changes. The registry database is separate from the database the users change was deployed to. By default, its name is sqitch, and it'll be used to manage all projects on a single ClickHouse server. Ideally, only Sqitch data will live in this database, so honestly it probably makes the most sense to create a superuser named sqitch or something similar and use that to deploy changes.
If you'd rather it use a different database as the registry database, use sqitch engine add clickhouse $name to configure it (or do it via the target command — more on that below). That's useful when you don't want the same registry database managing multiple databases on the same server.
Next, Sqitch deploys changes to the target database, which we specified right there on the command line. We only have one change so far; that little + reinforces the idea that the change is being added to the database. 💅
With this change deployed, if you connect to the database, you'll be able to see the user:
> clickhouse client -d flipr_test -q 'show tables'
users🔍 Trust, But Verify 🔍
But ugh, that's too much work. Do you really wanna do something like that after every deploy? No bestie. No you do not.
This is where the verify script comes in. Its job is to test that the deploy did what it was supposed to. It should do so without regard to any data that might be in the database, and it should throw an error if the deploy was not successful. The easiest way to do that with a table is to simply SELECT from it. Put this query into verify/users.sql:
SELECT nickname, password, fullname, mastodon
FROM users
WHERE false;Now you can run the verify script with the verify command:
> sqitch verify 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
Verifying db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
* users .. ok
Verify successfulLooks good! Now, if you wanna make sure the verify script correctly dies when 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 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 failedClickHouse is kind enough to tell us exactly what the problem is. 🫶 Don't forget to change the table name back before continuing!
📊 Status, Revert, Log, Repeat 📊
For purely informational purposes, we can always see how a deployment was recorded via the status command, which reads the registry tables straight 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 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 .. okThe revert command prompts first, to make sure we really want to revert. This is to prevent unnecessary accidents. You can pass the -y option to disable the prompt. Also, notice the - before the change name in the output — that reinforces that the change is being 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 deployedOf course, since nothing is deployed, the 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 deployedHowever, we still have a record that the change happened, visible via the 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. Very demure, very mindful.
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.sqlAnd then deploy again. This time, let's use the --verify option, so that the verify script gets 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 .. okAnd now the schema should be back:
> clickhouse client -d flipr_test -q 'show tables'
usersWhen we look at the status, the deployment will be there:
> 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:34:21 -0400
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)🎯 On Target 🎯
ngl, I'm getting a little tired of always having to type db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse, aren't you? That database connection URI (https://github.com/libwww-perl/uri-db/) tells Sqitch how to connect to the deployment target, but we don't have to keep using the URI. We can name the target:
> sqitch target add flipr_test 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'The target command, inspired by git-remote (https://git-scm.com/docs/git-remote), lets you manage one or more named deployment targets. We've just added a target named flipr_test, which means we can use the string flipr_test for the target instead of the whole URI. But since we're doing so much testing, we can also tell Sqitch to deploy to the flipr_test target by default:
> sqitch engine add clickhouse flipr_testNow we can omit the target argument altogether, unless we need to deploy to another database. Which we will, eventually — but at least our examples get simpler from here on in, like:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 02916d6d5dca9faef8f2bb5822c53de8b7ae16bc
# Name: users
# Deployed: 2025-09-19 14:34:21 -0400
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Yay, that makes everything a little more concise. ✨ Let's also make sure changes get verified after we deploy them:
> sqitch config --bool deploy.verify true
> sqitch config --bool rebase.verify trueWe'll see the rebase command a bit later. In the meantime, let's commit the new configuration and and make some more changes!
> git commit -am 'Set default deployment target and always verify.'
[main ad3a3b0] Set default deployment target and always verify.
1 file changed, 8 insertions(+)💖 Deploy with Dependency 💖
Ok bestie, time to level up. Let's add another change, because we're just that girl. Our app needs to store status messages from users. We're gonna call them — and the table that holds them — "flips". Cute, right? First, add the new change:
> sqitch add flips --requires users -n 'Adds table for storing flips.'
Created deploy/flips.sql
Created revert/flips.sql
Created verify/flips.sql
Added "flips [users]" to sqitch.planNotice how we made "users" a dependency of our new "flips" change? Like, yeah, that change was already added to the plan and obviously gets applied before "flips" anyway, but it's a really good idea to be explicit about your dependencies. No cap. 🫶
Now edit the scripts. When you're done, deploy/flips.sql should look exactly like this:
-- Deploy flipr:flips to clickhouse
-- requires: users
CREATE TABLE flips (
id INTEGER NOT NULL PRIMARY KEY,
nickname VARCHAR(50) NOT NULL,
body VARCHAR(512) DEFAULT '' NOT NULL,
created_at DATETIME64(6, 'UTC') NOT NULL DEFAULT now64(6, 'UTC'),
CONSTRAINT body_length_check CHECK ( char_length(body) <= 180 ),
) ENGINE = MergeTree;A couple things to peep here. 👀 On the second line, the dependence on the "users" change got listed. This doesn't do anything, but the default "deploy" template lists it there for your reference while you're editing the file. Genuinely useful, right?
The users.nickname column implicitly references the users table. That's literally why we need to require the "users" change — even though ClickHouse itself does not enforce such relationships. (It's giving "trust me bro" energy but in SQL, iykyk 💅)
Now for the verify script. Again, all we need to do is SELECT from the table. I highly recommend selecting each column by name too, so you can be sure no column is missing. Here's verify/flips.sql:
-- Verify flipr:flips on clickhouse
SELECT id, nickname, body, created_at
FROM flips
WHERE false;Now the revert script: all we have to do is drop the table. Add this to revert/flips.sql:
-- Revert flipr:flips from firebird
DROP TABLE flips;Couldn't be any simpler, right? Let's deploy this bad boy:
> sqitch deploy
Deploying changes to flipr_test
+ flips .. okWe already know the table must have been created since verification is enabled. But for the vibes, let's have a quick look:
> clickhouse client -d flipr_test -q 'show tables'
flips
usersWe can also verify all currently deployed changes with the verify command:
> sqitch verify
Verifying flipr_test
* users .. ok
* flips .. ok
Verify successfulNow have a look at the status:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 0756d81da8a858a991dca5ffd167fea2de8e98df
# Name: flips
# Deployed: 2025-09-19 14:36:09 -0400
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Success! 🎉 Let's make sure we can revert the change, as well:
> sqitch revert --to @HEAD^ -y
Reverting changes to users from flipr_test
- flips .. okNote that we used the --to option to specify the change to revert to. And what do we revert to? The symbolic tag @HEAD, when passed to revert, always refers to the last change deployed to the database. (For other commands, it refers to the last change in the plan.) Appending the caret (^) tells Sqitch to select the change prior to the last deployed change. So we revert to "users", the penultimate change. The other potentially useful symbolic tag is @ROOT, which refers to the first change deployed to the database (or in the plan, depending on the command).
Back to the database. The "flips" table should be gone but the "users" table should still be hanging around:
> clickhouse client -d flipr_test -q 'show tables'
usersThe status command politely informs us that we have undeployed changes:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 02916d6d5dca9faef8f2bb5822c53de8b7ae16bc
# Name: users
# Deployed: 2025-09-19 14:34:21 -0400
# By: Marge N. O’Vera <marge@example.com>
#
Undeployed change:
* flipsAs does the verify command:
> sqitch verify
Verifying flipr_test
* users .. ok
Undeployed change:
* flips
Verify successfulNote that the verify is successful, because all currently-deployed changes are verified. The list of undeployed changes (just "flips" here) is there to remind us about the current state. Lil' status update, love that. 💅
Okay, let's commit and deploy again:
> git add .
> git commit -am 'Add flips table.'
[main 347f867] Add flips table.
4 files changed, 19 insertions(+)
create mode 100644 deploy/flips.sql
create mode 100644 revert/flips.sql
create mode 100644 verify/flips.sql
> sqitch deploy
Deploying changes to flipr_test
+ flips .. okLooks good. Check the status:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 0756d81da8a858a991dca5ffd167fea2de8e98df
# Name: flips
# Deployed: 2025-09-19 14:38:00 -0400
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)🌸 View to a Thrill 🌸
One more thing to add before we're ready to ship a first beta release. Let's create a view that lists user names with their flips. ✨
> sqitch add userflips --requires users --requires flips \
-n 'Creates the userflips view.'
Created deploy/userflips.sql
Created revert/userflips.sql
Created verify/userflips.sql
Added "userflips [users flips]" to sqitch.planNow add this SQL to deploy/userflips.sql:
CREATE OR REPLACE VIEW userflips AS
SELECT f.id, u.nickname, u.fullname, f.body, f.created_at AS created_at
FROM users u
JOIN flips f ON u.nickname = f.nickname;Add this SQL to verify/userflips.sql:
SELECT id, nickname, fullname, body, created_at
FROM userflips
WHERE false;And add the DROP VIEW statement to revert/userflips.sql:
DROP VIEW userflips;Now try it out!
> sqitch deploy
Deploying changes to flipr_test
+ userflips .. ok
> sqitch revert -y
Reverting all changes from flipr_test
- userflips .. ok
- flips ...... ok
- users ...... ok
> sqitch deploy
Deploying changes to flipr_test
+ users ...... ok
+ flips ...... ok
+ userflips .. okLooks good! Commit it. 🫶
> git add .
> git commit -m 'Add the userflips view.'
[main f21de26] Add the userflips view.
4 files changed, 17 insertions(+)
create mode 100644 deploy/userflips.sql
create mode 100644 revert/userflips.sql
create mode 100644 verify/userflips.sql🚀 Ship It! 🚀
Now we're ready for the first development release of our app, bestie. Let's call it 1.0.0-dev1. Since we want deployments tied to the release, let's tag it:
> sqitch tag v1.0.0-dev1 -n 'Tag v1.0.0-dev1.'
Tagged "userflips" with @v1.0.0-dev1
> git commit -am 'Tag the database with v1.0.0-dev1.'
[main a821008] Tag the database with v1.0.0-dev1.
1 files changed, 1 insertions(+), 0 deletions(-)
> git tag v1.0.0-dev1 -am 'Tag v1.0.0-dev1'Now let's bundle everything up for release:
> sqitch bundle
Bundling into bundle
Writing config
Writing plan
Writing scripts
+ users
+ flips
+ userflips @v1.0.0-dev1Now we can package the bundle directory and distribute it. When it gets installed somewhere, users can use Sqitch to deploy to the database. We ought to try deploying it, but first we'll need to revert our existing databases, since a single Sqitch project cannot be deployed to two databases on the same server unless it uses a different registry database. Fortunately it's easy to build the database again, so let's just revert it. 💁
> sqitch revert -y
Reverting all changes from flipr_test
- userflips @v1.0.0-dev1 .. ok
- flips ................... ok
- users ................... okNow we can try deploying the bundle:
> cd bundle
> clickhouse client --query 'CREATE DATABASE flipr_dev'
> sqitch deploy 'db:clickhouse://default@localhost/flipr_dev?Driver=ClickHouse'
Deploying changes to db:clickhouse://default@localhost/flipr_dev?Driver=ClickHouse
+ users ................... ok
+ flips ................... ok
+ userflips @v1.0.0-dev1 .. okGreat, all four changes were deployed and "userflips" was tagged with @v1.0.0-dev1. Let's have a look at the status:
> sqitch status 'db:clickhouse://default@localhost/flipr_dev?Driver=ClickHouse'
# On database db:clickhouse://default@localhost/flipr_dev?Driver=ClickHouse
# Project: flipr
# Change: afdfa60d0fd2b7910601648667a23546fe0f7f43
# Name: userflips
# Tag: @v1.0.0-dev1
# Deployed: 2025-09-19 14:40:20 -0400
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Looks good, eh? Go ahead and revert it:
> sqitch revert -y 'db:clickhouse://default@localhost/flipr_dev?Driver=ClickHouse'
Revert all changes from db:clickhouse://default@localhost/flipr_dev?Driver=ClickHouse? [Yes]
- userflips @v1.0.0-dev1 .. ok
- flips ................... ok
- users ................... okNow package it up and ship it! 👑
> cd ..
> mv bundle flipr-v1.0.0-dev1
> tar -czf flipr-v1.0.0-dev1.tgz flipr-v1.0.0-dev1🎀 Making a Hash of Things 🎀
Okay bestie, the app basics are done, so obviously it's new-feature time. We gotta track the hashtags that go with flips, right? So let's make a table for them. But plot twist: other people are out here working on other stuff in this same repo, so we're doing this on a branch — everybody stays out of everybody's way. Branch it:
> git checkout -b hashtags
Switched to a new branch 'hashtags'Now we add a new change that creates the hashtags table.
> sqitch add hashtags --requires flips -n 'Adds table for storing hashtags.'
Created deploy/hashtags.sql
Created revert/hashtags.sql
Created verify/hashtags.sql
Added "hashtags [flips]" to sqitch.planYou know the drill by now, babe. Deploy script time, so drop this in deploy/hashtags.sql:
CREATE TABLE hashtags (
flip_id INTEGER NOT NULL,
hashtag VARCHAR(512) NOT NULL,
CONSTRAINT hashtag CHECK(char_length(hashtag) > 0)
) ENGINE = MergeTree
PRIMARY KEY (flip_id, hashtag);And again, select from the table in verify/hashtags.sql:
SELECT flip_id, hashtag FROM hashtags WHERE false;And drop it in revert/hashtags.sql:
DROP TABLE hashtags;Now give it a whirl:
> sqitch deploy
Deploying changes to flipr_test
+ users ................... ok
+ flips ................... ok
+ userflips @v1.0.0-dev1 .. ok
+ hashtags ................ okLook good? 👀
> sqitch status --show-tags
# On database flipr_test
# Project: flipr
# Change: dca0003c3d411c71a672172acab78e4a42dfaa8d
# Name: hashtags
# Deployed: 2025-09-19 14:48:13 -0400
# By: Marge N. O’Vera <marge@example.com>
#
# Tag:
# @v1.0.0-dev1 - 2025-09-19 14:48:13 -0400 - Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)See how we used --show-tags? That's what makes all the deployed tags actually show up. Obsessed. Now let's make it official:
> rm -rf flipr-v1.0.0-dev1*
> git add .
> git commit -am 'Add hashtags table.'
[hashtags 1359929] Add hashtags table.
4 files changed, 17 insertions(+)
create mode 100644 deploy/hashtags.sql
create mode 100644 revert/hashtags.sql
create mode 100644 verify/hashtags.sqlSlay, feature done. Time to merge back into main.
🚨 Emergency 🚨
Let's go:
> git checkout main
Switched to branch 'main'
> git pull
Updating a821008..b800878
Fast-forward
deploy/lists.sql | 10 ++++++++++
revert/lists.sql | 3 +++
sqitch.plan | 2 ++
verify/lists.sql | 5 +++++
4 files changed, 20 insertions(+)
create mode 100644 deploy/lists.sql
create mode 100644 revert/lists.sql
create mode 100644 verify/lists.sqlHrm. That's... interesting. Someone else has been touching main — looks like list support got added while we were away. Ngl, let's just see what happens when we merge our stuff in.
> git merge --no-ff hashtags
Auto-merging sqitch.plan
CONFLICT (content): Merge conflict in sqitch.plan
Automatic merge failed; fix conflicts and then commit the result.Oh. A conflict in sqitch.plan. Not exactly shocking, bestie — both the merged lists branch and our hashtags branch added changes to that plan. So let's try a different approach.
Real talk: we got lazy. 😭 Those changes when we pulled main from the origin should have raised a giant red flag. It is considered bad practice to not look at what's changed in main before merging in a branch. What you're actually supposed to do is either:
- Rebase the
hashtagsbranch frommainbefore merging. This literally "rewinds" the branch changes, pulls frommain, and then replays the changes right back on top of the pulled changes. - Create a patch and apply it to
maininstead. This is the move you'd have to pull if you're sending changes off to another user, especially when the VCS in question is not Git.
So let's restore everything to how it was at main:
> git merge --abortPoof, botched merge, gone. Now back to our branch, and let's rebase it onto main:
> git checkout hashtags
Switched to branch 'hashtags'
> git rebase main
Auto-merging sqitch.plan
CONFLICT (content): Merge conflict in sqitch.plan
error: could not apply 1359929... Add hashtags table.
hint: Resolve all conflicts manually, mark them as resolved with
hint: "git add/rm <conflicted_files>", then run "git rebase --continue".
hint: You can instead skip this commit: run "git rebase --skip".
hint: To abort and get back to the state before "git rebase", run "git rebase --abort".
hint: Disable this message with "git config set advice.mergeConflict false"
Could not apply 1359929... # Add hashtags table.Oy. That's kind of a pain. 😩 It really feels like no matter what we do, we're gonna have to resolve conflicts in that file. Except — in Git — we can just tell Git to resolve conflicts in sqitch.plan differently. Because the one thing we ever do to that file is append lines to it, we can hand it over to the union merge driver, which, per its docs at https://git-scm.com/docs/gitattributes#_built-in_merge_drivers, does this:
Run 3-way file level merge for text files, but take lines from both versions, instead of leaving conflict markers. This tends to leave the added lines in the resulting file in random order and the user should verify the result. Do not use this if you do not understand the implications.
Which has the effect of appending lines from all the merging files — exactly the thing we need. So let's try it. First, back out that botched rebase:
> git rebase --abortNow add the union merge driver to .gitattributes for sqitch.plan, and rebase again:
> echo sqitch.plan merge=union > .gitattributes
> git rebase main
Successfully rebased and updated refs/heads/hashtags.Ahh, that's a bit better. ✨ Let's peek at the plan:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-clickhouse-intro/
users 2025-09-19T18:29:21Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
flips [users] 2025-09-19T18:35:31Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
userflips [users flips] 2025-09-19T18:38:26Z Marge N. O’Vera <marge@example.com> # Creates the userflips view.
@v1.0.0-dev1 2025-09-19T18:39:48Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.
lists [flips] 2025-09-19T18:41:17Z An Oth R. Developer <another@example.com> # Adds table for storing lists.
hashtags [flips] 2025-09-19T18:47:02Z Marge N. O’Vera <marge@example.com> # Adds table for storing hashtags.See that? It appended the changes from the merged lists branch, and then merged in the changes from our hashtags branch. Now test it to make sure it actually works like we expect:
> sqitch rebase -y
Reverting all changes from flipr_test
- hashtags ................ ok
- userflips @v1.0.0-dev1 .. ok
- flips ................... ok
- users ................... ok
Deploying changes to flipr_test
+ users ................... ok
+ flips ................... ok
+ userflips @v1.0.0-dev1 .. ok
+ lists ................... ok
+ hashtags ................ okNote the rebase — it smashes a revert and a deploy together into a single command. Handy, right?? It correctly reverted our changes and then deployed the whole lot again in the proper order. So let's commit .gitattributes, because yeah, definitely worth keeping that:
> git add .
> git commit -m 'Add `.gitattributes` with union merge for `sqitch.plan`.'
[hashtags 862ea7a] Add `.gitattributes` with union merge for `sqitch.plan`.
1 file changed, 1 insertion(+)
create mode 100644 .gitattributes💖 Merges Mastered 💖
And now, finally, we can merge into main:
> git checkout main
Switched to branch 'main'
> git merge --no-ff hashtags -m "Merge branch 'hashtags'"
Merge made by the 'ort' strategy.
.gitattributes | 1 +
deploy/hashtags.sql | 9 +++++++++
revert/hashtags.sql | 3 +++
sqitch.plan | 1 +
verify/hashtags.sql | 3 +++
5 files changed, 17 insertions(+)
create mode 100644 .gitattributes
create mode 100644 deploy/hashtags.sql
create mode 100644 revert/hashtags.sql
create mode 100644 verify/hashtags.sqlAnd double-check our work:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-clickhouse-intro/
users 2025-09-19T18:29:21Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
flips [users] 2025-09-19T18:35:31Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
userflips [users flips] 2025-09-19T18:38:26Z Marge N. O’Vera <marge@example.com> # Creates the userflips view.
@v1.0.0-dev1 2025-09-19T18:39:48Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.
lists [flips] 2025-09-19T18:41:17Z An Oth R. Developer <another@example.com> # Adds table for storing lists.
hashtags [flips] 2025-09-19T18:47:02Z Marge N. O’Vera <marge@example.com> # Adds table for storing hashtags.Much much better — a nice clean main, and because it's now identical to the hashtags branch, we can just carry on. Go ahead: tag it, bundle it, release it:
> sqitch tag v1.0.0-dev2 -n 'Tag v1.0.0-dev2.'
Tagged "hashtags" with @v1.0.0-dev2
> git commit -am 'Tag the database with v1.0.0-dev2.'
[main 5b56772] Tag the database with v1.0.0-dev2.
1 file changed, 1 insertion(+)
> git tag v1.0.0-dev2 -am 'Tag v1.0.0-dev2'
> sqitch bundle --dest-dir flipr-1.0.0-dev2
Bundling into flipr-1.0.0-dev2
Writing config
Writing plan
Writing scripts
+ users
+ flips
+ userflips @v1.0.0-dev1
+ lists
+ hashtags @v1.0.0-dev2And yeah, notice the --dest-dir option on sqitch bundle. It's just a way nicer way to name that top-level directory, so we don't have to sit around renaming stuff away from bundle. 💅
💖 In Place Changes 💖
Ok bestie, so like... some folks have been testing the "1.0.0-dev2" release and they demanded that Mastodon user links be added to Flipr pages. Why would anyone want to include social network links in an anti-social networking app? Beyond us programmers fr fr, but we're just the plumbers, right? Gotta go with what Marketing demands. 😭 The upshot is that we need to update the "userflips" view, which is used for the feature in question, to include the Mastodon user names. 💅
Normally, modifying views in database changes is a PITA (see https://www.urbandictionary.com/define.php?term=pita). You have to make changes like these:
- Copy
deploy/userflips.sqltodeploy/userflips_mastodon.sql. - Edit
deploy/userflips_mastodon.sqlto re-create the view with the new"mastodon"column added to the view. - Copy
deploy/userflips.sqltorevert/userflips_mastodon.sql. Yes, copy the original change script to the new revert change. - Copy
verify/userflips.sqltoverify/userflips_mastodon.sql. - Modify
verify/userflips_mastodon.sqlto include a check for the"mastodon"column. - Test the changes to make sure you can deploy and revert the
"userflips_mastodon"change.
But you can have Sqitch do most of the work for you. ✨ The only requirement is that a tag appear between the two instances of a change we want to modify. In general, you're going to make a change like this after a release, which you've tagged anyway, right? Well we have, with "@v1.0.0-dev2" added in the previous section. With that, we can let Sqitch do most of the hard work for us, thanks to the "rework" command, which is similar to "add":
> git checkout -b mastodon
> sqitch rework userflips -n 'Adds userflips.mastodon.'
Added "userflips [userflips@v1.0.0-dev2]" to sqitch.plan.
Modify these files as appropriate:
* deploy/userflips.sql
* revert/userflips.sql
* verify/userflips.sqlOh, so we can edit those files in place. Nice! 🌸 How does Sqitch do it? Well, in point of fact, it has copied the files to stand in for the previous instance of the "userflips" change, which we can see via "git status":
> git status
On branch mastodon
Changes not staged for commit:
(use "git add <file>..." to update what will be committed)
(use "git restore <file>..." to discard changes in working directory)
modified: revert/userflips.sql
modified: sqitch.plan
Untracked files:
(use "git add <file>..." to include in what will be committed)
deploy/userflips@v1.0.0-dev2.sql
flipr-1.0.0-dev2/
revert/userflips@v1.0.0-dev2.sql
verify/userflips@v1.0.0-dev2.sql
no changes added to commit (use "git add" and/or "git commit -a")The "untracked files" part of the output is the first thing to notice. 👀 They are all named "userflips@v1.0.0-dev2.sql". What that means is: "the "userflips" change as it was implemented as of the "@v1.0.0-dev2" tag." These are copies of the original scripts, and thereafter Sqitch will find them when it needs to run scripts for the first instance of the "userflips" change. As such, it's important not to change them again. But hey, if you're reworking the change, you shouldn't need to. 💁
The other thing to notice is that revert/userflips.sql has changed. Sqitch replaced it with the original deploy script. As of now, deploy/userflips.sql and revert/userflips.sql are identical. This is on the assumption that the deploy script will be changed (we're reworking it, remember?), and that the revert script should actually change things back to how they were before. 🔄
Fortunately, our view deploy scripts are already almost idempotent (see https://en.wikipedia.org/wiki/Idempotence) -- that is, able to be applied multiple times without changing the result beyond the initial application. If it's not, you will likely need to modify it so that it properly restores things to how they were after the original deploy script was deployed. Or, more simply, it should revert changes back to how they were as-of the deployment of deploy/userflips@v1.0.0-dev2.sql. 😭
Fortunately (again! 💖), our view deploy scripts are already idempotent, thanks to the use of the "OR REPLACE" expression. No matter how many times a deployment script is run, the end result will be the same instance of the view, with no duplicates or errors. ✨
As a result, there is no need to explicitly add changes. So go ahead. Modify deploy/userflips.sql to add the "mastodon" column.
--- a/deploy/userflips.sql
+++ b/deploy/userflips.sql
@@ -3,6 +3,6 @@
-- requires: flips
CREATE OR REPLACE VIEW userflips AS
-SELECT f.id, u.nickname, u.fullname, f.body, f.created_at AS created_at
+SELECT f.id, u.nickname, u.fullname, u.mastodon, f.body, f.created_at created_at
FROM users u
JOIN flips f ON u.nickname = f.nickname;Next, modify verify/userflips.sql to check for the "mastodon" column. Here's the diff:
--- a/verify/userflips.sql
+++ b/verify/userflips.sql
@@ -1,5 +1,5 @@
-- Verify flipr:userflips on clickhouse
-SELECT id, nickname, fullname, body, created_at
+SELECT id, nickname, mastodon, fullname, body, created_at
FROM userflips
WHERE false;Now try a deployment:
> sqitch deploy
Deploying changes to flipr_test
+ userflips .. okSo, are the changes deployed?
> clickhouse client -d flipr_test -q 'describe userflips'
id Int32
nickname String
fullname String
mastodon String
body String
created_at DateTime64(6, \'UTC\')Awesome, the view now includes the "mastodon" column. 🔥 But can we revert?
> sqitch revert --to @HEAD^ -y
Reverting changes to hashtags @v1.0.0-dev2 from flipr_test
- userflips .. okDid that work, is the "mastodon" column gone?
> clickhouse client -d flipr_test -q 'describe userflips'
id Int32
nickname String
fullname String
body String
created_at DateTime64(6, \'UTC\')Yes, it works! 🎉 Sqitch properly finds the original instances of these changes in the new script files that include tags.
Excellent. Let's go ahead and commit these changes and merge them into main:
> rm -rf flipr-1.0.0-dev2
> git add .
> git commit -m 'Add the mastodon column to the userflips view.'
[mastodon 571662b] Add the mastodon column to the userflips view.
7 files changed, 26 insertions(+), 4 deletions(-)
create mode 100644 deploy/userflips@v1.0.0-dev2.sql
create mode 100644 revert/userflips@v1.0.0-dev2.sql
create mode 100644 verify/userflips@v1.0.0-dev2.sql
> git checkout main
> git merge --no-ff mastodon -m "Merge branch 'mastodon'"
Merge made by the 'ort' strategy.
deploy/userflips.sql | 2 +-
deploy/userflips@v1.0.0-dev2.sql | 8 ++++++++
revert/userflips.sql | 9 +++++++--
revert/userflips@v1.0.0-dev2.sql | 3 +++
sqitch.plan | 1 +
verify/userflips.sql | 2 +-
verify/userflips@v1.0.0-dev2.sql | 5 +++++
7 files changed, 26 insertions(+), 4 deletions(-)
create mode 100644 deploy/userflips@v1.0.0-dev2.sql
create mode 100644 revert/userflips@v1.0.0-dev2.sql
create mode 100644 verify/userflips@v1.0.0-dev2.sql🌈 More to Come 🌈
Sqitch is a work in progress. Better integration with version control systems is planned to make managing idempotent reworkings even easier. Stay tuned. 👀
👑 Author 👑
- David E. Wheeler <david@justatheory.com>
📜 License 📜
Copyright (c) 2012-2026 David E. Wheeler, 2012-2021 iovation Inc.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
That's the whole vibe, bestie! 💖 This page came from perl v5.40.1, dated 2026-07-26, and it's the sqitch...ckhouse(3pm) page. Stay slay! ✨🦋💅
📦 where this page comes from
- ubuntu stonking: sqitch 1.6.1-2 · MIT
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms