Tips to Help PL/SQL Developers Get Started With CI/CD

In most ways, PL/SQL development is just like working with any other language but, sometimes it can be a little different. If you'd like to create a Continuous Deployment Pipeline for your PL/SQL application, here is a short list of tips to help you get started.

Work From Files

Do not use the Database as your source code repository. If you are making changes to your application in a live database, stop right now and go read PL/SQL 101: Save your source code to files by Steven Feuerstein.

Now that you're working from files, those files should be checked into...

Version Control

There are a lot of version control applications out there, the most popular right now is probably Git. One advantage of using Git is, you work in your own local copy of the repository making frequent commits that only you see. If you're working in your own personal database you could compile your changes to the database at this point.

If you're working in a database shared with other people and you're ready to compile your code, from the central repository to get any changes since your last pull. Handle any merge conflicts, then your changes back up to the shared repository. After the push, you can compile your code to the database. This helps ensure that people don't overwrite each other's changes.

Making frequent small commits will help keep everything running smoothly.

What About Your Schema Objects?

Your schema objects, such as tables and views, are as much a part of your application as any other component and changes to these objects should be version controlled as well. The process of keeping track of schema changes is called Schema Migration and there are open source tools you can use such as Flyway and Liquibase.

Unit Testing

In any application it's smart to have good test coverage, If your goal is Continuous Deployment it's critical. A Unit Test is when you test a single unit of code, in PL/SQL that may be a function or procedure.

Unit tests typically follow these steps:

1. Setup the environment to simulate how it will be when the application is deployed. This might include changing configuration settings and loading test data.
2. Execute the code unit.
3. Validate the results.
4. Clean up the environment, resetting it to the state it was in before running the tests.

utPLSQL is a great tool for unit testing your PL/SQL applications. You will write a package of tests for each 'unit' of your application which should test all of the required functionality and potential errors. utPLSQL is an active open source project with an impressive set of features. Check out the docs to get started.

Build

Building a database application usually consists of running the change scripts in a specific order. It's common to create a master script that executes the others in order of how the objects depend on each other. However, if the master script simply executes the other scripts, you will need to create additional scripts to track and verify changes, and more scripts to give you the ability to rollback the changes if/when there's a problem.

There are build tools such as Gradle and Maven that can easily execute your scripts. But you'll still need to create the additional control scripts. If you use a Schema Migration tool it should include a lot of these additional functions without having to write extra scripts. For an example, check out Dino Date which has a Liquibase migration included.

How to Handle the PL/SQL Code

You could include your PL/SQL code in a Schema Migration changeset but adding schema migration notation to your PL/SQL introduces another layer of complexity and potential errors.

In the Dino Date runOnChange directory, you will find examples of setting up Liquibase changesets that watch for changes in the files of objects that you would rather keep 'pure'. When you run a migration, if the file has changed Liquibase will run the new version.

In a shared database environment, you should execute a schema migration after you pull/merge/push your changes into your version control system.

Automate!

All of these pieces can be tied together and automated with an automation server such as Hudson or Jenkins (both are open-source) to create a build pipeline.

A simple (maybe too simple) build pipeline using the above tools could follow these steps:

1. Developer makes a change and pushes it to the shared Git repository.
2. Hudson notices the repository has changed and triggers the build pipeline.
3. The project is pulled from Git.
4. Liquibase deploys the changes to a test database.
5. utPLSQL is trigged to run the unit tests.
6. Liquibase deploys the changes to the production database.

Other Useful Tools

• Edition Based Redefinition[pdf] can be used to deploy applications with little to no downtime.
• Oracle Developer Cloud Service comes with a ton of pre-configured tools to help with almost every aspect of your development process.
• Gitora can help you version control your application if you are not able to move out to files.