Introduction #

Databases are the backbone of modern applications, storing and managing the vast amounts of data that drive business operations, user interactions, and decision-making processes.
Database testing involves a systematic approach to validate the structure, behavior, and performance of a database. It encompasses a variety of tests to ensure that data is accurately stored, retrieved, and manipulated without any loss or corruption. It also involves checking that database operations adhere to business rules and performance expectations, and that they are secure from potential vulnerabilities.

Pre-requisite to start Database Testing in SoliteraTA #

Requirements: #

• Ensure that one or more of the following databases are installed that we support:
  1. MySQL 
  2. PostgreSQL 
  3. SQL Server 
  4. MariaDB 
  5. MongoDB
• You have to provided valid URL/IP of the database application for automation.
• Connection should be established and are working fine.
  1. For example, to setup connection in MYSQL, click on the below link
     • https://www.youtube.com/watch?v=nIr3vbGS7e4
• All the permission are granted to the user to test the database.

Application File format -: #

1. For STANDARD Connection -: #

<environment> 
<config> 
<name>configuration_name </name> 
<type>database</type> 
<db_type>Database_Name </db_type> 
<db_version>Database_Version</db_version> 
<db_url>Database_URL</db_url> 
<db_con_type>Database_Connection_Type</db_con_type> 
<db_username>Database_Connection_Username</db_username>
<db_password>Database_Connection_Password</db_password> <db_schema>Schema_Name</db_schema> 
<db_driver_name>Driver_Name</db_driver_name> 
</config> 
</environment> 

For example -: 

1. MySQL
<environment>  
<config> 
 <name>mySqlDatabase</name> 
 <type>database</type> 
 <db_type>MYSQL</db_type> 
 <db_version>8.3.0</db_version> 
 <db_url>jdbc:mysql://localhost:3306</db_url> 
 <db_con_type>STANDARD</db_con_type> 
 <db_username>root</db_username> 
 <db_password>root</db_password> 
 <db_schema>demo_test</db_schema> 
 <db_driver_name>com.mysql.cj.jdbc.Driver</db_driver_name> </config>
</environment>

2. MongoDB 
<environment> 
<config> 
 <name>mongoDB_testing</name> 
 <type>database</type> 
 <db_type>MongoDB</db_type> 
 <db_version>7.0.6</db_version> 
 <db_url>localhost:27017</db_url> 
 <db_con_type>STANDARD</db_con_type> 
 <db_username>root</db_username> 
 <db_password>root</db_password>
 <db_schema>local</db_schema> 
 <db_driver_name>com.mongodb.client.MongoClient</db_driver_name> </config> 
</environment> 

Description of Elements of the Application file: #

<environment>:

This is the root element, which encapsulates the entire configuration for the environment setup.

<config>: 

This element contains all the configuration details for a specific database connection.

<name>: 

A name for this particular database configuration. It helps in identifying different  configurations when multiple connections are defined. 

<type>: 

Specifies the type of configuration. In this context, it’s set to “database,” indicating that this  configuration pertains to a database connection. 

<db_type>: 

The type of database being connected to, such as MySQL, PostgreSQL, or MongoDB.

<db_version>: 

The version of the database. This is useful for ensuring compatibility with database features and driver capabilities. 

<db_url>: 

The URL or IP address where the database server can be accessed. It typically includes the protocol and port number, e.g., jdbc:mysql://localhost:3306. 

<db_con_type>: 

The connection type, usually indicating the protocol or method used to connect to the database, such as JDBC. i.e. STANDARD or SSH 

<db_username>: 

The username required to authenticate and connect to the database. <db_password>: 

The password corresponding to the db_username for database access. <db_schema>:

The specific schema within the database that the application will interact with. Schemas are used to organize and separate database objects. 

<db_driver_name>: The name of the driver that facilitates the connection to the database. For example, for MySQL, it might be com.mysql.cj.jdbc.Driver.

2. For SSH Connection -: #

