第一步是意識到：資料庫引擎本來就是應用程式邏輯的一部分。再簡單的 SQL 語句都內嵌了邏輯——投影特定欄位、用 where 過濾資料、要求特定排序——那已經是商業邏輯。應用程式碼，有一部分是用 SQL 寫的。

前面我們比較過八行 SQL 與典型物件模型程式碼在正確性與效率上的差異，也看過把 SQL 以 .sql 檔放進程式碼庫的做法。既然 SQL 是原始碼樹裡的程式碼，就該套用你熟悉的那套方法論：一致的縮排規則、程式註解、一致的命名、單元測試、版本控制。

SQL 風格準則#

程式風格的核心是最小驚訝原則（principle of least astonishment），所以較大的團隊需要一份人人遵守的內部風格指南。先看反例：

SELECT title, name FROM album LEFT JOIN track USING(albumid) WHERE albumid = 1 ORDER BY 2

整條擠在同一行，讀者難以一眼掌握結構；而且用了全大寫關鍵字的老習慣——現在有彩色螢幕和語法高亮，我們早就不寫全大寫的程式碼了，SQL 也一樣。

作者的建議：頂層 SQL 子句靠右對齊、各自成行：

  select title, name
    from album left join track using(albumid)
   where albumid = 1
order by 2;

結構一目了然，也更容易看出問題所在——order by 2。SQL 允許用輸出欄位編號當參照，在互動提示符下很方便（我們都懶嘛），但會讓重構變難：一旦改了輸出欄位，order by 2 的意義就變了，diff 裡會多出一行「為什麼改欄位要動 order by」的修改，徒增審查負擔。這個例子裡正確的排序其實該用 trackid（Chinook 模型中曲目在專輯上的順序）：

  select name, milliseconds
    from album left join track using(albumid)
   where albumid = 1
order by trackid;

另一種寫法是把 from 拆成一行一個來源關係，讓 join 更醒目；再進一步可把 join 條件（on 或 using）也獨立成行——這種展開式排版在使用子查詢時特別有用：

  select title, name, milliseconds
    from (
           select albumid, title
             from      album
                  join artist using(artistid)
            where artist.name = 'AC/DC'
         )
           as artist_albums
         left join track
             using(albumid)
order by trackid;

風格選擇最重要的是一致：上例中即使子查詢的 from 很簡單，也照樣拆行。SQL 規定子查詢必須加括號，正好可以把這個要求用在縮排上。

幾個具體的取捨：

  • join 條件寫在 where 裡（FROM artist, album WHERE artist.artistid = album.artistid ...）是 SQL 標準規範 join 語意之前、七〇八〇年代的遺風，極易混淆，應避免。用現代寫法 from artist inner join album using(artistid)。
  • inner 與 outer 都是噪音詞（noise words）：left/right/full join 一定是 outer join，直接寫 join 的一定是 inner join。
  • 避免 natural join：它自動以同名欄位展開 join 條件，只要加減一個欄位就可能悄悄改變查詢語意。Chinook 模型有五張表都有 name 欄位且都不是主鍵——你多半不想用 name 來 join。
延伸範例：用曲名向藝人致敬的查詢（關係別名）

好玩起見，找出 Chinook 資料集中「曲名取自另一位藝人名字」的案例：

select artist.name as artist,
       inspired.name as inspired,
       album.title as album,
       track.name as track
  from      artist
       join track on track.name = artist.name
       join album on album.albumid = track.albumid
       join artist inspired on inspired.artistid = album.artistid
 where artist.artistid <> inspired.artistid;
    artist     │   inspired    │       album        │     track
═══════════════╪═══════════════╪════════════════════╪═══════════════
 Iron Maiden   │ Paul D'Ianno  │ The Beast Live     │ Iron Maiden
 Black Sabbath │ Ozzy Osbourne │ Speak of the Devil │ Black Sabbath
(2 rows)

兩位主唱都用前樂團的名字命名歌曲。查詢中 artist 表出現兩次，靠 SQL 標準的**關係別名（relation alias）**區分。作者坦承在 psql 裡初次打這條查詢時用了 a1、a2 當別名——但就像變數命名：程式審查不會放過 a1、a2 這種變數名，SQL 別名也一樣不該用。

註解#

SQL 標準有兩種註解：雙破折號的單行註解，以及 C 式的 /* ... */ 區塊註解（與 C 不同，SQL 的區塊註解可以巢狀）。

-- artists names used as track names by other artists
select artist.name as artist,
       -- "inspired" is the other artist
       inspired.name as inspired,
       ...
  from      artist
       /*
        * Here we join the artist name on the track name,
        * which is not our usual kind of join and thus
        * we don't use the using() syntax.
        */
       join track
         on track.name = artist.name
       ...

原則與程式註解相同：解釋顯而易見的事毫無意義，該註解的是不尋常或難寫的部分，目標是讓讀者永遠不必猜測作者意圖。有人也用註解嵌入查詢的來源位置以利除錯——但有了 PostgreSQL 的 application_name 機制加上 .sql 檔的良好使用，這招的必要性就存疑了。

單元測試#

SQL 是程式碼，所以需要測試。單元測試的通則對 SQL 完全適用：給定已知輸入，查詢應永遠回傳相同的期望輸出——這讓你能放心改寫查詢，仍確認替代寫法通過測試。

