graphic-sql-test

Graphic-sql-test is designed for writing readable tests which test your SQL queries without a physical DB, by using the Sqlite library's in-memory model. In this way the library is limited to the SQL supported by Sqlite, so if you use some specific features of MySQL, Postgresql etc. which are not supported by Sqlite, you may find this library inconvenient.

Justification

Generally, your SQL queries can be complex and provide various results in various data scenarios. Therefore you want to set up those scenarios and run the query on all of them, to see if it always produces the correct results.

The accent is on readable, because in my experience preparing an SQL test by writing many update and insert statements is not very readable. You have to read the queries carefully, instead of getting the understanding on the first glance. Reading the test name can help, but if you want to understand how the test is implemented, it is very difficult. On the other hand, I think it is much easier to understand a DB setup and what is being tested by looking at the following example:

const { createDb, MockKnex } = require('graphic-sql-test');
const { getUserMoney } = require('./my-queries');
 
describe('getUserMoney', () => {
    it('Returns the correct amount when the specified ID exists', () => {
        const db = await createDb( `
            +----------------------------------+
            |       Wallet                     |
            +---------------+------------------+
            | user_id int   |  amount real     |
            +---------------+------------------+
            | 100           |    40000.00      |
            | 101           |    5000.00       |
            +---------------+------------------+
        `, `
            +---------------------------------------+
            |          Users                        |
            +-------------+-------------------------+
            |  id int     |   name varchar(30)      |
            +-------------+-------------------------+
            |  100        |     'Bob'               |
            |  101        |     'John'              |
            +-------------+-------------------------+
        `);
        const knex = new MockKnex(db);
        const result = await getUserMoney(knex, 101);
        expect(result).toEqual(5000);
    });
 
    it('Returns null if the specified ID does not exist', () => {
        const db = await createDb( `
            +----------------------------------+
            |       Wallet                     |
            +---------------+------------------+
            | user_id int   |  amount real     |
            +---------------+------------------+
            +---------------+------------------+
        `, `
            +---------------------------------------+
            |          Users                        |
            +-------------+-------------------------+
            |  id int     |   name varchar(30)      |
            +-------------+-------------------------+
            |  100        |     'Bob'               |
            +-------------+-------------------------+
        `);
        const knex = new MockKnex(db);
        const result = await getUserMoney(knex, 101);
        expect(result).toEqual(null);
    });
});

This library consumes ASCII drawings like this, and generates the proper SQL insert statements to create the tables and insert the data. After this you can run your queries on the "db" object returned as in the above snippet.

The library is extremely lightweight and can be used in any kind of testing framework like Jest etc.

API

createDb

Accepts any number of strings which define your tables, and returns a Promise which resolves to a database object. Once executed, it creates an Sqlite in-memory database, and runs generated queries to create the actual tables as specified by the drawing. When all queries are finished executing, the Promise will resolve to the database object and you can start doing your queries on it.

Details:

• The border symbols (+, |, -), as well as white space, do not have to be exactly correct (they are mostly ignored), so use your imagination how it looks best for you. However vertical lines must separate column definitions and values.
• The title line is taken as the table name
• The header line is interpreted as a number of column definitions. If a column does not contain a type, varchar(256) is taken as default.
• Each row with values generates an insert statement. The library does not add quotes for you, so for strings please use your own quotes (as in the example above).

run

Runs an SQL statement which does not yield rows (insert, delete, update, create etc). Returns a Promise which resolves when the statement is successfully completed, or rejects with an error.

const { run, createDb } = require('graphic-sql-test');
try {
    const db = await createDb(`... sql tables...`);
    await run(db, 'UPDATE users SET id = 100 WHERE id = 101');
} catch (e) {
    console.error(e);
}

getAll

Runs an SQL SELECT query. Returns a Promise which resolves with an array of results, or rejects with an error.

const { getAll, createDb } = require('graphic-sql-test');
try {
    const db = await createDb(`... sql tables ...`);
    const rows = await getAll(db, 'SELECT * FROM users');
    expect(rows).toEqual([{ id: 101, name: 'John' }]);
} catch (e) {
    console.error(e);
}

MockKnex

A class which accepts the above db object in the constructor, and can be used as a mock Knex object, for functions which expect a real Knex object and call the raw method on it. Other Knex functions are not yet supported. Placeholders with :variableName are supported, but placeholders with question marks (?) are not supported yet. Please feel free to contribute to the Knex functionality by creating a pull request with improvements, if you have an interest or free time to do so.

const { MockKnex, createDb } = require('graphic-sql-test');
try {
    const db = await createDb(`... sql tables ...`);
    const knex = new MockKnex(db);
    const [rows] = await knex.raw(
        'SELECT * FROM users where id = :id',
        { id: 101 },
    );
    expect(rows).toEqual([{ id: 101, name: 'John' }]);
} catch (e) {
    console.error(e);
}