D1 資料庫簡介

什麼是 Cloudflare D1？

Cloudflare D1 是基於 SQLite 的全球分散式資料庫，運行在 Cloudflare 的邊緣網路上。它提供了：

低延遲存取：資料存儲在靠近用戶的邊緣節點
自動擴展：無需管理伺服器容量
SQL 相容性：完整的 SQLite SQL 語法支援
全球複製：自動的資料複製和同步

D1 vs 傳統資料庫

特性	傳統資料庫	Cloudflare D1
部署複雜度	需要設定伺服器	零設定，即開即用
擴展性	手動擴展	自動擴展
全球可用性	單一地區	全球邊緣部署
維護成本	高	極低
查詢延遲	依距離而定	< 50ms 全球

基礎設定與配置

1. 環境準備

首先安裝 Wrangler CLI：

npm install -g wrangler
wrangler login

2. 建立 D1 資料庫

以下是2. 建立 D1 資料庫的詳細說明。

建立新的 D1 資料庫

wrangler d1 create blog-database

輸出範例

✅ Successfully created DB ‘blog-database’ in region APAC Created your database using D1’s new storage backend. database_name = “blog-database” database_id = “your-database-id-here”

3. 配置 wrangler.toml

在專案根目錄的 wrangler.toml 中添加：


name = "blog-system"
main = "functions/_middleware.js"
compatibility_date = "2023-12-01"

[[d1_databases]]
binding = "DB"
database_name = "blog-database"
database_id = "your-database-id-here"

4. 建立資料庫結構

建立 schema.sql 檔案：


