Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

thatjeffsmith

April 08, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

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

    View full-size slide

  2. What is CI/CD?
    Copyright © 2021, Oracle and/or its affiliates
    2

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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.

    View full-size slide

  8. Copyright © 2021, Oracle and/or its affiliates
    8

    View full-size slide

  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

    View full-size slide

  10. Copyright © 2021, Oracle and/or its affiliates
    10

    View full-size slide

  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

    View full-size slide

  12. Copyright © 2021, Oracle and/or its affiliates
    12

    View full-size slide

  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)

    View full-size slide

  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

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

    ...

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  17. The Development Process
    Putting it all together
    Copyright © 2021, Oracle and/or its affiliates
    17

    View full-size slide

  18. High Level Flow
    Copyright © 2021, Oracle and/or its affiliates
    18

    View full-size slide

  19. Development
    Copyright © 2021, Oracle and/or its affiliates
    19

    View full-size slide

  20. Development
    Copyright © 2021, Oracle and/or its affiliates
    20

    View full-size slide

  21. Copyright © 2021, Oracle and/or its affiliates
    21

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  25. Copyright © 2021, Oracle and/or its affiliates
    25

    View full-size slide

  26. Pipeline Process
    Copyright © 2021, Oracle and/or its affiliates
    26

    View full-size slide

  27. Copyright © 2021, Oracle and/or its affiliates
    27

    View full-size slide

  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

    View full-size slide

  29. Deployment
    Copyright © 2021, Oracle and/or its affiliates
    29

    View full-size slide

  30. Deployment
    Copyright © 2021, Oracle and/or its affiliates
    30

    View full-size slide

  31. Copyright © 2021, Oracle and/or its affiliates
    31

    View full-size slide

  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

    View full-size slide

  33. Next Steps
    Where do we go from here?
    Copyright © 2021, Oracle and/or its affiliates
    33

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  36. Copyright © 2021, Oracle and/or its affiliates
    36

    View full-size slide