Database versioning with data, APEX apps and migrations

F

Fifteen years ago I wrote my first script to extract (Oracle) database objects into files in folders, so I can track changes in Subversion. Since that I had rewritten it multiple times using different approaches and languages. Today I will share with you my latest creation available for free under the MIT licence on my GitHub, OPY.

What it does

• export database objects into files in folders
• you can export just objects compiled in past X days
• you can export just specific object types
• some objects are not supported (like dimensions, mview logs, types)
• export tables data into CSV files
• designed to keep track of LOV/setup tables
• so far just the export to CSV files
• LOBs and some data types not supported
• export APEX application as a whole and into folders
• update: now with also partial exports (since day)
• update: now also exporting embedded code (hardcoded queries)
• cleanup files to make them more readable
• remove some distractions, align columns...
• remove timestamps from APEX files
• prepare patch files and keep track of changes between releases
• you would have to create patches for table changes manually
• it is not designed to run the patches, it just creates them

So you can effortlessly track all changes in your database including changes in data and APEX. With some practice you would be able to create patches and apply them on a different environments. It is not about Liquibase nor the Flywheel, it is a custom script written in Python and you can modify it.

Exporting objects changed in past 7 days and APEX application.
You can see summary of all objects in database and the ones you will be exporting.

Typical day workflow

I am used to work in single Git branch and on a shared DEV server. But you can apply same principles to a feature branches approach.

I start my day by fetching latest version from Git to be aware what my colleagues changed.

I do my tasks/changes directly in files and compile them in database to verify they works. When I am done with the task, I fetch the branch again and then commit and push my changes. This way if another developer during this period recompile same objects in database with his changes, I wont lost mine and he will see that issue when he tries to commit his changes.

Typical call to export changes made today in database (recent=1) and in APEX application 100 (a=100):

python export.py -r=1 -a=100 -v

Release day

Release once a month? Once a week? On every commit? It doesn't matter how often do you do releases. To create a patch (a change set since last rollout/release) I call this:

python export.py -r=0 -patch

I check the output on screen, main effort is on changed tables, changed LOV data, maybe grants for new objects... I can append any changes I need to pass to another environment to a 20_diffs---MANUALLY/2022-07-06.sql file (for current day). This also generated patches_done/2022-07-06.sql file containing all changes since last rollout. You have to run this script on your next environment (typically UAT) or/and you can pass it to Jenkins, Gradle, Github actions... to run it for you.

For example on this screen you can see that I added poll_answers and poll_results tables and I changed poll_questions table. Only the changed table requires your attention, you might need to write and ALTER statements yourself. And I created a770 package. No need to focus on that.

When you consider this done (you did run it on next environment), then you run the rollout command to mark passed files as released.

python export.py -r=0 -rollout

Ta daa.

What you will need to install

• Python 3.8+
• update: now with YAML config file also PyYAML module is required
• cx_Oracle and python-oracledb module (to access Oracle database from Python)
• SQLcl (to be able to export APEX applications/pages)

python-oracledb is a newer version of cx_Oracle without the need to install Oracle instant client, unfortunatelly I am not able to connect to databases using SID. And with cx_Oracle I am not able to connect to Oracle Cloud (and probably any other connection requiring wallet). Hence until I solve this issue I am using cx_Oracle to connect to local servers and python-oracledb to OCI. I would like to get rid of cx_Oracle when I solve the SID issues.

Create connection file

I store credentials in a Python pickle file. To create this file, you call this:

python conn_create.py --help

My folder structure for a CORE project:

I will update this article when I have something valuable to add.