Have ideas to improve npm?Join in the discussion! »

    pg-testdb

    1.0.4 • Public • Published

    pg-testdb

    Richard Wen
    rrwen.dev@gmail.com

    Module for testing PostgreSQL queries inside isolated databases

    npm version Build Status Coverage Status npm GitHub license Twitter

    Install

    1. Install Node.js (v6.0.0+)
    2. Install pg-testdb via npm
    npm install pg-testdb --save-dev
    

    For the latest developer version, see Developer Install.

    Usage

    The easiest way to use this package is to first install the template generator pg-testdb-template globally with npm:

    npm install -g pg-testdb-template
    
    

    Use pg-testdb-template to generate a template file named pg-testdb-template.js in the current directory for editing:

    pg-testdb-template
    

    The template generated will be similar to the Full Example provided in the Guide.

    See the Guide for more details.

    Guide

    This guide will help you understand how to run tests inside a test PostgreSQL database using pg-testdb.

    A step-by-step guide, full example, and tape example are provided.

    Step 1. Define Connection Options

    First create an object options to store the temporary database name and connection details:

    • testdb: Name of the temporary database to create and test on (must not already exist)
    • messages: Set to true to enable create, drop, and error messages or false to disable
    • connection: Object containing PostgreSQL connection details
    • connection.host: Host IP address of the PostgreSQL database to connect to
    • connection.port: Port of the PostgreSQL database to connect to
    • connection.user: Name of PostgreSQL user with administrative privileges
    • connection.password: Password for connection.user
    var options = {
      testdb: 'pgtestdb',
      messages: false,
      connection: {
        host: 'localhost',
        port: 5432,
        user: 'user_name',
        password: 'secret_password'
      }
    };

    Step 2. Define Test Queries

    Define an Array [] of test queries to be run:

    • Each test query in the array is a callback function that consumes a client object from the pg package
    • Test queries in the array are run one after another (in order)
    options.tests = [];

    2.1 Creating a Test Table

    Initialize your tests by connecting the client object and creating a table named created_table with columns some_text and some_number:

    some_text some_number
    options.tests[0] = client => {
      client.connect();
      return client.query('CREATE TABLE created_table (some_text text, some_number numeric);')
        .then(() => {
          // do something after table creation
          console.log('Test table created!');
        })
        .catch(err => {
          // handle table creation error
          console.error('Test table creation failed.');
        });
    };

    2.2 Inserting Values into the Test Table

    Insert values 'text data 1', 1), ('text data 2', 2) into created_table after its creation:

    some_text some_number
    text data 1 1
    text data 2 2
    options.tests[1] = client => {
      return client.query("INSERT INTO created_table VALUES ('text data 1', 1), ('text data 2', 2);")
        .then(() => {
          // do something after insert
          console.log('INSERT test passed!');
        })
        .catch(err => {
          // handle insert error
          console.error('INSERT test failed.');
        });
    };

    2.3 Querying Values from the Test Table

    Select all values from created_table after inserting the values:

    options.tests[2] = client => {
      return client.query('SELECT * FROM created_table;')
        .then(res => {
          // do something after select
          console.log('SELECT test passed!');
          console.log(res.rows[0]); // {some_text: 'text data 1', some_number: '1'}
          console.log(res.rows[1]); // {some_text: 'text data 2', some_number: '2'}
        })
        .catch(err => {
          // handle select error
          console.log('SELECT test failed.');
        });
    };

    3. Run the Test Queries

    Using the options object with the connection details defined from Step 1 and test queries defined from Step 2, the test queries can then be executed in order.

    Running pgtestdb will:

    1. Create the temporary database options.testdb
    2. Run the test queries
    3. Drop the temporary database options.testdb whether tests passed or failed
    var pgtestdb = require('pg-testdb');
    pgtestdb(options, (err, res) => {
      // Do something after dropping the test database
      console.log('Testing ended.');
    });

    4. Full Example

    The code below sets up a temporary test database in PostgreSQL and runs queries in it.

    When the code is executed, the following happens:

    1. Test database details are defined in the options object
    2. Test functions are defined for the test database inside the options.tests array
    3. Test functions in options.tests are run in order inside the test database named by options.testdb
    4. Test database is dropped after the test functions in options.tests are run or if an error occurs
    5. Steps 1 to 4 are repeated when the code is run again to isolate options.tests inside of options.testdb
    var pgtestdb = require('pg-testdb');
     
    // 1. Define test database details
    // Enter your Postgres connection details below
    // The user should be have super user privileges
    // "testdb" is the test database, which should not already exist
    var options = {
      testdb: 'pgtestdb', // test db name
      messages: false, // display info
      connection: { // postgres connection details
        host: 'localhost',
        port: 5432,
        user: 'user_name', // should be a super user
        password: 'secret_password'
      }
    };
     
    // 2. Define test functions
    // Add test functions to execute inside the test database in order
    // Each function has access to the client object from pg (https://www.npmjs.com/package/pg)
    // Typical usage of the object involves returning "client.query();"
    options.tests = [
     
      // 2.1 Define initial test function
      // The first function should should run "client.connect();" to connect to the test database
      // This function can be used to initialize tables for testing
      client => {
        client.connect(); // IMPORTANT: connect client
        return client.query('CREATE TABLE created_table (some_text text, some_number numeric);')
          .then(() => {
            // Do something after table creation
            console.log('Test table "created_table" created.');
          })
          .catch(err => {
            // Handle table creation error
            console.log('Test table "created_table" creation failed.');
          });
      },
     
      // 2.2 Define second test function
      // The second function runs after the first one succeeds
      // This function can be used to include data into the table created from the first function
      client => {
        return client.query("INSERT INTO created_table VALUES ('text data 1', 1), ('text data 2', 2);")
          .then(() => {
            // Do something after insert
            console.log('INSERT test passed!');
          })
          .catch(err => {
            // Handle insert error
            console.log('INSERT test failed.');
          });
      },
     
      // 2.3 Define third test function
      // The third function runs after the second one succeeds
      // This function can be used to query the inserted data from the third function
      client => {
        return client.query('SELECT * FROM created_table;')
          .then(res => {
            // Do something after select query
            console.log('SELECT test passed!');
            console.log(res.rows[0]); // {some_text: 'text data 1', some_number: '1'}
            console.log(res.rows[1]); // {some_text: 'text data 2', some_number: '2'}
          })
          .catch(err => {
            // Handle select query error
            console.log('SELECT test failed.');
          });
      }
     
      // 2.4 Define additional test functions
      // Any number of functions following the above structure can be defined
      // If a function errors out, the test database will be dropped and the error handled
    ];
     
    // 3. Run test functions in test database
    // Each function in "options.tests" is run in order inside the defined "options.testdb"
    // If an error occurs, the error will be handled as defined and the test database dropped
    // Re-running this with the defined "options" will recreate the test database and run the test functions inside it
    pgtestdb(options, (err, res) => {
     
      // 4. Drop test database
      // The test database is dropped if all tests succeed or if an error occurs
      // Do something after dropping the test database
      console.log('Test database "pgtestdb" dropped.');
    });

    This example can be generated with pg-testdb-template as shown in Usage.

    5. Example with tape

    A testing framework such as tape can be used with pg-testdb such that the test functions and execution is inside tape's test function call:

    var pgtestdb = require('pg-testdb');
    var test = require('tape');
     
    // (test_db) Define a test database
    var options = {
      testdb: 'pgtestdb', // test db name
      messages: false, // display info
      connection: { // postgres connection details
        host: 'localhost',
        port: 5432,
        user: 'user_name', // should be an admin user
        password: 'secret_password'
      }
    };
     
    // (test_tape) Define test functions and run inside tape
    test('Tests for tape example', t => {
     
      // (test_functions) Define test functions
      options.tests = [
     
        // (test_init) Connect client and create test table
        client => {
          client.connect();
          return client.query('CREATE TABLE created_table (some_text text, some_number numeric);')
            .then(() => {
              t.pass('Test table "created_table" created.');
            })
            .catch(err => {
              t.fail('Test table "created_table" creation failed.');
            });
        },
     
        // (test_1) Test inserts into test table
        client => {
          return client.query("INSERT INTO created_table VALUES ('text data 1', 1), ('text data 2', 2);")
            .then(() => {
              t.pass('INSERT test passed!');
            })
            .catch(err => {
              t.fail('INSERT test failed.');
            });
        },
     
        // (test_2) Test select query on test table
        client => {
          return client.query('SELECT * FROM created_table;')
            .then(res => {
              t.pass('SELECT test passed!');
            })
            .catch(err => {
              t.fail('SELECT test failed.');
            });
        }
      ];
     
      // (test_run) Run the tests
      pgtestdb(options, (err, res) => {
        t.comment('Test database "pgtestdb" dropped.');
      });
    });

    See tests/test.js for more examples with tape.

    Developer Notes

    Developer Install

    Install the latest developer version with npm from github:

    npm install git+https://github.com/rrwen/pg-testdb
    

    Install from git cloned source:

    1. Ensure git is installed
    2. Clone into current path
    3. Install via npm
    git clone https://github.com/rrwen/pg-testdb
    cd pg-testdb
    npm install
    

    Tests

    1. Clone into current path git clone https://github.com/rrwen/pg-testdb
    2. Enter into folder cd pg-testdb
    3. Ensure tape and moment are available
    4. Setup test environment (See tests/README.md)
    5. Run tests
    6. Results are saved to ./tests/log with each file corresponding to a version tested
    npm install
    npm test
    

    Upload to Github

    1. Ensure git is installed
    2. Inside the pg-testdb folder, add all files and commit changes
    3. Push to github
    git add .
    git commit -a -m "Generic update"
    git push
    

    Upload to npm

    1. Update the version in package.json
    2. Run tests and check for OK status
    3. Login to npm
    4. Publish to npm
    npm test
    npm login
    npm publish
    

    Implementation

    The npm package pg-testdb was implemented with pg and pgtools. pg was used for creating client connections to PostgreSQL databases in Node.js, while pgtools was used to temporarily create and drop PostgreSQL databases:

    1. Create a temporary database with pgtools
    2. Create a client connection to the temporary database with pg
    3. Drop the temporary database with pgtools

    Install

    npm i pg-testdb

    DownloadsWeekly Downloads

    30

    Version

    1.0.4

    License

    MIT

    Last publish

    Collaborators

    • avatar