Likes (3)

Comment

Share

Motivation

I'd like to illustrate a typical migration from an application backed by Postgresql. Considering the operational database market with a variety of database choices, we find common issues moving from a monolithic database to a distributed SQL flavor. Considerations must be taken in approaching each migration, whether application code needs a rewrite or application logic must be changed to accommodate the new paradigms. I won't touch on every point but I will do my best to emphasize some of the salient points.

All my articles start with a customer problem. My client is considering CockroachDB for their database fleet and being a net new platform in the organization, the choice was made to evaluate CockroachDB as a drop-in replacement for Postgresql as a backing store for Unleash feature management platform. Should be easy right? Not so much.

High-Level Steps

• Problem
• Workaround
• Solution
• Next steps
• Clean up

Problem

We're going to use a local instance of Unleash, the problem is reproducible irrelevant of the deployment model.

First, we need to check out the Unleash repo, the same problem can be reproduced whether you used Unleash Docker image or the pre-built node application.

git clone https://github.com/Unleash/unleash.git

We also are going to use a single node instance of CockroachDB to keep it simple.

cockroach start-single-node --insecure --background

We need to create a target database

ockroach sql --insecure -e "CREATE DATABASE unleash;"

Find the connection configuration server-dev.ts in your checked-out repo and fill out the details.

 db: {
 user: 'root',
 password: '',
 host: 'localhost',
 port: 26257,
 database: 'unleash',
 ssl: false,
 },

Start the application - yarn install.

yarn install v1.22.17
[1/5] Validating package.json...
[2/5] Resolving packages...
success Already up-to-date.
$ yarn run build
yarn run v1.22.17
$ yarn run clean
$ del-cli --force dist
$ yarn run copy-templates && tsc --pretty
$ copyfiles -u 1 src/mailtemplates/**/*.mustache dist/
Done in 8.99s.
Done in 9.79s.

yarn start:dev

9:22:19 AM - Starting compilation in watch mode...


9:22:26 AM - Found 0 errors. Watching for file changes.
[2022-01-14T09:22:28.664] [DEBUG] server-impl.js - DB migration: start
[2022-01-14T09:22:33.542] [ERROR] server-impl.js - Failed to migrate db error: column "strategies" does not exist
 at addChunk (node:internal/streams/readable:324:12)
 at readableAddChunk (node:internal/streams/readable:297:9)
 at Socket.Readable.push (node:internal/streams/readable:234:10)
 at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
From previous event:
 at processImmediate (node:internal/timers:473:21) {
 length: 105,
 severity: 'ERROR',
 code: '42703',
 detail: undefined,
 hint: undefined,
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'column_resolver.go',
 line: '196',
 routine: 'NewUndefinedColumnError'
}
error: column "strategies" does not exist
 length: 105,
 severity: 'ERROR',
 code: '42703',
 detail: undefined,
 hint: undefined,
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'column_resolver.go',
 line: '196',
 routine: 'NewUndefinedColumnError'
}

This error appears as part of the initial Unleash migration attempt. This step works out of the box with Postgresql. Let's see what we have in our database.

root@:26257/unleash> show tables;
 schema_name | table_name | type | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
 public | events | table | root | 1 | NULL
 public | features | table | root | 0 | NULL
 public | migrations | table | root | 7 | NULL
 public | strategies | table | root | 1 | NULL
(4 rows)

There is some data but it is not what we expect to see in the stable version. Let's inspect the migrations table.

root@:26257/unleash> select * from migrations;
 id | name | run_on
---------------------+-------------------------------------------------------+--------------------------
 727776459344379905 | /20141020151056-initial-schema | 2022-01-14 09:22:29.554
 727776459921555457 | /20141110144153-add-description-to-features | 2022-01-14 09:22:29.731
 727776463439036417 | /20141117200435-add-parameters-template-to-strategies | 2022-01-14 09:22:30.804
 727776466625527809 | /20141117202209-insert-default-strategy | 2022-01-14 09:22:31.777
 727776467290947585 | /20141118071458-default-strategy-event | 2022-01-14 09:22:31.98
 727776467763429377 | /20141215210141-005-archived-flag-to-features | 2022-01-14 09:22:32.124
 727776472216240129 | /20150210152531-006-rename-eventtype | 2022-01-14 09:22:33.483
(7 rows)

This information tells me the migration step failed after 20150210152531-006-rename-eventtype step. Let's inspect the migration directory in our repo.

