Доступ к базе данных MySQL

Если объем данных большой, то удобнее воспользоваться базой данных. В качестве примера рассмотрим доступ к базе данных MySQL с помощью пакета mysql2. Установим пакет:

C:\book\e1>npm install mysql2

В раздел dependencies будет добавлена следующая строка:

"dependencies": {
   "mysql2": "^2.2.5"
}

Теперь нам понадобится собственно СУБД MySQL, а также программа для управления базой данных phpMyAdmin. Обе программы входят в состав пакета XAMPP. Именно им мы и воспользуемся.

Программа phpMyAdmin написана на языке PHP, поэтому, если вы хотите администрировать базы данных, вместе с сервером MySQL необходимо запустить и Web-сервер Apache. Для этого открываем XAMPP Control Panel (C:\xampp\xampp-control.exe) и щелкаем на кнопках Start напротив пунктов Apache и MySQL. Далее запускаем Web-браузер и в адресной строке вводим http://localhost/phpmyadmin/. В итоге должна отобразиться стартовая страница программы phpMyAdmin.

Создадим новую базу данных. Для этого слева щелкаем на ссылке Создать БД. Справа в поле Имя базы данных вводим название electron. Из списка Сравнение выбираем пункт utf8_general_ci. Нажимаем кнопку Создать. Новая база данных отобразится в списке слева.

Подключение к базе данных

Подключение к базе данных осуществляется с помощью метода createConnection(). Формат метода:

const mysql = require('mysql2/promise');
Promise<Connection> = mysql.createConnection(<Опции>);

В качестве параметра указывается объект со следующими основными свойствами:

host — домен, на котором расположен сервер MySQL;
user — имя пользователя;
password — пароль пользователя;
database — название базы данных.

В случае успешного подключения метод возвращает объект Promise, содержащий объект соединения Connection. Отключиться от базы данных позволяет метод end():

Promise<void> = conn.end([<Опции>]);

При нажатии кнопки подключимся к базе данных:

document.getElementById('btn1').addEventListener('click', async () => {
   try {
      let conn = await mysql.createConnection({
         host: 'localhost',
         user: 'root',
         password: '',
         database: 'electron'
      });
      console.log('Подключено');
      await conn.end();
   } catch (e) {
      console.log(e);
   }
});

Если все сделано правильно, то в окне консоли получим сообщение Подключено. В противном случае отобразится сообщение об ошибке.

Создание таблицы

Для создания таблицы можно воспользоваться методом query() объекта Connection. Формат метода:

Promise<results, fields> = conn.query(<SQL-запрос>[, <Данные>]);

Давайте добавим в базу данных electron три таблицы (листинг 9.4).

Листинг 9.4. Создание таблицы

async function connect() {
   try {
      let conn = await mysql.createConnection({
         host: 'localhost',
         user: 'root',
         password: '',
         database: 'electron'
      });
      return conn;
   } catch (e) {
      console.log(e);
      return null;
   }
}
document.getElementById('btn2').addEventListener('click', async () => {
   let conn = await connect();
   if (!conn) return;
   try {
      await conn.query("CREATE TABLE `user` ("
         + "`id_user` mediumint(9) auto_increment, "
         + "`email` char(50), "
         + "`passw` char(32), "
         + "PRIMARY KEY (`id_user`), "
         + "UNIQUE KEY (`email`) "
         + ") ENGINE=MyISAM DEFAULT CHARSET=utf8");
      await conn.query("CREATE TABLE `rubr` ("
         + "`id_rubr` mediumint(6) auto_increment, "
         + "`name_rubr` char(150), "
         + "PRIMARY KEY (`id_rubr`) "
         + ") ENGINE=MyISAM DEFAULT CHARSET=utf8");
      await conn.query("CREATE TABLE `site` ("
         + "`id_site` mediumint(9) auto_increment, "
         + "`id_user` mediumint(9), "
         + "`id_rubr` mediumint(6), "
         + "`url` char(255), "
         + "`title` char(80), "
         + "`msg` text, "
         + "`iq` tinyint, "
         + "PRIMARY KEY (`id_site`), "
         + "KEY (`id_rubr`) "
         + ") ENGINE=MyISAM DEFAULT CHARSET=utf8");
      console.log('Таблицы созданы');
   } catch (e) {
      console.log(e);
   }
   finally {
      try {
         await conn.end();
      } catch (e) { }
   }
});

Добавление записей

