Модуль для работы с sqlite3 -1


AliExpress RU&CIS

Сегодня Я хотел бы поделиться своей не большой разработкой, которая помогает мне уже более чем пол года: "Модуль для работы с sqlite3". Заранее предупрежу, мне 14 лет и данный модуль разработан не для улучшения безопасности, а для ускорения работы с SQL запросами.

Концепция

Вместо написания SQL запросов мы будем передавать ключи, значения, названия таблиц, условия и callback'и, которые будут вызывать по завершению запросов(в каждый callback мы будем передавать ошибку и результат, если такой есть).

Представим модуль в виде класса.

Всего будет 4 метода:

  1. getData() - для получения данных из таблицы.

  2. insertData() - для добавления данных в таблицу.

  3. updateData() - для обновления данных в таблице.

  4. deleteData() - для удаления данных из таблицы.

Конечно же с помощью 4 методов приведенных выше мы не сможем исключить все виды запросов, но в моем случаи эти запросы самые частые.

Кодим

Для начало создадим сам класс, настроем экспорт и подключимся к БД и создадим метод, который будет добавлять кавычки у элемента, если тип элемента соответствует типу строки.

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
};

Для начало напишем метод, который будет возвращать данные из таблицы, не обязательно, но мы будем учитывать, кол-во строк, которые нам будут возвращаться(одна или несколько).

Начинаем собирать запрос с ключевого слова "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
};

Остается совсем чуть-чуть, напишем метод для удаления данных(она максимально простой) и метод для добавления данных.

Добавления данных начнем с указания таблицы, потом передадим все ключи, которые мы хотим установить и заканчиваем установкой всех значений(значение должно иметь такой же индекс как и ключ, которому необходимо установить это значение).

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
};

Пример эксплуатации

Представим, что мы имеем таблицу users с полями id, first_name и last_name

Все совпадения случайны

id

first_name

last_name

1

Иван

Иванович

2

Николай

Николаевич

3

Георгий

Георгиевич

Получение данных

Для начала давайте получим Имя самого первого пользователя в нашей системе.

const { database } = require('database');

database.getData(['first_name'], 'users', 'WHERE `id` = 1', false, (err, row) => {
    if (row !== undefined)
        console.log(row);
    else
        console.log('User not found');
});

После выполнения ранее приведенного кода, мы получим ответ показанный ниже.

{
		'first_name': 'Иван'
}

Обновление данных

Обновим все имена Иван на Игорь.

const { database } = require('database');

database.updateData(['first_name'], ['Игорь'], 'users', 'WHERE `first_name` = \'Иван\'');

В результате мы получим следующую таблицу.

id

first_name

last_name

1

Игорь

Иванович

2

Николай

Николаевич

3

Георгий

Георгиевич

Добавление данных

Добавим нового пользователя Илью Ильича.

const { database } = require('database');

database.insertData(['first_name', 'last_name'], ['Илья', 'Ильич'], 'users');

В результате мы получим следующую таблицу.

id

first_name

last_name

1

Игорь

Иванович

2

Николай

Николаевич

3

Георгий

Георгиевич

4

Илья

Ильич

Удаление данных

Удалим всех Николаевичей из нашей таблицы.

const { database } = require('database');

database.deleteData('users', 'WHERE `last_name` = \'Николаевич\'');

В результате мы получим следующую таблицу.

id

first_name

last_name

1

Игорь

Иванович

3

Георгий

Георгиевич

4

Илья

Ильич

На этом все, спасибо за внимание!
Проект на GitHub