-- 部落格文章表
CREATE TABLE IF NOT EXISTS articles (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    category TEXT DEFAULT 'uncategorized',
    tags TEXT DEFAULT '[]',
    author_id TEXT,
    featured_image TEXT,
    is_premium INTEGER DEFAULT 0,
    price REAL DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    featured INTEGER DEFAULT 0,
    status TEXT DEFAULT 'published' CHECK (status IN ('draft', 'published', 'archived')),
    seo_title TEXT,
    seo_description TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 建立索引優化查詢效能
CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);
CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category);
CREATE INDEX IF NOT EXISTS idx_articles_featured ON articles(featured);
CREATE INDEX IF NOT EXISTS idx_articles_created_at ON articles(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_articles_view_count ON articles(view_count DESC);

-- 作者表
CREATE TABLE IF NOT EXISTS authors (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    bio TEXT,
    avatar_url TEXT,
    social_links TEXT DEFAULT '{}',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 分類表
CREATE TABLE IF NOT EXISTS categories (
    id TEXT PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    parent_id TEXT,
    display_order INTEGER DEFAULT 0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 標籤表
CREATE TABLE IF NOT EXISTS tags (
    id TEXT PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    color TEXT DEFAULT '#gray',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 文章標籤關聯表
CREATE TABLE IF NOT EXISTS article_tags (
    article_id TEXT,
    tag_id TEXT,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- 瀏覽記錄表（用於統計分析）
CREATE TABLE IF NOT EXISTS article_views (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id TEXT NOT NULL,
    user_ip TEXT,
    user_agent TEXT,
    referer TEXT,
    viewed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id)
);

執行資料庫遷移：


# 本地開發環境

wrangler d1 execute blog-database --local --file=./schema.sql

# 生產環境

wrangler d1 execute blog-database --remote --file=./schema.sql

資料庫設計最佳實踐

1. 正規化設計原則

適度正規化

D1 基於 SQLite，適合 2NF-3NF 的設計：


-- ✅ 好的設計：分離關注點
CREATE TABLE articles (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    category_id TEXT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- ❌ 避免過度正規化
CREATE TABLE article_title_words (
    article_id TEXT,
    word TEXT,
    position INTEGER
);

JSON 欄位的合理使用

對於不需要查詢的複雜資料，使用 JSON：


-- ✅ 適合用 JSON 的場景
CREATE TABLE articles (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT DEFAULT '[]',           -- 簡單列表
    metadata TEXT DEFAULT '{}',       -- 不常查詢的設定
    social_sharing TEXT DEFAULT '{}'  -- 社群分享設定
);

-- ✅ 查詢 JSON 資料
SELECT * FROM articles 
WHERE JSON_EXTRACT(metadata, '$.featured') = 1;

2. 索引策略

基本索引原則


-- 單欄位索引
CREATE INDEX idx_articles_status ON articles(status);

-- 複合索引（注意順序）
CREATE INDEX idx_articles_status_created ON articles(status, created_at DESC);

-- 部分索引（節省空間）
CREATE INDEX idx_articles_published ON articles(created_at DESC) 
WHERE status = 'published';

效能分析

使用 EXPLAIN QUERY PLAN 分析查詢效能：


EXPLAIN QUERY PLAN
SELECT * FROM articles 
WHERE status = 'published' 
ORDER BY created_at DESC 
LIMIT 10;

3. 資料類型選擇

SQLite 資料類型映射


-- ✅ 推薦的資料類型
CREATE TABLE examples (
    id TEXT PRIMARY KEY,              -- 字串 ID
    count INTEGER,                    -- 整數
    price REAL,                      -- 浮點數
    is_active INTEGER,               -- 布林值 (0/1)
    created_at TEXT,                 -- ISO 8601 時間戳
    metadata TEXT,                   -- JSON 資料
    content BLOB                     -- 二進位資料
);

SQL 操作與優化

1. 基本 CRUD 操作

創建 (Create)


-- 插入單筆記錄
INSERT INTO articles (id, title, slug, content, category, status) 
VALUES ('article-1', '我的第一篇文章', 'my-first-article', '文章內容...', 'tech', 'published');

-- 批次插入
INSERT INTO articles (id, title, slug, content, status) VALUES
    ('article-2', '第二篇文章', 'second-article', '內容...', 'published'),
    ('article-3', '第三篇文章', 'third-article', '內容...', 'draft');

-- 使用 ON CONFLICT 處理重複
INSERT INTO articles (id, title, content) 
VALUES ('article-1', '更新的標題', '新內容')
ON CONFLICT(id) DO UPDATE SET 
    title = excluded.title,
    content = excluded.content,
    updated_at = CURRENT_TIMESTAMP;

查詢 (Read)


-- 基本查詢
SELECT id, title, category, created_at 
FROM articles 
WHERE status = 'published'
ORDER BY created_at DESC;

-- 分頁查詢
SELECT id, title, excerpt, created_at
FROM articles 
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

-- 複雜查詢與 JOIN
SELECT a.title, a.view_count, c.name as category_name
FROM articles a
LEFT JOIN categories c ON a.category = c.slug
WHERE a.status = 'published' 
  AND a.created_at > date('now', '-30 days')
ORDER BY a.view_count DESC
LIMIT 5;

-- 全文搜尋（模擬）
SELECT * FROM articles 
WHERE (title LIKE '%關鍵字%' OR content LIKE '%關鍵字%')
  AND status = 'published';

更新 (Update)


-- 單筆更新
UPDATE articles 
SET view_count = view_count + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 'article-1';

-- 批次更新
UPDATE articles 
SET category = 'technology'
WHERE category = 'tech';

-- 條件更新
UPDATE articles 
SET featured = 1
WHERE view_count > 1000 AND status = 'published';

刪除 (Delete)


-- 軟刪除（推薦）
UPDATE articles 
SET status = 'archived',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 'article-1';

-- 硬刪除
DELETE FROM articles 
WHERE status = 'archived' 
  AND updated_at < date('now', '-1 year');

2. 進階查詢技巧

統計查詢


-- 文章統計
SELECT 
    category,
    COUNT(*) as article_count,
    AVG(view_count) as avg_views,
    MAX(view_count) as max_views
FROM articles 
WHERE status = 'published'
GROUP BY category
ORDER BY article_count DESC;

-- 時間序列統計
SELECT 
    DATE(created_at) as publish_date,
    COUNT(*) as daily_articles
FROM articles 
WHERE created_at >= date('now', '-30 days')
GROUP BY DATE(created_at)
ORDER BY publish_date;

窗口函數


-- 排名查詢
SELECT 
    title,
    view_count,
    ROW_NUMBER() OVER (ORDER BY view_count DESC) as rank,
    PERCENT_RANK() OVER (ORDER BY view_count DESC) as percentile
FROM articles 
WHERE status = 'published';

-- 每個分類的熱門文章
SELECT *
FROM (
    SELECT 
        title, category, view_count,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY view_count DESC) as rank
    FROM articles 
    WHERE status = 'published'
) ranked
WHERE rank <= 3;

3. 效能優化查詢

查詢優化技巧


-- ✅ 使用 LIMIT 限制結果
SELECT * FROM articles 
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

-- ✅ 選擇必要欄位
SELECT id, title, excerpt FROM articles;  -- 而非 SELECT *

-- ✅ 使用索引欄位進行 WHERE 條件
SELECT * FROM articles WHERE status = 'published';  -- status 有索引

-- ❌ 避免在 WHERE 中使用函數
SELECT * FROM articles WHERE UPPER(title) = 'HELLO';  -- 無法使用索引

-- ✅ 改為
SELECT * FROM articles WHERE title = 'hello';

整合 Pages Functions

1. 基本 API 結構

創建 functions/api/articles.js：


/**
 * Articles API - 處理文章相關的 CRUD 操作
 */
export async function onRequest(context) {
    const { request, env, params } = context;

    // CORS 設定
    const corsHeaders = {
        'Access-Control-Allow-Origin': '*',
        'Access-Control-Allow-Methods': 'GET, POST, PUT, DELETE, OPTIONS',
        'Access-Control-Allow-Headers': 'Content-Type, Authorization',
    };

    if (request.method === 'OPTIONS') {
        return new Response(null, { headers: corsHeaders });
    }

    // 檢查資料庫連線
    if (!env.DB) {
        return jsonError('Database not available', 500, corsHeaders);
    }

    try {
        switch (request.method) {
            case 'GET':
                return await handleGetArticles(env.DB, request, corsHeaders);
            case 'POST':
                return await handleCreateArticle(env.DB, request, corsHeaders);
            default:
                return jsonError('Method not allowed', 405, corsHeaders);
        }
    } catch (error) {
        console.error('API Error:', error);
        return jsonError('Internal server error', 500, corsHeaders);
    }
}

/**
 * 獲取文章列表
 */
async function handleGetArticles(db, request, corsHeaders) {
    const url = new URL(request.url);
    const searchParams = url.searchParams;

    // 查詢參數
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = Math.min(parseInt(searchParams.get('limit')) || 10, 50);
    const category = searchParams.get('category');
    const status = searchParams.get('status') || 'published';
    const search = searchParams.get('search');
    const sortBy = searchParams.get('sortBy') || 'created_at';
    const sortOrder = searchParams.get('sortOrder') || 'DESC';

    const offset = (page - 1) * limit;

    // 建構查詢條件
    let whereConditions = ['status = ?'];
    let params = [status];

    if (category && category !== 'all') {
        whereConditions.push('category = ?');
        params.push(category);
    }

    if (search) {
        whereConditions.push('(title LIKE ? OR content LIKE ?)');
        const searchTerm = `%${search}%`;
        params.push(searchTerm, searchTerm);
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 驗證排序欄位
    const allowedSortColumns = ['created_at', 'updated_at', 'view_count', 'title'];
    const sortColumn = allowedSortColumns.includes(sortBy) ? sortBy : 'created_at';
    const sortDirection = sortOrder.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    try {
        // 取得總數
        const countQuery = `SELECT COUNT(*) as total FROM articles ${whereClause}`;
        const countResult = await db.prepare(countQuery).bind(...params).first();
        const total = countResult.total;

        // 取得文章列表
        const articlesQuery = `
            SELECT 
                id, title, slug, excerpt, category, tags, 
                featured_image, view_count, like_count, featured, 
                status, created_at, updated_at
            FROM articles 
            ${whereClause}
            ORDER BY ${sortColumn} ${sortDirection}
            LIMIT ? OFFSET ?
        `;

        const articlesResult = await db.prepare(articlesQuery)
            .bind(...params, limit, offset)
            .all();

        // 處理 JSON 欄位
        const processedArticles = articlesResult.results.map(article => ({
            ...article,
            tags: JSON.parse(article.tags || '[]'),
            featured: Boolean(article.featured)
        }));

        return new Response(JSON.stringify({
            success: true,
            articles: processedArticles,
            pagination: {
                page,
                limit,
                total,
                totalPages: Math.ceil(total / limit),
                hasNext: page * limit < total,
                hasPrev: page > 1
            }
        }), {
            headers: { 
                'Content-Type': 'application/json',
                ...corsHeaders 
            }
        });

    } catch (error) {
        console.error('Database query error:', error);
        return jsonError('Query failed', 500, corsHeaders);
    }
}

/**
 * 創建新文章
 */
async function handleCreateArticle(db, request, corsHeaders) {
    try {
        const articleData = await request.json();

        // 資料驗證
        const validation = validateArticleData(articleData);
        if (!validation.isValid) {
            return jsonError(validation.errors, 400, corsHeaders);
        }

        // 生成 ID 和 slug
        const articleId = articleData.id || generateId();
        const slug = articleData.slug || generateSlug(articleData.title);

        // 準備插入資料
        const insertData = {
            id: articleId,
            title: articleData.title,
            slug: slug,
            content: articleData.content,
            excerpt: articleData.excerpt || generateExcerpt(articleData.content),
            category: articleData.category || 'uncategorized',
            tags: JSON.stringify(articleData.tags || []),
            featured_image: articleData.featured_image || null,
            status: articleData.status || 'draft',
            seo_title: articleData.seo_title || articleData.title,
            seo_description: articleData.seo_description || null
        };

        const insertQuery = `
            INSERT INTO articles (
                id, title, slug, content, excerpt, category, tags,
                featured_image, status, seo_title, seo_description
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;

        await db.prepare(insertQuery).bind(
            insertData.id,
            insertData.title,
            insertData.slug,
            insertData.content,
            insertData.excerpt,
            insertData.category,
            insertData.tags,
            insertData.featured_image,
            insertData.status,
            insertData.seo_title,
            insertData.seo_description
        ).run();

        return new Response(JSON.stringify({
            success: true,
            message: 'Article created successfully',
            article: {
                id: articleId,
                slug: slug
            }
        }), {
            status: 201,
            headers: { 
                'Content-Type': 'application/json',
                ...corsHeaders 
            }
        });

    } catch (error) {
        console.error('Create article error:', error);
        return jsonError('Failed to create article', 500, corsHeaders);
    }
}

// 輔助函數
function jsonError(message, status, corsHeaders) {
    return new Response(JSON.stringify({
        success: false,
        error: message
    }), {
        status,
        headers: { 
            'Content-Type': 'application/json',
            ...corsHeaders 
        }
    });
}

function validateArticleData(data) {
    const errors = [];

    if (!data.title || data.title.trim().length === 0) {
        errors.push('Title is required');
    }

    if (!data.content || data.content.trim().length === 0) {
        errors.push('Content is required');
    }

    return {
        isValid: errors.length === 0,
        errors: errors
    };
}

function generateId() {
    return Date.now().toString() + Math.random().toString(36).substr(2, 9);
}

function generateSlug(title) {
    return title
        .toLowerCase()
        .replace(/[^a-z0-9\s-]/g, '')
        .replace(/\s+/g, '-')
        .trim();
}

function generateExcerpt(content, length = 150) {
    return content
        .replace(/[#*`\[\]]/g, '')  // 移除 Markdown 符號
        .substring(0, length)
        .trim() + '...';
}

2. 個別文章 API

創建 functions/api/articles/[id].js：


/**
 * Individual Article API
 * 路徑: /api/articles/[id]
 */
export async function onRequest(context) {
    const { request, env, params } = context;
    const articleId = params.id;

    const corsHeaders = {
        'Access-Control-Allow-Origin': '*',
        'Access-Control-Allow-Methods': 'GET, PUT, DELETE, OPTIONS',
        'Access-Control-Allow-Headers': 'Content-Type, Authorization',
    };

    if (request.method === 'OPTIONS') {
        return new Response(null, { headers: corsHeaders });
    }

    if (!env.DB) {
        return jsonError('Database not available', 500, corsHeaders);
    }

    try {
        switch (request.method) {
            case 'GET':
                return await getArticle(env.DB, articleId, corsHeaders);
            case 'PUT':
                return await updateArticle(env.DB, articleId, request, corsHeaders);
            case 'DELETE':
                return await deleteArticle(env.DB, articleId, corsHeaders);
            default:
                return jsonError('Method not allowed', 405, corsHeaders);
        }
    } catch (error) {
        console.error('API Error:', error);
        return jsonError('Internal server error', 500, corsHeaders);
    }
}

async function getArticle(db, articleId, corsHeaders) {
    try {
        const query = `
            SELECT * FROM articles 
            WHERE (id = ? OR slug = ?) AND status = 'published'
        `;

        const article = await db.prepare(query).bind(articleId, articleId).first();

        if (!article) {
            return jsonError('Article not found', 404, corsHeaders);
        }

        // 處理 JSON 欄位
        const processedArticle = {
            ...article,
            tags: JSON.parse(article.tags || '[]')
        };

        return new Response(JSON.stringify({
            success: true,
            article: processedArticle
        }), {
            headers: { 
                'Content-Type': 'application/json',
                ...corsHeaders 
            }
        });

    } catch (error) {
        console.error('Get article error:', error);
        return jsonError('Failed to retrieve article', 500, corsHeaders);
    }
}

// 其他函數實現...

效能優化策略

1. 查詢優化

使用 Prepared Statements


// ✅ 好的做法 - 使用 prepared statements
const stmt = db.prepare('SELECT * FROM articles WHERE category = ? LIMIT ?');
const results = await stmt.bind(category, limit).all();

// ❌ 避免字串拼接（SQL 注入風險）
const query = `SELECT * FROM articles WHERE category = '${category}'`;

批次操作


// 批次插入優化
async function batchInsertArticles(db, articles) {
    const stmt = db.prepare(`
        INSERT INTO articles (id, title, content, created_at) 
        VALUES (?, ?, ?, ?)
    `);

    const batch = articles.map(article => 
        stmt.bind(article.id, article.title, article.content, new Date().toISOString())
    );

    return await db.batch(batch);
}

2. 快取策略

HTTP 快取標頭


// 在 API 回應中設定快取
export async function onRequest(context) {
    // ... 處理邏輯

    return new Response(JSON.stringify(data), {
        headers: {
            'Content-Type': 'application/json',
            'Cache-Control': 'public, max-age=300, s-maxage=600', // 5分鐘瀏覽器快取，10分鐘 CDN 快取
            'ETag': generateETag(data),
            ...corsHeaders
        }
    });
}

條件請求處理


function handleConditionalRequest(request, lastModified, etag) {
    const ifModifiedSince = request.headers.get('If-Modified-Since');
    const ifNoneMatch = request.headers.get('If-None-Match');

    if (ifNoneMatch === etag || 
        (ifModifiedSince && new Date(ifModifiedSince) >= lastModified)) {
        return new Response(null, { 
            status: 304,
            headers: {
                'Cache-Control': 'public, max-age=300',
                'ETag': etag,
                'Last-Modified': lastModified.toUTCString()
            }
        });
    }

    return null; // 繼續處理正常請求
}

3. 資料庫連線優化

連線池管理


// D1 自動管理連線，但要注意併發限制
class DatabaseManager {
    constructor(db) {
        this.db = db;
        this.activeConnections = 0;
        this.maxConnections = 10; // 根據需求調整
    }

    async query(sql, params = []) {
        if (this.activeConnections >= this.maxConnections) {
            throw new Error('Too many concurrent database connections');
        }

        this.activeConnections++;

        try {
            const stmt = this.db.prepare(sql);
            return await stmt.bind(...params).all();
        } finally {
            this.activeConnections--;
        }
    }
}

監控與除錯

1. 錯誤處理

結構化錯誤日誌


class DatabaseError extends Error {
    constructor(message, query, params, originalError) {
        super(message);
        this.name = 'DatabaseError';
        this.query = query;
        this.params = params;
        this.originalError = originalError;
        this.timestamp = new Date().toISOString();
    }
}

async function safeQuery(db, query, params = []) {
    try {
        return await db.prepare(query).bind(...params).all();
    } catch (error) {
        const dbError = new DatabaseError(
            'Database query failed',
            query,
            params,
            error
        );

        console.error('Database Error:', {
            message: dbError.message,
            query: dbError.query,
            params: dbError.params,
            error: dbError.originalError.message,
            timestamp: dbError.timestamp
        });

        throw dbError;
    }
}

2. 效能監控

查詢時間記錄


async function monitoredQuery(db, query, params = []) {
    const startTime = Date.now();

    try {
        const result = await db.prepare(query).bind(...params).all();
        const duration = Date.now() - startTime;

        // 記錄慢查詢
        if (duration > 1000) { // 超過 1 秒
            console.warn('Slow Query Detected:', {
                query,
                params,
                duration: `${duration}ms`,
                timestamp: new Date().toISOString()
            });
        }

        return result;
    } catch (error) {
        const duration = Date.now() - startTime;
        console.error('Query Failed:', {
            query,
            params,
            duration: `${duration}ms`,
            error: error.message
        });
        throw error;
    }
}

3. 健康檢查

資料庫健康檢查端點


// functions/api/health.js
export async function onRequest({ env }) {
    const healthCheck = {
        timestamp: new Date().toISOString(),
        status: 'healthy',
        services: {}
    };

    // 檢查資料庫連線
    try {
        const result = await env.DB.prepare('SELECT 1 as test').first();
        healthCheck.services.database = {
            status: result.test === 1 ? 'healthy' : 'unhealthy',
            responseTime: Date.now()
        };
    } catch (error) {
        healthCheck.services.database = {
            status: 'unhealthy',
            error: error.message
        };
        healthCheck.status = 'degraded';
    }

    const status = healthCheck.status === 'healthy' ? 200 : 503;

    return new Response(JSON.stringify(healthCheck), {
        status,
        headers: {
            'Content-Type': 'application/json',
            'Cache-Control': 'no-cache'
        }
    });
}

生產環境部署

1. 環境設定

多環境配置


# wrangler.toml

name = "blog-system"
compatibility_date = "2023-12-01"

[env.development]
[[env.development.d1_databases]]
binding = "DB"
database_name = "blog-database-dev"
database_id = "dev-database-id"

[env.production]
[[env.production.d1_databases]]
binding = "DB"
database_name = "blog-database-prod"
database_id = "prod-database-id"

部署腳本


{
  "scripts": {
    "dev": "wrangler pages dev . --d1=DB=blog-database-dev",
    "deploy:dev": "wrangler pages publish . --env development",
    "deploy:prod": "wrangler pages publish . --env production",
    "db:migrate:dev": "wrangler d1 execute blog-database-dev --local --file=./migrations/schema.sql",
    "db:migrate:prod": "wrangler d1 execute blog-database-prod --remote --file=./migrations/schema.sql"
  }
}

2. 資料庫遷移

版本化遷移系統


// migrations/001_initial_schema.sql
-- Migration: 001_initial_schema
-- Description: Create initial tables
-- Date: 2025-09-25

CREATE TABLE IF NOT EXISTS articles (
    -- table definition
);

-- migrations/002_add_author_table.sql
-- Migration: 002_add_author_table
-- Description: Add authors table and foreign key
-- Date: 2025-09-26

CREATE TABLE IF NOT EXISTS authors (
    -- table definition
);

ALTER TABLE articles ADD COLUMN author_id TEXT;

遷移執行腳本


// scripts/migrate.js
const migrations = [
    '001_initial_schema.sql',
    '002_add_author_table.sql',
    // 添加新的遷移檔案
];

async function runMigrations(env) {
    for (const migration of migrations) {
        console.log(`Running migration: ${migration}`);

        try {
            const sql = await readFile(`./migrations/${migration}`, 'utf8');
            await env.DB.exec(sql);
            console.log(`✅ Migration ${migration} completed`);
        } catch (error) {
            console.error(`❌ Migration ${migration} failed:`, error);
            throw error;
        }
    }
}

3. 備份與恢復

自動備份策略


// functions/cron/backup.js
export async function scheduled(event, env, ctx) {
    // 每日備份
    if (event.cron === '0 2 * * *') { // 每天凌晨 2 點
        await performDailyBackup(env.DB);
    }

    // 每週備份
    if (event.cron === '0 3 * * 0') { // 每週日凌晨 3 點
        await performWeeklyBackup(env.DB);
    }
}

async function performDailyBackup(db) {
    try {
        // 導出所有資料
        const tables = ['articles', 'authors', 'categories', 'tags'];
        const backupData = {};

        for (const table of tables) {
            const data = await db.prepare(`SELECT * FROM ${table}`).all();
            backupData[table] = data.results;
        }

        // 儲存到 R2 或其他儲存服務
        const backupJson = JSON.stringify(backupData, null, 2);
        const timestamp = new Date().toISOString().split('T')[0];

        // 這裡需要整合 R2 或其他儲存服務
        console.log(`Backup created for ${timestamp}`);

    } catch (error) {
        console.error('Backup failed:', error);
    }
}