🥇«Database as Code» опит

SQL, какво може да бъде по-просто? Всеки от нас може да напише простичък запитване — въвеждаме select, изброяваме необходимите колони, след това from, името на таблицата, малко условия в where и всичко — полезните данни са в нашия джоб, при това (почти) независимо от това коя СУБД е зад кулисите (или може би все пак не е СУБД). В резултат на това работата с почти всякакъв източник на данни (релационен и не само) може да бъде разглеждана в контекста на обикновен код (с всички произтичащи от това — контрол на версиите, преглед на кода, статичен анализ, автоматизирани тестове и всичко това). И това касае не само самите данни, схеми и миграции, а изобщо цялото функциониране на хранилището. В тази статия ще говорим за ежедневните задачи и проблеми при работа с различни бази данни под угледа на "database as code".

И ще започнем направо с ORM. Първите сблъсъци "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 код. Между другото, разработчиците се опитват с всички сили да се изолират от своята БД с километри абстракции, което говори за известна "омраза към SQL".

От другата страна на барикадата, привържениците на чистото "ръчно" SQL отбелязват възможността да извлекат всичко от своята СУБД без допълнителни прослойки и абстракции. В резултат на което се появяват "data-centric" проекти, където базата е в ръцете на специално обучени хора (същите тези "базици", "базовики", "базденьщики" и т.н.), а на разработчиците остава само да "издърпат" готовите изгледи и съхранени процедури, без да се задълбочават в детайлите.

А какво, ако вземем най-доброто от двата свята? Както е направено в забележителния инструмент с утвърдително име Yesql. Ще дам няколко реда от общата концепция в моят свободен превод, а по-подробно с нея може да се запознаете тук.

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})

