0

What is the fastest database connection check in grails? I need to create an endpoint which will be called again and again by a script so this needs to be efficient. The database goes down fom time to time and this script will call this api that will return a status code. Based on this status code the bot will know the database is down. If it is 200 then everything is ok.

So i need to check if database connection is intact in a very efficient manner. Since the call happens every few seconds what is the fastest way to check for database status. My thought is to use a simple gorm query like Status.read(1). If there is an exception then return a non 200 status code. Is there a more efficient way for a quick database status check?

I appreciate any insights. I am using Grails 2.2.

kofhearts
  • 3,607
  • 8
  • 46
  • 79

1 Answers1

1

Why not just avoid using GORM all together and just use a super simple database query like SELECT 1? Putting this into a service it might look like something like this:

package my.example

class MyService {

    def dataSource
    def someTimeoutValue = 1000

    boolean isDatabaseThere() {
        try {
            dataSource.connection.isValid(someTimeoutValue)
            return true // everything is okay
        } catch (Exception e) {
            return false // everything is not okay
        }
    }
}

Pardon any typos or anything, that was written on the fly.

Joshua Moore
  • 24,706
  • 6
  • 50
  • 73
  • thanks. one question. what is dataSource? is it injected in service. please note i am using grails 2.2. Is that supported by grails 2.2? – kofhearts May 07 '19 at 07:04
  • dataSource is the name of the database data source as defined in Datasource.groovy. The default data source is named `dataSource`. This should work in Grails 2.x – Joshua Moore May 07 '19 at 07:34
  • 2
    I don't think you need to create an `Sql` instance for this. Would `dataSource.connection.isValid(someTimeoutValue)` be more efficient and accomplish the same thing? – Jeff Scott Brown May 07 '19 at 14:32
  • @JeffScottBrown That's a very good point. I suppose that would require less resources at least. Do you mind if I edit the answer to reflect that? – Joshua Moore May 07 '19 at 15:05
  • for this simple check i am thinking putting the check logic in controller itself so i wonder how efficient would it be to use def ds = grailsApplication.mainContext.getBean('dataSource') inside checkStatus() method in controller. The checkStatus will be called every few seconds. – kofhearts May 09 '19 at 05:15
  • @kofhearts Why would you do that instead of `def dataSource` in the controller (just like the service)? There is no reason to resolve the `dataSource` every call. That's not efficient at all. Also, I'd still put all of this logic in a service and call the service from a controller, but then that's just me. – Joshua Moore May 09 '19 at 07:08
  • please make sure you close the connection as well. – kofhearts May 30 '21 at 06:18