🥇“Database as Code” Kev paub

SQL, dab tsi yuav yooj yim dua? Peb txhua tus tuaj yeem sau qhov kev thov yooj yim - peb ntaus xaiv, sau cov kab uas xav tau, ces los ntawm, rooj lub npe, tej yam kev mob nyob rau hauv qhov twg thiab qhov ntawd yog txhua yam - cov ntaub ntawv muaj txiaj ntsig yog nyob rau hauv peb lub hnab tshos, thiab (yuav luag) tsis hais seb DBMS nyob rau hauv lub hood thaum lub sijhawm ntawd (los yog tej zaum tsis yog DBMS kiag li). Raws li qhov tshwm sim, ua haujlwm nrog yuav luag txhua yam ntaub ntawv (kev sib raug zoo thiab tsis yog li ntawd) tuaj yeem txiav txim siab los ntawm qhov pom ntawm cov cai zoo tib yam (nrog rau txhua yam uas nws cuam tshuam - kev tswj hwm version, kev tshuaj xyuas cov lej, kev tshuaj xyuas zoo li qub, autotests, thiab txhua yam). Thiab qhov no siv tsis tau tsuas yog rau cov ntaub ntawv nws tus kheej, schemas thiab migrations, tab sis feem ntau rau tag nrho lub neej ntawm kev cia. Hauv tsab xov xwm no peb yuav tham txog cov haujlwm niaj hnub thiab teeb meem ntawm kev ua haujlwm nrog ntau cov ntaub ntawv hauv qab lub lens ntawm "database li code".

Thiab cia peb pib txoj cai los ntawm ORM. Thawj qhov kev sib ntaus sib tua ntawm "SQL vs ORM" hom tau pom rov qab rau hauv ua ntej Petrine Rus.

Object-relational mapping

ORM cov neeg txhawb nqa ib txwm muaj txiaj ntsig ceev thiab yooj yim ntawm kev txhim kho, kev ywj pheej los ntawm DBMS thiab cov cai huv. Rau ntau ntawm peb, cov cai rau kev ua haujlwm nrog cov ntaub ntawv (thiab feem ntau cov ntaub ntawv nws tus kheej)

feem ntau zoo li no...

@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;
    }
  ...

Tus qauv yog dai nrog cov lus piav qhia ntse, thiab qhov chaw tom qab ntawm qhov xwm txheej ib tus neeg muaj peev xwm ORM tsim thiab ua tiav tons ntawm qee qhov SQL code. Los ntawm txoj kev, cov neeg tsim khoom tau sim lawv qhov zoo tshaj plaws los cais lawv tus kheej los ntawm lawv cov ntaub ntawv nrog mais ntawm abstractions, uas qhia qee qhov "SQL ntxub".

Nyob rau sab nraud ntawm cov kev thaiv, cov neeg ua haujlwm ntawm cov ntshiab "s tes ua" SQL nco ntsoov lub peev xwm los nyem tag nrho cov kua txiv hmab txiv ntoo tawm ntawm lawv DBMS yam tsis muaj txheej txheej ntxiv thiab abstractions. Raws li qhov tshwm sim, cov haujlwm "cov ntaub ntawv-centric" tshwm sim, qhov twg cov neeg kawm tshwj xeeb tau koom nrog hauv cov ntaub ntawv (lawv kuj yog "cov kws tshaj lij", lawv kuj yog "cov kws tshaj lij", lawv kuj yog "basdeners", thiab lwm yam), thiab cov tsim tawm. tsuas yog yuav tsum tau "rub" cov npaj ua tiav saib thiab khaws cov txheej txheem, tsis tas yuav nkag mus rau cov ntsiab lus.

Yuav ua li cas yog tias peb muaj qhov zoo tshaj plaws ntawm ob lub ntiaj teb? Yuav ua li cas qhov no yog ua nyob rau hauv ib tug zoo kawg nkaus lub cuab tam nrog lub neej-paub lub npe Yesql. Kuv yuav muab ob peb kab los ntawm cov ntsiab lus dav dav hauv kuv phau ntawv txhais lus pub dawb, thiab koj tuaj yeem paub nrog nws kom ntxaws ntxiv no.

