Our DBA is very eager to help us with monitoring DB status. We had recently some problems with buggy deployments scripts (both manual and automated) that set up DB incorrectly or incompletely. So his idea is to have SELECT queries that will check the status of DB to provide quick feedback, e.g.

SELECT count(*) > 0 FROM user_table;

Obviously, many DB problems will be discovered by end-to-end tests, but having a number of tests cases checking directly DB will help us with troubleshooting and shorten the feedback loop.

I would like to add such DB checks as Jenkins jobs and provide to DBA some way (natural for him) to express such test cases. I found two approaches to do that:

  1. Translate SQL queries to JUnit tests, e.g. using DBAssert library:

    //Set condition to search record by. 
    dbAssert.table("authors").where("id", 123).and("last_name", "Coupland") ; 
    // assert customers _name_ column equals Douglas value in database.
    dbAssert.assert_column("name", "Douglas");
  2. Embed SQL queries into bash scripts, as proposed here:

    #!/bin/bash -l
    CHECK=`mysql -u jenkins -ppassword -h -BNe 'SELECT COUNT(*) FROM foo_db.jobs WHERE depth >= 100'`
    exit ${CHECK}

The choice will depend on our DBA, obviously. Though, I believe, the second approach is easier as it requires no Java skills.

Any other ideas?


If it were me, I would use a bash or python script that reads from a properties file and reads/executes each query from the file and prints the query string and its return value (your link says Jenkins has email notifications, which is good for this)

Then show the DBA how to edit the properties file to add/edit/remove queries.

I would think it would be convenient for the DBA because they only have to add sql statements and read the email notification.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.