;;; Функция с името `users-by-country` е създадена.
;;; Нека я използваме:
(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" и др.)
     , row_format     -- Формат на реда ("Fixed", "Dynamic" и др.)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle не поддържа information_schema, но разполага с Oracle metadata, и не възникват големи проблеми:

select table_name
     , pct_free       -- Минимално свободно място в блока с данни (%)
     , pct_used       -- Минимално използвано място в блока с данни (%)
     , last_analyzed  -- Дата на последното събиране на статистика
     , ...
  from all_tables
 where owner = 'HR'

Не прави изключение и ClickHouse:

select name
     , engine -- Използваният "движок" ("MergeTree", "Dictionary" и др.)
     , ...
  from system.tables
 where database = 'HR'

Нещо подобно може да се направи и в Cassandra (където има columnfamilies вместо таблици и keyspace-ове вместо схеми):

select columnfamily_name
     , compaction_strategy_class  -- Стратегия за събиране на боклук
     , gc_grace_seconds           -- Време на живот на боклука
     , ...
  from system.schema_columnfamilies
 where keyspace_name = 'HR'

За повечето други БД също могат да се измислят подобни заявки (дори в Mongo има специална системна колекция, която съдържа информация за всички колекции в системата).

Разбира се, по този начин може да се получи информация не само за таблиците, а и за всякакъв обект. От време на време добри хора споделят такъв код за различни бази данни, като например в серия от статии в Хабра "Функции за документиране на бази данни PostgreSQL" (айб, бен, гим). Разбира се, да държиш цялата тази купчина заявки в главата си и постоянно да ги набираш — това е "по-скоро" удоволствие, затова в любимата ми IDE/редактор имам предварително подготвен набор от сниппети за често използвани заявки, и остава само да напишеш имената на обектите в шаблона.

В крайна сметка този метод за навигация и търсене на обекти е много по-гъвкав, спестява много време, позволява да получиш точно информацията и в онази форма, която е необходима в момента (както например е описано в поста "Експорт на данни от БД в абсолютно всякакъв формат: какво могат IDE на платформата IntelliJ").

Операции с обекти

След като намерим и проучим нужните обекти, идва време да направим нещо полезно с тях. Разбира се, пак без да откъсваме пръстите от клавиатурата.

Не е тайна, че простото изтриване на таблица ще изглежда почти еднакво във всички бази данни:

drop table hr.persons

Но с изграждането на таблица вече е по-интересно. Практически всяка СУБД (включително и много NoSQL) по един или друг начин може да направи "create table", и основната ѝ част дори малко ще се различава (име, списък на колоните, типове данни), но останалите детайли могат драстично да се различават и зависят от вътрешната структура и възможностите на конкретната СУБД. Моят любим пример — в документацията на Oracle само "голите" БНФ-и за синтаксиса "create table" заети са 31 страници. Други СУБД имат по-скромни възможности, но всяка от тях също предлага множество интересни и уникални функции за създаване на таблици (postgres, mysql, cockroach, cassandra). Малко вероятно е някой графичен "wizard" от поредната IDE (особено универсалната) да може напълно да обхване всичките тези способности, а даже и да може, това ще е зрелище не за нервните. В същото време правилно и навреме написаният оператор create table ще позволи без усилие да се възползвате от всички тях, да направите съхранението и достъпа до вашите данни надеждни, оптимални и максимално комфортни.

Съществуват и специфични типове обекти в много СУБД, които отсъстват в други. Можем да извършваме операции не само над обекти на базата данни, но и над самата СУБД, например да "убием" процес, да освободим определена област памет, да активираме трасировка, да преминем в режим "read only" и много други.

А сега да поработим малко с рисунки

Една от най-често срещаните задачи е да се построи диаграма с обекти на базата данни, за да видим на красив графичен образ обектите и връзките между тях. Почти всяка графична IDE, както и отделни "command line" утилити, специализирани графични инструменти и моделиращи програми, могат да го направят. Те ще ви нарисуват „както могат“, а малко влияние върху процеса може да се окаже, че може да се упражни само с помощта на няколко параметъра в конфигурационния файл или отметки в интерфейса.

Но тази задача може да се реши много по-просто, гъвкаво и елегантно, разбира се, с помощта на код. За изграждане на диаграми с всякаква сложност разполагаме с няколко специализирани езика за разметка (DOT, GraphML и др.), а към тях се добавят множество приложения (GraphViz, PlantUML, Mermaid), които могат да четат такива инструкции и да визуализират в най-различни формати. А информацията за обектите и връзките между тях вече знаем как да получим.

Нека дадем малък пример за това как би могло да изглежда, използвайки PlantUML и демонстрационна база данни за PostgreSQL (вляво 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 диаграма на базата на ER шаблон за PlantUML може да доведе до нещо, което много наподобява истинска ER диаграма:

SQL заявката чуууть по-сложна

-- Заглавие
select '@startuml
        !define Table(name,desc) class name as "desc" << (T,#FFAAAA) >&gt;
        !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 информация за %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" --&gt; "0..N" ' || tc.table_name || format(' : "A %s може да има много %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'

Ако погледнем внимателно, много от инструментите за визуализация използват подобни заявки в основата. Вярно е, че тези заявки обикновено са дълбоко "вградени" в кода на самото приложение и са трудни за разбирене, да не споменаваме за каквато и да е модификация.

Метрики и мониторинг

Да преминем към традиционно сложната тема — мониторинг на производителността на БД. Ще си спомня една малка истинска история, разказана ми "от един мой приятел". На поредния проект живял-бил един мощен DBA, и малко от разработчиците го познавали лично, нито пък изобщо били виждали лицето му (въпреки че слуховете твърдят, че е работел някъде в съседната сграда). В часа "X", когато системата на голям ритейлър отново започвала да "се чувства зле", той мълчаливо изпращал скрийншоти на графици от Oracle Enterprise Manager, на които внимателно маркирал критичните места с червен маркер за "разбираемост" (което, меко казано, малко помагало). И по тази "фотокартичка" трябвало да се лекуваме. В същото време достъпът до ценния (в двата смисъла на думата) Enterprise Manager не бил предоставен на никого, тъй като системата била сложна и скъпа, вдруги "разработчиците да не натъкнат на нещо и всичко да се счупи". Затова разработчиците по "емпиричен" начин намирали мястото и причината за забавянията и пускали пач. Ако страховитото писмо от DBA не пристигнело отново в близко време, всички с облекчение си поемали въздух и се връщали към текущите си задачи (до ново Писмо).

Но процесът на мониторинг може да изглежда по-весел и приятелски, а най-важното — достъпен и прозрачен за всички. Най-малко основната му част, като допълнение към основните системи за мониторинг (които безспорно са полезни и в много случаи незаменими). Всяка СУБД свободно и абсолютно безплатно е готова да сподели информация за текущото си състояние и производителност. В същата "кървава" Oracle DB почти всяка информация за производителността може да бъде получена от системните представления, започвайки от процесите и сесиите и завършвайки със състоянието на буферния кеш (например, DBA Scripts, раздел "Мониторинг"). В Postgresql също има цяла поредица от системни представления за мониторинг на работата на БД, включително такива незаменими в ежедневието на всеки DBA, като pg_stat_activity, pg_stat_database, pg_stat_bgwriter. В MySQL за тази цел е предвидена дори отделна схема performance_schema. А в Mongo вграден профайлер агрегира данни за производителността в системната колекция system.profile.

Така, като сме се сдобили с някакъв сборник на метрики (Telegraf, Metricbeat, Collectd), който може да изпълнява персонализирани SQL запитвания, хранилище за тези метрики (InfluxDB, Elasticsearch, Timescaledb) и визуализатор (Grafana, Kibana), можем да получим сравнително лесна и гъвкава система за мониторинг, която ще бъде тясно интегрирана с други общи системни метрики (получавани например от приложен сървър, от ОС и др.). Както е направено в pgwatch2, където се използва комбинация от InfluxDB + Grafana и набор от запитвания към системни представяния, към които също могат да се добавят персонализирани запитвания.

Итого

И това е само приблизителен списък на нещата, които можем да направим с нашата база данни чрез обикновен SQL код. Уверен съм, че можем да намерим още много приложения, пишете в коментарите. А за това как (и най-важното, защо) да автоматизираме всичко това и да го включим в своя CI/CD pipeline, ще говорим следващия път.

Източник: habr.com