<environment> 
<config> 
 <name>configuration_name</name> 
 <type>database</type> 
 <db_type>database_name</db_type> 
 <db_version>database_version</db_version> 
 <db_url>database_url</db_url> 
 <db_con_type>connection_type</db_con_type> 
 <db_username>database_username</db_username> 
 <db_password>database_password</db_password> 
 <db_schema>database_schema_name</db_schema> 
 <db_driver_name>database_driver_name</db_driver_name> 
 <local_port>local_port_no</local_port> 
 <remote_port>remote_port_no</remote_port> 
 <local_host>localhost_name</local_host> 
 <remote_host>remotehost_name</remote_host> 
 <port_no>port_no</port_no> 
 <ssh_username>ssh_user_name</ssh_username> 
 <ssh_password>ssh_password</ssh_password> 
</config> 
</environment> 

For Example -:  

<environment> 
<config> 
 <name>ssh_config</name> 
 <type>database</type> 
 <db_type>MYSQL</db_type> 
 <db_version>8.3.0</db_version> 
 <db_url>jdbc:mysql://localhost:3306</db_url> 
 <db_con_type>SSH</db_con_type> 
 <db_username>root</db_username> 
 <db_password>root</db_password> 
 <db_schema>demo_schema</db_schema> 
 <db_driver_name>com.mysql.cj.jdbc.Driver</db_driver_name> 
 <local_port>3306</local_port> 
 <remote_port>3306</remote_port> 
 <local_host>localhost</local_host> 
 <remote_host>remote.host.com</remote_host> 
 <port_no>22</port_no> 
 <ssh_username>sshuser1</ssh_username> 
 <ssh_password>12345</ssh_password> 
</config> 
</environment>

Description of Elements of the Application file: #

<environment>: 

This is the root element, which encapsulates the entire configuration for the environment setup.

<config>: 

This element contains all the configuration details for a specific database connection.

<name>: 

A name for this particular database configuration. It helps in identifying different configurations  when multiple connections are defined.

<type>: 

Specifies the type of configuration. In this context, it’s set to “database,” indicating that this  configuration pertains to a database connection. 

<db_type>: 

The type of database being connected to, such as MySQL, PostgreSQL, or MongoDB.

<db_version>: 

The version of the database. This is useful for ensuring compatibility with database features and  driver capabilities. 

<db_url>: 

The URL or IP address where the database server can be accessed. It typically includes the protocol  and port number, e.g., jdbc:mysql://localhost:3306. 

<db_con_type>: 

The connection type, usually indicating the protocol or method used to connect to the database,  such as JDBC. i.e. STANDARD or SSH 

<db_username>: 

The username required to authenticate and connect to the database. 

<db_password>: 

The password corresponding to the db_username for database access. 

<db_schema>: 

The specific schema within the database that the application will interact with. Schemas are used to  organize and separate database objects. 

<db_driver_name>: 

The name of the driver that facilitates the connection to the database. For example, for MySQL, it  might be com.mysql.cj.jdbc.Driver. 

<local_port> 

The local port number to which the SSH tunnel will bind on the local machine. This is the port you’ll  connect to locally. 

<remote_port> 

The port number on the remote host to which the SSH tunnel will forward traffic. This is the port on  which the database listens. 

<local_host> 

The hostname or IP address of the local machine where the SSH tunnel will bind. Typically, this is  localhost.

<remote_host> 

The hostname or IP address of the remote machine where the database is hosted.

<port_no> 

The port number used for establishing the SSH connection, typically 22 for SSH.

<ssh_username> 

The username for authenticating the SSH connection. 

<ssh_password> 

The password for authenticating the SSH connection.

Database Script File: – #

Feature: Database_Testing

Scenario Outline: Connect to database
Given connect to database with "LocalDatabase" configuration
Then verify the database connection
Then run query "<InsertData>"
Then run "<selectCommand>" query and verify "Abhay" text in "EFname" column name in the database
Then create a procedure "procedure1" with query "<selectCommand>" in the database
Then run "procedure1" procedure and verify "Abhay" text in "EFname" column name in the database
Then verify "procedure2" procedure in the database
Then close database connection with "LocalDatabase" configuration

Examples: 
|selectCommand|InsertData|