Бази данни

Проблем

Каталог във файл

Каталогът на магазина е CSV файл, както в модула за файловете. Докато продуктите са сто, става. После стават сто хиляди и се появяват проблеми:

• за да намериш един продукт, четеш целия файл;
• „всички под 5 €, подредени по цена“ е цикъл, if и сортиране, писани на ръка;
• две каси пишат във файла едновременно и единият запис се губи или файлът се поврежда;
• токът спира по средата на записа и половината файл го няма.

Затова данните на почти всяка истинска програма са в база данни: програма, чиято работа е да пази данни, да ги търси бързо и да не ги губи.

Идеята

Таблици и SQL

Релационната база пази данните в таблици. Таблицата има колони (име, тип) и редове (по един за всеки продукт). Колоната id е първичен ключ: стойността ѝ е различна за всеки ред, затова по нея се намира точно един.

С базата се говори на езика SQL. Петте най-чести команди:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Кафе', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE прави таблица, INSERT добавя ред, UPDATE променя, DELETE трие.
• SELECT чете: кои колони, от коя таблица, при какво условие (WHERE) и в какъв ред (ORDER BY).

Ти казваш какво искаш, а базата решава как да го намери бързо.

По-точно, за любопитните

• Известни релационни бази са PostgreSQL, MySQL, Oracle и SQL Server. Те работят като отделни сървъри. Тук ползваме SQLite: малка база, която живее във файл или изцяло в паметта на програмата. Модулът node:sqlite е вграден в Node 22 и не иска флаг, но е още експериментален: при пускане печата ExperimentalWarning: SQLite is an experimental feature в stderr. Адресът ":memory:" е база в паметта: всяко пускане започва с празна.
• Текстът в SQL е в единични кавички: 'Кафе'. В PostgreSQL и MySQL DECIMAL(8, 2) е точно число с 2 знака след запетаята, подходящо за пари. SQLite не пази точни десетични числа: там 7.50 е обикновено число с плаваща запетая.

Виж го

node:sqlite: връзка, заявка, резултат

Модулът node:sqlite говори със SQLite. Връзката е DatabaseSync, а заявка с резултат се прави с prepare. Функцията setup прави таблицата и добавя три продукта:

function setup(db) {
  db.exec("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))");
  db.exec("INSERT INTO products VALUES (1, 'Кафе', 7.50), (2, 'Чай', 3.20), (3, 'Шоколад', 2.40)");
}

Четем редовете и правим от всеки обект от класа Product с полета id, name и price:

class Product {
  constructor(id, name, price) {
    this.id = id;
    this.name = name;
    this.price = price;
  }
}

const db = new DatabaseSync(":memory:");
setup(db);
const products = [];
for (const row of db.prepare("SELECT id, name, price FROM products ORDER BY price").all()) {
  products.push(new Product(row.id, row.name, row.price));
}
for (const p of products) {
  console.log(p);
}
db.close();

Изход

Product { id: 3, name: 'Шоколад', price: 2.4 }
Product { id: 2, name: 'Чай', price: 3.2 }
Product { id: 1, name: 'Кафе', price: 7.5 }

• new DatabaseSync(адрес) отваря връзката. Адресът казва коя база: тук ":memory:", или име на файл като "shop.db". „Sync“ значи, че методите връщат резултата веднага, без await.
• db.exec(sql) праща команди без резултат (CREATE, INSERT, UPDATE, DELETE). db.prepare(sql) подготвя заявка, а .all() връща всички редове като масив. Всеки ред е обект с поле за всяка колона: row.name.
• .get() връща само първия ред, а .run() е за промени и връща колко реда са променени (changes).
• По подразбиране всяка заявка се записва веднага. db.close() затваря връзката.
• Грешките се хвърлят като Error с code "ERR_SQLITE_ERROR".

const db = new DatabaseSync(":memory:");
setup(db);
const products = [];
for (const row of db.prepare("SELECT id, name, price FROM products ORDER BY price").all()) {
  products.push(new Product(row.id, row.name, row.price));
}
for (const p of products) {
  console.log(p);
}
db.close();

Стъпка по стъпка, с думи

1. Връзка с базата в паметта. setup прави таблицата products и добавя 3 реда.
2. Празен списък, в който ще влязат обектите.
3. SELECT с ORDER BY price: all() връща всички редове наведнъж като масив, от най-евтиния.
4. Цикълът взима ред 1: обект с полета по имената на колоните. new Product прави от тях обект: Шоколад.
5. Цикълът взима ред 2: обект с полета по имената на колоните. new Product прави от тях обект: Чай.
6. Цикълът взима ред 3: обект с полета по имената на колоните. new Product прави от тях обект: Кафе.
7. Масивът свърши и цикълът спира.
8. Масивът е от обикновени обекти. console.log показва класа и полетата им.

По-точно, за любопитните

• С истинска база на сървър адресът е друг и има потребител и парола. Също трябва и драйверът на базата като зависимост в проекта (за MySQL: пакетът mysql2):

import mysql from "mysql2/promise";

const conn = await mysql.createConnection({ host: "localhost", port: 3306, database: "shop", user: "shop_app", password: "парола" });
console.log("Връзката с базата е успешна!");
await conn.end();

• Паролата не се пише в кода, а се чете от настройките или от променлива на средата (process.env).

• Цените тук са number, за да е кратко. В истински магазин парите се пазят точно: като цели стотинки, защото JavaScript няма точен десетичен тип.
• console.log(row) показва [Object: null prototype] { … }: редовете са обекти без прототип, само с полетата на колоните.

Пробвай

Кое ще излезе?

Продуктите са Кафе 7,50 €, Чай 3,20 € и Шоколад 2,40 €. Какво ще отпечата програмата?

const db = new DatabaseSync(":memory:");
setup(db);
for (const row of db.prepare("SELECT name FROM products WHERE price < 5 ORDER BY price DESC").all()) {
  console.log(row.name);
}
db.close();

Отговор

Кафе
Чай
Шоколад

Чай
Шоколад

Шоколад
Чай

Провери

Проблем

Търсачката, която показва всичко

Сайтът търси продукт по име, което потребителят пише. Заявката се сглобява с шаблон `…${name}…`:

function search(db, name) {
  const sql = `SELECT name, price FROM products WHERE name = '${name}'`;
  for (const row of db.prepare(sql).all()) {
    console.log(`  ${row.name}: ${row.price} €`);
  }
}

const db = new DatabaseSync(":memory:");
setup(db);
console.log("Търся: Чай");
search(db, "Чай");
console.log("Търся: x' OR '1'='1");
search(db, "x' OR '1'='1");
db.close();

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1
  Кафе: 7.5 €
  Чай: 3.2 €
  Шоколад: 2.4 €

С „Чай“ работи. С x' OR '1'='1 заявката става ... WHERE name = 'x' OR '1'='1'. Условието е винаги вярно и излиза цялата таблица. Входът на потребителя е станал част от SQL. Това е SQL injection, една от най-честите атаки срещу сайтове. Със същия трик може да се влезе без парола или да се изтрие таблица.

Идеята

Параметри с ?

Решението е заявката и стойностите да вървят отделно. Заявката от prepare има ? на местата на стойностите, а те се подават като аргументи на all, get или run, в същия ред:

function search(db, name) {
  const sql = "SELECT name, price FROM products WHERE name = ?";
  for (const row of db.prepare(sql).all(name)) {
    console.log(`  ${row.name}: ${row.price} €`);
  }
}

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1

Сега x' OR '1'='1 е просто текст, който се търси като име. Такъв продукт няма и не излиза нищо. Каквото и да напише потребителят, то не може да стане SQL.

Правилото е просто: стойност от потребителя, от файл или от мрежата никога не се долепя в SQL. Винаги ?.

Пробвай

Кафе с апостроф

Функцията преименува продукт. С името Кафе 'Арабика' програмата спира с Error: near "Арабика": syntax error. На кой ред е грешката?

Избери реда с грешката.

