sql-unittest v1.1.2
SQL Queries Unit Testing
A package for writing unit tests for SQL queries.
Usage
This package allows assert methods for writing unit tests for sql queries to check number of records and whether an object is present or not in the results of the test query.
The package simulates a databse instance to allow it to fill with mock tables and data relevant to the query being tested. Then, the test query can be run against this instance created and get the results. Then unit tests can be written.
Following steps are required to write unit tests in before hook in mocha. 1) First a db instance needs to be created ( instantiateDb())
2) Create the tables relevant to the query being tested (createTable()).
3) Insert the data in the table. (insert())
4) Run the query being tested depending on the type. If the query being tested is select query then use either execute() or runQuery() and if its insert update or delete use runDataManiulationQuery(). (execute(), runQuery() or runDataManipulationQuery())
For the unit tests following asserts are provided 1) assertLength -> Check whether the number of records in the results of test query is equal to the given value.
2) assertContains -> Check whether the given object is present in the results of the test query.
3) assertNotContains -> Check whether the given object is not present in the results of the test query.
Then in after hook 1) Close the db conncection (closeDb())
List of Functions
instantiateDb()
instantiate mock database.
Return
Returns the database instance created.createTable(databaseName, tableName, tableSchema)
creates table in the database specified by databaseName.
Parameters
databaseName -> should be the database instance created by instantiateDb function.
tableName -> should be a string specifing the name of table to be created.
tableSchema -> should be a JSON object with column names as keys and column data type as values.insert(databaseName, tableName, data)
inserts data in the database specified by databaseName
Parameters
databaseName -> should be the database instance created by instantiateDb function.
tableName -> should be a string specifing the name of table to be created.
data -> should be an array of JSON objects with column names as keys and value of that column name as values for the JSON object.execute(databaseName, testQuery, testQueryParameters)
runs the testQuery with testQueryParameters
Parameters
databaseName -> should be the database instance created by instantiateDb function.
testQuery -> the query being tested enclosed within ``.
testQueryParameters(optional) -> if the testQuery contains parameters, they can be passed as an array.Return
Returns the results got after running the test query.runQuery(databaseName, testQuery, testQueryParameters)
runs the testQuery with testQueryParameters
Parameters
databaseName -> should be the database instance created by instantiateDb function.
testQuery -> the query being tested enclosed within ``.
testQueryParameters(optional) -> if the testQuery contains parameters, they can be passed as an array.Return
Returns the results got after running the test query.runDataManipulationQuery(databaseName, tableName, testQuery, testQueryParameters)
runs the testQuery with testQueryParameters
Parameters
databaseName -> should be the database instance created by instantiateDb function.
tableName -> the name of table which is being manipulated in test query.
testQuery -> the query being tested enclosed within ``.
testQueryParameters(optional) -> if the testQuery contains parameters, they can be passed as an array.Return
Returns all the records of the table which is being manipulated in the test query.closeDb(databaseName)
close the database connection
Parameters
databaseName -> should be the database instance created by instantiateDb function.deleteAll(databaseName, tableName)
deletes all the records from the table
Parameters
databaseName -> should be the database instance created by instantiateDb function.
tableName -> the name of table.
AssertContains(results, searchObject)
asserts that the searchObject is present in the test query results.
Parameters
results -> Array of records.
searchObject -> JSON object to be searched in the results array.AssertNotContains(results, searchObject)
asserts that the searchObject is not present in the test query results.
Parameters
results -> Array of records.
searchObject -> JSON object to be searched in the results array.AssertLength(results, expectedLength)
checks whether the number of records is equal to the expected length.
Parameters
results -> Array of records.
expectedLength -> integer denoting the expected number of records.Example
Below is an example of a test suite with mocha framework.
Suppose you want to write unit tests for a query which fetches all records of table "contacts" with "Name" and "PhoneNumber" as columns and contains 2 records. Then, test cases can be written to check the number of records returned and to check whether a given record is present or not in the result generated by running test query.
let unitTest = require('sql-unittest');
//Test suite
describe('Testing contacts select query', () => {
let db;
let testSelectQuery = 'SELECT * FROM contacts';
let testQueryResult;
//Table Schema
let schema = {
Name: 'TEXT',
PhoneNumber: 'NUMERIC',
};
//Mock data
let data = [
{
Name: 'Test1',
PhoneNumber: 1234567890
},
{
Name: 'Test2',
PhoneNumber: 0987654321
}
];
before(async () => {
//instantiate a new db instance
let db = unitTest.instantiateDb();
//create table
await unitTest.createTable(db, "contacts", schema);
//insert data in table
await unitTest.insert(db, "contacts", data);
//Run the query being tested
testQueryResult = await unitTest.runQuery(db, testQuery, []);
});
after(function(){
unitTest.closeDb(db);
});
//Test Cases
it('should verify that number of records returned by the testquery is 2', async() => {
unitTest.AssertLength(testQueryResult, 2);
});
it('should contain the given user in the results from running test query', async() => {
unitTest.AssertContains(testQueryResult, {Name: 'Test1', PhoneNumber: 1234567890});
});
it('should not contain the given user in the results from running test query', async() => {
unitTest.AssertNotContains(testQueryResult, {Name: 'Test3'});
});
});Suppose you want to write unit tests for a query which deletes a user with Test1 Name from table "contacts" . Then, test cases can be written to check the number of records left and to check whether the record supposed to be deleted is deleted from the table.
describe('Testing contacts delete query', () => {
let db;
let testDeleteQuery = 'DELETE FROM contacts';
let testQueryResult;
//Table Schema
let schema = {
Name: 'TEXT',
PhoneNumber: 'NUMERIC',
};
//Mock data
let data = [
{
Name: 'Test1',
PhoneNumber: 1234567890
},
{
Name: 'Test2',
PhoneNumber: 0987654321
}
];
before(async () => {
//instantiate a new db instance
let db = unitTest.instantiateDb();
//create table
await unitTest.createTable(db, "contacts", schema);
//insert data in table
await unitTest.insert(db, "contacts", data);
//Run the query being tested
testQueryResult = await unitTest.runDataManipulationQuery(db, "contacts", testQuery, ['Test2']);
});
after(function(){
unitTest.closeDb(db);
});
//Test Cases
it('should verify that number of records returned by the testquery is 2', async() => {
unitTest.AssertLength(testQueryResult, 1);
});
it('should not contain the given user in the results from running test query', async() => {
unitTest.AssertNotContains(testQueryResult, {Name: 'Test2'});
});
});