August 29, 2020

Managing Cockroach Database Schema Changes

Anyone that manages a Cockroach database knows that at some point you’ll need to modify your database schemas. Perhaps you need to store new kinds of information. Or maybe you’ll add a field to a table or even change a primary key or two. Regardless of the reason, application requirements change and so your database will need to change.

CockroachDB has recently improved compatibility with Liquibase so users can take advantage of faster and easier schema migrations. Here’s a quick overview of how to apply schema changes using Liquibase with your Cockroach database.

Prerequisites

My setup

  • Mac
  • Visual Studio Code

Once the Docker images are up and running, I’ll create a bank database with a user named Max. First, let’s go to the Cockroach command line.

docker exec -it roach1 ./cockroach sql --insecure

Then, to create our bank database, use the following commands:

Now, let’s configure our Liquibase properties. To do that, create a cockroachdocker folder. In the folder, create a liquibase.properties file to access your database. Here’s what should be included in the liquibase.properties file:

Making changes to the Cockroach database

Now that we have the connection information set up, it’s time to start making changes to our database.

Let’s create a formatted SQL file to update your database. (Note: Liquibase let’s you use plain SQL, XML, YAML, or JSON changesets to update your database.)

We’ll name the file mainchangelog.sql and edit it to add an employees table in the bank database. We’re also going to make sure there’s a rollback script in place.

> touch mainchangelog.sql
> code mainchangelog.sql
--liquibase formatted sql
--changeset mike:createtableemployees
create table employees
(
   id      int            not null primary key default unique_rowid(),
   name    varchar(128)   not null,
   salary  numeric(19, 2) not null,
   type    varchar(25)    not null
);
--rollback drop table employees;

Let’s add some data, too. Enter in the following data after the last line in the file mainchangelog.sql.

--changeset mike:insertintoemployees
INSERT INTO employees (id, name, salary, type) VALUES ('1', 'Emma', 4000.00, 'Full-Time');
INSERT INTO employees (id, name, salary, type) VALUES ('2', 'Mike', 4000.00, 'Part-Time');
INSERT INTO employees (id, name, salary, type) VALUES ('3', 'Val', 4000.00, 'Full-Time');
INSERT INTO employees (id, name, salary, type) VALUES ('4', 'Charlotte', 4000.00, 'Temporary');
--rollback delete from employees where id = '1';
--rollback delete from employees where id = '2';
--rollback delete from employees where id = '3';
--rollback delete from employees where id = '4';

Now, the file looks like this:

--liquibase formatted sql
--changeset mike:createtableemployees-1
create table employees
(
   id      int            not null primary key default unique_rowid(),
   name    varchar(128)   not null,
   salary  numeric(19, 2) not null,
   type    varchar(25)    not null
);
--rollback drop table employees;
 
--changeset mike:insertintoemployees-2
INSERT INTO employees (id, name, salary, type) VALUES ('1', 'Emma', 4000.00, 'Full-Time');
INSERT INTO employees (id, name, salary, type) VALUES ('2', 'Mike', 4000.00, 'Part-Time');
INSERT INTO employees (id, name, salary, type) VALUES ('3', 'Val', 4000.00, 'Full-Time');
INSERT INTO employees (id, name, salary, type) VALUES ('4', 'Charlotte', 4000.00, 'Temporary');
--rollback delete from employees where id = '1';
--rollback delete from employees where id = '2';
--rollback delete from employees where id = '3';
--rollback delete from employees where id = '4';

We have created a changelog and can execute it against the database by running the liquibase update command.

~/cockroachdocker>  liquibase update   

You’ll see something like this:

