Migrating Feature Toggles With Unleash and CockroachDB
This tutorial will demonstrate a real-world migration story. Migrations are not easy and challenges exist no matter the source database, including PostgreSQL.
CORE
·
Join the DZone community and get the full member experience.
Join For FreeMotivation
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!
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments