SQL, що може бути простіше? Кожен із нас може написати простенький запит – набираємо вибрати, перераховуємо необхідні колонки, потім від, ім'я таблиці, небагато умов у де і все — корисні дані у нас у кишені, причому (майже) незалежно від того, яка СУБД у цей час знаходиться під капотом (а може й ). В результаті роботу практично з будь-яким джерелом даних (реляційним і не дуже) можна розглядати з погляду звичайного коду (з усіма витікаючими - version control, code review, статичний аналіз, автотести і це все). І це стосується не лише самих даних, схем та міграцій, а взагалі всієї життєдіяльності сховища. У цій статті поговоримо про повсякденні завдання та проблеми роботи з різними БД під прицілом "database as code".
І почнемо прямо з . Перші батли виду "SQL vs ORM" були помічені ще в .
Об'єктно-реляційний мапінг
Прихильники ORM традиційно цінують швидкість та простоту розробки, незалежність від СУБД та чистоту коду. Для багатьох із нас код роботи з БД (а найчастіше і сама БД)
зазвичай виглядає приблизно так…
@Entity
@Table(name = "stock", catalog = "maindb", uniqueConstraints = {
@UniqueConstraint(columnNames = "STOCK_NAME"),
@UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STOCK_ID", unique = true, nullable = false)
public Integer getStockId() {
return this.stockId;
}
...Модель обвішана розумними анотаціями, а десь за лаштунками доблесний ORM генерує та виконує тонни якогось SQL-коду. До речі, розробники всіма силами намагаються відгородитися від своєї БД кілометрами абстракцій, що говорить про деяку .
По інший бік барикад прихильники чистого "handmade"-SQL відзначають можливість вичавлювати всі соки зі своєї СУБД без додаткових прошарків та абстракцій. В результаті чого з'являються "data-centric" проекти, де базою займаються спеціально навчені люди (вони ж "базисти", вони ж "базовики", вони ж "базенщики" і т.д.), а розробникам тільки залишається "смикати" готові завірюхи і процедурки, що зберігаються, не вдаючись в подробиці.
А що якщо взяти найкраще із двох світів? Як це зроблено у чудовому інструменті з життєствердною назвою . Наведу пару рядків із загальної концепції у моєму вільному перекладі, а докладніше з нею можна познайомитись .
Clojure це крута мова для створення DSL'ів, але SQL вже сам по собі є крутим DSL, і нам не потрібна ще одна. S-вираження прекрасні, але тут вони не додають нічого нового. У результаті отримуємо дужки для дужок. Не згодні? Тоді дочекайтеся того моменту, коли абстракція над БД дасть текти, і ви почнете боротьбу з функцією (raw-sql)
І що робити? Давайте залишимо SQL звичайним SQL'ем - один файл на один запит:
-- name: users-by-country
select *
from users
where country_code = :country_code… а потім прочитайте цей файл, перетворивши його на звичайну Clojure функцію:
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; A function with the name `users-by-country` has been created.
;;; Let's use it:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)Дотримуючись принципу "SQL окремо, Clojure окремо", ви отримуєте:
- Жодних синтаксичних сюрпризів. Ваша база даних (як і будь-яка інша) не відповідає SQL стандарту на 100%, але для Yesql це не важливо. Ви ніколи не будете витрачати час на полювання за функціями із синтаксисом еквівалентним SQL. Вам ніколи не доведеться повертатися до функції (raw-sql "some ('funky'::SYNTAX)")).
- Найкраща підтримка редактора. Ваш редактор вже має чудову підтримку SQL. Зберігаючи SQL як SQL, можна просто використовувати його.
- Командна сумісність. Ваші DBA можуть читати та писати SQL, який ви використовуєте у своєму Clojure проекті.
- Простіше налаштування продуктивності. Чи потрібно побудувати план для проблемного запиту? Це не проблема, коли ваш запит є звичайним SQL.
- Повторне використання запитів. Перетягніть ці ж SQL-файли в інші проекти, тому що це просто старий добрий SQL - просто поділіться ним.
На мою ідея дуже крута і при цьому дуже проста, завдяки чому проект знайшов безліч різними мовами. А ми далі спробуємо застосувати схожу філософію відокремлення SQL-коду від решти далеко за межами ORM.
IDE & DB-менеджери
Почнемо з простого повсякденного завдання. Часто нам доводиться шукати якісь об'єкти в БД, наприклад, знайти таблицю у схемі та вивчити її структуру (які використовуються колонки, ключі, індекси, констрейнти та інше). І від будь-якої графічної IDE або малого DB-manager'а, в першу чергу, ми чекаємо саме цих здібностей. Щоб було швидко і не довелося чекати по півгодини, поки намалюється віконце з потрібною інформацією (особливо при повільному з'єднанні з віддаленою БД), і щоб отримана інформація була свіжою і актуальною, а не старіння. Причому що складніше і більше БД і більше їх кількість, то складніше це зробити.
Але зазвичай я закидаю мишу кудись подалі і просто пишу код. Допустимо, необхідно дізнатися, які таблиці (і з якими властивостями) містяться у схемі "HR". У більшості СУБД потрібного результату можна досягти таким нехитрим запитом з information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Від бази до бази вміст таблиць-довідників варіюється в залежності від здібностей кожної СУБД. І, наприклад, для MySQL із цього ж довідника можна отримати специфічні для цієї СУБД параметри таблиці:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle не вміє information_schema, натомість у нього є , і великих проблем не виникає:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'Не виняток і ClickHouse:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Щось схоже можна зробити і в Cassandra (де є columnfamilies замість tables і keyspace'и замість схем):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Для більшості інших БД також можна вигадати схожі запити (навіть у Mongo є яка містить у собі інформацію про всі колекції в системі).
Само собою, у такий спосіб можна отримати інформацію не тільки про таблиці, а взагалі про будь-який об'єкт. Періодично добрі люди діляться таким кодом для різних БД, як, наприклад, серії хабра-статей "Функції для документування баз даних PostgreSQL" (, , ). Само собою, тримати всю цю гору запитів у голові і постійно набирати їх - це "таке собі" задоволення, тому в улюбленій IDE/редакторі у мене є заздалегідь заготовлений набір сніппетів для запитів, що часто використовуються, і залишається тільки вдрукувати імена об'єктів у шаблон.
У результаті такий спосіб навігації та пошуку об'єктів набагато гнучкіший, економить багато часу, дозволяє отримати саме ту інформацію і в тому вигляді, в якому зараз необхідно (як, наприклад, описано в пості ).
Операції з об'єктами
Після того як ми знайшли і вивчили потрібні об'єкти, саме час з ними зробити щось корисне. Звичайно, також не відриваючи пальців від клавіатури.
Не секрет, що просте видалення таблиці виглядатиме однаково майже у всіх БД:
drop table hr.personsА ось із створенням таблиці вже цікавіше. Практично будь-яка СУБД (у тому числі і багато NoSQL) у тому чи іншому вигляді вміє "create table", і основна його частина навіть мало буде відрізнятися (ім'я, список колонок, типи даних), але інші деталі можуть разюче відрізнятися і залежать від внутрішнього устрою та можливостей конкретної СУБД. Мій улюблений приклад - у документації Oracle лише одні "голі" БНФ'и для синтаксису "create table" . Інші СУБД мають більш скромні можливості, але кожна з них також має безліч цікавих і унікальних фіч зі створення таблиць (, , , ). Навряд чи якийсь графічний "wizard" з чергової IDE (особливо універсальної) зможе повністю покрити всі ці здібності, а якщо і зможе, то це буде видовище не для людей зі слабкими нервами. Водночас правильно і вчасно написаний оператор створити таблицю дозволить легко скористатися всіма з них, зробити зберігання і доступ до ваших даних надійним, оптимальним і максимально комфортним.
Також у багатьох СУБД є свої специфічні типи об'єктів, які відсутні в інших СУБД. Причому ми можемо виконувати операції не лише над об'єктами БД, а й над самою СУБД, наприклад "вбити" процес, звільнити будь-яку область пам'яті, включити трасування, перейти в режим "read only" та багато іншого.
А тепер трохи помалюємо
Одне з найпоширеніших завдань – побудувати діаграму з об'єктами БД, на красивій картинці побачити об'єкти та зв'язки між ними. Це вміє практично будь-яка графічна IDE, окремі "command line"-утиліти, спеціалізовані графічні тули та моделлери. Які вам щось намалюють як вміють, а трохи вплинути на цей процес можна тільки за допомогою декількох параметрів в конфігураційному файлі або галочок в інтерфейсі.
Але цю проблему можна вирішити набагато простіше, гнучкіше та елегантніше, і звичайно ж за допомогою коду. Для побудови діаграм будь-якої складності ми маємо відразу кілька спеціалізованих мов розмітки (DOT, GraphML etc), а до них — цілий розсип додатків (GraphViz, PlantUML, Mermaid), які вміють читати такі інструкції та візуалізувати в різних форматах. Ну а інформацію про об'єкти та зв'язки між ними ми вже знаємо як отримати.
Наведемо невеликий приклад того, як це могло б виглядати з використанням PlantUML і (зліва SQL-запит, який згенерує потрібну інструкцію для PlantUML, а праворуч результат):
select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all
select distinct ccu.table_name || ' --|> ' ||
tc.table_name as val
from table_constraints as tc
join key_column_usage as kcu
on tc.constraint_name = kcu.constraint_name
join constraint_column_usage as ccu
on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and tc.table_name ~ '.*' union all
select '@enduml'А якщо трохи постаратися, то на основі можна отримати щось дуже схоже на справжню ER-діаграму:
SQL-запит чуюуть складніше
-- Шапка
select '@startuml
!define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
!define primary_key(x) <b>x</b>
!define unique(x) <color:green>x</color>
!define not_null(x) <u>x</u>
hide methods
hide stereotypes'
union all
-- Таблицы
select format('Table(%s, "%s n information about %s") {'||chr(10), table_name, table_name, table_name) ||
(select string_agg(column_name || ' ' || upper(udt_name), chr(10))
from information_schema.columns
where table_schema = 'public'
and table_name = t.table_name) || chr(10) || '}'
from information_schema.tables t
where table_schema = 'public'
union all
-- Связи между таблицами
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s may haven many %s"', ccu.table_name, tc.table_name)
from information_schema.table_constraints as tc
join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and ccu.constraint_schema = 'public'
and tc.table_name ~ '.*'
union all
-- Подвал
select '@enduml'
Якщо уважно придивитися, то під капотом багато інструментів-візуалізаторів також використовують схожі запити. Щоправда, ці запити зазвичай глибоко , не кажучи вже про будь-яку їх модифікацію.
Метрики та моніторинг
Перейдемо до традиційно складної теми моніторинг продуктивності БД. Згадаю невелику true story, розказану мені одним моїм другом. На черговому проекті жив-був якийсь могутній DBA, і мало хто з розробників був з ним знайомий особисто, та й взагалі бачив колись його в очі (попри те, що працював він, за чутками, десь у сусідньому корпусі) . У годину "X", коли poduction-система великого ретейлера починала в черговий раз "погано почуватися", він мовчки надсилав скріншоти графіків з ораклового Enterprise Manager, на яких дбайливо виділяв критичні місця червоним маркером для "зрозумілості" (це, м'яко кажучи, мало допомагало). І ось по цій "фотокартці" доводилося лікувати. У цьому доступу до дорогоцінного (в обох сенсах цього терміну) Enterprise Manager ні в кого був, т.к. система складна і дорога, раптом "розробники чогось нитка натикають і все поламають". Тому розробники "емпіричним" шляхом знаходили місце та причину гальм та випускали патч. Якщо грізний лист від DBA не приходив повторно найближчим часом, то всі з полегшенням видихали та поверталися до своїх поточних завдань (до нового Листа).
Але процес моніторингу може виглядати веселіше і дружелюбно, а найголовніше — доступно і прозоро для всіх. Хоча б базова його частина, як доповнення до основних систем моніторингу (які, безумовно, корисні і в багатьох випадках незамінні). Будь-яка СУБД вільно і абсолютно безоплатно готова поділитися інформацією про свій поточний стан та продуктивність. В тій же "кривавій" Oracle DB практично будь-яку інформацію про продуктивність можна отримати з системних уявлень, починаючи від процесів і сесій і закінчуючи станом буферного кешу (наприклад, , Розділ "Monitoring"). У Postgresql також є цілий розсип системних уявлень для , зокрема такі незамінні у повсякденному житті будь-якого DBA, як , , . MySQL для цього призначена навіть окрема схема . А в Mongo вбудований агрегує дані про продуктивність до системної колекції .
Т.ч., озброївшись будь-яким збирачем метрик (Telegraf, Metricbeat, Collectd), який вміє виконувати кастомні sql-запити, сховищем цих метрик (InfluxDB, Elasticsearch, Timescaledb) та візуалізатором (Grafana, Kibana), можна отримати досить легку гнучку систему моніторингу, яка буде тісно інтегрована з іншими загальносистемними метриками (одержуваними, наприклад, сервера додатків, від ОС тощо). Як, наприклад, це зроблено в pgwatch2, де використовується зв'язка InfluxDB + Grafana і набір запитів до системних уявлень, до яких можна також .
Разом
І це лише приблизний перелік того, що можна зробити з нашою базою даних за допомогою звичайного SQL-коду. Упевнений, можна знайти ще багато застосувань, пишіть у коментарях. А про те, як (і найголовніше навіщо) це все заавтоматизувати та включити до свого CI/CD pipeline ми поговоримо наступного разу.
Джерело: habr.com