Managing Cockroach Database Schema Changes

system · October 27, 2021, 3:07pm

Originally published at: Managing Cockroach Database Schema Changes | Liquibase.com

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

Download and install Liquibase on your local system
Make sure CockroachDB is running in local Docker containers
Download the JDBC driver jar file for PostgreSQL and copy it into your liquibase/lib directory. (For example, mine was “/Users/molivas/Documents/liquibase-4.0.0/lib” since I have Liquibase installed in the /Users/molivas/Documents/liquibase-4.0.0 directory
JDBC Connection String to your Docker instances. Here’s what mine looks like: jdbc:postgresql://localhost:26257/bank?sslmode=disable

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:

CREATE DATABASE bank;
CREATE USER max;
GRANT admin TO max;
q

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:

changeLogFile: mainchangelog.sql
driver: org.postgresql.Driver
url: jdbc:postgresql://localhost:26257/bank?sslmode=disable
username: max

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;

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.

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 change

Let’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

Roll a change forward and update the primary key

Let’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;

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

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 up

Hopefully 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.

Topic		Replies	Views
Changing tables to different schema General Discussion	4	1630	January 24, 2011
How to Handle Manual Changes?	9	2875	January 11, 2024
Advice on how to create a schema for defaultSchemaName General Discussion	5	4534	November 26, 2022
Spring-based schema update management General Discussion	2	459	March 11, 2011
Copy database schema created by liquibase into another database by java code General Discussion	5	2613	December 20, 2019