cd src/migrations && ls

20141020151056-initial-schema.js
20141110144153-add-description-to-features.js
20141117200435-add-parameters-template-to-strategies.js
20141117202209-insert-default-strategy.js
20141118071458-default-strategy-event.js
20141215210141-005-archived-flag-to-features.js
20150210152531-006-rename-eventtype.js
20160618193924-add-strategies-to-features.js
...

The migrations execute in order and our problem appears after 20150210152531-006-rename-eventtype.js so it must be 20160618193924-add-strategies-to-features.js file. Our overarching goal is to execute all of the migrations in this directory and there are currently 90+ migrations in this directory. Recall we were able to only execute 7 so far! We will look at the why of the problem in a later step. Let's see how we can bypass the migrations and actually get the application running atop CockroachDB.

Workaround

The workaround is rather simple and I have the amazing Unleash team to thank for that. We're going to perform the initialization of Unleash on Postgresql, take a backup and restore it to CockroachDB, then attempt to start the application again.

Change the connection configuration to reflect your Postgresql instance and run the application again. The application will start shortly after the successful migration.

Let's inspect the database.

psql "postgresql://localhost:5432/unleash?sslmode=disable" -U unleash_user

Let's list out all of the tables, recall we only had 4 tables in CockroachDB.

psql (14.1)
Type "help" for help.

unleash=# \dt
 List of relations
 Schema | Name | Type | Owner 
--------+----------------------+-------+--------------
 public | addons | table | unleash_user
 public | api_tokens | table | unleash_user
 public | client_applications | table | unleash_user
 public | client_instances | table | unleash_user
 public | client_metrics_env | table | unleash_user
 public | context_fields | table | unleash_user
 public | environments | table | unleash_user
 public | events | table | unleash_user
 public | feature_environments | table | unleash_user
 public | feature_strategies | table | unleash_user
 public | feature_tag | table | unleash_user
 public | feature_types | table | unleash_user
 public | features | table | unleash_user
 public | migrations | table | unleash_user
 public | project_environments | table | unleash_user
 public | projects | table | unleash_user
 public | reset_tokens | table | unleash_user
 public | role_permission | table | unleash_user
 public | role_user | table | unleash_user
 public | roles | table | unleash_user
 public | settings | table | unleash_user
 public | strategies | table | unleash_user
 public | tag_types | table | unleash_user
 public | tags | table | unleash_user
 public | unleash_session | table | unleash_user
 public | user_feedback | table | unleash_user
 public | user_splash | table | unleash_user
 public | users | table | unleash_user

We have a lot more tables in PostgreSQL.

Let's take a backup we're going to use in CockroachDB.

pg_dump unleash > /tmp/unleash.sql -U unleash_user

Upload the file to the CockroachDB user file filesystem.

cockroach userfile upload /tmp/unleash.sql --insecure --host=localhost

successfully uploaded to userfile://defaultdb.public.userfiles_root/unleash.sql

Import the Database

cockroach sql --insecure -e "import pgdump 'userfile://defaultdb.public.userfiles_root/unleash.sql' WITH ignore_unsupported_statements;" --database unleash;

The last step will produce an error.

ERROR: referenced table "public.events_id_seq" not found in tables being imported (public.feature_types,public.projects)

It is due to the following syntax with SEQUENCE

CREATE SEQUENCE public.events_id_seq
 AS integer
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;

invalid syntax: statement ignored: at or near "start": syntax error: unimplemented: this syntax
SQLSTATE: 0A000
DETAIL: source SQL:
CREATE SEQUENCE public.events_id_seq
 AS integer
 START WITH 1
 ^
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/25110/v21.2

The problem line is AS integer, luckily it's the only issue common to this pgdump and there is a total of 5 instances of this error. Find and replace all of the instances and repeat the last steps to import the pgdump into CockroachDB. By the way, AS integer is an accepted syntax in CockroachDB 22.1 which is coming in the first half of 2022!

CREATE SEQUENCE public.events_id_seq
 --AS integer
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;

Every instance of the syntax needs to be addressed.

CREATE SEQUENCE public.addons_id_seq
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;

CREATE SEQUENCE public.events_id_seq
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;

CREATE SEQUENCE public.migrations_id_seq
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;

CREATE SEQUENCE public.roles_id_seq
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;
 
CREATE SEQUENCE public.users_id_seq
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;

Attempt to Import Again, Optionally Re-Create the Database

