Check to see if I can connect to a database

Hi, You can do a test query to ping the database. In Oracle and MySQL you can execute

select 1 from dual

Your result set will have 1. You will get a network or database error otherwise. Its is a very lightweight query.

Thanks @jimmck. I was hoping there was a way within Eloquent to check for this, but it's not a show stopper to do it this way.

Can do it with DB?

// or just DB::connection()
if (DB::connection()->getDatabaseName())
{
   return 'Connected to the DB: ' . DB::connection()->getDatabaseName();
}

Could catch the PDOException as well?

@bashy - I'll give that a shot!

I'm going to keep playing around with this. With an initial, blind shot at it, it is returning my "main" Laravel database name. Makes sense since in this one controller, I'm using a model from that db and a model that is the "other" database. Actually, I've got three different databases in use for this project. One which I consider the "main" Laravel database and two others that are on another host. They all work, just tweaking what you posted @bashy to see how I can test for the other two.

Got it. Just have to check for the appropriate database connection.

if (DB::connection('myDamnDbConnection')->getDatabaseName())
{
   return 'Connected to the DB: ' . DB::connection('myDamnDbConnection')->getDatabaseName();
}

....where "myDamnDbConnection" is defined in config/database.php

Thanks for the help @bashy and @jimmck

Hello @goatshark , Their's a simple way to do this : create your database config foreach database you want : mysql , mysql2 ,mysql3 ,,,, or what ever name you would like ,

E.g :

    # Our primary database connection
    'mysql' => array(
        'driver'    => 'mysql',
        'host'      => 'host1',
        'database'  => 'database1',
        'username'  => 'user1',
        'password'  => 'pass1'
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ),

    # Our secondary database connection
    'mysql2' => array(
        'driver'    => 'mysql',
        'host'      => 'host2',
        'database'  => 'database2',
        'username'  => 'user2',
        'password'  => 'pass2'
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ),

then you have two ways to use your databases in a specified Model

1 - Creating a protected $connection in model class to define the db configuration that you want to use

