Checking Database Connectivity With a Script

1. Overview

Reliable database connectivity is a foundational aspect of modern application development and system integration. Whether validating access to a production database or ensuring test environments are correctly configured, a reliable connectivity check is essential. Such a check helps avoid runtime failures and supports better system health monitoring.

In this tutorial, we’ll demonstrate how to verify database connectivity using scripts. We’ll explore various scripting environments, such as Bash and Python. For illustration purposes, we’ll use the Baeldung University database schema.

2. Using Bash With Command-Line Utilities

Command-line tools provide a lightweight and effective way to test database access from shell scripts. These utilities prove especially useful in automated deployments, cron jobs, and DevOps health checks. Let’s explore some examples.

2.1. Using psql for PostgreSQL

The psql command-line tool offers direct access to PostgreSQL databases. We’ll use the psql command in a Bash script to verify connectivity and check for the Student table.

First, let’s create a postgresql.env file to securely manage database credentials:

$ nano postgresql.env

Then, we add the following content to the file:

DB_HOST=localhost
DB_PORT=5432
DB_USER=user
DB_PASS=Password2024
DB_NAME=University

When done, we save the file and exit the editor. Notably, we should never commit this to version control.

Next, let’s create a script file named test-db-connect.sh:

$ nano test-db-connect.sh

Similarly, we add the following scripts to the file:

#!/bin/bash

# Load environment variables
source postgresql.env

# Test PostgreSQL connectivity and check for 'student' table
PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c '\dt student' >/dev/null 2>&1

if [ $? -eq 0 ]; then
  echo "PostgreSQL: Connection successful and student table exists."
else
  echo "PostgreSQL: Connection failed or student table does not exist."
fi

We make the script executable with the use of the chmod command:

$ chmod +x test-db-connect.sh

Finally, let’s test the database connection:

$ ./test-db-connect.sh
PostgreSQL: Connection successful and student table exists.

The output confirms that the database connection was established successfully and the Student table exists in the Baeldung University database.

2.2. Using mysql CLI for MySQL

The mysql command-line client provides a straightforward way to verify connectivity to MySQL databases. Similarly, we can also use the mysql command to check both a database connection and the existence of a table in the database.

First, let’s create another environment file named mysql.env and include MySQL credentials:

$ nano mysql.env

Then, we update the content:

# MySQL Configuration
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASS=MySQL2024
MYSQL_DB=University

We save the file. Furthermore, let’s create another file named test-mysql-connect.sh:

$ nano test-mysql-connect.sh

We then add the following script to the file:

#!/bin/bash

# Load environment variables
source mysql.env

# Test MySQL connectivity and check for 'Department' table
mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "USE $MYSQL_DB; SHOW TABLES LIKE 'Department';" >/dev/null 2>&1

if [ $? -eq 0 ]; then
  echo "MySQL: Connection successful and department table exists."
else
  echo "MySQL: Connection failed or department table does not exist."
fi

Next, we make the script executable by changing its permissions:

$ chmod +x test-mysql-connect.sh

Finally, let’s test the MySQL database connection:

$ sudo ./test-mysql-connect.sh
MySQL: Connection successful and department table exists.

This output confirms that the database connection was established successfully and that the Department table exists in the Baeldung University database.

3. Using Python to Check Database Connectivity

Python provides powerful libraries that make it easy to interact with relational databases. Additionally, using Python scripts to verify database connectivity is useful in applications where logic needs to be embedded into monitoring tools or custom dashboards.

Let’s explore how to use Python to test connections to PostgreSQL and MySQL databases. First, we begin by installing the necessary Python database drivers:

$ pip install psycopg2-binary mysql-connector-python python-dotenv

Here, we use psycopg2 for PostgreSQL and mysql-connector-python for MySQL.

3.1. Testing PostgreSQL Connectivity With Python

We create a file named test_pg_connect.py and add the following Python script to the file:

import os
import psycopg2
from dotenv import load_dotenv

# Load environment variables from postgresql.env
load_dotenv("postgresql.env")

DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASS = os.getenv("DB_PASS")

try:
    connection = psycopg2.connect(
        host=DB_HOST,
        port=DB_PORT,
        dbname=DB_NAME,
        user=DB_USER,
        password=DB_PASS
    )

    cursor = connection.cursor()
    cursor.execute("SELECT to_regclass('public.student');")
    table_exists = cursor.fetchone()[0]

    if table_exists:
        print("PostgreSQL: Connection successful and student table exists.")
    else:
        print("PostgreSQL: Connection successful but student table does not exist.")

except Exception as e:
    print(f"PostgreSQL: Connection failed - {e}")

finally:
    if 'connection' in locals():
        connection.close()

This script uses psycopg2 to connect to the PostgreSQL database and dotenv to securely load environment variables. Additionally, it checks whether the Student table exists using to_regclass, which returns None if the table doesn’t exist.

Finally, to test the connection, we run the created Python script:

$ python test_pg_connect.py
PostgreSQL: Connection successful and student table exists.

This successful run confirms that both the database connection and the Student table exist.

3.2. Testing MySQL Connectivity With Python

Next, let’s test connectivity to a MySQL database using a similar approach. We create a file named test_mysql_connect.py and add the following Python script:

import os
import mysql.connector
from dotenv import load_dotenv

# Load environment variables from mysql.env
load_dotenv()

MYSQL_HOST = os.getenv("MYSQL_HOST")
MYSQL_PORT = os.getenv("MYSQL_PORT")
MYSQL_USER = os.getenv("MYSQL_USER")
MYSQL_PASS = os.getenv("MYSQL_PASS")
MYSQL_DB = os.getenv("MYSQL_DB")

try:
    connection = mysql.connector.connect(
        host=MYSQL_HOST,
        port=MYSQL_PORT,
        user=MYSQL_USER,
        password=MYSQL_PASS,
        database=MYSQL_DB
    )

    cursor = connection.cursor()
    cursor.execute("SHOW TABLES LIKE 'Department';")
    result = cursor.fetchone()

    if result:
        print("MySQL: Connection successful and department table exists.")
    else:
        print("MySQL: Connection successful but department table does not exist.")

except mysql.connector.Error as err:
    print(f"MySQL: Connection failed - {err}")

finally:
    if 'connection' in locals() and connection.is_connected():
        connection.close()

This script uses mysql-connector-python to connect to the MySQL database and perform a basic query to check for the Department table. Let’s run the script:

$ python test_mysql_connect.py
MySQL: Connection successful and department table exists.

The output confirms that both the database connection and the Department table exist.

4. Conclusion

In this article, we explored practical ways to verify database connectivity using Bash and Python. These checks help ensure applications can access required resources and that key tables exist. Implementing such tests can greatly improve deployment reliability and system monitoring.