035.3 Урок 1

035.3 Урок 1

Сертифікат:

Основи веброзробки

Версія:

1.0

Розділ:

035 Серверне програмування Node.js

Тема:

035.3 Основи SQL

Урок:

1 з 1

Вступ

Хоча ви можете написати власні функції для реалізації постійного сховища даних, може бути зручніше використовувати систему керування базами даних, щоб прискорити розробку та забезпечити кращу безпеку та стабільність для даних у форматі таблиць. Найпопулярнішою стратегією зберігання даних, організованих у взаємопов’язаних таблицях, особливо коли ці таблиці інтенсивно запитуються й оновлюються, є встановлення реляційної бази даних, яка підтримує Мова структурованих запитів (SQL, Structured Query Language), мову, орієнтовану на реляційні бази даних. Node.js підтримує різні системи керування базами даних SQL. Дотримуючись принципів переносимості та виконання у просторі користувача, прийнятих у Node.js Express, SQLite є відповідним вибором для постійного зберігання даних, які використовуються цим типом HTTP-сервера.

SQL

Мова структурованих запитів специфічна для баз даних. Операції запису та читання виражаються реченнями, які називаються statements (оператори) та queries (запити). І оператори, і запити складаються з clauses (команд), які визначають умови виконання операції.

Наприклад, імена та адреси електронної пошти можна зберігати в таблиці бази даних, яка містить поля name та email. База даних може містити кілька таблиць, тому кожна таблиця повинна мати унікальне ім’я. Якщо ми використовуємо назву contacts для таблиці імен і адрес електронної пошти, можна вставити новий запис за допомогою такого оператора:

INSERT INTO contacts (name, email) VALUES ("Carol", "carol@example.com");

Цей оператор вставки складається з команди INSERT INTO, яка визначає таблицю та поля, куди будуть вставлені дані. Друге речення, VALUES, встановлює значення, які будуть вставлені. Немає необхідності писати команду великими літерами, але це звичайна практика, щоб краще розпізнавати ключові слова SQL в операторі або запиті.

Запит до таблиці контактів виконується у такий самий спосіб, але з використанням команди SELECT:

SELECT email FROM contacts;
dave@example.com
carol@example.com

У цьому випадку команда SELECT email вибирає одне поле із записів у таблиці contacts. Фраза WHERE обмежує запит певними рядками:

SELECT email FROM contacts WHERE name = "Dave";
dave@example.com

У SQL є багато інших команд, і ми розглянемо деякі з них у наступних розділах. Але спочатку необхідно побачити, як інтегрувати базу даних SQL з Node.js.

SQLite

SQLite, мабуть, є найпростішим рішенням для включення функцій бази даних SQL до застосунку. На відміну від інших популярних систем керування базами даних, SQLite не є сервером баз даних, до якого підключається клієнт. Натомість SQLite надає набір функцій, які дають змогу розробнику створювати базу даних як звичайний файл. У випадку сервера HTTP, реалізованого за допомогою Node.js Express, цей файл зазвичай знаходиться в тому ж каталозі, що й сценарій сервера.

Перш ніж використовувати SQLite в Node.js, вам потрібно встановити модуль sqlite3. Виконайте наступну команду в каталозі встановлення сервера; тобто каталозі, що містить сценарій Node.js, який ви запускатимете.

$ npm install sqlite3

Майте на увазі, що існує кілька модулів, які підтримують SQLite, наприклад better-sqlite3, використання яких суттєво відрізняється від sqlite3. Приклади в цьому уроці розраховані на модуль sqlite3, тому вони можуть працювати не так, як очікувалося, якщо ви виберете інший модуль.

Відкриття бази даних

Щоб продемонструвати, як сервер Node.js Express може працювати з базою даних SQL, давайте напишемо сценарій, який зберігає та відображає повідомлення, надіслані клієнтом, ідентифікованим файлом cookie. Повідомлення надсилаються клієнтом за допомогою методу HTTP POST, а відповідь сервера може бути відформатована як JSON або HTML (з шаблону), залежно від формату, запитаного клієнтом. У цьому уроці не будуть детально розглядатися методи HTTP, файли cookie та шаблони. Наведені тут фрагменти коду припускають, що у вас уже є сервер Node.js Express, на якому ці функції налаштовані та доступні.

Найпростіший спосіб зберігання повідомлень, надісланих клієнтом, – це зберігати їх у глобальному масиві, де кожне повідомлення, надіслане раніше, асоціюється з унікальним ідентифікаційним ключем для кожного клієнта. Цей ключ можна надіслати клієнту як файл cookie, який надається серверу під час майбутніх запитів на отримання попередніх повідомлень.

Однак у цього підходу є недолік: оскільки повідомлення зберігаються лише в глобальному масиві, усі повідомлення будуть втрачені, коли поточний сеанс сервера завершиться. Одна з переваг роботи з базами даних та, що дані постійно зберігаються і не втрачаються при перезавантаженні сервера.

Використовуючи файл index.js як основний сценарій сервера, ми можемо включити модуль sqlite3 і вказати файл, який слугує базою даних, таким чином:

const sqlite3 = require('sqlite3')
const db = new sqlite3.Database('messages.sqlite3');

Якщо його ще не існує, файл messages.sqlite3 буде створено в тому ж каталозі, що й файл index.js. Усередині цього одного файлу будуть зберігатися всі структури та відповідні дані. Усі операції з базою даних, що виконуються в сценарії, будуть опосередковані константою db, яка є ім’ям, наданим новому об’єкту sqlite3, який відкриває файл messages.sqlite3.

Структура таблиці

Жодні дані не можуть бути вставлені в базу даних, поки не буде створена хоча б одна таблиця. Таблиці створюються за допомогою оператора CREATE TABLE:

db.run('CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY AUTOINCREMENT, uuid CHAR(36), message TEXT)')

Метод db.run() використовується для виконання операторів SQL у базі даних. Сам оператор записується як параметр для методу. Хоча оператори SQL повинні закінчуватися крапкою з комою, коли вводяться в процесорі командного рядка, крапка з комою необов’язкова в операторах, які передаються як параметри в програмі.

Оскільки метод run буде виконуватися кожного разу, коли сценарій виконується з node index.js, оператор SQL містить фразу умови IF NOT EXISTS, щоб уникнути помилок виконання в майбутньому, коли таблиця messages вже існує.

Поля, що складають таблицю messages, це id, uuid та message. Поле id – це унікальне ціле число, яке використовується для ідентифікації кожного запису в таблиці, тому воно створюється як PRIMARY KEY. Первинні ключі не можуть бути null, і в одній таблиці не може бути двох однакових первинних ключів. Тому майже кожна таблиця SQL має первинний ключ для відстеження вмісту таблиці. Хоча можна явно вибрати значення для первинного ключа нового запису (за умови, що воно ще не існує в таблиці), зручно, щоб ключ генерувався автоматично. Для цього використовується прапор AUTOINCREMENT у полі id.