class YourModelName extends Eloquent { protected $connection = 'mysql2' # or what ever config you have; }

2 - using setConnection method :

$YourModelName= new SomeModel; $YourModelName->setConnection('mysql2') # or what ever config you have;

3 - Simply using it in query builder :

$users = DB::connection('mysql2')->insert(Query);

Hope this help you !

Bitcoin Boy

Hey @bitcoinboy, thanks for this. It is good to see that I'm not way off base here. My model for this "external-to-Laravel" database looks like this (below). The totally amazeBalls thing here is that I have a method/relationship to my main Laravel database in here --- tying the two together. Mind.blown. I love this shit.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class NagiosHost extends Model
{
    /**
     * Connect to the ndoutils database and use nagios_hosts table
     */
    protected $connection = 'mysql_ndoutils';
    protected $table = 'nagios_hosts';
    protected $primaryKey = 'host_object_id';

    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;

    /**
     * Relationship to Port model.
     */
    public function port()
    {
        return $this->belongsTo('App\Port', 'address', 'management_ip');
    }

}

Thanks again for your input!

Hi thanks @goatshark , Also please don't forget to define protected $fillable for rows that you allow to be modified in queries

Bitcoin Boy.

Oh yeah, good point. You know, I did consider that as I was reviewing the documentation to get this going. It occurred to me that I don't intend to write to this database, although I totally get that protected $fillable would need to be set in order to do so. This one was all about needing to read data from this "old crusty" application into the web interface I'm creating with Laravel.

As soon as I realized that I could use Laravel/Eloquent to tap into other databases (yeah, yeah, I know, that's obvious to actual developers), I just wanted to write new interfaces for dozens of old-can-not-get-rid-of-it applications. Good stuff.

Okay, perhaps I should start a new thread with this question, but I'm considering the effects (performance, etc.) on creating middleware that would check for this db connection for certain routes. Maybe throw a flash message or something (or maybe not) if the db is not available. Some way to check that's not just in the controller. I can just stick to checking for it in the controller, but trying to keep my controllers clean where I can. All that said, I only try to keep controllers clean as a matter of practice. I'm kind of an old 'just get the job done' type, so I don't have much soreness over doing this in the controller where necessary.

Thoughts?

if (DB::connection('myDamnDbConnection')->getDatabaseName())
{
   return 'Connected to the DB: ' . DB::connection('myDamnDbConnection')->getDatabaseName();
}

This call is simply the getter of dataBaseName, it comes from your config file, not the database. If you want to know the database is up and running you must perform a database op. select 1 from dual is commonly used by connection pools to test if a database is available. Any database call involves overhead, you want to do it as cheaply as possible.

@jimmck, that makes sense. I'm just trying to figure out how that translates into an eloquent call. Just a syntax question at this point. Thanks.

I have work on insert query into laravel migrate. I try to add http.conf file pdo_pgsql.so extension

I got the following error into Command Prompt. php artisan make:model login

PHP Warning: PHP Startup: Unable to load dynamic library 'mylocalpath\php\ext\pdo_pgsql.so' - The specified module could not be found.

You need to install PostgreSQL driver for your installed php.

@jimmck, @bashy, @bitcoinboy (and everyone) - Thanks for the great discussion. Here's what I ended up with as a way with Eloquent to check to see if the database is available. In this example, mysql_ndoutils is my remote database configured in config/database.php.

if (DB::connection('mysql_ndoutils')->table(DB::raw('DUAL'))->first([DB::raw(1)]))
{
    return 'database is available!';
}

So that's my call to check to see if the database is availble (thanks again everyone!). Now, if I "else" that to "do something" when/if it's not available, I get the PDOException no matter what. So that seems to be an issue with my skills + actual exception handling --> so I'm going to try to remedy that right now.

@goatshark Do you use a different database connection? Try a new Eloquent instance?

Hi @jimmck, I have three databases associated with this project. One that I consider the "main" Laravel database, named 'mysql' in config/database.php. One remote databased named 'mysql_syslog' (as per config/database.php), and finally, one named 'mysql_ndoutils' (as per config/database.php).

They all work great. Sometimes though, I have the mysql_ndoutils database offline and instead of blowing up and throwing the exception page, I'd like to just make it to my view and present a message about the database being unavailable. So this seems like more of an issue with my handling (or lack thereof) of \Illuminate\Database\QueryException.

I tried getting this working with middleware with a tiny bit of success, however, when the database is down and I move on to the controller, it's back to the same issue of me not handling QueryException.

Example...in middleware:

<?php

namespace App\Http\Middleware;

use Closure;

use DB;

class CheckDbConnectivity
{
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {
        try
        {
            DB::connection('mysql_ndoutils')->table(DB::raw('DUAL'))->first([DB::raw(1)]);
        }
        catch(\Exception $e)
        {
            return 'Oh snap! Your DB is not available!';
        }
        return $next($request);
    }
}

That works, but obviously, if I don't return anything when it's down and instead move on to the controller, well, the model that uses this database is pulled in and I'm back to the same QueryException page. So I was thinking that I should try to handle this in my model . . . but, sadly, that's right where I run out of skills. :( :)

@goatshark does $next actually do the request? If so, I would move that out. Why not have a function that gets the first available database in order of priority. Your skills look fine, everyone has to refine their logic flow. It always looks different after I type it in. There are days when I ask myself, repeatedly, How the Frack did that even work? :)

Thanks @jimmck. The code above works. It presents the text returned in catch if the db is down. Otherwise, it continues on to the controller . . . where things will blow up because when I use that model, well - PDOException just like it should be.

I tried catching PDOException in app\Exceptions\Handler.php also. That too works - I can display whatever I need to if the database is down, or move on to the rest of the workflow if it isn't.

So these are two working ways to "do something other than blow up with a PDOException". Here's my problem though, and maybe this is something I didn't explain well previously. When I get all the way to my view, I have panels that display information obtained from all of my databases (think dashboard). What I was aiming for was this: in the event that a database was not accessible, instead of blowing up (PDOException) or even instead of presenting my own "your s#$t is down" message, I wanted to populate the panel itself with something like "sorry, that database is down" or something like that.

What I expected when I started this little side-track was that I could somehow test for whether or not that database was down in my controller and pass some sort of "is_up" boolean through to my view. It seems though that without giant efforts, the exceptions that "blow up" are going to blow up whether that be from the normal PDOException or some replacement message that I present. ...because the error must present itself when I use that model (makes sense).

So, catch this in middleware == I'm still going to get the PDOException when I use that model in my controller. Or, handle the exception in app\Exceptions\Handler.php == the most I get is controlling the behavior of the application when the PDOException occurs.

So a poll - you're thinking:

A) Yeah, that's an issue.

B) Why in the world would you want to build it like that?

C) ....umm, I don't have a C

This isn't a huge deal for my application. This is more of an OCD trip that I got on when I wondered how I could gracefully, within my dashboard, say "that's not available". :)

@goatshark Love OCD thing. I have built a few control panel/dashboards. They usually report from multiple resources. Heres an example from New Horizons mission. The Mom's dash board reports on over all system status. If the resource is available and the status ain't good the message is red. If the resource is available and the status is good, the color is green. If the resource is not available, the color is grey, cuz I cannot tell you one way or the other.

So if your controller is what feeds your dash board.

1. Get contact with panel resource.
2. If 1 is true get status, set color.
3. if 1 is false grey it out
4. Next Panel
5. when done: publish view

@jimmck, that's cool. I approach the red, green, grey thing just like that. Speaking generically, if I "can't see" it, grey. Otherwise, check its status and red/green as appropriate. That's why when I started down this rabbit hole, things seemed pretty clear. It's the way in which the exceptions work that's throwing a wrench in things. That's not to say I disagree with how they're handled . . . I was just taken back when I started to realize (and understand) how this type of exception is . . . how to say it - handled by "the system" so the throwing of these exceptions feels a little out reach for what I am trying to do with it. Talking in circles now, I'm sure.