Clojure yog hom lus txias rau kev tsim DSLs, tab sis SQL nws tus kheej yog DSL txias, thiab peb tsis xav tau lwm tus. S-hais lus zoo heev, tab sis lawv tsis ntxiv dab tsi tshiab ntawm no. Yog li ntawd, peb tau txais cov brackets rau lub hom phiaj ntawm cov brackets. Tsis pom zoo? Tom qab ntawd tos rau lub sijhawm thaum lub abstraction dhau ntawm cov ntaub ntawv pib xau thiab koj pib sib ntaus nrog cov haujlwm (raws-sql)

Yog li kuv yuav ua li cas? Cia peb tawm SQL li niaj zaus SQL - ib daim ntawv thov:

-- name: users-by-country
select *
  from users
 where country_code = :country_code

... thiab tom qab ntawd nyeem cov ntaub ntawv no, tig mus rau hauv Clojure muaj nuj nqi:

(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" ...} ...)

Los ntawm kev ua raws li "SQL los ntawm nws tus kheej, Clojure los ntawm nws tus kheej", koj tau txais:

Tsis muaj syntactic surprises. Koj lub database (zoo li lwm yam) tsis yog 100% raws li SQL tus qauv - tab sis qhov no tsis muaj teeb meem rau Yesql. Koj yuav tsis nkim sij hawm mus yos hav zoov rau kev ua haujlwm nrog SQL sib npaug syntax. Koj yuav tsis tau rov qab mus ua haujlwm (raw-sql "some('funky'::SYNTAX)")).

Qhov zoo tshaj plaws editor txhawb. Koj tus editor twb muaj kev txhawb nqa SQL zoo heev. Los ntawm kev txuag SQL li SQL koj tuaj yeem siv nws yooj yim.

Pab neeg compatibility. Koj DBAs tuaj yeem nyeem thiab sau SQL koj siv hauv koj qhov project Clojure.

Kev ua haujlwm tau yooj yim dua. Xav tau tsim ib txoj kev npaj rau cov lus nug uas muaj teeb meem? Qhov no tsis yog teeb meem thaum koj cov lus nug tsis tu ncua SQL.

Rov siv cov lus nug. Luag thiab tso cov tib SQL cov ntaub ntawv mus rau lwm qhov haujlwm vim nws tsuas yog SQL qub qub - tsuas yog qhia nws.

Hauv kuv lub tswv yim, lub tswv yim yog txias heev thiab tib lub sijhawm yooj yim heev, ua tsaug rau qhov project tau txais ntau yam cov thwjtim hauv ntau hom lus. Thiab tom ntej no peb yuav sim siv lub tswv yim zoo sib xws ntawm kev sib cais SQL code los ntawm txhua yam nyob deb dhau ntawm ORM.

IDE & DB managers

Cia peb pib nrog txoj haujlwm yooj yim txhua hnub. Feem ntau peb yuav tsum tshawb nrhiav qee yam khoom hauv cov ntaub ntawv, piv txwv li, nrhiav ib lub rooj hauv schema thiab kawm nws cov qauv (cov kab ntawv dab tsi, cov yuam sij, qhov ntsuas, qhov txwv, thiab lwm yam yog siv). Thiab los ntawm ib qho graphical IDE lossis DB-tus thawj coj me me, ua ntej ntawm tag nrho cov, peb cia siab tias cov peev txheej no. Yog li ntawd nws ceev ceev thiab koj tsis tas yuav tos ib nrab ib teev kom txog thaum lub qhov rais nrog cov ntaub ntawv tsim nyog tau kos (tshwj xeeb tshaj yog nrog kev sib txuas qeeb rau cov chaw taws teeb database), thiab tib lub sijhawm, cov ntaub ntawv tau txais yog tshiab thiab cuam tshuam, thiab tsis cached junk. Ntxiv mus, qhov nyuaj thiab loj dua cov ntaub ntawv thiab ntau dua ntawm lawv, qhov nyuaj dua yog ua qhov no.

Tab sis feem ntau kuv muab tus nas pov tseg thiab tsuas yog sau code. Cia peb hais tias koj yuav tsum paub seb cov ntxhuav twg (thiab cov khoom twg) muaj nyob rau hauv "HR" schema. Hauv feem ntau DBMSs, qhov xav tau tshwm sim tuaj yeem ua tiav nrog cov lus nug yooj yim no los ntawm information_schema:

select table_name
     , ...
  from information_schema.tables
 where schema = 'HR'

