SQL для котиків: перша таблиця з PGlite

PostgreSQL прямо у браузері

Зазвичай PostgreSQL працює окремо від браузера: застосунок надсилає запит на сервер, а сервер звертається до бази даних. У цьому прикладі сервера для виконання SQL немає.

PGlite дозволяє запустити PostgreSQL безпосередньо у браузері за допомогою WebAssembly. Тому SQL-запит із Playground виконується локально — на пристрої користувача.

Як дані потрапляють у PostgreSQL

PGlite запускає базу даних, але спочатку вона порожня. Дані для цього прикладу зберігаються у CSV-файлі Seattle Pet Licenses, тому його потрібно завантажити у браузер і перенести в PostgreSQL.

Шлях даних виглядає так:

Seattle Pet Licenses CSV
              ↓
            fetch()
              ↓
           pets_raw
              ↓
             pets
              ↓
          SQL-запити

1. Завантажуємо CSV

Спочатку браузер завантажує CSV-файл за допомогою fetch(). Отриману відповідь перетворюємо на Blob, який потім передамо PGlite.

const response = await fetch(CSV_URL);

if (!response.ok) {
  throw new Error(`Failed to load CSV: ${response.status}`);
}

const csvBlob = await response.blob();

2. Створюємо таблицю для сирих даних

Дані з CSV спочатку завантажимо у проміжну таблицю pets_raw. На цьому етапі всі колонки мають тип TEXT — ми зберігаємо сирі значення без перетворення типів.

CREATE TABLE pets_raw (
  license_issue_date_raw TEXT,
  license_number TEXT,
  animal_name TEXT,
  species TEXT,
  primary_breed TEXT,
  secondary_breed TEXT,
  zip_code TEXT
);

Наприклад, дата в CSV приходить як текст на кшталт December 17, 2023. У pets_raw вона потрапляє без змін у license_issue_date_raw, а перетворимо її на справжній PostgreSQL-тип DATE пізніше.

3. Імпортуємо CSV

Тепер передаємо завантажений Blob у PGlite і використовуємо PostgreSQL-команду COPY, щоб імпортувати CSV у pets_raw.

await db.query(
  `
    COPY pets_raw (
      license_issue_date_raw,
      license_number,
      animal_name,
      species,
      primary_breed,
      secondary_breed,
      zip_code
    )
    FROM '/dev/blob'
    WITH (
      FORMAT csv,
      HEADER true
    );
  `,
  [],
  {
    blob: csvBlob,
  },
);

Шлях /dev/blob тут не означає реальний файл на комп'ютері. PGlite використовує його як спеціальний шлях до Blob, який ми передали в параметрі blob.

FORMAT csv вказує PostgreSQL формат даних, а HEADER true повідомляє, що перший рядок CSV містить назви колонок і його не потрібно імпортувати як звичайний запис.

4. Створюємо таблицю pets

pets_raw зберігає дані в тому вигляді, у якому вони прийшли з CSV. Для роботи з ними створимо окрему таблицю pets з потрібними типами колонок.

CREATE TABLE pets (
  license_issue_date DATE,
  license_number TEXT,
  animal_name TEXT,
  species TEXT,
  primary_breed TEXT,
  secondary_breed TEXT,
  zip_code TEXT
);

Більшість значень залишаються текстовими, але license_issue_date уже має тип DATE. Тепер PostgreSQL сприйматиме це значення саме як дату, а не як звичайний текст.

5. Переносимо дані в pets

Таблиця pets створена, але поки порожня. Заповнимо її даними з pets_raw за допомогою INSERT INTO ... SELECT.

INSERT INTO pets (
  license_issue_date,
  license_number,
  animal_name,
  species,
  primary_breed,
  secondary_breed,
  zip_code
)
SELECT
  TO_DATE(license_issue_date_raw, 'Month DD, YYYY'),
  license_number,
  animal_name,
  species,
  primary_breed,
  secondary_breed,
  zip_code
FROM pets_raw;

Майже всі значення можна перенести без змін. Виняток — license_issue_date_raw: у сирій таблиці дата зберігається як текст, тому TO_DATE() перетворює її на PostgreSQL-типDATE.

Тепер pets готова до SQL-запитів. Саме з цією таблицею працює Playground на початку статті.

Дивимося на структуру бази

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

SELECT
  table_name,
  column_name,
  data_type,
  is_nullable,
  ordinal_position
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

information_schema — це набір системних представлень PostgreSQL. До них можна звертатися звичайними SQL-запитами так само, як до наших таблиць.

Скопіюйте цей запит у Playground вище — у результаті будуть видні колонки таблиць pets_raw і pets.

Що в результаті

Ми запустили PostgreSQL прямо у браузері, завантажили відкриті дані з CSV, підготували таблицю pets і виконали справжні SQL-запити — без окремого сервера та зовнішньої бази даних.

PGlite робить такий підхід особливо зручним для інтерактивних прикладів: дані й база знаходяться поруч із кодом статті, а читач може одразу змінювати SQL і бачити результат.