cockroach sql --insecure -e "DROP DATABASE unleash CASCADE;"
cockroach sql --insecure -e "CREATE DATABASE unleash;"

Execute Import

cockroach sql -e "import pgdump 'userfile://defaultdb.public.userfiles_root/unleash.sql' WITH ignore_unsupported_statements" --insecure --host=localhost --database=unleash

 job_id | status | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
 726931514777108481 | succeeded | 1 | 166 | 59 | 16566
(1 row)

Let's inspect the database, now in CockroachDB.

root@:26257/unleash> show tables;
 schema_name | table_name | type | owner | estimated_row_count | locality
--------------+----------------------+----------+-------+---------------------+-----------
 public | addons | table | root | 0 | NULL
 public | addons_id_seq | sequence | root | 0 | NULL
 public | api_tokens | table | root | 0 | NULL
 public | client_applications | table | root | 0 | NULL
 public | client_instances | table | root | 0 | NULL
 public | client_metrics_env | table | root | 0 | NULL
 public | context_fields | table | root | 0 | NULL
 public | environments | table | root | 0 | NULL
 public | events | table | root | 0 | NULL
 public | events_id_seq | sequence | root | 0 | NULL
 public | feature_environments | table | root | 0 | NULL
 public | feature_strategies | table | root | 0 | NULL
 public | feature_tag | table | root | 0 | NULL
 public | feature_types | table | root | 0 | NULL
 public | features | table | root | 0 | NULL
 public | migrations | table | root | 0 | NULL
 public | migrations_id_seq | sequence | root | 0 | NULL
 public | project_environments | table | root | 0 | NULL
 public | projects | table | root | 0 | NULL
 public | reset_tokens | table | root | 0 | NULL
 public | role_permission | table | root | 0 | NULL
 public | role_user | table | root | 0 | NULL
 public | roles | table | root | 0 | NULL
 public | roles_id_seq | sequence | root | 0 | NULL
 public | settings | table | root | 0 | NULL
 public | strategies | table | root | 0 | NULL
 public | tag_types | table | root | 0 | NULL
 public | tags | table | root | 0 | NULL
 public | unleash_session | table | root | 0 | NULL
 public | user_feedback | table | root | 0 | NULL
 public | user_splash | table | root | 0 | NULL
 public | users | table | root | 0 | NULL
 public | users_id_seq | sequence | root | 0 | NULL
(33 rows)

Let's inspect the migrations table.

root@:26257/unleash> select * from migrations;
 id | name | run_on
