Database Continous Integration (CI) and Database Continuous Delivery (CD)

Database continuous integration (CI) is the process of frequently integrating database schema and logic changes into application development, aiming to provide immediate feedback on any issues. Database continuous delivery (CD) focuses on quickly and safely deploying those changes to production, ensuring that both application and database code are always in a deployable state. Together, database CI/CD reduces time-to-market and delivers consistent end-user value through frequent, high-quality releases.

Database Project Scaffolding

When initializing SQLcl Projects (using the project init command), SQLcl creates a set of files that are used to manage the creation of files and changelogs.

There are three folders in the initial setup:

• .dbtools: This folder contains the following:

  • Project filters that are used by project export to filter out objects that will be exported.
  • Project format rules that are used to format the code when it is exported.
  • Project configuration settings.

• src: This folder is where the exported objects from the database get placed. This is broken down by schema and objects types.

• dist: The release artifacts are created in this folder. This folder gets populated by the project stage command and the project release command compresses its contents to create a release artifact.

──.dbtools
│   ├── filters
│   │   └── project.filters
│   ├── project.config.json
│   └── project.sqlformat.xml
├── dist
│   └── install.sql
└── src
│   └── database
├── .gitignore
└── README.md

Advanced Database Object Explorer

One of the main features of SQLcl Projects is exporting database objects to the source control repository. The export includes the SQL needed to create the object and a "snapshot" of the object's metadata. The snapshot is stored as a comment and is prefixed with sqlcl_snapshot. It contains the following information:

• Checksum calculated on the exported code
• Object type
• Object name
• Schema name
• SXML representation of the object as exported (see DBMS_METADATA SXML)

src/database/<SCHEMA>/<OBJECT_TYPE>/<OBJECT_NAME>[.sql|.pks|.pkb]

create table hr.doc (
    id number
);
-- sqlcl_snapshot {"hash":"d992dceaa87a936e8242bdc81201f93030084ab5","type":"TABLE","name":"DOC","schemaName":"HR","sxml":"<TABLE xmlns=\"http://xmlns.abc.com/ku\" version=\"1.0\"> <SCHEMA>HR</SCHEMA> <NAME>DOC</NAME> <RELATIONAL_TABLE><COL_LIST> <COL_LIST_ITEM><NAME>ID</NAME><DATATYPE>NUMBER</DATATYPE> </COL_LIST_ITEM></COL_LIST><DEFAULT_COLLATION>USING_NLS_COMP</DEFAULT_COLLATION><PHYSICAL_PROPERTIES> <HEAP_TABLE></HEAP_TABLE></PHYSICAL_PROPERTIES> </RELATIONAL_TABLE></TABLE>"}

Automated Database Migration Script Generation

As developers add changes to the database, they are exported to the branch. Git branches are used to identify changes between units of work. Using the diffs between the branches, the changed files are identified.

Each object file has a specific checksum and, in some cases, specific SXML for the specification of the object. Using Oracle DBMS_METADATA_DIFF, the SXML files are compared and the DDL to make both objects the same is generated and added to the repository.

Building Releasable Artifacts

To ensure deployment consistency, statements need to run in a particular order. Once a statement is executed, it will never be run again (immutable). Liquibase support, which was introduced in SQLcl to allow users to create changesets for schemas or objects, can be run to install the objects into a database. SQLcl Projects builds on that support to generate changesets for each block of changes on a branch and build those together into a release.

This is achieved by taking the comparison set between branches and generating a hierarchy scaffolding of changelogs that represent the list of comparisons. Then, the comparison set is used to generate the SXML differences, which is then used to generate the DDL for the changes.

After an export of the changes, the project stage command generates the changesets for the differences from the current branch and the main branch.

–-Example
next
├── release.changelog.xml
└── changes
    └── initial-export
        ├── hr
        │   └── table
        │       └── doc.sql
        └── stage.changelog.xml

project config -list -name git.defaultBranch

SQL> project config -list -name git.defaultBranch 
 +============================+
 | SETTING NAME      | VALUE  |
 +============================+
 | git.defaultBranch | master |
 +----------------------------+

next/changes/<branch_name>

In this example, the doc.sql file is automatically prefixed with Liquibase-formatted SQL formats and is defined as an individual changeset. doc.sql will be referenced in a changelog file.

-- Liquibase-formatted sql
-- changeset HR:d992dceaa87a936e8242bdc81201f93030084ab5 stripComments:false logicalFilePath:initial-export/hr/table/doc.sql
-- sqlcl_snapshot src/database/hr/tables/doc.sql:null:d992dceaa87a936e8242bdc81201f93030084ab5:create
 
create table hr.doc (
    id number
);

In this example, the stage.changelog.xml references the Liquibase-formatted SQL changeset.

--stage.changelog.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<!--BEGIN-->
<include file="hr/table/doc.sql" relativeToChangelogFile="true"/>
<!--END-->
<!--BEGIN CUSTOM-->
<!--END CUSTOM-->
</databaseChangeLog>

The next folder has a release.changelog.xml right at the top. This file is referenced again in the project release command.

--release.changelog.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<!--BEGIN CHANGE-->
<include file="changes/initial-export/stage.changelog.xml" relativeToChangelogFile="true"/>
<!--END CHANGE-->
<!--BEGIN CODE-->
<!--END CODE-->
</databaseChangeLog>

When all the changes are created for a release, run the project release command to take all files under the next folder and move them to a "numbered release" folder.

The final structure is:

releases
├── 1.0
│   ├── code
│   │   └── code.changelog.xml
│   ├── release.changelog.xml
│   └── changes
│       └── initial-export
│           ├── _custom
│           ├── hr
│           │   └── table
│           │       └── doc.sql
│           └── stage.changelog.xml
├── main.changelog.xml
├── next
│   └── release.changelog.xml
└── util
    ├── prechecks.sql
    └── recompile.sql

To generate a re-runnable artifact, run the project gen-artifact command. This creates a file in artifacts/<projectname>-<version>.zip. By default, artifacts are not included in the Git repository (To change this, modify the .gitignore file).

The contents of this artifact include:

releases/next/release.changelog.xml
releases/1.0/changes/initial-export/stage.changelog.xml
releases/1.0/changes/initial-export/hr/table/doc.sql
releases/1.0/release.changelog.xml
releases/1.0/code/code.changelog.xml
releases/util/recompile.sql
releases/util/prechecks.sql
releases/main.changelog.xml
README.md
install.sql

The last part is deploying the artifacts into a production database. The command project deploy takes the generated artifact and runs it with SQLcl.

SQL> project deploy -file demo-1.0.zip 
Starting the migration...
Running Changeset: initial-export/hr/table/doc.sql::d992dceaa87a936e8242bdc81201f93030084ab5::HR
Table HR.DOC created.
Liquibase: Update has been successful. Rows affected: 1
Installing/updating schemas
--Starting Liquibase at 2024-09-19T09:09:02.326601 (version [local build] #0 built at 2024-08-14 18:40+0000)
Table HR.DOC created.
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
Produced logfile: sqlcl-lb-1726733337322.log
Operation completed successfully.
Migration has been completed.