Liquibase Community 4.0.0 by Datical
Starting Liquibase at 23:30:34 (version 4.0.0 #19 built at 2020-07-13 19:45+0000)
Liquibase: Update has been successful.

Let’s go back to the Cockroach client and take a look at the database and the databasechangelog table that were created as a result of running the liquibase update command.

This time we are connecting to the second instance in the cluster (roach2). Run the following commands:

~/cockroachdocker>docker exec -it roach2 ./cockroach sql --insecure

select * from bank.employees;
root@:26257/defaultdb> select * from bank.employees;
id |   name | salary  |   type
-----+-----------+---------+------------
1 | Emma  | 4000.00 | Full-Time
2 | Mike  | 4000.00 | Part-Time
3 | Val   | 4000.00 | Full-Time
4 | Charlotte | 4000.00 | Temporary
(4 rows)
Time: 69.745075ms
root@:26257/defaultdb> select * from bank.databasechangelog;
id       | author | filename  |       dateexecuted       | orderexecuted | exectype |           md5sum           | description | comments | tag  | liquibase | contexts | labels | deployment_id
-------------------------+--------+-------------------+----------------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+----------------
createtableemployees-1 | mike   | mainchangelog.sql | 2020-08-21 11:47:55.349736+00:00 |         1 | EXECUTED | 8:d3d7e087ebe584c93412459393fd4cdf | sql     |      | NULL | 4.0.0 | NULL | NULL   | 8028475303
insertintoemployees-2  | mike   | mainchangelog.sql | 2020-08-21 11:47:55.44052+00:00  |         2 | EXECUTED | 8:2d39e56d48c3dfd73ee36ecb212f667c | sql     |      | NULL | 4.0.0 | NULL | NULL   | 8028475303
(2 rows)
Time: 15.610848ms

Use q to get out of the Cockroach client and back to your local command line.

Add a column

Now, let’s add a UUID column to the table. Add the following lines to the end of your mainchangelog.sql file:

--changeset mike:adduuidcolumn
/* Add new UUID-typed column */
ALTER TABLE employees ADD COLUMN unique_id UUID NOT NULL DEFAULT gen_random_uuid();
--rollback alter table employees drop column unique_id;

Let’s run the Liquibase update command again.

liquibase update

Go back to the Cockroach command line.

~/cockroachdocker>docker exec -it roach2 ./cockroach sql --insecure

select * from bank.employees;

Let’s take a look at how the database has changed.

root@:26257/defaultdb> select * from bank.employees;
id |   name | salary  |   type |          unique_id
-----+-----------+---------+-----------+---------------------------------------
1 | Emma  | 4000.00 | Full-Time | 6b241c18-43a7-40da-aa6d-2bb2e856004d
2 | Mike  | 4000.00 | Part-Time | 99aaaba5-eb87-4b21-a78b-da101e4a3220
3 | Val   | 4000.00 | Full-Time | 4ead0486-449f-45e0-8b0f-c2c15d1e0131
4 | Charlotte | 4000.00 | Temporary | b8581647-7600-4bbf-8d7f-cbd609cdf4fd
(4 rows)
Time: 19.251119ms
root@:26257/defaultdb>

Use q to get back to your local command line.

The database looks good! But I want to remove that unique_id column.

Rollback a changeLet’s rollback the last change and remove it. Since we added the rollback scripts for each change, we made this really easy on ourselves.

liquibase rollbackCount 1

When we check the database again, it has removed the unique_id column.

~/cockroachdocker>docker exec -it roach3 ./cockroach sql --insecure
root@:26257/defaultdb> select * from bank.employees;
id |   name | salary  |   type
-----+-----------+---------+-----------
3 | Val   | 4000.00 | Full-Time
1 | Emma  | 4000.00 | Full-Time
4 | Charlotte | 4000.00 | Temporary
2 | Mike  | 4000.00 | Part-Time
(4 rows)

Roll a change forward and update the primary keyLet’s update the changelog again. I want to roll forward and add the unique key back and I also want to change the primary key. Update the mainchangelog.sql file to change the primary key to the unique id you just added:

--changeset mike:updateprimarykey
/* Change primary key */
ALTER TABLE employees ALTER PRIMARY KEY USING COLUMNS (unique_id);
--rollback alter table employees alter primary key using columns (id);

Run liquibase update to alter the table.

~/cockroachdocker> liquibase update                             
Liquibase Community 4.0.0 by Datical
Starting Liquibase at 23:39:50 (version 4.0.0 #19 built at 2020-07-13 19:45+0000)
Liquibase: Update has been successful.

Let’s go ahead and check the database one last time to see our changes. This time, let’s use node 3.

~/cockroachdocker>docker exec -it roach3 ./cockroach sql --insecure

select * from bank.databasechangelog;

select * from bank.employees;

SHOW CREATE TABLE bank.employees;
select * from bank.databasechangelog;
root@:26257/defaultdb> select * from bank.databasechangelog;
    id       | author | filename  |       dateexecuted       | orderexecuted | exectype |           md5sum           | description | comments | tag  | liquibase | contexts | labels | deployment_id
-------------------------+--------+-------------------+----------------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+----------------
  createtableemployees-1 | mike   | mainchangelog.sql | 2020-08-21 11:47:55.349736+00:00 |         1 | EXECUTED | 8:d3d7e087ebe584c93412459393fd4cdf | sql     |      | NULL | 4.0.0 | NULL | NULL   | 8028475303
  insertintoemployees-2  | mike   | mainchangelog.sql | 2020-08-21 11:47:55.44052+00:00  |         2 | EXECUTED | 8:2d39e56d48c3dfd73ee36ecb212f667c | sql     |      | NULL | 4.0.0 | NULL | NULL   | 8028475303
  adduuidcolumn      | mike   | mainchangelog.sql | 2020-08-21 11:54:15.656146+00:00 |         3 | EXECUTED | 8:642d77b4d3e212a93a8805ca674c8f33 | sql     |      | NULL | 4.0.0 | NULL | NULL   | 8028855443
  updateprimarykey   | mike   | mainchangelog.sql | 2020-08-21 11:54:15.883018+00:00 |         4 | EXECUTED | 8:51e849b0f5ab42e23b3cff2d57eae552 | sql     |      | NULL | 4.0.0 | NULL | NULL   | 8028855443
(4 rows)
Time: 8.460297ms
root@:26257/defaultdb>

While we’re here, let’s also check the details of the database.

root@:26257/defaultdb> select * from bank.employees;
  id |   name | salary  |   type |          unique_id
-----+-----------+---------+-----------+---------------------------------------
  3 | Val   | 4000.00 | Full-Time | 2e6292d5-ee30-4633-a1a3-47b10c5c36a0
   1 | Emma  | 4000.00 | Full-Time | 6f79f808-7681-491b-af3b-56451f71751b
  4 | Charlotte | 4000.00 | Temporary | af78c216-1231-41c5-bf40-90bd096200f7
   2 | Mike  | 4000.00 | Part-Time | eb55c147-9467-455f-ad9c-f27bb5612231
(4 rows)
Time: 62.541268ms

Here’s the layout of the table.

root@:26257/defaultdb> SHOW CREATE TABLE bank.employees;
   table_name   |                 create_statement
------------------------+-----------------------------------
  bank.public.employees | CREATE TABLE employees (
                     | id INT8 NOT NULL DEFAULT unique_rowid(),
                     | name VARCHAR(128) NOT NULL,
                     | salary DECIMAL(19,2) NOT NULL,
                     | type VARCHAR(25) NOT NULL,
                     | unique_id UUID NOT NULL DEFAULT gen_random_uuid(),
                     | CONSTRAINT "primary" PRIMARY KEY (unique_id ASC),
                     | UNIQUE INDEX employees_id_key (id ASC),
                     | FAMILY "primary" (id, name, salary, type, unique_id)
                     | )
(1 row)

You’ll notice that the unique_id is back and it’s also now the primary key.

Wrapping upHopefully this little demonstration has given you a taste of using Liquibase and CockroachDB together to help manage your database schema changes quickly and easily. For more information on using Liquibase with Cockroach, check out our Docs.

Note: This blog is a variation of the great blog written by the excellent folks are Cockroach Labs. Check out their tutorial.

Mike Olivas
Mike Olivas
Senior Solutions Architect
Share on: