SQLite base 資料庫欄位更動的小發現

Jul 02, 2025

近期蠻着迷 serverless 相關技術,serverless 的概念對我來說是共享與獨立處理,所以只需要依照算力與佔用時間付費,目前一些小專案都使用在 cloudflare 上面,除了運算另一個要處理的是資料儲存。

Serverless 資料庫(Database)

Serverless(無伺服器)技術以其共享資源與按需計費的特性,廣泛應用於現代雲端開發。我的小型專案目前主要部署在 Cloudflare 平台,運算部分由 Serverless 架構處理,資料儲存則仰賴以下資料庫方案。

PostgreSQL

Neon

我的部落格資料庫目前托管於 Neon,其設置方式與本地端 PostgreSQL 相近,只需更換連線網址即可。Neon 提供免費方案,適合儲存需求較低的情境,例如我的部落格僅在編輯靜態頁面時讀取資料庫,使用量極小,相當符合需求。

Prisma 與 PostgreSQL

自從使用 Node.js 開發後,我對 Prisma 的喜愛有增無減。相較於過去使用的 TypeORM(缺乏真正的型別安全),Prisma 提供完善的型別檢查,所有查詢組合在執行前即可發現錯誤,大幅降低撰寫錯誤的機率。Prisma 支援的 PostgreSQL 資料庫具備自動擴展與按需計費功能,無需自行估算使用量,特別適合與 AWS 帳單整合的企業場景。

SQLite

傳統上,SQLite 以單一檔案形式存在,適用於本地端或嵌入式系統。然而,libsql 的出現改變了這一限制,使 SQLite 能透過 HTTP 連線存取,並實現運算與儲存分離,適應現代 Web 應用需求。

Cloudflare D1

Cloudflare D1Cloudflare 原生支援的 Serverless 資料庫,設定極為簡便。透過 Cloudflare 的 CLI 工具,僅需提供 database_id(UUID 格式),即可在內網中完成連線。若需對外存取,則需生成一組 token。其分散式架構無需指定區域,交由平台自動處理,極大簡化部署流程。

TURSO

TURSO 憑藉 HTTP 存取 SQLite 的技術,領先同類產品。設定時需選擇資料儲存位置(推測為 AWS S3 的區域設定),但其介面直觀且操作簡潔。TURSO 提供的資料庫數量充足,近期更計畫推出無限資料庫方案,適合 SaaS(軟體即服務) 應用,讓每個使用者擁有獨立資料庫以提升安全性。然而,多資料庫的遷移(migrations)管理需謹慎設計,以避免複雜性。

小發現:資料庫遷移的差異

在最近的欄位異動操作中,我注意到 SQLitePostgreSQL 在資料庫遷移(migrations)上的顯著差異。

PostgreSQL 的欄位操作

PostgreSQL 的欄位新增與修改簡單直接。例如,新增欄位:

ALTER TABLE 資料表名稱 ADD COLUMN 新欄位名稱 資料型態 [約束條件];

短短兩行就好。

修改欄位 從 NULLNOT NULL

ALTER TABLE 資料表名稱 ALTER COLUMN 欄位名稱 SET NOT NULL;

SQLite 的欄位修改則需重建表格,步驟如下:

  1. 新增一個 新table
  2. 舊 table 資料全部 insert
  3. Drop 舊 table
  4. Rename 新 table name舊 table name

以下是實際的範例

-- RedefineTables PRAGMA defer_foreign_keys=ON; PRAGMA foreign_keys=OFF; CREATE TABLE "new_Transcation" ( "id" TEXT NOT NULL PRIMARY KEY, "purchaseDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "direction" TEXT NOT NULL DEFAULT 'BUY', "userId" TEXT NOT NULL, "symbol" TEXT NOT NULL, "amount" REAL NOT NULL, "price" REAL NOT NULL, "note" TEXT, "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" DATETIME NOT NULL, CONSTRAINT "Transcation_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE ); INSERT INTO "new_Transcation" ("amount", "createdAt", "id", "note", "price", "purchaseDate", "symbol", "updatedAt", "userId") SELECT "amount", "createdAt", "id", "note", "price", "purchaseDate", "symbol", "updatedAt", "userId" FROM "Transcation"; DROP TABLE "Transcation"; ALTER TABLE "new_Transcation" RENAME TO "Transcation"; CREATE INDEX "Transcation_symbol_idx" ON "Transcation"("symbol"); CREATE INDEX "Transcation_purchaseDate_idx" ON "Transcation"("purchaseDate"); PRAGMA foreign_keys=ON; PRAGMA defer_foreign_keys=OFF;

結論與技術差異

SQLite 的欄位修改操作相對繁瑣,主要是為了維持其輕量與簡單的設計原則。以下是 SQLite 與 PostgreSQL 在欄位操作差異的關鍵原因:

  1. 動態型態 vs. 嚴格型態:
  • SQLite 採用動態型態(duck typing),資料儲存靈活,但限制了直接修改欄位約束的能力。
  • PostgreSQL 具備嚴格型態系統,支援複雜的型態轉換與約束檢查,適合高資料完整性需求的應用。
  1. 事務性 DDL:
  • PostgreSQL 支援事務性資料定義語言(DDL),允許在事務中執行欄位修改,失敗可回滾。
  • SQLite 不支援事務性 DDL,需手動重建表格,增加操作複雜度。
  1. 並發性與鎖定:
  • SQLite 的單一檔案結構在修改時鎖定整個資料庫,限制了複雜修改操作。
  • PostgreSQL 採用多版本並行控制(MVCC),支援高並發修改。
  1. 功能定位:
  • SQLite 專為輕量級與嵌入式應用設計,犧牲部分進階功能以換取低資源消耗。
  • PostgreSQL 針對企業級應用,提供複雜查詢與高並發支援。並發,提供了更靈活的欄位操作功能。
Ekman Hsieh

文字工作者,寫作時間常常在人類與電腦之間拉鋸,相信閱讀,相信文字與思想所構築的美麗境界