Комментарии (19):

  1. debagger
    /#23011098 / +1

    Вы бы хоть пару примеров добавили, как ваш модуль использовать. Ну и в целом непонятно, зачем — если уж хочется уйти от написания SQL, что вам мешает использовать <название любой ORM по вкусу>, там хотя бы уже подумали о защите от SQL-инъекций например.

    • debagger
      /#23012304

      Извиняюсь за несколько токсичный комментарий выше. Илья, ты молодец, что развиваешься и не стесняешься делиться своими достижениями. По статье есть следующие замечания:
      1. Опечатки. Их довольно много, кто-то не придаст значения, а кто-то сочтет признаком неаккуратности и легкомыслия (например это может быть твой будущий работодатель, когда захочет ознакомиться с твоим профилем на хабре перед тем как принять решение, стоит ли тебя брать на работу).
      2. Нет примеров использования. Если ты не объясняешь, как использовать твою разработку, то ее никто не сможет использовать, а проще всего объяснить в чем удобство через примеры.
      3. Ну и наконец настоятельно рекомендую почитать про ORM, а также про атаки SQL-инъекцией.

    • Ilya-cmd
      /#23013654

      Добавил примеры использования, спасибо

  2. /#23011154 / +4

    Перед любой презентацией широкой публике "своей разработки" стоит делать абзац-введение с рассказом о том, чем она лучше или хотя бы просто отличается от аналогичных уже существующих и используемых решений.

    Впрочем, если вы не лукавите в профиле и вам действительно 14 лет, то почему бы и не позаниматься велосипедостроением пока есть время, это всё-таки полезный опыт. Главное вовремя остановиться :)

  3. kai3341
    /#23011700 / +1

    Ilya-cmd дабы не собирать минусы, укажите в начале статьи ваш возраст. Статья очень слабенькая для хабра, но очень, очень крутая для 14 лет


    Теперь по содержимому. Вместо критики я расскажу о своём первом проекте, который ушёл в прод. Мне было вовсе не 14 лет, а 25. Моей задачей было реализовать протокол CRQ для доступа к данным АСКУЭ. С учётом вариаций протокола, я пытался генерировать строку SQL запроса и потом её исполнять. Всё классно работало, но в коде была аналогичная каша


    Немного после, читая статью об SQL инъекциях, я задался вопросом, а насколько им подвержено моё приложение. Для значения из блока WHERE — целочисленного идентификатора — я по пробовал передать 1 OR 1=1. Результаты меня огорчили — БД сожрала запрос и не подавилась. Это значит, что любой пользователь мог "случайно" передать в запросе что-то типа 1; DROP DATABASE; -- — и дела были бы очень плохи. Во 2й версии этого приложения я отказался от процедурной генерации SQL-запросов в пользу статических SQL-запросов просто в файлах, а значения подставлял через драйвер БД — это на корню решило проблему SQL-инъекции


    К чему была вся эта лирика? Уязвимости одинаковые. Через вашу либу атакующий может сделать с вашей БД любое непотребство, включая удаление


    Теперь о производительности. Я сам из мира python, и там в любом учебнике в главе Строки обычно капсом пишут "НЕ КОНКАТЕНИРУЙТЕ СТРОКИ" (да простит меня хабр за капс). Почему? Строки иммутабельны, и на каждый чих создаётся новая строка. Причём тут JS? При том, что в JS строки тоже иммутабельны, и болячки ровно те же — низкая производительность. Насколько низкая? Каждая конкатенация приводит к созданию нового инстанса строки, что реализуется копированием всей существующей строки с добавлением новой части. То есть в худшем случае имеем нотацию O(N^2)


    Теперь рекомендация. Посмотрите в сторону knex. Эта ORM не связывает по рукам и ногам рахитектурными ограничениями и вроде позволяет писать сложные запросы. Это ИМХО — опыт работы с Oracle не прошёл бесследно, я знаю, насколько крутые возможности БД, я торчу от написания сложных SQL-запросов

    • Ilya-cmd
      /#23012124

      Спасибо за комментарий, но я делал этот модуль, только чтобы облегчить себе жизнь, чтобы не писать большие SQL-запросы, а просто передавать определенные значения, а модуль уже сам все соберет, не было задачи сделать какую-то защиту, во всех моих проектах(а в основном я сейчас делаю VKMiniApps под ключ) данный модуль используется на сервере, а сервер связывается с клиентом или сервером по socket или api соответственно и пользователь не имеет свободного доступа к созданию запросов.

      Про конкатенацию строк учту, спасибо, ранее не знал, какую боль она может предоставить. А вот по поводу возраста готов поспорить, на личном опыте проверил, что не нужно указывать возраст в статье, если кому-то станет интересно, то он(как и Вы) зайдет и посмотрит профиль.

      Еще раз спасибо за критику.

      • TheSprightlyDuke
        /#23012254

        Зря готовы поспорить, хотя для 14-ти это характерно и вполне нормально. Вам прямо сказали причину лучшести указания возраста — слабость статьи для АУДИТОРИИ хабра. Указав возраст Вы не отпугнёте читателя, скорее заинтересуете. И получите лучше отношение по итогам прочтения, т.к. я лично, и многие, нечасто хожу в профили (это уже надо прям заинтересовать).

        Дело Ваше, но я бы рекомендовал прислушаться к совету. Несамовлюблённых эгоцентристов, коих тут большинство, Вы не отпугнёте этим, не беспокойтесь. А вот получить негатив, обоснованный из-за завышенных возрастных ожиданий, это на раз.

        • Ilya-cmd
          /#23013576

          Хорошо, попробую еще раз указать свой возраст в начале статьи, надеюсь все получится…

        • Ilya-cmd
          /#23013652

          Добавил в начало статьи свой возраст, спасибо

          • TheSprightlyDuke
            /#23013804

            В идеале приписать ещё что-то типа «прошу сильно не ругаться». Иначе может быть воспринято как самоиндульгенция для всего и всея. Но и так можно.

            Пожалуйста.

            • Ilya-cmd
              /#23013934

              Сейчас проверять не буду, но если мне не изменяет память, то такое не приветствуется на Habr'е

      • sshikov
        /#23013178

        Однако на первый взгляд смотрится так, что эксплуатация все же возможна. Данные для where кто формирует?

        • Ilya-cmd
          /#23013582

          Как уже ранее было сказано не хватает примеров использование, это я исправлю в течение дня, данные для WHERE формируете Вы сами, передовая условие в `condition`

          • sshikov
            /#23014174

            Если условие содержит ввод пользователя, и вы его не экранируете, то почти наверняка можно эксплуатировать

      • kai3341
        /#23013266

        не имеет свободного доступа к созданию запросов

        Это опасная иллюзия. Попробуйте в поля ввода вашего приложения ввести символ ' и залогируйте запросы.

    • evkochurov
      /#23014944 / +1

      Каждая конкатенация приводит к созданию нового инстанса строки, что реализуется копированием всей существующей строки с добавлением новой части. То есть в худшем случае имеем нотацию O(N^2)

      Вот тут вы заблуждаетесь, большие строки js-движки при конкатенации не копируют. ru.stackoverflow.com/questions/694846/string-stringbuilder

  4. Zenitchik
    /#23026652

    А в promise обернуть не догадались?
    Я свой модуль для sqlite3 писал сугубо ради использования с await.