liquibase: adding a table
When not using liquibase, to add a new table, you simple create the @Entity class and the @Repository class.
When using liquibase, there is an additional step: you must generate the changelog file and store it in src/main/resources/db/changes
You can generate the changelog by hand, but it is easier to use a tool to do it. This guide walks you through the process with an example.
Example: Adding a course table
Step 1: Create the @Entity and @Repository classes
As an example, suppose you are adding the following @Entity (imports omitted to save space):
@Data
@AllArgsConstructor
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Builder
@Entity(name = "courses")
public class Course {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
private String name;
private String school;
private String term;
private LocalDateTime start;
private LocalDateTime end;
private String githubOrg;
}
We also need the @Repository class, for example:
@Repository
public interface CourseRepository extends CrudRepository<Course, Integer> {
}
Create these files first. Then you are ready for the next step.
Step 2: Temporarily modify application.properties so that the table is created
Next, find this section in src/main/resources/application.properties:
# There are two settings for spring.jpa.hibernate.ddl-auto, namely "update" and "none"
# Normally the value should be none, because we are using liquibase to manage migrations
# However, temporarily, the value may need to be "update" when you want tables created
# or updated automatically by Spring Boot.
spring.jpa.hibernate.ddl-auto=none
# spring.jpa.hibernate.ddl-auto=update
As explained in the comment, you need to temporarily switch this to:
# spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.ddl-auto=update
And then run the command:
mvn spring-boot:run
You do not need to run the frontend. The web browser will show this:
Click on the link for the h2-console and connect. You should see the new database tables have been created, for example:
Step 3: Change application.properties back
Now CTRL/C to stop the backend running, and change the value in src/main/resources/application.properties: back to:
spring.jpa.hibernate.ddl-auto=none
# spring.jpa.hibernate.ddl-auto=update
Step 4: Choose a filename for the change log
We next want to generate a change log, so we much choose a filename. The choice is important, because change logs in multiple file are applied in the order of their filenames (in alphabetical order, or more precisely, in “lexicographic order”.) We have chosen a naming convention to ensure the migrations are applied in the order we intend.
Look in the directory src/main/resources/db/migration/changes/ to see what the next avaiable number is; the naming convention we use in CS156 is a four digit number followed by an underscore, followed by a brief title that describes the migration. For example, we see:
The next available number is 0005_ and we are defining the course table, so we choose the name 0005_CreateCourseTable.json.
The full name is src/main/resources/db/migration/changes/0005_CreateCourseTable.json.
Step 5: Generate the full migration change log
The next step is to generate a full change log that represents the entire database; we’ll then edit it down to only the change that we want.
We can do that with the following command:
mvn liquibase:generateChangeLog -Dliquibase.outputChangeLogFile=src/main/resources/db/migration/changes/0005_CreateCourseTable.json
Step 6: Edit the full change log into the one you need
Now look over the file 0005_CreateCourseTable.json that was generated. You’ll see that it is actually now a set of changes that would produce the entire database. You’l need to:
- edit that down to just the changes that you are introducing in the
@Entityand@Repoisotryclasses that are in your pull request - change some of the autogenerated ids to ones that match the course naming conventions
In this example, the few lines of the file look like this:
{ "databaseChangeLog": [
{
"changeSet": {
"id": "1699899121513-1",
"author": "pconrad (generated)",
"changes": [
Make these changes (you can look at other migration files already in the project for examples):
| Before | After | Explanation |
|---|---|---|
"id": "1699899121513-1" | "id": "changeset-0005 | This name matches the filename. If we have multiple changesets in the same file, we can name them changeset-0005a, changeset-0005b, etc |
"author": "pconrad (generated)", | ` “author”: “pconrad”,` | The github id of the author of the change is sufficient. By the time we are done, this will no longer be the generated code |
Scrolling down further, we see that this first changeset is the creation of the Courses table, which is one of the two things we want to keep in this migration.
"changes": [
{
"createTable": {
"columns": [
{
"column": {
"autoIncrement": true,
"constraints": {
"primaryKey": true,
"primaryKeyName": "CONSTRAINT_6"
},
"name": "ID",
"type": "BIGINT"
}
},
{
"column": {
"name": "END",
"type": "TIMESTAMP"
}
},
{
"column": {
"name": "GITHUB_ORG",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "NAME",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "SCHOOL",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "START",
"type": "TIMESTAMP"
}
},
{
"column": {
"name": "TERM",
"type": "VARCHAR(255)"
}
}]
,
"tableName": "COURSES"
}
}]
Scrolling down further, we see another changeset; this one is for the CoursesStaff table. We’ll keep it too, but change the ids
Original:
{
"changeSet": {
"id": "1699899121513-2",
"author": "pconrad (generated)",
We’ll update this as follows.
{
"changeSet": {
"id": "changeset-0005b",
"author": "pconrad",
We also change the first id to "changeset-0005a" now that there are two of them.
As we look through the rest of the file, we see that it pertains to tables that were already in the database, we we simply:
- remove the remaining change sets
- remove the trailing comma on the last change set.
We are left with the following as our changeset:
{ "databaseChangeLog": [
{
"changeSet": {
"id": "changeset-0005a",
"author": "pconrad",
"changes": [
{
"createTable": {
"columns": [
{
"column": {
"autoIncrement": true,
"constraints": {
"primaryKey": true,
"primaryKeyName": "CONSTRAINT_6"
},
"name": "ID",
"type": "BIGINT"
}
},
{
"column": {
"name": "END",
"type": "TIMESTAMP"
}
},
{
"column": {
"name": "GITHUB_ORG",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "NAME",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "SCHOOL",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "START",
"type": "TIMESTAMP"
}
},
{
"column": {
"name": "TERM",
"type": "VARCHAR(255)"
}
}]
,
"tableName": "COURSES"
}
}]
}
},
{
"changeSet": {
"id": "changeset-0005b",
"author": "pconrad",
"changes": [
{
"createTable": {
"columns": [
{
"column": {
"autoIncrement": true,
"constraints": {
"primaryKey": true,
"primaryKeyName": "CONSTRAINT_2"
},
"name": "ID",
"type": "BIGINT"
}
},
{
"column": {
"name": "COURSE_ID",
"type": "BIGINT"
}
},
{
"column": {
"name": "GITHUB_ID",
"type": "INT"
}
}]
,
"tableName": "COURSE_STAFF"
}
}]
}
}
]}
Step 7: Add pre-conditions
The first four lines of each of our changesets for adding a new table now looks like this:
"changeSet": {
"id": "changeset-0005a",
"author": "pconrad",
"changes": [
We now want to add some code between the "author" and "changes" keys that will make sure that the migration to create the table doesn’t run if the table already exists. Here’s what that looks like for the courses table:
"preConditions": [
{
"onFail": "MARK_RAN"
},
{
"not": [
{
"tableExists": {
"tableName": "COURSES"
}
}
]
}
],
Copy and paste this into your migrations, changing the value of the tableName key to your table. Afterwards, the entire file looks like this:
{ "databaseChangeLog": [
{
"changeSet": {
"id": "changeset-0005a",
"author": "pconrad",
"preConditions": [
{
"onFail": "MARK_RAN"
},
{
"not": [
{
"tableExists": {
"tableName": "COURSES"
}
}
]
}
],
"changes": [
{
"createTable": {
"columns": [
{
"column": {
"autoIncrement": true,
"constraints": {
"primaryKey": true,
"primaryKeyName": "CONSTRAINT_6"
},
"name": "ID",
"type": "BIGINT"
}
},
{
"column": {
"name": "END",
"type": "TIMESTAMP"
}
},
{
"column": {
"name": "GITHUB_ORG",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "NAME",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "SCHOOL",
"type": "VARCHAR(255)"
}
},
{
"column": {
"name": "START",
"type": "TIMESTAMP"
}
},
{
"column": {
"name": "TERM",
"type": "VARCHAR(255)"
}
}]
,
"tableName": "COURSES"
}
}]
}
},
{
"changeSet": {
"id": "changeset-0005b",
"author": "pconrad",
"preConditions": [
{
"onFail": "MARK_RAN"
},
{
"not": [
{
"tableExists": {
"tableName": "COURSE_STAFF"
}
}
]
}
],
"changes": [
{
"createTable": {
"columns": [
{
"column": {
"autoIncrement": true,
"constraints": {
"primaryKey": true,
"primaryKeyName": "CONSTRAINT_2"
},
"name": "ID",
"type": "BIGINT"
}
},
{
"column": {
"name": "COURSE_ID",
"type": "BIGINT"
}
},
{
"column": {
"name": "GITHUB_ID",
"type": "INT"
}
}]
,
"tableName": "COURSE_STAFF"
}
}]
}
}
]}
Step 8: Test by running mvn spring-boot:run on localhost
Next, on localhost, run mvn spring-boot:run. This will check your syntax; if there’s an error in your JSON, you’ll find out now and hopefully you can fix it and try again.
If your syntax is correct, the first time you do this, as part of startup, you should see something like this:
2023-11-13 10:31:24.989 INFO 44817 --- [ restartedMain] liquibase.changelog : Marking ChangeSet: db/migration/changes/0005_CreateCourseTable.json::changeset-0005a::pconrad ran despite precondition failure due to onFail='MARK_RAN':
db/migration/changelog-master.json : Not precondition failed
2023-11-13 10:31:24.997 INFO 44817 --- [ restartedMain] liquibase.changelog : Marking ChangeSet: db/migration/changes/0005_CreateCourseTable.json::changeset-0005b::pconrad ran despite precondition failure due to onFail='MARK_RAN':
db/migration/changelog-master.json : Not precondition failed
This lets you know that liquibase has marked the migration as “RAN” (meaning, it has already been run), so it won’t try it again.
CTRL/C and run mvn spring-boot:run on localhost a second time. The second time, the output should be more like this:
2023-11-13 10:34:22.097 INFO 46913 --- [ restartedMain] liquibase.changelog : Reading resource: db/migration/changes/0001_CreateJobsTable.json
2023-11-13 10:34:22.196 INFO 46913 --- [ restartedMain] liquibase.changelog : Reading resource: db/migration/changes/0002_CreateUserEmailsTable.json
2023-11-13 10:34:22.199 INFO 46913 --- [ restartedMain] liquibase.changelog : Reading resource: db/migration/changes/0003_CreateUsersTable.json
2023-11-13 10:34:22.204 INFO 46913 --- [ restartedMain] liquibase.changelog : Reading resource: db/migration/changes/0004_AddIndexesAndForeignKeys.json
2023-11-13 10:34:22.208 INFO 46913 --- [ restartedMain] liquibase.changelog : Reading resource: db/migration/changes/0005_CreateCourseTable.json
This output shows that all of the migrations were processed in order with no errors.
Step 9: Test the migration on a dokku qa instance starting from a clean main
You are now ready to try the migrations on a dokku instance, which is where they really matter. A localhost instance is only for development and testing; we typically don’t store important real data there. But when we deploy to a dokku qa or dev instance, it’s a practice run for when we roll out the new changes to a production database. So if the database migration doesn’t go smoothly when you roll it out to a dokku instance, it’s a sign that things may go awry when your PR is merged into production, and the migration hits the production database.
So, pay close attention to whether the migration succeeds when rolling out to dokku. A good way to start is with a clean database that matches what’s on main. You can do that by first resetting the database of your qa instance, and then redeploying the main branch to it, like this:
Step 9a: Stop your qa instance
First, stop your qa instance. This is necessary, because otherwise you won’t be able to drop the database and recreate it (because the app will be using it.)
pconrad@dokku-00:~$ dokku ps:stop organic-qa
Stopping organic-qa
pconrad@dokku-00:~$
Step 9b: Reset qa database to an empty database
Next, connect to the postgres console, drop the database, and recreate it.
The command to connect is
dokku postgres:connect organic-qa-db
Once inside, the commands are as follows:
| Command | Explanation |
|---|---|
\c postgres | Disconnect from the organic_qa_db database, and connect to a different database, the postgres default database. This is needed because you can’t drop a database if you are connected to it. |
drop database organic_qa_db; | This drops the database: all data, and all table definitions. Don’t forget the semicolon, since this is an SQL statement |
create database organic_qa_db; | This recreates a brand new fresh database |
The next three commands are not necessary; you can just use \q at this point, but they help to illustrate what’s going on:
| Command | Explanation |
|---|---|
\c organic_qa_db | Reconnect to the original organic_qa_db database. |
\dt | `\dt\ is the “describe tables” command in postgres; we should see that there are no tables (aka “relations”) |
\q | Quit from postgres |
Here’s an example session:
pconrad@dokku-00:~$ dokku postgres:connect organic-qa-db
psql (15.2 (Debian 15.2-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
organic_qa_db=# \c postgres
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "postgres" as user "postgres". ^
postgres=# drop database organic_qa_db;
DROP DATABASE
postgres=# create database organic_qa_db;
CREATE DATABASE
postgres=# \c organic_qa_db
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "organic_qa_db" as user "postgres".
organic_qa_db=# \dt
Did not find any relations.
organic_qa_db=# \q
pconrad@dokku-00:~$
Step 9c: Deploy main branch to qa site to create database via existing migrations
Next, git:sync the main branch and deploy:
dokku git:sync organic-qa https://github.com/ucsb-cs156/proj-organic main
dokku ps:rebuild organic-qa
Step 9d: Verify that qa database has correct starting tables (before your new migrations)
Once the deploy is finished, use the postgres command line to verify that your tables were created by the main branch; the purpose is to get the database into the exact state it will be in prior to your migrations hitting it when your PR is merged into main, so that we can be sure they work as intended. You may have to repeat this test immediately before merging your PR, since the main branch may have changed by then, and indeed other migrations may have been added (in which case you may need to change the number of your migration.)
Once again, the command to connect is:
dokku postgres:connect organic-qa-db
Once inside, the commands are as follows:
| Command | Explanation |
|---|---|
\dt | Describe tables; make sure that they look like they should before the migration |
\d tablename | Only necessary if you are checking the details of a table (i.e. its column definitions, etc.) |
\q | Quit from postgres |
Here’s an example session:
pconrad@dokku-00:~$ dokku postgres:connect organic-qa-db
psql (15.2 (Debian 15.2-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
organic_qa_db=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+----------
public | databasechangelog | table | postgres
public | databasechangeloglock | table | postgres
public | jobs | table | postgres
public | useremails | table | postgres
public | users | table | postgres
(5 rows)
organic_qa_db=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
github_id | integer | | not null |
access_token | character varying(255) | | |
admin | boolean | | not null |
email | character varying(255) | | |
email_verified | boolean | | not null |
full_name | character varying(255) | | |
github_login | character varying(255) | | |
github_node_id | character varying(255) | | |
instructor | boolean | | not null |
last_online | timestamp without time zone | | |
picture_url | character varying(255) | | |
Indexes:
"github_id" PRIMARY KEY, btree (github_id)
Referenced by:
TABLE "useremails" CONSTRAINT "fk8wod0wrceoifbbpwone12smev" FOREIGN KEY (user_github_id) REFERENCES users(github_id) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "jobs" CONSTRAINT "fkkjpyguuyd5shxtabv9v5jpe6x" FOREIGN KEY (created_by_id) REFERENCES users(github_id) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "jobs" CONSTRAINT "jobs_created_by_id_fk" FOREIGN KEY (created_by_id) REFERENCES users(github_id) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "useremails" CONSTRAINT "useremails_user_github_id_fk" FOREIGN KEY (user_github_id) REFERENCES users(github_id) ON UPDATE RESTRICT ON DELETE RESTRICT
organic_qa_db=# \q
pconrad@dokku-00:~$
Step 9e: Commit the migrations and push to your github branch
If you haven’t already, commit the files for the entities, repository, and the change set to github. You need them on your branch so that you can deploy to the qa site.
Step 9f: Deploy new branch to qa site
Now, you are ready to deploy your branch with the migrations to the qa site:
dokku git:sync organic-qa https://github.com/ucsb-cs156/proj-organic your-branch-name
dokku ps:rebuild organic-qa
When the deploy finishes successfully with this message:
=====> Application deployed:
http://organic-qa.dokku-00.cs.ucsb.edu
https://organic-qa.dokku-00.cs.ucsb.edu
pconrad@dokku-00:~$
Then it’s time to check the database. No need to fire up the app at first; we can check it directly as shown in the next step.
Step 9g: Examine the new qa database to see if migrations worked.
After this is deployed, repeat the command to examine the database:
dokku postgres:connect organic-qa-db
Once inside, use these commands again to examine the database schema (the definitions of tables, columns, indexes, etc.)
| Command | Explanation |
|---|---|
\dt | Describe tables; make sure that they look like they should before the migration |
\d tablename | Only necessary if you are checking the details of a table (i.e. its column definitions, etc.) |
\q | Quit from postgres |
Here’s an example of what that looks like:
pconrad@dokku-00:~$ dokku postgres:connect organic-qa-db
psql (15.2 (Debian 15.2-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
organic_qa_db=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+----------
public | course_staff | table | postgres
public | courses | table | postgres
public | databasechangelog | table | postgres
public | databasechangeloglock | table | postgres
public | jobs | table | postgres
public | useremails | table | postgres
public | users | table | postgres
(7 rows)
organic_qa_db=# \d courses
Table "public.courses"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+----------------------------------
id | bigint | | not null | generated by default as identity
END | timestamp without time zone | | |
github_org | character varying(255) | | |
name | character varying(255) | | |
school | character varying(255) | | |
start | timestamp without time zone | | |
term | character varying(255) | | |
Indexes:
"constraint_6" PRIMARY KEY, btree (id)
organic_qa_db=# \q
pconrad@dokku-00:~$
Step 9h: Try the app
Now, try the app itself by visiting the url (e.g. https://organic-qa.dokku-00.cs.ucsb.edu). At the end of the day, if the database tables look perfect, but the app is broken, that’s not good, so be sure to check that too.
But as long as everything seems to be working, then you are finished.
Done!
If everything looks as it should, congratualations: you have successfully built a liquibase migration to create a new table.