It is almost as if I need to check for the database connectivity, and based on that, decide whether or not to even have my controller use the model that needs that database. Hhhmm, conditionally "use"ing models. Is that ridiculous? Although, if I don't do the use ModelName at the top of my controller, and instead point to it as "\App\ModelName" only in methods that need it . . . that might allow me to use middleware or the Handler.php to set some boolean that can be checked when deciding whether or not to reference the model at all in the controller. Again, is that ridiculous?

Just thinking out loud here, but with middleware.....

1. check to see if the database is available. If it is, cool, move on. If it is not, keep calm, carry on, but set a session variable (or whatever).

2. In my controller method, check to see if that session variable is there. If it is, pass some 'databaseNotAvailable' data into the view. If the session variable is not there, process normally (make whatever calls to the model that I'm doing now).

.....that's a bit messy, but some iteration of the above might work. I could always go back to work at the coffee shop, too.

@goatshark You got it man! BTW Exceptions are a Royal Pain In the Ass. You are not left to catch just Exception, many API's throw more specific ones and for your own API's you can build your own. I know it sounds messy when you map it all out, but you really gotta do it at some point. Or your app just dies a mysterious death.

@jimmck Awesome. Thank you. I'm going to start with the middleware strategy. I just like that I can nail it down to certain routes and isn't something I'm committing to for handling all PDOExceptions (or whatever I use this strategy with in the future). Oh yeah, and then there's the part where I really dig working with middleware, so there's that. I'm not totally convinced that setting a session variable is the best way to approach "signaling" whether or not the database is available, but like middleware, I really dig working with session variables, so I suppose it's as good of a place to start as any. Never thought there would be a reason to not do a use App\Model at the top of a controller that's going to use that Model, but this seems to make sense here.

Thanks for all your input. Much appreciated!

@jimmck Looks like it's working. This feels kludgy and I might refactor it tomorrow, but here is it:

Middleware:

    public function handle($request, Closure $next)
    {
        $request->session()->put('ndoutilsAvailable', 'yes');
        try
        {
            DB::connection('mysql_ndoutils')->table(DB::raw('DUAL'))->first([DB::raw(1)]);
        }
        catch (\PDOException $e)
        {
            $request->session()->put('ndoutilsAvailable', 'no');
        }
        return $next($request);
    }

    public function index(Request $request)
    {
        $switches = AccessSwitch::all();
        $ports = Port::all();
        $ndoutilsAvailable = $request->session()->get('ndoutilsAvailable');
        if ($ndoutilsAvailable == 'yes')
        {
            $nagios_hosts = \App\NagiosHost::all();
            return view('dashboard.index')
                ->with('switches', $switches)
                ->with('ports', $ports)
                ->with('nagiosHosts', $nagios_hosts);
        }
        elseif ($ndoutilsAvailable == 'no')
        {
            $nagios_hosts = [];
            return view('dashboard.index')
                ->with('switches', $switches)
                ->with('ports', $ports)
                ->with('nagiosHosts', $nagios_hosts);
        }
    }

For now, I'm just echoing {{ Session::get('ndoutilsAvailable') }} in my view. No more PDOException explosion, and the "yes" or "no" that lands into my view is correct..... and the panel on the page is happy because I'm passing that empty $nagios_hosts = []; array through to the view --> which is double amazeBalls because that panel was only doing a count() on it anyway, so it's correct as well.

Maybe some refactoring of the flow here and some of the naming tomorrow, but it's working. Much much thanks for your help and input!

@goatshark Looks great! Glad I could help. So the Panel knows only what it should know. This is good, in six months you make look at the code and ask, is such and such leaking into that. And discover, no, ah a good thing... Saw your post on Command Exec shells, very fun to build!

@jimmck On one hand, it feels like there was a lot to getting this done.... in that "am I doing this shit wrong" kind of way. On the other hand, some things aren't free, so hopefully I just found myself in one of those situations where a little work was actually needed . :)

In regards to the command exec work, I really dig building devops tools. In between attempts to tackle this PDOExceptions itch, I started digging into laravel/php/shell/system things more. I read up on Symfony's Process component, and while seemingly more suited to a Laravel environment that (dirty, dirty) exec calls (they just always get messy, imo), I found and embraced Ansible. Now, I don't yet know how to wield it within Laravel, but it looks freaking amazing so that is most definitely my next OCD itch to scratch. I wish Ansible was around a long long time ago. Just need to figure out where it meets Laravel.

...so much to build!!!

So Much In Deed!!!! Ansible, never heard of, Thank You Sir! Releases named after Van Halen songs, nice :)

Just ran across this thread & needed a quick and dirty way to check the db connection was configured and the database was running. In case it helps anyone else here is how to check using tinker.

$ artisan tinker

Psy Shell v0.7.2 (PHP 5.6.17-0+deb8u1 — cli) by Justin Hileman

>>> use DB;

>>> DB::select(DB::raw("SELECT 1 From dual"));
=> [
         {#642
             +"1": "1",
      },
     ]