Los ntawm cov ntaub ntawv mus rau cov ntaub ntawv, cov ntsiab lus ntawm cov lus siv no txawv nyob ntawm qhov muaj peev xwm ntawm txhua DBMS. Thiab, piv txwv li, rau MySQL, los ntawm tib phau ntawv siv koj tuaj yeem tau txais cov lus qhia tshwj xeeb rau DBMS no:

select table_name
     , storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
     , row_format     -- Формат строки ("Fixed", "Dynamic" etc)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle tsis paub information_schema, tab sis nws muaj Oracle metadata, thiab tsis muaj teeb meem loj tshwm sim:

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

ClickHouse tsis muaj kev zam:

select name
     , engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
     , ...
  from system.tables
 where database = 'HR'

Ib yam dab tsi zoo sib xws tuaj yeem ua tiav hauv Cassandra (uas muaj cov tsev neeg hauv tsev tsis yog cov rooj thiab cov chaw tseem ceeb tsis yog schemas):

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

Rau feem ntau lwm cov ntaub ntawv, koj tuaj yeem tuaj nrog cov lus nug zoo sib xws (txawm Mongo muaj kev sau tshwj xeeb, uas muaj cov ntaub ntawv hais txog txhua qhov kev sau hauv qhov system).

Ntawm chav kawm, nyob rau hauv txoj kev no koj tuaj yeem tau txais cov ntaub ntawv tsis yog hais txog cov ntxhuav, tab sis hais txog txhua yam khoom. Txij lub sij hawm, cov neeg zoo sib koom cov cai no rau cov ntaub ntawv sib txawv, xws li, piv txwv li, hauv cov kab lus ntawm habra "Functions for documenting PostgreSQL databases" (Aib, Ben, gym). Tau kawg, khaws tag nrho cov roob ntawm cov lus nug hauv kuv lub taub hau thiab niaj hnub ntaus ntawv yog qhov zoo siab, yog li hauv kuv tus nyiam IDE / editor kuv muaj cov txheej txheem ua ntej npaj rau cov lus nug nquag siv, thiab txhua yam uas tseem tshuav yog ntaus ntawv. cov npe khoom rau hauv template.

Yog li ntawd, txoj kev tshawb nrhiav thiab tshawb nrhiav cov khoom no hloov pauv tau yooj yim, txuag sijhawm ntau, thiab tso cai rau koj kom tau txais cov ntaub ntawv raws nraim hauv daim ntawv uas tam sim no tsim nyog (xws li, piv txwv li, tau piav qhia hauv tsab ntawv tshaj tawm. "Kev xa tawm cov ntaub ntawv los ntawm cov ntaub ntawv hauv txhua hom: IDEs tuaj yeem ua li cas ntawm IntelliJ platform").

Kev ua haujlwm nrog cov khoom

Tom qab peb tau pom thiab kawm txog cov khoom tsim nyog, nws yog lub sijhawm los ua qee yam muaj txiaj ntsig nrog lawv. Lawm, kuj tsis tas siv koj cov ntiv tes tawm ntawm cov keyboard.

Nws tsis pub leejtwg paub tias tsuas yog rho tawm ib lub rooj yuav zoo ib yam hauv yuav luag txhua cov ntaub ntawv:

drop table hr.persons

Tab sis nrog cov creation ntawm lub rooj nws ua ntau nthuav. Yuav luag txhua DBMS (nrog rau ntau NoSQL) tuaj yeem "tsim lub rooj" hauv ib daim ntawv lossis lwm qhov, thiab qhov tseem ceeb ntawm nws yuav txawv me ntsis (lub npe, daim ntawv teev npe, hom ntaub ntawv), tab sis lwm cov ntsiab lus tuaj yeem sib txawv thiab nyob ntawm cov khoom siv sab hauv thiab muaj peev xwm ntawm DBMS tshwj xeeb. Kuv nyiam qhov piv txwv yog tias hauv Oracle cov ntaub ntawv tsuas muaj "liab qab" BNFs rau "tsim rooj" syntax ua 31 pages. Lwm cov DBMSs muaj peev xwm me me, tab sis txhua tus ntawm lawv kuj muaj ntau yam nthuav thiab tshwj xeeb rau kev tsim cov rooj (postgres, mysql, kab laum, taumdub). Nws tsis zoo li cov duab "wizard" los ntawm lwm tus IDE (tshwj xeeb tshaj yog ib qho thoob ntiaj teb) yuav tuaj yeem npog tag nrho cov peev txheej no, thiab txawm tias nws tuaj yeem ua tau, nws yuav tsis yog qhov pom ntawm lub plawv. Nyob rau tib lub sijhawm, cov lus sau raug thiab raws sijhawm tsim rooj yuav tso cai rau koj kom yooj yim siv tag nrho cov ntawm lawv, ua kom cia thiab nkag mus rau koj cov ntaub ntawv txhim khu kev qha, pom thiab nyiam li sai tau.

