SQLite layer of CRUD

Simple module for common CRUD operations with SQLite database.

const DB = require('sqlite-crud');
DB.connectToDB('./server/your-file.db');

Dependencies

• sqlite3

How to use

I haven't published this package in npm, therefore you'll need to add github link as source to you package.json:

"dependencies": {
    // ...
    "sqlite-crud": "git+https://github.com/artemdemo/sqlite-crud.git#2.0"
}

API

getDB

Will return database object and you can use it for your own calls

const myDB = DB.getDB();

insertRow

/**
 * Insert row into given table
 * @param tableName {String}
 * @param data {Object}
 */
DB.insertRow(tableName, data);

DB.insertRow('tasks', {
        name: newTask.name,
        description: newTask.description,
        added: now.format('YYYY-MM-DD HH:mm:ss'),
        updated: now.format('YYYY-MM-DD HH:mm:ss')
    }).then((result) => {
        resolve({
            id: result.id,
            added: now.format('YYYY-MM-DD HH:mm:ss'),
            updated: now.format('YYYY-MM-DD HH:mm:ss')
        });
    });

updateRow

/**
 * Update table row
 * @param tableName {String}
 * @param data {Object}
 * @param where {Array}
 *  [
 *      {
 *          column: '',
 *          comparator: '',
 *          value: ''
 *      },
 *      ...
 *  ]
 */
DB.updateRow(tableName, data, where);

DB.updateRow('tasks', {
        name: task.name,
        description: task.description,
        updated: now.format('YYYY-MM-DD HH:mm:ss')
    }, [{
        column: 'id',
        comparator: '=',
        value: task.id
    }]).then(() => {
        resolve();
    });

getRows

/**
 * Fetch rows from the table
 * @param tableName {String}
 * @param where {Array}
 *  [
 *      {
 *          column: '',
 *          comparator: '',
 *          value: ''
 *      },
 *      ...
 *  ]
 * @returns {Promise}
 */
 DB.getRows(tableName, where);

DB.getRows('tasks', [{
        column: 'id',
        comparator: '=',
        value: id
    }])
        .then((rows) => {
            resolve(rows);
        });

deleteRows

/**
 * Delete rows from table
 * @param tableName {String}
 * @param where {Array}
 *  [
 *      {
 *          column: '',
 *          comparator: '',
 *          value: ''
 *      },
 *      ...
 *  ]
 */
DB.deleteRows(tableName, where);

DB.deleteRows('tasks', [{
        column: 'id',
        comparator: '=',
        value: taskId
    }]).then(() => {
        resolve();
    });

queryOneRow

/**
 * Return first result row
 * @param query {String}
 */
DB.queryOneRow(query);

DB.queryOneRow('SELECT * FROM table_name;')
    .then((result) => {
        // result - will be an object
        // If there is no match will be undefined
        resolve(result);
    });

queryRows

/**
 * Return all results (rows) for given query
 * @param query {String}
 * @param parameters {Array} array of parameters to th query (optional)
 */
DB.queryRows(query, parameters);

DB.queryRows('SELECT * FROM table_name WHERE name = ?;', ['Row name'])
    .then((rows) => {
        // rows - will be an array
        resolve(rows);
    });

run

/**
 * Proxy function for run
 * @param query {String}
 * @param parameters {Array} array of parameters to th query (optional)
 * @param options {Object} (optional)
 * @param options.saveRun {Boolean} if `true` will always resolve promise
 */

DB.run(
    'INSERT INTO table_name (name, description) VALUES (?, ?)',
    ['run-test', 'run-test description']
).then((result) => {
    resolve(result.lastID);
});

Migration

File migration

{
    "query": "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name CHAR (100), description TEXT, added DATETIME);"
}

or

{
    "queries": [
        "CREATE TABLE dummy01 (id INTEGER PRIMARY KEY AUTOINCREMENT, name CHAR (100));",
        "CREATE TABLE dummy02 (id INTEGER PRIMARY KEY AUTOINCREMENT, name CHAR (100));"
    ]
}

/**
 * Migrate file
 * @param pathToFile {String} - path to migrate json file (or folder)
 */
DB.migrate(pathToFile)

DB.migrate('./migrations/20151102_create_dummy_tables.json')
    .then(() => {
        // Migration was successful
    });

Print errors

Set env variable DEBUG:

DEBUG=sqlite-crud:*

SQLite database manager

Multiplatform software for sqlite database managing:

http://sqlitestudio.pl/