1function rename(db, id, newName) {2 db.exec(`UPDATE products SET name = '${newName}' WHERE id = ${id}`);3}

Провери

Идеята

Транзакция: всичко или нищо

Ана превежда 150 € на Борис. Това са две заявки: добави на Борис, извади от Ана. В таблицата има условие CHECK (balance >= 0), затова втората заявка се проваля: Ана има само 100 €.

function openAccounts(db) {
  db.exec("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))");
  db.exec("INSERT INTO accounts VALUES (1, 'Ана', 100), (2, 'Борис', 50)");
}

function move(db, id, amount) {
  db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?").run(amount, id);
}

function printBalances(db) {
  for (const row of db.prepare("SELECT owner, balance FROM accounts ORDER BY id").all()) {
    console.log(`  ${row.owner}: ${row.balance} €`);
  }
}

function transfer(db, from, to, amount) {
  move(db, to, amount);
  move(db, from, -amount);
}

const db = new DatabaseSync(":memory:");
openAccounts(db);
try {
  transfer(db, 1, 2, 150);
} catch {
  console.log("Преводът на 150 € е отказан.");
}
printBalances(db);
db.close();

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 200 €

Преводът е отказан, но Борис вече е получил 150 € от нищото. По подразбиране всяка заявка се записва веднага. Двете заявки трябва да са транзакция: или минават и двете, или нито една.

function transfer(db, from, to, amount) {
  db.exec("BEGIN");
  try {
    move(db, to, amount);
    move(db, from, -amount);
    db.exec("COMMIT");
  } catch (error) {
    db.exec("ROLLBACK");
    throw error;
  }
}

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 50 €

• db.exec("BEGIN") започва транзакция: промените още не са окончателни.
• db.exec("COMMIT") ги записва всички наведнъж.
• db.exec("ROLLBACK") ги отменя всички, ако нещо се провали. Изключението продължава нагоре с throw, за да разбере и викащият.
• BEGIN, COMMIT и ROLLBACK са обикновени SQL команди: така се правят транзакции и в самия SQL.

По-точно, за любопитните

• Грешката при провалената заявка е с code ERR_SQLITE_ERROR и съобщение CHECK constraint failed: no_debt. no_debt е името на условието: в таблицата то е balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Базите обещават четири неща за транзакциите, известни като ACID: всичко или нищо (atomicity), данните остават верни според правилата на таблиците (consistency), едновременните транзакции не си пречат (isolation) и записаното не се губи дори при спрял ток (durability).

Идеята

ORM: обекти вместо редове

С node:sqlite за всеки клас пишеш SQL и превръщаш редовете в обекти на ръка. ORM (object-relational mapping) прави това вместо теб: описваш модела, а библиотеката сама прави таблицата, заявките и обектите. В Node ORM няма. Един от най-старите и известни е Sequelize (пакетите sequelize и sqlite3 от npm). Други са Prisma, TypeORM и Drizzle.

models.js

import { DataTypes, Op, Sequelize } from "sequelize";

const sequelize = new Sequelize({ dialect: "sqlite", storage: ":memory:", logging: false });

const Product = sequelize.define("Product", {
  name: { type: DataTypes.STRING(50), allowNull: false },
  price: DataTypes.DOUBLE,
}, { tableName: "products", timestamps: false });

• sequelize.define описва модел: таблица с име products (tableName).
• Всяко поле е колона с тип от DataTypes. allowNull: false е NOT NULL.
• Колона id Sequelize добавя сам като първичен ключ, а базата му дава стойност.
• timestamps: false спира колоните createdAt и updatedAt, които Sequelize иначе добавя.

Записът и четенето минават през модела:

await sequelize.sync();
await Product.create({ name: "Кафе", price: 7.50 });
await Product.create({ name: "Чай", price: 3.20 });

const cheap = await Product.findAll({ where: { price: { [Op.lt]: 5 } }, order: [["price", "ASC"]] });
for (const p of cheap) {
  console.log(`${p.name}: ${p.price} €`);
}

Изход (с инсталирани sequelize и sqlite3)