改寫查詢的方式很多而語意不變：把共同資料表運算式（Common Table Expression, CTE）內聯成子查詢、把 where 的 or 分支展開成 union all、用視窗函數（window function）取代複雜的子查詢雜耍等。例如這條 CTE 查詢：

with artist_albums as
  (
     select albumid, title
       from      album
            join artist using(artistid)
      where artist.name = 'AC/DC'
  )
  select title, name, milliseconds
    from artist_albums
         left join track
             using(albumid)
order by trackid;

可以改寫成語意完全相同（但執行期特性不同）的子查詢版本。

PostgreSQL 專案本身就用大量 SQL 測試驗證其解析器、最佳化器與執行器，其**回歸測試套件（regression tests suite）**的想法極簡單：

  1. 用 psql 執行含測試的 SQL 檔
  2. 把輸出（含查詢與結果）擷取成文字檔
  3. 用標準 diff 工具與版本庫中維護的期望輸出比較
  4. 有差異即回報失敗

（可參考 PostgreSQL 版本庫中的 src/test/regress/sql/aggregates.sql 與對應的 src/test/regress/expected/aggregates.out。）你的應用要實作同樣的機制很容易——驅動程式只是 psql 與 diff 的薄薄包裝，記得在測試 SQL 檔中安排 setup（建模型、灌測試資料）與 teardown（清除）步驟。

要更進一步自動化，pgTap 是一套資料庫函式，讓你在 psql 腳本或 xUnit 風格的測試函式中寫出輸出 TAP 格式的單元測試。針對結果集的測試可用 relation-testing 函式，例如比對 VALUES：

SELECT results_eq(
    'SELECT * FROM active_users()',
    $$
       VALUES (42, 'Anna'),
              (19, 'Strongrrl'),
              (39, 'Theory')
    $$,
    'active_users() should return active users'
);

pgTap 的單元測試本身也是 SQL 寫的——你擁有 SQL 的全部威力來寫測試，還能用 PostgreSQL catalog 函式直接以 SQL 檢查 schema 完整性。測試以 pg_prove 命令列工具執行與收割。

其他整合選項：Debian 系的 pg_virtualenv 可建立只在測試期間存在的暫時 PostgreSQL 安裝；用 Python 的話，可讀 Julien Danjou 關於資料庫整合測試策略的文章。

你的應用依賴 SQL；你依賴測試才敢改動與演進應用。測試必須涵蓋應用中的 SQL 部分。

回歸測試#

回歸測試（regression test）防止重構時引入 bug。SQL 也會被重構：呼叫端程式碼改了、查詢也得跟著改；或正式環境出了問題，要簽入最佳化後的新版查詢取代錯誤舊版。做法是登錄查詢的期望結果，之後每次改動查詢就拿實際結果比對。

RegreSQL 工具實作了這個想法：找出程式碼庫中的 SQL 檔、允許對它們登錄測試計畫（plan），再比對結果與期望值。正常輸出如下：

$ regresql test
Connecting to 'postgres:///chinook?sslmode=disable'… ✓
TAP version 13
ok 1 - src/sql/album-by-artist.1.out
ok 2 - src/sql/album-tracks.1.out
ok 3 - src/sql/artist.1.out
ok 4 - src/sql/genre-topn.top-3.out
ok 5 - src/sql/genre-topn.top-1.out
ok 6 - src/sql/genre-tracks.out
延伸輸出：RegreSQL 抓到回歸時的診斷

故意改測試計畫但不更新期望結果，RegreSQL 會以 diff 呈現差異：

$ regresql test
Connecting to 'postgres:///chinook?sslmode=disable'… ✓
TAP version 13
ok 1 - src/sql/album-by-artist.1.out
ok 2 - src/sql/album-tracks.1.out
# Query File: 'src/sql/artist.sql'
# Bindings File: 'regresql/plans/src/sql/artist.yaml'
# Bindings Name: '1'
# Query Parameters: 'map[n:2]'
# Expected Result File: 'regresql/expected/src/sql/artist.1.out'
# Actual Result File: 'regresql/out/src/sql/artist.1.out'
#
# --- regresql/expected/src/sql/artist.1.out
# +++ regresql/out/src/sql/artist.1.out
# @@ -1,4 +1,5 @@
# -   name      | albums
# -------------+-------
# -Iron Maiden | 21
# +    name      | albums
# +-------------+-------
# +Iron Maiden  | 21
# +Led Zeppelin | 14
#
not ok 3 - src/sql/artist.1.out
ok 4 - src/sql/genre-topn.top-3.out
ok 5 - src/sql/genre-topn.top-1.out
ok 6 - src/sql/genre-tracks.out

診斷輸出指向兩種修法：更新期望輸出（regresql update），或修正 regresql/plans/src/sql/artist.yaml 檔。

更進一步#

正式環境出狀況時，重要任務之一是找出「監控、日誌或活動視圖裡看到的那條查詢，是哪段程式送出的」。

PostgreSQL 的 application_name 參數可在連線字串中設定，也可在連線階段內用 SET 指令設定；它會出現在伺服器日誌，也是系統活動視圖 pg_stat_activity 的一部分。

這個設定值得切得細一點——依你的程式語言，細到模組或套件層級。它應由主應用程式完全掌控，所以外部（與內部）函式庫慣例上不去設定它。

Backward SQL REPL：互動式環境 索引策略 Forward