I asked this question several months ago, when I was in a position to introduce versioning with Git into a database development company that didn't have versioning 'till this point. Due to the fact that I have since solved the problem, I thought I'd share my steps for people having the same issues...
At the beginning, I had no idea of how to find a way to create a fluent development circle between an Oracle database, different editors (SQL Developer, VS Code, etc.) and GitLab.
The ultimate purpose was to make it as easy as possible for developers to version, document and store the data during their development process. Therefore, the code needs to be extracted/exported from the database, be brought to GitLab, be compiled and built and then be brought back to (different) databases, e.g. a dev and a prod database.
Basically, my steps were
- Extract the files from the database using a filedump function/ export function in database browsers (e.g. SQL developer) or in my case, writing a specific python script for this task. I replaced all critical information with xyz or example...
- Create a Remote Repository in GitLab and upload all the files (keeping the database hierarchy) to the GitLab repo.
- Clone the Repository and create a local repo.
- Write a yaml file that brings files back into the Oracle database and is triggered whenever a new commit is pushed into the repo.
The most important line in the yaml file is the command to built the code that has been pushed in the last 5 minutes and bring it back into the database.
-"git diff --stat ('git diff --stat @{5.minutes.ago} --name-only') do sqlplus xyz"
xyz needs to be replaced by your Oracle database credentials, e.g. SCHEMA/password@//instance/name
- Setup a Jenkins server that takes certain files from the repository and brings them into a different database. This is fairly simple, everything in Jenkins has a GUI.
As you can see, we use yaml and Jenkins to address two different databases. This is intentional as we have both a development database and a production database. Therefore, we wanted to be able to address them individually. The yaml script is supposed to trigger whenever a commit is pushed and to immediatly update the development database. Later, when a file is seen as done (needs no more development), a senior engineer can go ahead and use Jenkins to transfer this file into the production database.