Every Database/APEX developer faces challenges when implementing a CI/CD pipeline for their projects due to the stateful nature of databases. The state, which is represented by the data, is crucial and cannot be lost. This presents unique considerations for ensuring data integrity and consistency throughout the CI/CD process.
Another common problem faced by database developers is the lack of standardization in database applications. While it might not seem like a major issue at first, working on multiple projects with varying standards and approaches can significantly increase the workload for database developers. This is because they need to adapt to different processes for each project, which could be avoided if there were more widespread standardization similar to what exists in other technologies like Java.
How can we address these database challenges and keep the DBAs happy and open to adopting the DevOps culture within the database?
SQLcl Projects
Let me introduce the new SQLcl extension, SQLcl Projects — a new CI/CD tool that comes with SQLcl.
This tool was first released in SQLcl version 24.3.0 (link to download it https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/)
The main goal of this tool is to standardize database software versioning and create releasable artifacts, including APEX elements. it supports a consistent model for development and operations, enabling repeatable builds that can be applied in a specific order.
You can access the tool using SQLcl by using the project command.
SQL> help project
CICD SQLcl project extension
Subcommands:
init|in
Initialize a new project
export|ex
Export database objects to your repository
config|cfg
Display the project configuration to the screen
gen-artifact|ga
Generate a local artifact for the current project
deploy|dp
Deploy a release or release artifact to a specific database
release|re
Move the current set of work into a release state and start a new body of work
verify|v
Run a set of verification checks to ensure the validity of your project, and it is output
stage|st
Generate Liquibase changelogs and changesets for all src and custom SQL files
More help topics:
PROJECT EXAMPLES
PROJECT SYNTAX
PROJECT INIT
PROJECT EXPORT
PROJECT CONFIG
PROJECT GEN-ARTIFACT
PROJECT DEPLOY
PROJECT RELEASE
PROJECT VERIFY
PROJECT STAGE
SQLcl Project Standard Structure
When initializing a SQLcl project using the project init command,
SQLcl generates a standard file structure that will be similar between all projects that start using SQLcl Project.
──.dbtools
│ ├── filters
│ │ └── project.filters
│ ├── project.config.json
│ └── project.sqlformat.xml
├── dist
│ ├── README.md
│ └── install.sql
└── src
├── README.md
└── database
├── foo
└── README.md
There are three folders in the initial setup:
.dbtools: This folder contains the following:
- Project filters that are used byproject exportto filter out objects that will be exported.
- Project format rules that are used to format the code when it is exported.
- Project configuration file that contains all the configuration used by the projects and each project command.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 theproject stagecommand and theproject releasecommand compresses its contents to create a release artifact.
Git-Based
The project command relies on Git branching to compare and identify differences (diffs). Diffing involves taking two input data sets and generating a list of changes between them.
So, working with the SQLcl project requires using a Git-based development approach.
Liquibase
SQLcl integrates Liquibase (a database change management tool designed to manage schema evolution over time), which means the dist folder contains auto-generated changelogs and changesets, forming an immutable artifact that can be deployed to any environment.
Liquibase simplifies deployment, allowing you to set up a new application from scratch or upgrade an existing one by applying the necessary changesets. Whether deploying to a new environment or updating an existing one, Liquibase ensures the proper changes are applied.
A key feature of SQLcl Projects is the ability to upgrade your application from any version to another, even if older versions are no longer in active development. For example, if your production application is currently on version 2 and you’re developing version 8, SQLcl Projects allows you to automatically upgrade your production environment from version 2 to version 8.
Conclusion
This is the first blog post in a series about SQLcl Projects. In this series, I’ll provide an overview of the tool and delve into its various features. We’ll cover topics such as:
The
projectcommand in detailStarting your database application using SQLcl projects
Building your CI/CD pipeline using SQLcl Projects and Jenkins