Чай: 3.2 €

new Sequelize({ dialect: "sqlite", storage: ":memory:" }) казва коя база: тук SQLite в паметта. sync() прави таблиците. Product.create записва обект. findAll строи заявката от обект: { price: { [Op.lt]: 5 } } е WHERE price < 5. Резултатът е масив от готови обекти. Всички методи връщат обещания, затова await.

Sequelize е пакет и в проверката на сайта го няма, затова този код тук не се пуска. Изходът е от пускане с инсталирани sequelize 6 и sqlite3 5 (npm install sequelize sqlite3).

По-точно, за любопитните

• Условията на findAll са обикновен обект, затова лесно се сглобяват според избора на потребителя. Функция, която търси по име:

function named(name) {
  return Product.findAll({ where: { name } });
}

• Транзакция с отмяна при грешка: sequelize.transaction с функция записва всичко накрая, а ако функцията хвърли, отменя всичко. Всяка заявка вътре получава { transaction }:

await sequelize.transaction(async (transaction) => {
  await Product.create({ name: "Шоколад", price: 2.40 }, { transaction });
  // още промени: при изключение всичко се отменя, иначе се записва
});

• С logging: console.log вместо false Sequelize печата SQL-а, който праща. Така се вижда, че отдолу пак е SQL.

Упражнение

Магазин с база

Реши задачите в редактора си, с node:sqlite и база ":memory:".1. Каталог. Направи таблица products и функции add, setPrice, remove и list, всички с ? освен list. Добави Кафе (7,50 €), Чай (3,20 €) и Шоколад (2,40 €), отпечатай, смени цената на кафето на 6,90 €, изтрий шоколада и отпечатай пак:

Изход

1. Кафе: 7.5 €
2. Чай: 3.2 €
3. Шоколад: 2.4 €
---
1. Кафе: 6.9 €
2. Чай: 3.2 €

2. Търсене по част от името. Продуктите са Кафе, Какао, Чай и Шоколад. Функция find(db, text) намира всички, чието име съдържа текста, без значение от малки и главни букви. LOWER в SQLite сменя само латинските букви, затова дай на базата своя функция: db.function("lower_text", (text) => text.toLowerCase()), после WHERE lower_text(name) LIKE ? със стойност `%${text.toLowerCase()}%`. Пробвай „КА“, „чай“ и опит за injection:

Изход

Търся „КА“:
  Какао: 4.8 €
  Кафе: 7.5 €
Търся „чай“:
  Чай: 3.2 €
Търся „' OR '1'='1“:
  няма такива продукти

3. Превод, който се проваля наполовина. Сметките са Ана (100 €) и Борис (50 €). transfer(db, from, to, amount) първо вади от from, после добавя на to. Ако run() върне changes 0 (няма такава сметка), хвърли new Error(`Няма сметка ${id}`). Всичко е между BEGIN и COMMIT. Преведи 30 € от 1 към 2, после 40 € от 1 към несъществуващата 99:

Изход

Преведени 30 € от 1 към 2
Отказан: Няма сметка 99
  Ана: 70 €
  Борис: 80 €

Вторият превод е извадил 40 € от Ана, преди да се провали, но ROLLBACK ги е върнал.

Тест

Провери се

1. Какво връща db.prepare("SELECT ...").all()?

Броя на променените редовеМасив от обекти ProductМасив от редовете: всеки е обект с поле за всяка колона

2. Защо `... WHERE name = '${name}'` е опасно?

Защото е бавноЗащото текстът от потребителя става част от SQL и може да промени заявкатаНе е опасно, ако name е string

3. Преводът е две заявки между BEGIN и COMMIT. Първата минава, втората се проваля. Какво прави ROLLBACK?

Отменя всички промени от BEGIN насам, и първатаОтменя само вторатаПовтаря втората заявка

4. Какво прави ORM като Sequelize?

Заменя базата данниПревръща обекти в редове и обратно, без да пишеш SQL за всяко нещоСамо ускорява SQL заявките

Провери

Започни урока отначало