Tsis tas li ntawd, ntau DBMSs muaj lawv tus kheej cov khoom tshwj xeeb uas tsis muaj nyob hauv lwm DBMSs. Ntxiv mus, peb tuaj yeem ua haujlwm tsis yog ntawm cov khoom siv hauv database nkaus xwb, tab sis kuj ntawm DBMS nws tus kheej, piv txwv li, "tua" txheej txheem, tso qee qhov chaw nco, pab kom taug qab, hloov mus rau "nyeem nkaus xwb" hom, thiab ntau ntxiv.

Tam sim no cia peb kos me ntsis

Ib qho ntawm cov haujlwm tshaj plaws yog tsim cov duab kos nrog cov khoom siv database thiab pom cov khoom thiab kev sib txuas ntawm lawv hauv daim duab zoo nkauj. Yuav luag txhua daim duab IDE, cais "kab lus txib" cov khoom siv hluav taws xob, cov cuab yeej siv duab tshwj xeeb thiab cov qauv ua qauv tuaj yeem ua qhov no. Lawv yuav kos ib yam dab tsi rau koj "zoo li lawv tuaj yeem ua tau," thiab koj tuaj yeem cuam tshuam cov txheej txheem no me ntsis tsuas yog nrog kev pab ntawm ob peb qhov kev teeb tsa hauv cov ntaub ntawv teeb tsa lossis checkboxes hauv lub interface.

Tab sis qhov teeb meem no tuaj yeem daws tau yooj yim dua, hloov tau yooj yim thiab muaj kuab heev, thiab tau kawg nrog kev pab ntawm cov cai. Txhawm rau tsim cov duab kos ntawm txhua qhov nyuaj, peb muaj ntau hom lus tshwj xeeb (DOT, GraphML thiab lwm yam), thiab rau lawv tag nrho cov kev siv (GraphViz, PlantUML, Mermaid) uas tuaj yeem nyeem cov lus qhia thiab pom lawv hauv ntau hom ntawv. . Zoo, peb twb paub yuav ua li cas kom tau txais cov ntaub ntawv hais txog cov khoom thiab kev sib txuas ntawm lawv.

Nov yog ib qho piv txwv me me ntawm qhov no yuav zoo li cas, siv PlantUML thiab Demo database rau PostgreSQL (ntawm sab laug yog SQL lus nug uas yuav tsim cov lus qhia yuav tsum tau rau PlantUML, thiab sab xis yog qhov tshwm sim):

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'

Thiab yog tias koj sim me ntsis, ces raws li ER template rau PlantUML Koj tuaj yeem tau txais qee yam zoo ib yam li daim duab ER tiag tiag:

Cov lus nug SQL yog qhov nyuaj me ntsis

-- Шапка
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'

Yog tias koj saib ze, hauv qab hood ntau cov cuab yeej pom pom kuj siv cov lus nug zoo sib xws. Muaj tseeb tiag, cov kev thov no feem ntau yog tob heev "hardwired" rau hauv cov cai ntawm daim ntawv thov nws tus kheej thiab nyuaj rau kev nkag siab, tsis hais txog kev hloov kho ntawm lawv.

Metrics thiab saib xyuas