-----+------------------------------------------------------------------------+--------------------------
 1 | /20141020151056-initial-schema | 2022-01-10 19:42:19.111
 2 | /20141110144153-add-description-to-features | 2022-01-10 19:42:19.121
 3 | /20141117200435-add-parameters-template-to-strategies | 2022-01-10 19:42:19.123
 4 | /20141117202209-insert-default-strategy | 2022-01-10 19:42:19.126
 5 | /20141118071458-default-strategy-event | 2022-01-10 19:42:19.129
 6 | /20141215210141-005-archived-flag-to-features | 2022-01-10 19:42:19.132
 7 | /20150210152531-006-rename-eventtype | 2022-01-10 19:42:19.135
 8 | /20160618193924-add-strategies-to-features | 2022-01-10 19:42:19.139
 9 | /20161027134128-create-metrics | 2022-01-10 19:42:19.145
 10 | /20161104074441-create-client-instances | 2022-01-10 19:42:19.149
 11 | /20161205203516-create-client-applications | 2022-01-10 19:42:19.154
 12 | /20161212101749-better-strategy-parameter-definitions | 2022-01-10 19:42:19.165
 13 | /20170211085502-built-in-strategies | 2022-01-10 19:42:19.168
 14 | /20170211090541-add-default-strategies | 2022-01-10 19:42:19.179
 15 | /20170306233934-timestamp-with-tz | 2022-01-10 19:42:19.183
 16 | /20170628205541-add-sdk-version-to-client-instances | 2022-01-10 19:42:19.186
 17 | /20190123204125-add-variants-to-features | 2022-01-10 19:42:19.188
 18 | /20191023184858-flexible-rollout-strategy | 2022-01-10 19:42:19.192
 19 | /20200102184820-create-context-fields | 2022-01-10 19:42:19.198
 20 | /20200227202711-settings | 2022-01-10 19:42:19.203
 21 | /20200329191251-settings-secret | 2022-01-10 19:42:19.206
 22 | /20200416201319-create-users | 2022-01-10 19:42:19.214
 23 | /20200429175747-users-settings | 2022-01-10 19:42:19.216
 24 | /20200805091409-add-feature-toggle-type | 2022-01-10 19:42:19.222
 25 | /20200805094311-add-feature-type-to-features | 2022-01-10 19:42:19.224
 26 | /20200806091734-add-stale-flag-to-features | 2022-01-10 19:42:19.226
 27 | /20200810200901-add-created-at-to-feature-types | 2022-01-10 19:42:19.229
 28 | /20200928194947-add-projects | 2022-01-10 19:42:19.234
 29 | /20200928195238-add-project-id-to-features | 2022-01-10 19:42:19.237
 30 | /20201216140726-add-last-seen-to-features | 2022-01-10 19:42:19.239
 31 | /20210105083014-add-tag-and-tag-types | 2022-01-10 19:42:19.25
 32 | /20210119084617-add-addon-table | 2022-01-10 19:42:19.255
 33 | /20210121115438-add-deprecated-column-to-strategies | 2022-01-10 19:42:19.257
 34 | /20210127094440-add-tags-column-to-events | 2022-01-10 19:42:19.259
 35 | /20210208203708-add-stickiness-to-context | 2022-01-10 19:42:19.262
 36 | /20210212114759-add-session-table | 2022-01-10 19:42:19.268
 37 | /20210217195834-rbac-tables | 2022-01-10 19:42:19.276
 38 | /20210218090213-generate-server-identifier | 2022-01-10 19:42:19.28
 39 | /20210302080040-add-pk-to-client-instances | 2022-01-10 19:42:19.284
 40 | /20210304115810-change-default-timestamp-to-now | 2022-01-10 19:42:19.287
 41 | /20210304141005-add-announce-field-to-application | 2022-01-10 19:42:19.29
 42 | /20210304150739-add-created-by-to-application | 2022-01-10 19:42:19.293
 43 | /20210322104356-api-tokens-table | 2022-01-10 19:42:19.299
 44 | /20210322104357-api-tokens-convert-enterprise | 2022-01-10 19:42:19.303
 45 | /20210323073508-reset-application-announcements | 2022-01-10 19:42:19.306
 46 | /20210409120136-create-reset-token-table | 2022-01-10 19:42:19.314
 47 | /20210414141220-fix-misspellings-in-role-descriptions | 2022-01-10 19:42:19.318
 48 | /20210415173116-rbac-rename-roles | 2022-01-10 19:42:19.322
 49 | /20210421133845-add-sort-order-to-strategies | 2022-01-10 19:42:19.328
 50 | /20210421135405-add-display-name-and-update-description-for-strategies | 2022-01-10 19:42:19.333
 51 | /20210423103647-lowercase-all-emails | 2022-01-10 19:42:19.339
 52 | /20210428062103-user-permission-to-rbac | 2022-01-10 19:42:19.344
 53 | /20210428103923-onboard-projects-to-rbac | 2022-01-10 19:42:19.349
 54 | /20210504101429-deprecate-strategies | 2022-01-10 19:42:19.353
 55 | /20210520171325-update-role-descriptions | 2022-01-10 19:42:19.357
 56 | /20210602115555-create-feedback-table | 2022-01-10 19:42:19.368
 57 | /20210610085817-features-strategies-table | 2022-01-10 19:42:19.382
 58 | /20210615115226-migrate-strategies-to-feature-strategies | 2022-01-10 19:42:19.385
 59 | /20210618091331-project-environments-table | 2022-01-10 19:42:19.391
 60 | /20210618100913-add-cascade-for-user-feedback | 2022-01-10 19:42:19.395
 61 | /20210624114602-change-type-of-feature-archived | 2022-01-10 19:42:19.401
 62 | /20210624114855-drop-strategies-column-from-features | 2022-01-10 19:42:19.405
 63 | /20210624115109-drop-enabled-column-from-features | 2022-01-10 19:42:19.408
 64 | /20210625102126-connect-default-project-to-global-environment | 2022-01-10 19:42:19.412
 65 | /20210629130734-add-health-rating-to-project | 2022-01-10 19:42:19.416
 66 | /20210830113948-connect-projects-to-global-envrionments | 2022-01-10 19:42:19.42
 67 | /20210831072631-add-sort-order-and-type-to-env | 2022-01-10 19:42:19.426
 68 | /20210907124058-add-dbcritic-indices | 2022-01-10 19:42:19.442
 69 | /20210907124850-add-dbcritic-primary-keys | 2022-01-10 19:42:19.449
 70 | /20210908100701-add-enabled-to-environments | 2022-01-10 19:42:19.456
 71 | /20210909085651-add-protected-field-to-environments | 2022-01-10 19:42:19.462
 72 | /20210913103159-api-keys-scoping | 2022-01-10 19:42:19.467
 73 | /20210915122001-add-project-and-environment-columns-to-events | 2022-01-10 19:42:19.477
 74 | /20210920104218-rename-global-env-to-default-env | 2022-01-10 19:42:19.483
 75 | /20210921105032-client-api-tokens-default | 2022-01-10 19:42:19.487
 76 | /20210922084509-add-non-null-constraint-to-environment-type | 2022-01-10 19:42:19.491
 77 | /20210922120521-add-tag-type-permission | 2022-01-10 19:42:19.495
 78 | /20210928065411-remove-displayname-from-environments | 2022-01-10 19:42:19.499
 79 | /20210928080601-add-development-and-production-environments | 2022-01-10 19:42:19.503
 80 | /20210928082228-connect-default-environment-to-all-existing-projects | 2022-01-10 19:42:19.507
 81 | /20211004104917-client-metrics-env | 2022-01-10 19:42:19.52
 82 | /20211011094226-add-environment-to-client-instances | 2022-01-10 19:42:19.526
 83 | /20211013093114-feature-strategies-parameters-not-null | 2022-01-10 19:42:19.528
 84 | /20211029094324-set-sort-order-env | 2022-01-10 19:42:19.531
 85 | /20211105104316-add-feature-name-column-to-events | 2022-01-10 19:42:19.534
 86 | /20211105105509-add-predata-column-to-events | 2022-01-10 19:42:19.537
 87 | /20211108130333-create-user-splash-table | 2022-01-10 19:42:19.543
 88 | /20211109103930-add-splash-entry-for-users | 2022-01-10 19:42:19.546
 89 | /20211126112551-disable-default-environment | 2022-01-10 19:42:19.549
 90 | /20211130142314-add-updated-at-to-projects | 2022-01-10 19:42:19.552
 91 | /20211209205201-drop-client-metrics | 2022-01-10 19:42:19.555
