Continuous Integration & Delivery (CD/CD) with Oracle Database and APEX

With the rising popularity of DevOps, Oracle Database and APEX developers must evolve to embrace CI/CD pipelines for developing, testing, and deploying database code changes.

Today, many database developers struggle with the concept of using a central repository for storing, reviewing, and deploying code, where traditionally, all it took was a ZIP file of scripts and SYSDBA. This session will cover using Oracle’s free development tools combined with open source repository tools to create a full CI/CD pipeline for database and APEX development groups.

#DevOps #100daysofcode #CICD #Programming #Technology #Innovation #continuousintegration

Resources:
Oracle GitHub Repo for DevOps:
https://github.com/oracle/oracle-db-tools/tree/master/devops

YouTube Presentation:
https://youtu.be/P3La7ig37CU

Transcript

1. CI/CD for Oracle Database & APEX Developers Brian Spendolini Product Manager Oracle Database Development Tools @btspendo https://blogs.oracle.com Join our Discord Server! oracledevs Jeff Smith Distinguished Product Manager Oracle Database Development Tools @thatjeffsmith https://www.thatjeffsmith.com
2. What is CI/CD? Copyright © 2021, Oracle and/or its affiliates 2
3. CI/CD is continuous integration, continuous delivery, and continuous deployment • Introduces automation into all stages of app/database development • Helps developers work on the same app and merge their code changes back to a shared branch frequently • Development changes are automatically tested on push/commits and merges Why? • Consistency/Repeatability • Accountability • Security • Standardization • Find Issues Faster/Better Code/Quality Releases • More Frequent Releases What is CI/CD and Why do we need it? Copyright © 2021, Oracle and/or its affiliates 3
4. Traditionality this has been hard. Why? • APEX/DB bucks the trend of isolated development environments • Dev instances can be expensive • Licensing issues • DB/APEX changes are different from traditional files/code seen with DevOPs and CI/CD processes • Metadata driven • Versioning or lack there of • Rollback issues • Stateful vs Stateless • Very manual process and a lot of individual accountability • DB change tracking • APEX change tracking CI/CD with the database and APEX? Copyright © 2021, Oracle and/or its affiliates 4
5. What tools do I need to start? • Git/code repository • SQLcl/Liquibase • Jenkins Extra Credit • VS Code/SQL Developer • utPLSQL • Terraform • OCI account (free/paid) CI/CD with the database and APEX? Copyright © 2021, Oracle and/or its affiliates 5
6. SQLcl and Liquibase Copyright © 2021, Oracle and/or its affiliates 6 What’s SQLcl? Oracle SQLcl (SQL Developer Command Line) is a Java-based command-line interface for Oracle Database. Using SQLcl, you can execute SQL and PL/SQL statements interactively or as as a batch file. SQLcl provides inline editing, statement completion, command recall, and also supports existing SQL*Plus scripts. What’s Liquibase? Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. How do they work together? The Liquibase feature in SQLcl enables you to execute commands to generate a changelog for a single object or for a full schema (changeset and changelogs).
7. Liquibase and SQLcl Copyright © 2021, Oracle and/or its affiliates 7 SQL> lb genobject -type table -name zipcodes SQL> lb genobject -type ords –name myModule Generates change logs for APEX, ORDS RESTful Service Modules, or database objects SQL> lb genschema Generates changelogs and controller file for the connected schema SQL> lb update –changelog controller.xml Applies the specified change log using the current connection. SQL> lb rollbacksql -changelog controller.xml -count 100 Writes SQL to roll back the database to the state requested.
8. Copyright © 2021, Oracle and/or its affiliates 8
9. Liquibase and SQLcl Copyright © 2021, Oracle and/or its affiliates 9 DATABASECHANGELOG_DETAILS is a view that consolidates information from the DATABASECHANGELOG and DATABASECHANGELOG_ACTIONS tables
10. Copyright © 2021, Oracle and/or its affiliates 10
11. SQLcl and Liquibase and APEX Copyright © 2021, Oracle and/or its affiliates 11 SQL> lb genobject -type apex -applicationid 101 Creates a single f101.xml file for your application SQL> lb genobject -type apex -applicationid 101 -skipExportDate - expOriginalIds –split Creates multiple folders/files per component and an install file (like with the database) NOTE: Liquibase will only update the new/altered files when exporting so that your repository only reflects the changes SQL> lb update -changelog f101.xml To install the single file or SQL> lb update –changelog controller.xml To install the multiple files
12. Copyright © 2021, Oracle and/or its affiliates 12
13. SQLcl and Liquibase and Table Data Copyright © 2021, Oracle and/or its affiliates 13 SQL> lb data –object TABLE_NAME,TABLE_NAME... Exports the data from the indicated table(s) SQL> lb update -changelog data.xml Imports the data into the table(s) Great for metadata tables, not great for millions of rows.. (use this thing called ORDS for that)
14. Liquibase and SQLcl Copyright © 2021, Oracle and/or its affiliates 14 Roll your own! SQL> lb gencontrolfile Action successfully completed please review created file controller.xml SQL> !more controller.xml <?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"> <include file="{filename.xml}"/> ... </databaseChangeLog>
15. • Terraform is a tool for building, changing, and versioning infrastructure safely and efficiently. • Think Infrastructure as code • Full set of APIs/Resources for OCI via Terraform • https://registry.terraform.io/providers/hashicorp/oci/latest/docs • Create and Destroy OCI resources quickly and consistently • Similar to OCI CLI Terraform and OCI Copyright © 2021, Oracle and/or its affiliates 15 resource "oci_database_autonomous_database" "new_autonomous_database" { #Required compartment_id = var.compartment_ocid cpu_core_count = "1" data_storage_size_in_tbs = "1" db_name = "TESTDB${random_string.db_name.result}" admin_password = random_string.password.result db_workload = "OLTP" display_name = "TESTDB${random_string.db_name.result}" }
16. What’s a sample environment look like for CI/CD with the Autonomous database and OCI? Sample Environment Copyright © 2021, Oracle and/or its affiliates 16 Github OCI Virtual Cloud Network Public Subnet Compute VM with Jenkins Code Repository Jenkins Webhook Autonomous Database Internet Gateway OCI Resources Compute Load Balancer Object Store OCI Resources can be created via instance principles for the CI/CD process Network SQLcl Git Terraform Created on demand via Terraform Developers Compute VM with Spinnaker
17. The Development Process Putting it all together Copyright © 2021, Oracle and/or its affiliates 17
18. High Level Flow Copyright © 2021, Oracle and/or its affiliates 18
19. Development Copyright © 2021, Oracle and/or its affiliates 19
20. Development Copyright © 2021, Oracle and/or its affiliates 20
21. Copyright © 2021, Oracle and/or its affiliates 21
22. Individual environments need • APEX (Apps and workspace) • DB Schema(s) • Code from the repository (latest) or deployment Automate the process with APIs • OCI CLI/PLSQL SDK • ORDS and the SQL Endpoint • Terraform Individual Environments and Creation Copyright © 2021, Oracle and/or its affiliates 22
23. • Autonomous Database Clones • OCI DB VMs • Clone a VM DB • Create a new DB VM from a backup • Using OCI ExaCS • Sparse Cloning • Using Multi-tenancy • DB-APIs via ORDS • Using REST-Enabled SQL Service • Reusable Instance • Guaranteed Restore Points/Flashback Database • RMAN duplicate/clone • Datapump • Docker/Virtual Machines • ACFS/gDBClone Individual Environments and Creation Copyright © 2021, Oracle and/or its affiliates 23
24. Every code pull request/merge (maybe push) should spawn a CI/CD pipeline The pipeline should • Clone from an environment with the latest version of main • Apply the developers branch/merged main into that database • Run unit tests • Report back on the status of the pipeline • Destroy the environment if it is successful/Keep it running if not successful CI Process for code pushes Copyright © 2021, Oracle and/or its affiliates 24
25. Copyright © 2021, Oracle and/or its affiliates 25
26. Pipeline Process Copyright © 2021, Oracle and/or its affiliates 26
27. Copyright © 2021, Oracle and/or its affiliates 27
28. • Pipelines can also be scheduled or started manually • Choose deployment target with pipelines • Deploy Manually • Zip up the repo and deploy when you want • Eliminate surprises by having a logical progression of environments • Dev • UAT • Production • Use ADB/DB clones to practice deployments manually or via pipelines Deployment Targets and Automating the Process Copyright © 2021, Oracle and/or its affiliates 28
29. Deployment Copyright © 2021, Oracle and/or its affiliates 29
30. Deployment Copyright © 2021, Oracle and/or its affiliates 30
31. Copyright © 2021, Oracle and/or its affiliates 31
32. Rollback Copyright © 2021, Oracle and/or its affiliates 32 Or Roll Forward? In some cases, it may be easier/better to fix the issue in production and bring the fix back to the main code line Constant testing/automation should help with these incidents
33. Next Steps Where do we go from here? Copyright © 2021, Oracle and/or its affiliates 33
34. Go Slow Cultivate an environment that encourages CI/CD Incorporate pieces/adopt what works for you and your teams All code is hosted in github (devops folder) https://github.com/oracle/oracle-db-tools Any issues let us know and the projects will be updated on a regular basis Oracle LiveLabs https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/home Demos and Labs to get you started in OCI and with the Oracle database Next Steps Copyright © 2021, Oracle and/or its affiliates 34
35. Reach out directly Let us know about your journey and any issues/enhancements you would like to see Join our Discord Group! Next Steps Copyright © 2021, Oracle and/or its affiliates 35
36. Copyright © 2021, Oracle and/or its affiliates 36