Cia peb txav mus rau lub ntsiab lus ib txwm ua nyuaj - kev saib xyuas cov ntaub ntawv kev ua haujlwm. Kuv nco qab ib zaj dab neeg tseeb me me qhia rau kuv los ntawm "ib tug ntawm kuv cov phooj ywg." Nyob rau lwm qhov project muaj nyob ib tug tej yam haib DBA, thiab ob peb ntawm cov developers paub nws tus kheej, los yog puas tau pom nws nyob rau hauv tib neeg (txawm hais tias, raws li cov lus xaiv, nws ua hauj lwm nyob rau hauv ib qho chaw nyob rau hauv lub tom ntej no lub tsev). Thaum lub sijhawm "X", thaum lub kaw lus ntawm lub khw muag khoom loj pib "xav tsis zoo" ib zaug ntxiv, nws ntsiag to xa cov duab thaij duab ntawm Oracle Enterprise Manager, uas nws ua tib zoo qhia qhov chaw tseem ceeb nrog lub cim liab rau "kev nkag siab" ( qhov no, muab nws me me, tsis pab ntau). Thiab raws li "daim npav daim npav" no kuv yuav tsum tau kho. Nyob rau tib lub sijhawm, tsis muaj leej twg tau nkag mus rau qhov muaj nuj nqis (hauv ob qho kev nkag siab ntawm lo lus) Tus Thawj Saib Xyuas Kev Lag Luam, vim Lub kaw lus yog qhov nyuaj thiab kim, tam sim ntawd "cov neeg tsim khoom ntog rau ib yam dab tsi thiab tawg txhua yam." Yog li ntawd, cov neeg tsim khoom "empirically" pom qhov chaw thiab ua rau lub brakes thiab tso tawm ib thaj. Yog tias tsab ntawv ceeb toom los ntawm DBA tsis tuaj txog dua yav tom ntej, ces txhua tus yuav ua pa zoo siab thiab rov qab mus rau lawv txoj haujlwm tam sim no (txog tsab ntawv tshiab).

Tab sis cov txheej txheem kev soj ntsuam tuaj yeem saib kev lom zem thiab ua phooj ywg ntau dua, thiab qhov tseem ceeb tshaj plaws, siv tau thiab pob tshab rau txhua tus. Yam tsawg kawg ntawm nws qhov tseem ceeb, ua ib qho ntxiv rau cov kev saib xyuas tseem ceeb (uas yeej muaj txiaj ntsig zoo thiab ntau qhov tsis tuaj yeem hloov tau). Txhua DBMS yog dawb thiab dawb xwb los qhia cov ntaub ntawv hais txog nws lub xeev tam sim no thiab kev ua haujlwm. Hauv tib "ntshav" Oracle DB, yuav luag txhua cov ntaub ntawv hais txog kev ua tau zoo tuaj yeem tau txais los ntawm kev saib xyuas, xws li cov txheej txheem thiab ntu mus rau lub xeev ntawm qhov tsis muaj cache (piv txwv li, DBA Scripts, seem "Saib xyuas"). Postgresql kuj tseem muaj ntau pawg ntawm qhov system saib rau kev soj ntsuam database, tshwj xeeb tshaj yog cov uas tseem ceeb hauv lub neej niaj hnub ntawm DBA, xws li pg_stat_activity, pg_stat_database, pg_stat_bgwriter. MySQL txawm muaj cais schema rau qhov no. kev ua haujlwm_schema. Ib In Mongo built-in profiler aggregates cov ntaub ntawv kev ua tau zoo rau hauv qhov system sau system.profile.

Yog li, riam phom nrog qee yam kev ntsuas ntsuas (Telegraf, Metricbeat, Sau) uas tuaj yeem ua cov lus nug sql kev cai, kev khaws cia ntawm cov ntsuas no (InfluxDB, Elasticsearch, Timescaledb) thiab cov duab pom (Grafana, Kibana), koj tuaj yeem tau txais ib qho yooj yim heev. thiab ib qho kev soj ntsuam hloov tau yooj yim uas yuav raug sib raug zoo nrog lwm cov kev ntsuas thoob plaws hauv lub cev (tau txais, piv txwv li, los ntawm daim ntawv thov server, los ntawm OS, thiab lwm yam). Raws li, piv txwv li, qhov no yog ua tiav hauv pgwatch2, uas siv InfluxDB + Grafana kev sib xyaw ua ke thiab cov lus nug rau cov kev pom, uas tuaj yeem nkag mus. ntxiv cov lus nug.

Tag nrho

Thiab qhov no tsuas yog cov npe kwv yees ntawm qhov ua tau nrog peb cov ntaub ntawv siv SQL code li niaj zaus. Kuv paub tseeb tias koj tuaj yeem nrhiav tau ntau yam ntxiv, sau rau hauv cov lus. Thiab peb yuav tham txog yuav ua li cas (thiab qhov tseem ceeb tshaj yog vim li cas) kom ua tiav txhua qhov no thiab suav nrog hauv koj lub CI / CD kav dej rau lwm zaus.

Tau qhov twg los: www.hab.com

"Database as Code" Kev paub