(91 rows)

So far so good, let's update the Unleash connection parameters in server-dev.ts file back to CockroachDB as above. Unleash uses knex for database connection management. CockroachDB has its own dialect when it comes to knex. We have to change the client before we can proceed. Find the db-pool.ts file and edit it.

Change the client line from pg to cockroachdb

- client: 'pg',
+ client: 'cockroachdb',

export function createDb({
 db,
 getLogger,
}: Pick<IUnleashConfig, 'db' | 'getLogger'>): Knex {
 const logger = getLogger('db-pool.js');
 return knex({
 client: 'cockroachdb',
 version: db.version,
 connection: db,
 pool: db.pool,
 searchPath: db.schema,
 asyncStackTraces: true,
 log: {
 debug: (msg) => logger.debug(msg),
 warn: (msg) => logger.warn(msg),
 error: (msg) => logger.error(msg),
 },
 });
}

The last step is to start the application again.

yarn install
yarn start:dev

10:28:48 AM - Starting compilation in watch mode...


10:28:56 AM - Found 0 errors. Watching for file changes.
[2022-01-14T10:28:57.646] [DEBUG] server-impl.js - DB migration: start
[2022-01-14T10:28:57.778] [DEBUG] server-impl.js - DB migration: end
[2022-01-14T10:28:57.833] [DEBUG] /middleware/api-token.ts - Enabling api-token middleware
[2022-01-14T10:28:57.834] [DEBUG] /middleware/rbac-middleware.ts - Enabling RBAC middleware
[2022-01-14T10:28:57.849] [INFO] server-impl.js - Unleash has started. { address: '::', family: 'IPv6', port: 4242 }
[2022-01-14T10:28:57.850] [DEBUG] server-impl.js - Registering graceful shutdown

We can now navigate to the Unleash UI and authenticate with the default credentials.

And we're in!

I am not going to cover further than that as I've done some superficial testing of Unleash with CockroachDB and it worked most of the time. A full end-to-end test of the integration is still required. I'm merely demonstrating the migration story in this article.