Добавить записи в таблицу можно несколькими способами. Начнем с уже знакомого нам метода query() объекта Connection. Добавим пользователя в таблицу user:

let [results, fields] = await conn.query(
   "INSERT INTO `user` (`email`, `passw`)"
   + " VALUES ('user1@mail.ru', 'password')");

Если нам необходимо узнать какой индекс был автоматически сгенерирован при добавлении записи (поле id_user в таблице user обозначено как auto_increment), то обратимся к свойству insertId объекта results:

console.log('Индекс новой записи:', results.insertId);

Запрос можно также выполнить с помощью метода execute() объекта Connection. Формат метода:

Promise<results, fields> = conn.execute(<SQL-запрос>[, <Данные>]);

Пример добавления нового пользователя:

let [results, fields] = await conn.execute(
   "INSERT INTO `user` (`email`, `passw`)"
   + " VALUES ('user2@mail.ru', 'пароль')");
console.log('Индекс новой записи:', results.insertId);

В некоторых случаях в SQL-запрос необходимо подставлять данные, полученные от пользователя. Если данные не обработать и подставить в SQL-запрос, то пользователь получает возможность видоизменить запрос и, например, зайти в закрытый раздел без ввода пароля. Чтобы значения были правильно подставлены, необходимо использовать подготовленные запросы. С составе SQL-запроса вместо значений следует указать символ вопроса:

"INSERT INTO `rubr` VALUES (?, ?)"

Сами данные следует передать в виде массива во втором параметре. При этом все специальные символы внутри строки будут автоматически экранированы. Давайте добавим несколько рубрик в таблицу rubr:

let arr = ['Программирование', 'Музыка',
           'Поисковые \' " порталы', 'Кино'];
for (const value of arr) {
   await conn.execute(
      'INSERT INTO `rubr` (`name_rubr`) VALUES (?)',
      [value]);
}
console.log('Добавлено');

Добавим два сайта в таблицу site:

let sql = 'INSERT INTO `site` (`id_user`, `id_rubr`, `url`,'
          + ' `title`, `msg`, `iq`) VALUES (?, ?, ?, ?, ?, ?)';
await conn.execute(sql,
   [1, 1, 'http://python.org', 'Python', 
    'Язык программирования Python', 50]);
await conn.execute(sql,
   [1, 3, 'http://google.ru', 'Гугль', 'Поисковый портал', 80]);
console.log('Добавлено');

Обновление и удаление записей

Изменим название рубрики с индексом 3 и удалим рубрику с индексом 4 (листинг 9.5).

let [results] = await conn.execute("UPDATE `rubr` "
   + "SET `name_rubr`='Поисковые порталы' "
   + "WHERE `id_rubr`=3");
console.log('Изменено записей:', results.changedRows);
let [results2] = await conn.execute("DELETE FROM `rubr` "
   + "WHERE `id_rubr`=4");
console.log('Удалено записей:', results2.affectedRows);

Получение записей

Получим все записи из таблицы rubr с сортировкой по имени рубрики:

let [results, fields]  = await conn.query(
   'SELECT * FROM `rubr` ORDER BY `name_rubr`');
let msg = '';
for (let i = 0; i < results.length; i++) {
   msg += results[i]['id_rubr'] + ' '
        + results[i]['name_rubr'] + '<br>';
}
let res = document.getElementById('result');
res.innerHTML = msg;

Результат доступен через переменную results, а описание полей через переменную fields.

Получим записи сразу из трех таблиц, выполнив всего один запрос:

let [results, fields]  = await conn.query(
   "SELECT `site`.`url` AS `site_url`, "
   + "`site`.`title` AS `site_title`, "
   + "`rubr`.`name_rubr` AS `rubr_name`, "
   + "`user`.`email` AS `user_email` "
   + "FROM `site`, `rubr`, `user` "
   + "WHERE `site`.`id_rubr`=`rubr`.`id_rubr` "
   + "AND `site`.`id_user`=`user`.`id_user`");
let msg = '';
for (let i = 0; i < results.length; i++) {
   msg += results[i]['site_url'] + '<br>';
   msg += results[i]['site_title'] + '<br>';
   msg += results[i]['rubr_name'] + '<br>';
   msg += results[i]['user_email'] + '<br><br>';
}
let res = document.getElementById('result');
res.innerHTML = msg;

Результат:

http://python.org
Python
Программирование
user1@mail.ru
http://google.ru
Гугль
Поисковые порталы
user1@mail.ru