Automate Database Maintenance for Microservices with Jenkins

This repository contains the code for maintaining the database script automatically with Jenkins for your microservices.

Flow Diagram

!

Requirements

1. Python 3.5 +

2. Jenkins

Features

• Automatic execution of base schema DDL and DML scripts for defined microservices.
• Automatic execution of service version delta scripts.
• Notifying developer for failed query execution.
• Maintaining the service delta for reference and

Setup

Fork this repository and take clone.

git clone https://github.com/mustafanw/auto-postgres-deployment.git

Define database configuration in config.ini file

[DATABASE_CONFIGS]
hostname = localhost
username = postgres
password = postgres
database = ecommerce

Install Jenkins for pipeline creation

Steps for Jenkins installation

Create below pipeline in Jenkins ( Replace the repository url with your forked repo)

pipeline {
  agent any
  stages {
  stage('Cloning Git') {
      steps {
        git 'https://github.com/mustafanw/auto-postgres-deployment.git'
      }
    }
    stage('Run Script') {
      steps {
                script {
                        withPythonEnv('python3'){
                      sh 'pip install psycopg2'
                      sh './job.sh'
                    }
                }
    }
  }
  }
    }

Follow below steps for setup

• Create folder in root directory for all your micrtoservices.
• Create below four files inside each microservice folder - DDL.sql --> file for (CREATE, DROP, RENAME, ALTER etc) statements. - DML.sql --> file for ( UPDATE, INSERT, MERGE etc) statements. - sprint_delta.sql --> You need to add all the delta scripts for the service version delta. - service_delta.sql --> This file automatically tracks whole service delta scripts.

Refer account_service, shipping_service,inventory_service folder for above files

• Define below four varibales in config.ini file

[EXECUTE_SCRIPTS]
type = base - You have to define "base" 0r "delta" in type file. "base" for executing DDL and DML scripts and "delta" for executing sprint_delta file.
services = account_service,inventory_service,shipping_service - You have to list down the service folders here for which database scripts needs to be executed.
account_service = 1.0 - Define the version number corresponding to the service code version on GIT
inventory_service = 1.0
shipping_service = 1.0

• Follow below steps for port forwarding to trigger local jenkins job if you don't have Jenkins public IP or DNS available. Steps for port forwading

• Follow below steps for setting webhook on GIT to auto trigger jenkins job on every push Set GIT Webhook

Upcoming Features

• Database Schema Backup.
• Database DML backup of all tables except operational table.

Benefits

• This script can be useful if you have manual db script execution for your microservices.
• This will reduce the manual intervention of maintaining and validating queries.
• This will be beneficial if you have multiple client deployments and have to maintain delta for different product version deployments.
• This will track scripts of all the services in only one repo.

Upcoming Features

• Auto execute base script DDL and DML after every script in a test database to validate all the scripts.
• Auto database backup of all the tables except operational one.