The real solution is to make CockroachDB a first-class citizen as we've covered the running of Unleash atop CockroachDB but the elephant in the room is migrations, as they still don't work for initial deployment and likely continuous operation of Unleash. The longer-term solution is below:

Solution

Considering the original problem was with a migration, let's take a look at the 20160618193924-add-strategies-to-features.js file, we see the following code block.

exports.up = function (db, callback) {
 db.runSql(
 `
--create new strategies-column
ALTER TABLE features ADD "strategies" json;

--populate the strategies column
UPDATE features
SET strategies = ('[{"name":"'||f.strategy_name||'","parameters":'||f.parameters||'}]')::json
FROM features as f
WHERE f.name = features.name;

--delete old strategy-columns
ALTER TABLE features DROP COLUMN "strategy_name";
ALTER TABLE features DROP COLUMN "parameters";
 `,
 callback,
 );
};

Recall the original error message.

[2022-01-14T09:22:33.542] [ERROR] server-impl.js - Failed to migrate db error: column "strategies" does not exist

If you know something about CockroachDB schema changes currently in 21.2 and below, schema change jobs are executed asynchronously and the current recommendation is to separate DML and DDL statements into their own migrations.

This coincides with what Unleash team was seeing in the initial investigation of the issue. The solution is to split up the migration into 3 different steps. The quickest solution is to make 2 more copies of the migration file and name it in the order of execution

20160618193924-add-strategies-to-features1.js 20160618193924-add-strategies-to-features3.js
20160618193924-add-strategies-to-features2.js

The first file will include the first ALTER statement.

'use strict';

exports.up = function (db, callback) {
 db.runSql(
 `
--create new strategies-column
ALTER TABLE features ADD "strategies" json;
 `,
 callback,
 );
};

exports.down = function (db, callback) {
 db.runSql(
 `
--create old columns
ALTER TABLE features ADD "parameters" json;
ALTER TABLE features ADD "strategy_name" varchar(255);
 `,
 callback,
 );
};

The second file will include the UPDATE statement.

'use strict';

exports.up = function (db, callback) {
 db.runSql(
 `
--populate the strategies column
UPDATE features
SET strategies = ('[{"name":"'||f.strategy_name||'","parameters":'||f.parameters||'}]')::json
FROM features as f
WHERE f.name = features.name;
 `,
 callback,
 );
};

exports.down = function (db, callback) {
 db.runSql(
 `
--populate old columns
UPDATE features
SET strategy_name = f.strategies->0->>'name',
 parameters = f.strategies->0->'parameters'
FROM features as f
WHERE f.name = features.name;
 `,
 callback,
 );
};

The third file will include the last ALTER statement.

'use strict';

exports.up = function (db, callback) {
 db.runSql(
 `
--delete old strategy-columns
ALTER TABLE features DROP COLUMN "strategy_name";
ALTER TABLE features DROP COLUMN "parameters";
 `,
 callback,
 );
};

exports.down = function (db, callback) {
 db.runSql(
 `
--drop new column
ALTER TABLE features DROP COLUMN "strategies";
 `,
 callback,
 );
};

CockroachDB supports multiple statements in schema change but we don't support mixing DML and DDL together today. Work is ongoing to address this gap in the near future!

Drop the existing database and re-create it.

DROP DATABASE unleash CASCADE;
CREATE DATABASE unleash;

Start the application again.

yarn install
yarn start:dev

10:50:20 AM - Starting compilation in watch mode...


10:50:27 AM - Found 0 errors. Watching for file changes.
[2022-01-14T10:50:29.054] [DEBUG] server-impl.js - DB migration: start
[2022-01-14T10:50:36.076] [ERROR] server-impl.js - Failed to migrate db error: column "parameters" does not exist
 length: 105,
 severity: 'ERROR',
 code: '42703',
 detail: undefined,
 hint: undefined,
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'column_resolver.go',
 line: '196',
 routine: 'NewUndefinedColumnError'
}
error: column "parameters" does not exist
 length: 105,
 severity: 'ERROR',
 code: '42703',
 detail: undefined,
 hint: undefined,
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'column_resolver.go',
 line: '196',
 routine: 'NewUndefinedColumnError'
}

Notice, it's a different error now, which means we either broke something else or we successfully advanced to the next failing migration!

Let's look at the tables, recall we had 4 tables in the original migration.

