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

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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).

Slide 7

Slide 7 text

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.

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

Slide 13

Slide 13 text

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)

Slide 14

Slide 14 text

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 ...

Slide 15

Slide 15 text

• 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}" }

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

• 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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

• 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

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

Slide 34

Slide 34 text

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