Явне налаштування первинних ключів у SQLite необов’язкове, оскільки SQLite створює первинний ключ автоматично. Як зазначено в документації SQLite: «У SQLite рядки таблиці зазвичай мають 64-бітове ціле число `ROWID, яке є унікальним серед усіх рядків в одній таблиці. Якщо таблиця містить стовпець типу INTEGER PRIMARY KEY, тоді цей стовпець стає псевдонімом для ROWID. Потім ви можете отримати доступ до ROWID, використовуючи будь-яке з чотирьох різних імен, оригінальні три імена, описані вище, або ім’я, надане стовпцю INTEGER PRIMARY KEY. Усі ці імена є псевдонімами один для одного і однаково добре працюють у будь-якому контексті.»

Поля uuid та message зберігають ідентифікатор клієнта та вміст повідомлення відповідно. Поле типу CHAR(36) зберігає фіксовану кількість із 36 символів, а поле типу TEXT зберігає тексти довільної довжини.

Введення даних

Основною функцією нашого прикладу сервера є зберігання повідомлень, які пов’язані з клієнтом, який їх надіслав. Клієнт надсилає повідомлення в полі message в тілі запиту, надісланого методом HTTP POST. Ідентифікація клієнта міститься у файлі cookie під назвою uuid. Маючи цю інформацію, ми можемо написати Express-маршрут для вставки нових повідомлень до бази даних:

app.post('/', (req, res) => {

  let uuid = req.cookies.uuid

  if ( uuid === undefined )
    uuid = uuidv4()

  // Додаємо нове повідомлення до бази даних
  db.run('INSERT INTO messages (uuid, message) VALUES (?, ?)', uuid, req.body.message)

  // Якщо виникає помилка, об’єкт err містить повідомлення про помилку.
  db.all('SELECT id, message FROM messages WHERE uuid = ?', uuid, (err, rows) => {

    let expires = new Date(Date.now());
    expires.setDate(expires.getDate() + 30);
    res.cookie('uuid', uuid, { expires: expires })

    if ( req.headers.accept == "application/json" )
      res.json(rows)
    else
      res.render('index', {title: "My messages", rows: rows})

  })

})

Цього разу метод db.run() виконує оператор вставки, але зверніть увагу, що uuid і req.body.message не записуються безпосередньо в рядку оператора. Натомість значення були замінені знаками питання. Кожен знак питання відповідає параметру, який слідує за оператором SQL у методі db.run().

Використання знаків питання в якості заповнювачів у операторі, який виконується в базі даних, полегшує задачу SQLite розрізняти статичні елементи оператора та його змінні дані. Ця стратегія дає змогу SQLite екранувати або санітаризувати змінний вміст, який є частиною оператора, запобігаючи поширеному порушенню безпеки, яке називається SQL ін’єкція. Під час цієї атаки зловмисники вставляють оператори SQL у змінні дані в надії, що оператори мимоволі будуть виконані; санітаризація перешкоджає атаці, вимикаючи небезпечні символи в даних.

Запити

Як показано в прикладі коду, ми маємо намір використовувати той самий маршрут для додавання нових повідомлень до бази даних і створення списку раніше надісланих повідомлень. Метод db.all () повертає колекцію всіх записів у таблиці, які відповідають критеріям, визначеним у запиті.

На відміну від операторів, які виконує db.run(), db.all() генерує список записів, які обробляються функцією стрілки, зазначеною в останньому параметрі:

(err, rows) => {}

Ця функція насамперед приймає два параметри: err і rows. Параметр err використовуватиметься, якщо станеться помилка, яка перешкоджає виконанню запиту. У разі успіху всі записи будуть доступні в масиві rows, де кожен елемент є об’єктом, що відповідає окремому запису з таблиці. Властивості цього об’єкта відповідають іменам полів, зазначеним у запиті: uuid та message.

Масив rows є структурою даних JavaScript. Таким чином, його можна використовувати для генерування відповідей за допомогою методів, наданих Express, таких як res.json() та res.render(). При візуалізації всередині шаблону EJS звичайний цикл може перерахувати всі записи:

<ul>
<% rows.forEach( (row) => { %>
<li><strong><%= row.id %></strong>: <%= row.message %></li>
<% }) %>
</ul>

Замість того, щоб заповнювати масив рядків усіма записами, які повертає запит, у деяких випадках є можливість зручніше обробляти кожен запис окремо за допомогою методу db.each(). Синтаксис методу db.each() схожий на метод db.all(), але параметр row у (err, row) ⇒ {} відповідає одному запису за раз.

Зміна вмісту бази даних

Поки що наш клієнт може лише додавати та запитувати повідомлення на сервері. Оскільки клієнт тепер знає id раніше надісланих повідомлень, ми можемо реалізувати функцію для зміни певного запису. Змінене повідомлення також можна надіслати маршруту методу HTTP POST, але цього разу з параметром маршруту, щоб перехопити id, заданий клієнтом у шляху запиту:

app.post('/:id', (req, res) => {
  let uuid = req.cookies.uuid

  if ( uuid === undefined ){
    uuid = uuidv4()
    // 401 Unauthorized
    res.sendStatus(401)
  }
  else {

    // Оновлення збереженого повідомлення
    //за допомогою іменованих параметрів
    let param = {
      $message: req.body.message,
      $id: req.params.id,
      $uuid: uuid
    }

    db.run('UPDATE messages SET message = $message WHERE id = $id AND uuid = $uuid', param, function(err){

      if ( this.changes > 0 )
      {
        // Код стану 204 (No Content) означає, що дія була виконана
        // і більше надавати контенту не потрібно
        res.sendStatus(204)
      }
      else
        res.sendStatus(404)

    })
  }
})

Цей маршрут демонструє, як використовувати фрази UPDATE і WHERE для зміни існуючого запису. Важливою відмінністю від попередніх прикладів є використання іменованих параметрів, де значення об’єднуються в один об’єкт (param) і передаються до методу db.run() замість того, щоб вказувати кожне значення окремо. У цьому випадку імена полів (перед якими стоїть $) є властивостями об’єкта. Іменовані параметри дають змогу використовувати назви полів (перед якими стоїть $) як заповнювачі замість знаків питання.

Оператор, подібний до цього прикладу, не спричинить жодних змін в базі даних, якщо умова, накладена фразою WHERE, не відповідає певному запису в таблиці. Щоб оцінити, чи були якісь записи змінені оператором, функція зворотного виклику може використовуватися як останній параметр методу db.run(). Усередині функції кількість змінених записів можна запитати з this.changes. Зауважте, що в цьому випадку не можна використовувати функції зі стрілками, оскільки лише звичайні функції виду function(){} визначають об’єкт this.

Видалення запису дуже схоже на його зміну. Наприклад, ми можемо продовжувати використовувати параметр маршруту :id для ідентифікації повідомлення, яке потрібно видалити, але цього разу в маршруті, викликаному методом HTTP DELETE клієнта:

app.delete('/:id', (req, res) => {
  let uuid = req.cookies.uuid

  if ( uuid === undefined ){
    uuid = uuidv4()
    res.sendStatus(401)
  }
  else {
    // Іменовані параметри
    let param = {
    $id: req.params.id,
    $uuid: uuid
    }

    db.run('DELETE FROM messages WHERE id = $id AND uuid = $uuid', param, function(err){
      if ( this.changes > 0 )
        res.sendStatus(204)
      else
        res.sendStatus(404)
    })
  }
})

Записи видаляються з таблиці командою DELETE FROM. Ми знову використали функцію зворотного виклику, щоб оцінити, скільки записів було вилучено з таблиці.

Закриття бази даних

Після визначення об’єкта db на нього можна посилатися в будь-який момент під час виконання сценарію, оскільки файл бази даних залишається відкритим протягом поточного сеансу. Не прийнято закривати базу даних під час виконання сценарію.

Проте функція закриття бази даних корисна, щоб уникнути раптового закриття бази даних після завершення процесу сервера. Хоча це малоймовірно, раптове закриття бази даних може призвести до невідповідностей, якщо дані в пам’яті ще не зафіксовані у файлі. Наприклад, раптове завершення роботи бази даних із втратою даних може статися, якщо сценарій завершується користувачем, після натискання комбінації клавіш Ctrl+C.

У щойно описаному сценарії розвитку подій з використанням Ctrl+C метод process.on() може перехоплювати сигнали, надіслані операційною системою, і виконувати впорядковане завершення роботи як бази даних, так і сервера:

process.on('SIGINT', () => {
  db.close()
  server.close()
  console.log('HTTP server closed')
})

Комбінація клавіш Ctrl+C викликає сигнал операційної системи SIGINT, який завершує програму переднього плану в терміналі. Перед завершенням процесу при отриманні сигналу SIGINT система викликає функцію зворотного виклику (останній параметр у методі process.on()). Усередину функції зворотного виклику ви можете помістити будь-який код очищення, зокрема метод db.close(), щоб закрити базу даних, і server.close(), який акуратно закриє безпосередньо екземпляр Express.

Вправи до посібника

  1. Для чого потрібен первинний ключ в таблиці бази даних SQL?

  2. Яка різниця між запитами за допомогою db.all() та db.each()?

  3. Чому важливо використовувати заповнювачі, а не включати дані, надіслані клієнтом, безпосередньо до SQL-оператора або запиту?

Дослідницькі вправи

  1. Який метод у модулі sqlite3 можна використовувати для повернення лише одного запису таблиці, навіть якщо запит відповідає кільком записам?

  2. Припустимо, що масив rows був переданий у якості параметра функції зворотного виклику та містить результат запиту, зробленого за допомогою db.all(). Як можна усередині функції зворотного виклику посилатися на поле під назвою price, яке знаходиться на першій позиції в rows?

  3. Метод db.run() виконує оператори модифікації бази даних, наприклад INSERT INTO. Після вставки нового запису в таблицю, як можна отримати первинний ключ щойно доданого запису?

Підсумки

Цей урок охоплює основи використання баз даних SQL у Node.js Express застосунках. Модуль sqlite3 пропонує простий спосіб зберігання постійних даних у базі даних SQLite, де один файл містить всю базу даних і не потребує спеціалізованого сервера бази даних. Цей урок розглядає наступні концепції та процедури:

  • Як встановити підключення до бази даних з Node.js.

  • Як створити просту таблицю і яка роль первинних ключів.

  • Використання оператора SQL INSERT INTO для додавання нових даних із сценарію.

  • SQL-запити з використанням стандартних методів SQLite і функцій зворотного виклику.

  • Зміна даних у базі даних за допомогою операторів SQL UPDATE та DELETE.

Відповіді до вправ посібника

  1. Для чого потрібен первинний ключ в таблиці бази даних SQL?

    Первинний ключ – це унікальне поле ідентифікації для кожного запису в таблиці бази даних.

  2. Яка різниця між запитами за допомогою db.all() та db.each()?

    Метод db.all() викликає функцію зворотного виклику з одним масивом, що містить усі записи, які відповідають запиту. Метод db.each() викликає функцію зворотного виклику для кожного рядка результату.

  3. Чому важливо використовувати заповнювачі, а не включати дані, надіслані клієнтом, безпосередньо до SQL-оператора або запиту?

    За допомогою заповнювачів дані, надіслані користувачем, екрануються перед включенням у запит або оператор. Це перешкоджає атакам ін’єкцій SQL, коли оператори SQL розміщуються всередині змінних даних, з метою виконання довільних операцій з базою даних.

Відповіді до дослідницьких вправ

  1. Який метод у модулі sqlite3 можна використовувати для повернення лише одного запису таблиці, навіть якщо запит відповідає кільком записам?

    Метод db.get() має той самий синтаксис, що й db.all(), але повертає лише перший запис, що відповідає запиту.

  2. Припустимо, що масив rows був переданий у якості параметра функції зворотного виклику та містить результат запиту, зробленого за допомогою db.all(). Як можна усередині функції зворотного виклику посилатися на поле під назвою price, яке знаходиться на першій позиції в rows?

    Кожен елемент у rows є об’єктом, властивості якого відповідають іменам полів бази даних. Отже, значення поля price в першому результаті знаходиться в rows[0].price.

  3. Метод db.run() виконує оператори модифікації бази даних, наприклад INSERT INTO. Після вставки нового запису в таблицю, як можна отримати первинний ключ щойно доданого запису?

    Звичайну функцію у формі function(){} можна використовувати як функцію зворотного виклику методу db.run(). Усередині неї властивість this.lastID містить значення первинного ключа останнього доданого запису.

Linux Professional Institute Inc. Всі права захищені. Відвідайте веб-сайт навчальних матеріалів: https://learning.lpi.org
Ця робота ліцензована відповідно до міжнародної ліцензії Creative Commons Attribution-некомерційна-NoDerivatives 4.0.