root@:26257/unleash> show tables;
 schema_name | table_name | type | owner | estimated_row_count | locality
--------------+---------------------+-------+-------+---------------------+-----------
 public | client_applications | table | root | 0 | NULL
 public | client_instances | table | root | 0 | NULL
 public | client_metrics | table | root | 0 | NULL
 public | events | table | root | 1 | NULL
 public | features | table | root | 0 | NULL
 public | migrations | table | root | 6 | NULL
 public | strategies | table | root | 1 | NULL
(7 rows)

We were able to advance! Let's look at the migrations table.

root@:26257/unleash> select * from migrations;
 id | name | run_on
---------------------+-------------------------------------------------------+--------------------------
 727793761311883265 | /20141020151056-initial-schema | 2022-01-14 10:50:29.696
 727793761921007617 | /20141110144153-add-description-to-features | 2022-01-14 10:50:29.882
 727793765553864705 | /20141117200435-add-parameters-template-to-strategies | 2022-01-14 10:50:30.991
 727793768073625601 | /20141117202209-insert-default-strategy | 2022-01-14 10:50:31.76
 727793768659845121 | /20141118071458-default-strategy-event | 2022-01-14 10:50:31.939
 727793768965963777 | /20141215210141-005-archived-flag-to-features | 2022-01-14 10:50:32.032
 727793772693061633 | /20150210152531-006-rename-eventtype | 2022-01-14 10:50:33.169
 727793772928466945 | /20160618193924-add-strategies-to-features1 | 2022-01-14 10:50:33.241
 727793776370712577 | /20160618193924-add-strategies-to-features2 | 2022-01-14 10:50:34.292
 727793776680828929 | /20160618193924-add-strategies-to-features3 | 2022-01-14 10:50:34.387
 727793780668694529 | /20161027134128-create-metrics | 2022-01-14 10:50:35.603
 727793781111259137 | /20161104074441-create-client-instances | 2022-01-14 10:50:35.738
 727793781682929665 | /20161205203516-create-client-applications | 2022-01-14 10:50:35.913
(13 rows)

We were able to advance through 20161027134128-create-metrics, 20161104074441-create-client-instances and 20161205203516-create-client-applications migrations!

I'm going to wrap it up here and spoil the surprise by saying that I've attempted to execute all subsequent failing migrations. Eventually, there's a happy path but this article is getting lengthy already.

Next Steps

The next step with Unleash and CockroachDB is to collaborate and introduce a CockroachDB specific version of the features management platform. There's work and architectural changes necessary to make some of the migrations work in the way CockroachDB expects, for example, one of the migrations is using the DELETE USING syntax:

Failed to migrate db error: at or near "where": syntax error: unimplemented: this syntax
 length: 460,
 severity: 'ERROR',
 code: '0A000',
 detail: 'source SQL:\n' +
 'DELETE FROM client_instances a USING client_instances b WHERE a.app_name = b.app_name AND a.instance_id = b.instance_id AND a.created_at < b.created_at\n' +
 ' ^',
 hint: 'You have attempted to use a feature that is not yet implemented.\n' +
 'See: https://go.crdb.dev/issue-v/40963/v21.2',
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'lexer.go',
 line: '217',
 routine: 'UnimplementedWithIssueDetail'
}
error: at or near "where": syntax error: unimplemented: this syntax length: 460,
 severity: 'ERROR',
 code: '0A000',
 detail: 'source SQL:\n' +
 'DELETE FROM client_instances a USING client_instances b WHERE a.app_name = b.app_name AND a.instance_id = b.instance_id AND a.created_at < b.created_at\n' +
 ' ^',
 hint: 'You have attempted to use a feature that is not yet implemented.\n' +
 'See: https://go.crdb.dev/issue-v/40963/v21.2',
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'lexer.go',
 line: '217',
 routine: 'UnimplementedWithIssueDetail'

We do not support the syntax today, so the workaround is to rewrite the statement with something like this:

DELETE FROM client_instances a
WHERE a.instance_id IN
(
SELECT a.app_name
FROM client_instances a
INNER JOIN client_instances b ON a.app_name = b.app_name
WHERE a.app_name = b.app_name AND a.instance_id = b.instance_id AND a.created_at < b.created_at
);

And that's just some of the failing errors along with the original where DML and DDL are intermixed. We're going to continue our wonderful collaboration with the Unleash team, whom I thank wholly for their incredible support throughout this experiment. Stay tuned!