/
DB and API Tests

DB and API Tests

Owned by Mélissa Henriquez
2023-10-13 09:19

With Alithya GoTest, you can perform database tests and API tests.

Database Tests (DB)

The database library is already included and available in Alithya GoTest.
You can access this library directly in the scripting section of Alithya GoTest the way you access any other library. Simply add a new line of script, and then type the word Database for Alithya GoTest to automatically display the available options for this library.
Here are simple examples for using this library to connect to PostgreSQL, Oracle, DB2, SQL Server, MySQL, MariaDB and SAP HANA.
For Kafka databases, there is the KafkaLibrary.

PostgreSQL

Keyword: Connect To Database (DatabaseLibrary)


CommentKeywordArgument

Connect To Database(DatabaseLibrary)dbapiModuleName=psycopg2dbName=petclinicdbUsername=petclinicdbPassword=rootdbHost=ct-demo.askida.landbPort=9091

Table Must Exist(DatabaseLibrary)vets




Row Count Is Equal To X(DatabaseLibrary)select * from vets




Check If Exists In Database(DatabaseLibrary)select * from vets where first_name = 'James'

Disconnect from the current connected database. This keyword works for all the databases specified aboveDisconnect From Database(DatabaseLibra

Oracle

Keyword: Connect To Database Using Custom Params

Driver required on the database server under test: ojdbc8-21.7.0.0.jar
Compatible with the following versions: Oracle 21c, 19c, 18.3, 12.2
dbApiModuleName: jaydebeapidbConnectString: 'oracle.jdbc.driver.OracleDriver', 'jdbc:oracle:thin:@HOST:PORT/DB_NAME', ['USERNAME', 'PASSWORD']


    Example:

Comment Return Value  KeywordArgument


Connect To Database Using Custom Params(DatabaseLibrary)dbapiModuleName=jaydebeapidb_connect_string='oracle.jdbc.driver.OracleDriver', 'jdbc:oracle:thin:@${DATABASE_SERVER_NAME}:49161/xe', ['system', 'oracle']  

${first_name_male}First Name Male(FakerLibrary)


Execute Sql String(DatabaseLibrary)insert into employee (name) values ('${first_name_male}')sansTran=true

${first_name_female}First Name Female(FakerLibrary)

Sends an SQL Query for a SELECT statement on the database. It doesn't work for most other statements such as:

CREATE TABLE, INSERT INTO, etc.
To avoid potential issues:

  • It is very important to include the semicolon; character at the end of the SQL query.

  • The parameter "sansTran" must be "True" for most databases. Otherwise, it can fail the test in Alithya GoTest if the database is set to automatically save all changes permanently to the database.| |Execute Sql String(DatabaseLibrary)|insert into employee (name) values ('${first_name_female}')|sansTran=true|


Execute Sql String(DatabaseLibrary)insert into employee (name) values ('${first_name_female}')sansTran=true

Execute Sql String(DatabaseLibrary)
insert into employee (name) values ('${first_name_female}')
sansTran=true
@{query_result}Query(DatabaseLibrary)select * from employee;sansTran=true

Log(BuiltIn)${query_result}

Disconnect From Database(DatabaseLibrary)

DB2

dbApiModuleName: jaydebeapi

dbConnectString: 'com.ibm.db2.jcc.DB2Driver', 'jdbc:db2://HOST:PORT/DB_NAME', ['USERNAME', 'PASSWORD']


  • Driver required on server: db2jcc-db2jcc4.jar
  • Compatible with the following versions: DB2 11.1; DB2 10.5; DB2 10.1; DB2 9.7; DB2 9.5

·       Example:


Return Value KeywordArgument

Connect To Database Using Custom Params(DatabaseLibrary)dbapiModuleName=jaydebeapidb_connect_string='com.ibm.db2.jcc.DB2Driver', 'jdbc:db2://${DATABASE_SERVER_NAME}:50000/SAMPLE', ['db2inst1', 'askidact']
@{query_result}Query(DatabaseLibrary)select * from employeesansTran=true

Log(BuiltIn)${query_result}

Disconnect From Database(DatabaseLibrary)


Back to Top


SQL Server 

dbApiModuleName: jaydebeapi

dbConnectString: 'com.microsoft.sqlserver.jdbc.SQLServerDriver', 'jdbc:sqlserver://HOST:PORT;database=DB_NAME', ['USERNAME', 'PASSWORD']

dbApiModuleName: jaydebeapi

dbConnectString: 'com.microsoft.sqlserver.jdbc.SQLServerDriver', 'jdbc:sqlserver://HOST\\INSTANCE;database=DB_NAME', ['USERNAME', 'PASSWORD']

  • Driver required on server: mssql-jdbc-11.2.1.jre8.jar
  • Compatible with the following versions:
  • Azure SQL Managed Instance
  • SQL Server 2017
  • SQL Server 2016
  • SQL Server 2014
  • PDW 2008R2 AU34

  • Azure SQL Database

  • SQL Server 2012  

  • SQL Server 2008R2


  • Example 1:

Return Value 

Keyword

Argument



Log(BuiltIn)${query_result}

Connect To Database Using Custom Params(DatabaseLibrary)dbapiModuleName=jaydebeapidb_connect_string='com.microsoft.sqlserver.jdbc.SQLServerDriver', 'jdbc:sqlserver://sql-dev-mmq.askida.lan:1433;database=Claims_MMQ', ['sa', 'password_xxx']
@{query_result}Query(DatabaseLibrary)select * from AccountssansTran=true

Disconnect From Database(DatabaseLibrary)

  • Example 2:

Return Value 

Keyword

Argument



Connect To Database Using Custom Params(DatabaseLibrary)dbapiModuleName=jaydebeapidb_connect_string='com.microsoft.sqlserver.jdbc.SQLServerDriver', 'jdbc:sqlserver://sql-dev-mmq.askida.lan\\MMQSQLSERVER;database=Claims_MMQ', ['sa', 'password_xxx']


Back to Top


MySQL

  • Keyword: Connect To Database Using Custom Params (DatabaseLibrary)


dbApiModuleName: jaydebeapidbConnectString: 'com.mysql.cj.jdbc.Driver', 'jdbc:mysql://HOST:PORT/DB_NAME', ['USERNAME', 'PASSWORD']


  • Driver required on server: mysql-connector-java-8.0.30.jar
  • Compatible with the following versions:
    • MySQL 8.0 / MySQL 5.7 

·       

  • Example:

Return Value 

Keyword

Argument



Connect To Database Using Custom Params(DatabaseLibrary)dbapiModuleName=jaydebeapidb_connect_string='com.mysql.cj.jdbc.Driver', 'jdbc:mysql://ct-prod-test2.askida.lan:3306/askida', ['root', 'password']
@{query_result}Query(DatabaseLibrary)select * from projectsansTran=true

Log(BuiltIn)${query_result}

Disconnect From Database(DatabaseLibrary)

Back to Top


MariaDB

  • To connect to MySQL, you can use MySQL or MariaDB Driver.

  • Keyword: Connect To Database Using Custom Params (DatabaseLibrary)
dbApiModuleName: jaydebeapidbConnectString: 'com.mysql.jdbc.Driver', 'jdbc:mysql://HOST:PORT/DB_NAME', ['USERNAME', 'PASSWORD']
  • Driver required on server: mariadb-java-client-3.0.8.jar
  • Compatible with the following versions:
    • MariaDB and MySQL server versions 5.5.3 and later.

  • Example:

Return Value 

Keyword

Argument



Connect To Database Using Custom Params(DatabaseLibrary)dbapiModuleName=jaydebeapidb_connect_string='org.mariadb.jdbc.Driver', 'jdbc:mariadb://ct-prod-test2.askida.lan:3306/askida', ['root', 'password']
@{query_result}Query(DatabaseLibrary)select * from projectsansTran=true

Log(BuiltIn)${query_result}

Disconnect From Database(DatabaseLibrary)

Back to Top


SAP HANA

  • Library.Keyword: DatabaseLibrary.Connect To Database Using Custom Params


dbApiModuleName: jaydebeapidbConnectString: 'com.sap.db.jdbc.Driver', 'jdbc:sap://HOST:PORT/DB_NAME', ['USERNAME', 'PASSWORD']


  • Driver required on server: ngdbc-2.14.9.jar
  • Compatible with the following versions:
    • All versions of SAP HANA

  • Example:

Return Value 

Keyword

Argument



Connect To Database Using Custom Params(DatabaseLibrary)dbapiModuleName=jaydebeapidb_connect_string='com.sap.db.jdbc.Driver', 'jdbc:sap://ct-prod-test2.askida.lan:8080/askida', ['root', 'password']
@{query_result}Query(DatabaseLibrary)select * from project;sansTran=true

Log(BuiltIn)${query_result}

Disconnect From Database(DatabaseLibrary)

Back to Top



In addition, information for each keyword is shown by pressing the F1 key on the keyboard. See Keyword F1 Help for details.

 
This special keyword contains the keyword "Connect To Database Using Custom Params" with the correct connection string for ease of use.


PostGres

Oracle 

DB2

SQL Server

Special Keyword 

Connect to PostGresSQL database

Connect to Oracle database

Connect to DB2 database

Connect to SQL Server database

dbName 

postgres




dbUsername

postgres

system

user

root

dbPassword

admin

oracle

pass

admin

dbHost

127.0.0.1

127.0.0.1

127.0.0.1

127.0.0.1

dbPort

5432

49161

50000

3306

Keyword: Query

Keyword: Execute SQL String

Sends an SQL Query for a SELECT statement on the database. Doesn't work for most other statements such as CREATE TABLE, INSERT INTO, etc.
To avoid potential issues:

  • It is very important to include the semicolon ( ; ) character at the end of the SQL query.

  • The parameter "sansTran" must be "True" for most databases. Otherwise, it can fail the test in Alithya GoTest if the database is set to automatically save all changes permanently to the database.

      Example #1: Sending an SQL query to get the names of all tables in the database.

      Example #2: Extracting a specific data piece from the SQL results.



     Example #3: Using an alternative method, record specific data pieces from the SQL results.




     

KeywordArguments
Execute Sql String(DatabaseLibrary)COMMIT;True




Back to Top



API Tests

Keywords RequestLibrary - JSON File Testing

In addition, information for each keyword is shown by pressing the F1 key on the keyboard. See Keyword F1 Help for details.

Keywords: Create Session & Get Request

Creates a session and an HTTP request to fetch data. It can download any file, including HTML, JSON, or XML files.

For the exercise, it is a prerequisite to download the following JSON file from the Internet.


Depending on the JSON file's content, either the "Get From Dictionary" or "Get From List" keyword will be needed to extract data.
Example: JSON file downloaded from http://api.github.com/events. This site's data is changed each time it is accessed.

Return Value

Keyword

Arguments 



Comment(BuiltIn)

Fetches a JSON file 



Create Session(RequestsLibrary)

githubAlias

http://api.github.com

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Comment(BuiltIn)

Should verify the HTTP status code is 200



Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200


Log(BuiltIn)

Content of the variable resp: ${resp}



Log(BuiltIn)

Content of the variable resp_statut_code: ${resp_status_code}



Log(BuiltIn)

Content of the variable resp : ${resp.json()[0]}



Back to Top



Keyword: Get Dictionary Keys (for JSON extraction)

In the context of a JSON file downloaded from the internet, extracts the keys of a JSON entity. 
Example:

  Simplified JSON example

Return Value

Keyword

Arguments 



Comment(BuiltIn)

Fetches a JSON file from URL address



Create Session(RequestsLibrary)

githubAlias

http://api.github.com+

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200


Comment(BuiltIn)

Extract the first "object"


${jsonKeys}

Get Dictionary Keys(Collections)

${resp.json()[0]}



List Should Contain Value(Collections)

${jsonKeys}

id

Keyword: Get From Dictionary (for JSON extraction)

In the context of a JSON file downloaded from the internet, extracts the value from the specified key.
Example #1: Get the value of the key "public" of the first object.
 

 JSON Example

Back to Top



Return Value

Keyword

Arguments 



Comment(BuiltIn)

Fetches a JSON file from URL address



Create Session(RequestsLibrary)

githubAlias

http://api.github.com+

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200


Comment(BuiltIn)

Extract the value


${value}

Get From Dictionary(Collections)

${resp.json()[0]}

public


Should Be Equal As Strings(BuiltIn)

${value}

True

 

  Example #2: Get the value deeper than the first level of the first object.

Return Value

Keyword

Arguments 



Create Session(RequestsLibrary)

githubAlias

http://api.github.com+

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200

${subdata}

Get From Dictionary(Collections)

${resp.json()[0]}

repo

${value}

Get From Dictionary(Collections)

${subdata}

name


Log (BuiltIn)

The current value of the first repo's name is ${value}


Return Value

Keyword

Arguments 



Create Session(RequestsLibrary)

githubAlias

http://api.github.com+

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200

${subdata}

Get From Dictionary(Collections)

${resp.json()[0]}

repo

${value}

Get From Dictionary(Collections)

${subdata}

name


Log (BuiltIn)

The current value of the first repo's name is ${value}


 
 

Example #3: Get the value of the key "id" of the three first objects.

  Example #3: Get the value of the key "id" of the three first objects.

Return Value

Keyword

Arguments 



Create Session(RequestsLibrary)

githubAlias

http://api.github.com+

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200

${value1}

Get From Dictionary(Collections)

${resp.json()[0]}

id

${value2}

Get From Dictionary(Collections)

${resp.json()[1]}

id

${value3}

Get From Dictionary(Collections)

${resp.json()[2]}

id


Log (BuiltIn)

The extracted id values are: ${value1}, ${value2}, ${value3}



Keyword: Get From List (for JSON extraction)
In the context of a JSON file downloaded from the internet, extracts an item if the data is in list form.
Example:

  JSON File

Return Value

Keyword

Arguments 



Create Session(RequestsLibrary)

githubAlias

http://api.github.com+

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200

${firstEntireObject}

Get From List(Collections)

${resp.json()}

0

${secondEntireObject}

Get From List(Collections)

${resp.json()}

1


Back to Top


Keywords: To Json & Get Value From Json (JSONPath)

The "To Json" keyword converts the text data from a JSON file into a JSON structure. Afterwards, the "Get Value From Json" keyword allows you to use JSONPath on a JSON structure to get the data.       The "Get Value From Json" keyword might require the "Get From Dictionary" or "Get From List" keyword to extract more specific data.
Example:

Return Value

Keyword

Arguments 



Create Session(RequestsLibrary)

githubAlias

http://api.github.com+

${resp}

Get Request(RequestsLibrary)

githubAlias

/events


Should Be Equal As Strings(BuiltIn)

${resp_status_code}

200


Comment(BuiltIn)

Convert the data into JSO data structure


${jsonStructure}

To json(RequestsLibrary)

${resp.content}

0

${secondEntireObject}

Get From List(Collections)

${resp.json()}

1


Back to Top



     
 


Keywords RESTLibrary - JSON File Testing (with API REST).

The REST library allows you to send GET, PUT, POST, PATCH, and DELETE requests to an API. Compared to the RequestLibrary, it allows you to easily extract the sent requests and the received responses into a JSON file. It also allows schema outputs and more direct testing of the received data.
The following examples use the Postman Echo API.
In addition, information for each keyword is shown by pressing the F1 key on the keyboard. See Keyword F1 Help for details.
 

Keyword: Get (REST API - GET request)

Uses the keyword "Get" to send a GET request to a REST API service. 
Example: Sends a GET request to https://postman-echo.com/get?foo1=bar1&foo2=bar2.


Keyword: Get Value From Json (REST API - GET request)

Uses the keyword "Get" to send a GET request to a REST API service. 
Example: Sends a GET request to https://postman-echo.com/get?foo1=bar1&foo2=bar2. Then tests if the response's status is 200 (OK) and if the response's "foo1" field has the value "bar1".


Back to Top


Keyword: Rest Instances (REST API)

After sending REST API requests, allows to write the sent requests and received responses in a JSON log file.
Example: Sends three GET requests and uses the "Rest Instances" keyword to get their sent requests and received responses.

 

 

Keyword: Output Schema (REST API)

After sending REST API requests, outputs the partial schema discovered based on the responses so far.
Example: Sends a GET request and outputs the current partial schema
Example #1: Outputs the partial schema into a JSON file.

 
Example #2: Outputs the partial schema into a variable. This variable can be read with the keyword "Get Value From Json" after.


Back to Top


Keyword: String (REST API - Asserts string value)

Asserts a string value in the last received API response. Can use JSONPath or a custom-specific path to determine the position in the JSON to validate.
Also consider the keywords "Integer", "Boolean", "Object", "Array", and similar keywords for other types of fields to assert


Keyword: Post (REST API - POST request)

Sends a POST request to the target REST API.
Example: Sends a POST request to https://postman-echo.com/post. This example includes headers, and they are assigned as an argument at the end of the "Post" keyword. This specific API will return some of the same header data in its response
 

Keyword: Put (REST API - PUT request)

Sends a PUT request to the target REST API.
Example: Sends a PUT request to https://postman-echo.com/put. This example includes headers, and they are assigned as an argument at the end of the "Put" keyword. This specific API will return some of the same header data in its response


Back to Top




Keyword: Patch (REST API - PATCH request)

Sends a PATCH request to the target REST API.
Example: Sends a PATCH request to https://postman-echo.com/patch. This example includes headers, and they are assigned as an argument at the end of the "Patch" keyword. This specific API will return some of the same header data in its response.



 



Back to Top



Keyword: Delete (REST API - DELETE request)

Sends a DELETE request to the target REST API.
Example: Sends a DELETE request to https://postman-echo.com/delete. This example includes headers, and they are assigned as an argument at the end of the "Delete" keyword. This specific API will return the same sent header data in its response.


Back to Top




Keyword: Get (Basic Auth for REST API)

A more advanced example of a GET request for Basic Authentication.
Example: Sends a GET request to https://postman-echo.com/basic-auth to authenticate as the username "postman" and the password "password". In this example, the username and password are converted into a base64 value and are sent in the headers as "Authorization=Basic cG9zdG1hbjpwYXNzd29yZA==
 


Back to Top




 Related articles

Information about this API can be found here: https://docs.postman-echo.com/?version=latest+
Postman, a tool for API tests https://www.postman.com/downloads/

Related content

Desktop Testing 
Desktop Testing 
Alithya GoTest - En
Read with this
Test BD et API
Test BD et API
Alithya GoTest - Fr
More like this
Navigating the Interface
Navigating the Interface
Alithya GoTest - En
Read with this
Documentation
Documentation
Alithya GoTest - En
More like this
Libraries and Functionalities
Libraries and Functionalities
Alithya GoTest - En
Read with this
Version 2.13.0
Version 2.13.0
Alithya GoTest - En
More like this

©️2023 Alithya GoTest - All Rights Reserved
Need Help? You have a suggestion?