Today I would like to share my small development, which has been helping me for more than half a year: "Module for working with sqlite3".
Concept
Instead of writing SQL queries, we will pass keys, values, table names, conditions and callbacks that will be called upon completion of queries (we will pass an error and result, if any, to each callback).
Let's represent the module as a class.
There will be 4 methods in total:
getData () - to get data from the table.
insertData () - to add data to the table.
updateData () - to update the data in the table.
deleteData () - to delete data from the table.
Of course, with the help of the 4 methods above, we will not be able to exclude all types of requests, but in my case, these requests are the most frequent.
Code
To begin with, we will create the class itself, configure the export and connect to the database and create a method that will add quotes to the element if the element type matches the string type.
class DataBase {
/**
*
* @readonly
*/
static sqlite3 = require('sqlite3').verbose();
/**
*
* @readonly
*/
static database = new this.sqlite3.Database('./database/database.db');
static ToString(value) {
return typeof(value) === 'string' ? '\'' + value + '\'' : value;
}
}
module.exports = {
database: DataBase
};
To begin with, we will write a method that will return data from the table, not necessarily, but we will take into account the number of rows that will be returned to us (one or more).
"SELECT", , , , "*" "" , , . . , - , .
class DataBase {
/**
*
* @readonly
*/
static sqlite3 = require('sqlite3').verbose();
/**
*
* @readonly
*/
static database = new this.sqlite3.Database('./database/database.db');
/**
*
* @param {String[]} keys
* @param {String} table
* @param {String} condition
* @param {Boolean} some
* @param {Function()} callback
*/
static getData(keys, table, condition = '', some = true, callback = () => {}) {
let sql = 'SELECT ';
for (let i = 0; i < keys.length; i++) {
sql += keys[i] === '*' ? keys[i] : '`' + keys[i] + '`';
if (keys.length > i + 1)
sql += ', ';
}
sql += ' FROM `' + table + '` ' + condition;
if (some)
this.database.all(sql, (err, rows) => {
callback(err, rows);
});
else
this.database.get(sql, (err, row) => {
callback(err, row);
});
};
static ToString(value) {
return typeof(value) === 'string' ? '\'' + value + '\'' : value;
}
}
module.exports = {
database: DataBase
};
.
, , .
class DataBase {
/**
*
* @readonly
*/
static sqlite3 = require('sqlite3').verbose();
/**
*
* @readonly
*/
static database = new this.sqlite3.Database('./database/database.db');
/**
*
* @param {String[]} keys
* @param {String} table
* @param {String} condition
* @param {Boolean} some
* @param {Function()} callback
*/
static getData(keys, table, condition = '', some = true, callback = () => {}) {
let sql = 'SELECT ';
for (let i = 0; i < keys.length; i++) {
sql += keys[i] === '*' ? keys[i] : '`' + keys[i] + '`';
if (keys.length > i + 1)
sql += ', ';
}
sql += ' FROM `' + table + '` ' + condition;
if (some)
this.database.all(sql, (err, rows) => {
callback(err, rows);
});
else
this.database.get(sql, (err, row) => {
callback(err, row);
});
};
/**
*
* @param {String[]} keys
* @param {Values[]} values
* @param {String} table
* @param {String} condition
* @param {Function()} callback
*/
static updateData(keys, values, table, condition, callback = () => {}) {
let sql = 'UPDATE `' + table + '` SET ';
for (let i = 0; i < keys.length; i++) {
sql += '`' + keys[i] + '` = ' + this.ToString(values[i]);
if (keys.length > i + 1)
sql += ', ';
}
sql += ' ' + condition;
this.database.run(sql, (err) => {
callback(err);
});
}
static ToString(value) {
return typeof(value) === 'string' ? '\'' + value + '\'' : value;
}
}
module.exports = {
database: DataBase
};
-, ( ) .
We start adding data by specifying the table, then pass all the keys that we want to set and end with setting all values (the value must have the same index as the key that needs to set this value).
class DataBase {
/**
*
* @readonly
*/
static sqlite3 = require('sqlite3').verbose();
/**
*
* @readonly
*/
static database = new this.sqlite3.Database('./database/database.db');
/**
*
* @param {String[]} keys
* @param {String} table
* @param {String} condition
* @param {Boolean} some
* @param {Function()} callback
*/
static getData(keys, table, condition = '', some = true, callback = () => {}) {
let sql = 'SELECT ';
for (let i = 0; i < keys.length; i++) {
sql += keys[i] === '*' ? keys[i] : '`' + keys[i] + '`';
if (keys.length > i + 1)
sql += ', ';
}
sql += ' FROM `' + table + '` ' + condition;
if (some)
this.database.all(sql, (err, rows) => {
callback(err, rows);
});
else
this.database.get(sql, (err, row) => {
callback(err, row);
});
};
/**
*
* @param {String[]} keys
* @param {Values[]} values
* @param {String} table
* @param {String} condition
* @param {Function()} callback
*/
static updateData(keys, values, table, condition, callback = () => {}) {
let sql = 'UPDATE `' + table + '` SET ';
for (let i = 0; i < keys.length; i++) {
sql += '`' + keys[i] + '` = ' + this.ToString(values[i]);
if (keys.length > i + 1)
sql += ', ';
}
sql += ' ' + condition;
this.database.run(sql, (err) => {
callback(err);
});
}
/**
* @param {String[]} keys
* @param {String[]} values
* @param {String} table
* @param {Function()} callback
*/
static insertData(keys, values, table, callback = () => {}) {
let sql = 'INSERT INTO `' + table + '` (';
for (let i = 0; i < keys.length; i++) {
sql += '`' + keys[i] + '`';
if (keys.length > i + 1)
sql += ', ';
}
sql += ') VALUES (';
for (let i = 0; i < values.length; i++) {
sql += this.ToString(values[i]);
if (values.length > i + 1)
sql += ', ';
}
sql += ')';
this.database.run(sql, (err) => {
callback(err);
});
};
/**
*
* @param {String} table
* @param {String} condition
* @param {Function()} callback
*/
static deleteData(table, condition = '', callback = () => {}) {
this.database.run('DELETE FROM `' + table + '` ' + condition, (err) => {
callback(err);
});
}
static ToString(value) {
return typeof(value) === 'string' ? '\'' + value + '\'' : value;
}
}
module.exports = {
database: DataBase
};
That's all, thanks for your attention!
Project on GitHub