Node.js 内置 SQLite 数据库完整指南

┌─────────────────────────────────────────────────────────┐
│                    Node.js 应用                        │
├─────────────────────────────────────────────────────────┤
│  ┌─────────────────┐  ┌─────────────────┐              │
│  │   业务逻辑层     │  │    API 层       │              │
│  │   (Services)    │  │  (Controllers)  │              │
│  └────────┬────────┘  └────────┬────────┘              │
│           │                     │                        │
│  ┌────────▼─────────────────────▼────────┐             │
│  │        数据访问层 (DAL)                │             │
│  │  ┌─────────────────────────────────┐  │             │
│  │  │     SQLite 数据库操作封装          │  │             │
│  │  │  (Connection, Models, Queries)   │  │             │
│  │  └────────┬──────────────────────┬────┘  │             │
│  └───────────┼──────────────────────┼───────┘             │
│             │                      │                      │
│  ┌──────────▼──────────┐  ┌─────▼──────┐               │
│  │  Node.js SQLite API   │  │  第三方包   │               │
│  │  (实验性，需启用)    │  │ (推荐方案)  │               │
│  └──────────┬──────────┘  └─────┬────────┘               │
└─────────────┼───────────────────┼──────────────────────────┘
              │                   │
              │                   │
┌─────────────▼───────────────────▼─────────────────────────┐
│              SQLite 数据库引擎 (C 语言实现)                │
├─────────────────────────────────────────────────────────┤
│  ┌─────────────┐  ┌──────────────┐  ┌──────────────┐   │
│  │  SQL 解析器  │  │  查询优化器   │  │  存储引擎     │   │
│  └─────────────┘  └──────────────┘  └──────────────┘   │
└─────────────────────────────────────────────────────────┘
                            │
┌───────────────────────────▼─────────────────────────────┐
│                   数据库文件 (*.db)                      │
└─────────────────────────────────────────────────────────┘

工具链选择建议：

新手入门 → sqlite 包 (Promise API)
           ↓
生产环境 → sqlite 包 + 连接池
           ↓
性能优化 → 索引优化 + WAL 模式
           ↓
企业应用 → 考虑 MySQL/PostgreSQL

# 1. 创建项目目录
mkdir sqlite-demo && cd sqlite-demo

# 2. 初始化项目
npm init -y

# 3. 安装 SQLite 包（推荐 Promise 版本）
npm install sqlite

# 4. 创建示例文件
cat > app.js << 'EOF'
import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

async function main() {
  // 打开数据库连接
  const db = await open({
    filename: 'demo.db',
    driver: sqlite3.Database
  })

  // 创建表
  await db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT UNIQUE
    )
  `)

  // 插入数据
  await db.run('INSERT INTO users (name, email) VALUES (?, ?)',
              ['张三', 'zhangsan@example.com'])

  // 查询数据
  const users = await db.all('SELECT * FROM users')
  console.log('用户列表:', users)

  // 关闭连接
  await db.close()
}

main().catch(console.error)
EOF

# 5. 运行示例
node app.js

用户列表: [ { id: 1, name: '张三', email: 'zhangsan@example.com' } ]

# 检查 Node.js 版本
node --version

# 建议使用 Node.js 22.12.0 LTS 或更高版本
# 如果版本过低，请升级到最新 LTS 版本

# 使用 nvm 升级 Node.js（推荐）
nvm install 22.12.0
nvm use 22.12.0
nvm alias default 22.12.0

# 运行启用 SQLite 的脚本
node --experimental-sqlite your-script.js

# 或者在 package.json 中配置
{
  "scripts": {
    "start": "node --experimental-sqlite src/index.js",
    "dev": "node --experimental-sqlite --watch src/index.js"
  }
}

# TypeScript 项目配置
{
  "scripts": {
    "build": "tsc",
    "start": "node --experimental-sqlite dist/index.js",
    "dev": "tsx --experimental-sqlite src/index.ts"
  }
}

# 安装 sqlite3（基础驱动）
npm install sqlite3

# 安装 sqlite（Promise 封装版本，推荐）
npm install sqlite

# 或者同时安装两者
npm install sqlite3 sqlite

# 开发环境依赖
npm install --save-dev @types/sqlite3  # TypeScript 类型支持

# 创建项目目录
mkdir sqlite-project
cd sqlite-project

# 初始化 npm 项目
npm init -y

# 创建源代码目录
mkdir src
touch src/index.js

// src/config/database.js
import path from "path"
import { fileURLToPath } from "url"

const __filename = fileURLToPath(import.meta.url)
const __dirname = path.dirname(__filename)

export const databaseConfig = {
  // 开发环境数据库文件
  development: path.join(__dirname, "../data/dev.db"),

  // 测试环境数据库文件
  test: path.join(__dirname, "../data/test.db"),

  // 生产环境数据库文件
  production: path.join(__dirname, "../data/prod.db"),

  // 内存数据库（用于单元测试）
  memory: ":memory:"
}

export const getDatabasePath = (env = process.env.NODE_ENV || "development") => {
  return databaseConfig[env] || databaseConfig.development
}

# .env 文件
NODE_ENV=development
DATABASE_URL=./data/app.db
DATABASE_TIMEOUT=5000
DATABASE_BUSY_TIMEOUT=3000

// src/config/env.js
import dotenv from "dotenv"
import path from "path"

// 加载环境变量
dotenv.config({ path: path.join(process.cwd(), ".env") })

export const env = {
  NODE_ENV: process.env.NODE_ENV || "development",
  DATABASE_URL: process.env.DATABASE_URL || "./data/app.db",
  DATABASE_TIMEOUT: parseInt(process.env.DATABASE_TIMEOUT) || 5000,
  DATABASE_BUSY_TIMEOUT: parseInt(process.env.DATABASE_BUSY_TIMEOUT) || 3000
}

// src/utils/database.js
import sqlite3 from "sqlite3"
import { open } from "sqlite"
import { env } from "../config/env.js"

let dbInstance = null

export async function getDatabase() {
  if (dbInstance) {
    return dbInstance
  }

  dbInstance = await open({
    filename: env.DATABASE_URL,
    driver: sqlite3.Database,
    mode: sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE
  })

  // 配置数据库参数
  await dbInstance.exec(`
    PRAGMA journal_mode = WAL;
    PRAGMA synchronous = NORMAL;
    PRAGMA cache_size = -64000;
    PRAGMA temp_store = MEMORY;
    PRAGMA mmap_size = 30000000;
    PRAGMA busy_timeout = ${env.DATABASE_BUSY_TIMEOUT};
  `)

  return dbInstance
}

export async function closeDatabase() {
  if (dbInstance) {
    await dbInstance.close()
    dbInstance = null
  }
}

// 优雅关闭
process.on("SIGINT", async () => {
  console.log("正在关闭数据库连接...")
  await closeDatabase()
  process.exit(0)
})

process.on("SIGTERM", async () => {
  console.log("正在关闭数据库连接...")
  await closeDatabase()
  process.exit(0)
})

# macOS (使用 Homebrew)
brew install --cask db-browser-for-sqlite

# Windows (使用 Chocolatey)
choco install sqlitebrowser

# Ubuntu/Debian
sudo apt install sqlitebrowser

# 下载地址
# https://sqlitestudio.pl/

# SQLite Viewer
# SQLite Explorer
# SQLTools + SQLTools SQLite Driver

// 导入内置 SQLite 模块
import { DatabaseSync } from "node:sqlite"

// 创建数据库连接（文件存储）
const database = new DatabaseSync("data.db")

// 创建表结构
database.exec(`
  CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  ) STRICT
`)

// 插入数据
const insertStmt = database.prepare(
  "INSERT INTO students (name, age, email) VALUES (?, ?, ?)"
)
insertStmt.run("张三", 20, "zhangsan@example.com")
insertStmt.run("李四", 21, "lisi@example.com")
insertStmt.run("王五", 22, "wangwu@example.com")

// 查询数据
const queryStmt = database.prepare("SELECT * FROM students ORDER BY id")
const results = queryStmt.all()
console.log("查询结果:", results)

// 关闭数据库连接
database.close()

import { DatabaseSync } from "node:sqlite"

const db = new DatabaseSync("data.db")

try {
  // 开始事务
  db.exec("BEGIN TRANSACTION")

  // 执行多个操作
  const stmt1 = db.prepare("INSERT INTO students (name, age) VALUES (?, ?)")
  stmt1.run("赵六", 23)

  const stmt2 = db.prepare("UPDATE students SET age = ? WHERE name = ?")
  stmt2.run(24, "张三")

  // 提交事务
  db.exec("COMMIT")
  console.log("事务提交成功")
} catch (error) {
  // 回滚事务
  db.exec("ROLLBACK")
  console.error("事务回滚:", error)
} finally {
  db.close()
}
### 内存数据库

内存数据库是一种特殊的数据库类型，数据只存在于程序运行期间，程序结束后数据会自动消失。这种数据库特别适合以下场景：

- **单元测试**：避免测试数据污染实际数据库
- **临时数据处理**：需要快速处理大量临时数据
- **原型开发**：快速验证数据结构和查询逻辑
- **缓存机制**：作为高速缓存存储热点数据

```javascript
import { DatabaseSync } from "node:sqlite"

// 创建内存数据库（数据只在程序运行期间存在）
const db = new DatabaseSync(":memory:")

// 内存数据库适合测试和临时数据处理
// 注意：程序退出后数据会完全消失

// 创建内存数据库示例
const db = new DatabaseSync(":memory:")

db.exec(`CREATE TABLE temp_data (
  id INTEGER PRIMARY KEY,
  value TEXT
)`)

// 使用内存数据库...
// 程序结束时自动释放

import sqlite3 from "sqlite3"
import { Database } from "sqlite3"

// 创建数据库连接
const db = new Database("data.db")

// 序列化执行（确保操作顺序）
db.serialize(() => {
  // 创建表
  db.run(`
    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      price REAL,
      stock INTEGER DEFAULT 0
    )
  `)

  // 插入数据
  const stmt = db.prepare(
    "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)"
  )
  stmt.run("iPhone 15", 7999.0, 100)
  stmt.run("MacBook Pro", 15999.0, 50)
  stmt.finalize()

  // 查询数据
  db.each("SELECT * FROM products", (err, row) => {
    if (err) {
      console.error("查询错误:", err)
      return
    }
    console.log(`产品: ${row.name}, 价格: ¥${row.price}, 库存: ${row.stock}`)
  })
})

// 关闭数据库
db.close()

import sqlite3 from "sqlite3"
import { open } from "sqlite"

async function main() {
  // 打开数据库连接
  const db = await open({
    filename: "data.db",
    driver: sqlite3.Database
  })

  try {
    // 创建表
    await db.exec(`
      CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        customer_name TEXT NOT NULL,
        total_amount REAL,
        status TEXT DEFAULT 'pending',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `)

    // 插入订单数据
    const insertStmt = await db.prepare(
      "INSERT INTO orders (customer_name, total_amount) VALUES (?, ?)"
    )
    await insertStmt.run("客户A", 2999.99)
    await insertStmt.run("客户B", 5999.99)
    await insertStmt.finalize()

    // 查询特定订单
    const specificOrder = await db.get(
      "SELECT * FROM orders WHERE customer_name = ?",
      ["客户A"]
    )
    console.log("特定订单:", specificOrder)

    // 查询所有订单
    const allOrders = await db.all("SELECT * FROM orders ORDER BY created_at DESC")
    console.log("所有订单:", allOrders)

    // 更新订单状态
    await db.run("UPDATE orders SET status = ? WHERE id = ?", ["completed", 1])

    // 统计订单数量和总金额
    const stats = await db.get(`
      SELECT
        COUNT(*) as total_orders,
        SUM(total_amount) as total_revenue,
        AVG(total_amount) as avg_amount
      FROM orders
    `)
    console.log("订单统计:", stats)
  } catch (error) {
    console.error("数据库操作错误:", error)
  } finally {
    await db.close()
  }
}

main()

// 连接选项
const db = await open({
  filename: "data.db", // 数据库文件路径
  driver: sqlite3.Database, // 驱动程序
  mode: sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, // 打开模式
  verbose: true // 启用详细日志
})

// 关闭连接
await db.close()

// 创建表
await db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    email TEXT UNIQUE,
    role TEXT DEFAULT 'user',
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`)

// 修改表结构
await db.exec("ALTER TABLE users ADD COLUMN phone TEXT")
await db.exec("CREATE INDEX idx_users_email ON users(email)")
await db.exec("CREATE INDEX idx_users_username ON users(username)")

// 删除表（谨慎使用）
// await db.exec('DROP TABLE IF EXISTS users');

// 插入数据
const insertUser = await db.prepare(
  "INSERT INTO users (username, password, email) VALUES (?, ?, ?)"
)
const result = await insertUser.run(
  "john_doe",
  "hashed_password",
  "john@example.com"
)
console.log("新用户ID:", result.lastID)
await insertUser.finalize()

// 批量插入
const insertMany = await db.prepare(
  "INSERT INTO users (username, email) VALUES (?, ?)"
)
const users = [
  ["alice", "alice@example.com"],
  ["bob", "bob@example.com"],
  ["charlie", "charlie@example.com"]
]

for (const user of users) {
  await insertMany.run(...user)
}
await insertMany.finalize()

// 更新数据
const updateUser = await db.prepare(
  "UPDATE users SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE username = ?"
)
await updateUser.run("new_email@example.com", "john_doe")
await updateUser.finalize()

// 删除数据
const deleteUser = await db.prepare("DELETE FROM users WHERE username = ?")
await deleteUser.run("john_doe")
await deleteUser.finalize()

// 查询单条记录
const user = await db.get("SELECT * FROM users WHERE username = ?", ["alice"])
console.log("用户信息:", user)

// 查询多条记录
const allUsers = await db.all(
  "SELECT * FROM users ORDER BY created_at DESC LIMIT 10"
)
console.log("用户列表:", allUsers)

// 条件查询
const activeUsers = await db.all(
  `
  SELECT username, email, created_at
  FROM users
  WHERE is_active = 1 AND role = ?
  ORDER BY username
`,
  ["user"]
)

// 聚合查询
const userStats = await db.get(`
  SELECT
    COUNT(*) as total_users,
    COUNT(CASE WHEN is_active = 1 THEN 1 END) as active_users,
    COUNT(CASE WHEN role = 'admin' THEN 1 END) as admin_users,
    MIN(created_at) as first_user,
    MAX(created_at) as latest_user
  FROM users
`)
console.log("用户统计:", userStats)

// 分页查询
const page = 1
const pageSize = 10
const offset = (page - 1) * pageSize
const paginatedUsers = await db.all(
  `
  SELECT * FROM users
  ORDER BY created_at DESC
  LIMIT ? OFFSET ?
`,
  [pageSize, offset]
)

async function transferMoney(fromUser, toUser, amount) {
  const db = await open({
    filename: "bank.db",
    driver: sqlite3.Database
  })

  try {
    // 开始事务
    await db.exec("BEGIN TRANSACTION")

    // 检查余额
    const fromBalance = await db.get(
      "SELECT balance FROM accounts WHERE user_id = ?",
      [fromUser]
    )
    if (fromBalance.balance < amount) {
      throw new Error("余额不足")
    }

    // 扣款
    await db.run("UPDATE accounts SET balance = balance - ? WHERE user_id = ?", [
      amount,
      fromUser
    ])

    // 收款
    await db.run("UPDATE accounts SET balance = balance + ? WHERE user_id = ?", [
      amount,
      toUser
    ])

    // 记录交易
    await db.run(
      `
      INSERT INTO transactions (from_user, to_user, amount, type, created_at)
      VALUES (?, ?, ?, 'transfer', CURRENT_TIMESTAMP)
    `,
      [fromUser, toUser, amount]
    )

    // 提交事务
    await db.exec("COMMIT")
    console.log("转账成功")
  } catch (error) {
    // 回滚事务
    await db.exec("ROLLBACK")
    console.error("转账失败:", error)
    throw error
  } finally {
    await db.close()
  }
}

// 1. 使用外键约束
await db.exec(`
  CREATE TABLE IF NOT EXISTS departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`)

await db.exec(`
  CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary REAL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
  )
`)

// 启用外键约束（重要！）
await db.exec("PRAGMA foreign_keys = ON")

// 2. 使用视图简化复杂查询
await db.exec(`
  CREATE VIEW IF NOT EXISTS employee_details AS
  SELECT
    e.id,
    e.name as employee_name,
    d.name as department_name,
    e.salary,
    e.created_at
  FROM employees e
  LEFT JOIN departments d ON e.department_id = d.id
`)

// 使用视图查询
const employeeDetails = await db.all(
  "SELECT * FROM employee_details WHERE department_name = ?",
  ["技术部"]
)

import winston from "winston"

// 配置日志
const logger = winston.createLogger({
  level: "info",
  format: winston.format.json(),
  transports: [
    new winston.transports.File({ filename: "database-error.log", level: "error" }),
    new winston.transports.File({ filename: "database-combined.log" })
  ]
})

class DatabaseManager {
  constructor(dbPath) {
    this.dbPath = dbPath
    this.db = null
  }

  async connect() {
    try {
      this.db = await open({
        filename: this.dbPath,
        driver: sqlite3.Database,
        verbose: process.env.NODE_ENV === "development" ? console.log : null
      })

      // 启用外键约束
      await this.db.exec("PRAGMA foreign_keys = ON")

      logger.info("数据库连接成功", { dbPath: this.dbPath })
      return this.db
    } catch (error) {
      logger.error("数据库连接失败", { error: error.message, dbPath: this.dbPath })
      throw error
    }
  }

  async executeWithRetry(sql, params = [], retries = 3) {
    for (let i = 0; i < retries; i++) {
      try {
        return await this.db.get(sql, params)
      } catch (error) {
        logger.warn(`数据库操作失败，重试 ${i + 1}/${retries}`, {
          error: error.message,
          sql,
          params
        })

        if (i === retries - 1) {
          logger.error("数据库操作最终失败", { error: error.message, sql, params })
          throw error
        }

        // 等待一段时间后重试
        await new Promise((resolve) => setTimeout(resolve, 100 * (i + 1)))
      }
    }
  }

  async close() {
    if (this.db) {
      await this.db.close()
      logger.info("数据库连接关闭")
    }
  }
}

class UserRepository {
  constructor(db) {
    this.db = db
  }

  // 数据验证
  validateUser(userData) {
    const errors = []

    if (!userData.username || userData.username.length < 3) {
      errors.push("用户名至少需要3个字符")
    }

    if (!userData.email || !this.isValidEmail(userData.email)) {
      errors.push("邮箱格式不正确")
    }

    if (userData.age && (userData.age < 0 || userData.age > 150)) {
      errors.push("年龄必须在0-150之间")
    }

    if (errors.length > 0) {
      throw new Error(`数据验证失败: ${errors.join(", ")}`)
    }
  }

  isValidEmail(email) {
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/
    return emailRegex.test(email)
  }

  // 清理输入数据
  sanitizeInput(data) {
    const sanitized = {}

    Object.keys(data).forEach((key) => {
      if (typeof data[key] === "string") {
        sanitized[key] = data[key].trim()
      } else {
        sanitized[key] = data[key]
      }
    })

    return sanitized
  }

  async createUser(userData) {
    // 清理和验证数据
    const sanitized = this.sanitizeInput(userData)
    this.validateUser(sanitized)

    // 检查用户名是否已存在
    const existingUser = await this.db.get(
      "SELECT id FROM users WHERE username = ?",
      [sanitized.username]
    )
    if (existingUser) {
      throw new Error("用户名已存在")
    }

    // 插入数据
    const result = await this.db.run(
      `
      INSERT INTO users (username, email, age, created_at)
      VALUES (?, ?, ?, CURRENT_TIMESTAMP)
    `,
      [sanitized.username, sanitized.email, sanitized.age]
    )

    return result.lastID
  }
}

// 1. 为经常查询的列创建索引
await db.exec("CREATE INDEX idx_users_email ON users(email)")
await db.exec("CREATE INDEX idx_users_username ON users(username)")

// 2. 复合索引
await db.exec("CREATE INDEX idx_orders_user_status ON orders(user_id, status)")

// 3. 部分索引（只为满足条件的行创建索引）
await db.exec(`
  CREATE INDEX idx_active_users ON users(created_at)
  WHERE is_active = 1
`)

// 4. 查看查询计划
const queryPlan = await db.all(
  "EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?",
  ["test@example.com"]
)
console.log("查询计划:", queryPlan)

// 低效的逐条插入
async function inefficientInsert(users) {
  for (const user of users) {
    await db.run("INSERT INTO users (username, email) VALUES (?, ?)", [
      user.username,
      user.email
    ])
  }
}

// 高效的批量插入
async function efficientInsert(users) {
  const db = await open({ filename: "data.db", driver: sqlite3.Database })

  try {
    await db.exec("BEGIN TRANSACTION")

    const stmt = await db.prepare(
      "INSERT INTO users (username, email) VALUES (?, ?)"
    )

    for (const user of users) {
      await stmt.run(user.username, user.email)
    }

    await stmt.finalize()
    await db.exec("COMMIT")
  } catch (error) {
    await db.exec("ROLLBACK")
    throw error
  } finally {
    await db.close()
  }
}

// 使用 UNION ALL 进行批量插入（适用于大量数据）
async function bulkInsertWithUnion(users) {
  if (users.length === 0) return

  const values = users
    .map(
      (user, index) =>
        `SELECT $username${index} as username, $email${index} as email`
    )
    .join(" UNION ALL ")

  const sql = `INSERT INTO users (username, email) ${values}`

  const params = {}
  users.forEach((user, index) => {
    params[`$username${index}`] = user.username
    params[`$email${index}`] = user.email
  })

  await db.run(sql, params)
}

// 1. 使用 LIMIT 限制结果集大小
const recentUsers = await db.all(`
  SELECT username, email, created_at
  FROM users
  ORDER BY created_at DESC
  LIMIT 20
`)

// 2. 只查询需要的列，避免 SELECT *
const userEmails = await db.all("SELECT email FROM users WHERE is_active = 1")

// 3. 使用 JOIN 而不是多次查询
const userWithDepartment = await db.get(
  `
  SELECT
    u.username,
    u.email,
    d.name as department_name
  FROM users u
  LEFT JOIN departments d ON u.department_id = d.id
  WHERE u.id = ?
`,
  [userId]
)

// 4. 使用 EXISTS 而不是 IN（对于大表）
const hasActiveUsers = await db.get(
  `
  SELECT EXISTS(
    SELECT 1 FROM users
    WHERE department_id = ? AND is_active = 1
  ) as has_active
`,
  [departmentId]
)

查询慢？
├─ 是否使用了索引？
│  ├─ 是 → 检查索引是否生效（EXPLAIN QUERY PLAN）
│  └─ 否 → 为 WHERE/JOIN/ORDER BY 列创建索引
├─ 是否查询了不必要的数据？
│  ├─ 是 → 只查询需要的列，添加 LIMIT
│  └─ 否 → 继续下一步
├─ 是否可以优化查询结构？
│  ├─ 是 → 使用 EXISTS 替代 IN，优化 JOIN
│  └─ 否 → 考虑其他方案
└─ 是否可以缓存结果？
   ├─ 是 → 实施查询结果缓存
   └─ 否 → 考虑数据库升级

import sqlite3 from "sqlite3"

// 虽然 SQLite 本身不支持连接池，但可以通过以下方式优化
class SQLitePool {
  constructor(dbPath, maxConnections = 5) {
    this.dbPath = dbPath
    this.maxConnections = maxConnections
    this.connections = []
    this.available = []
  }

  async getConnection() {
    if (this.available.length > 0) {
      return this.available.pop()
    }

    if (this.connections.length < this.maxConnections) {
      const db = await open({
        filename: this.dbPath,
        driver: sqlite3.Database
      })

      this.connections.push(db)
      return db
    }

    // 等待可用连接
    return new Promise((resolve) => {
      const checkAvailable = () => {
        if (this.available.length > 0) {
          resolve(this.available.pop())
        } else {
          setTimeout(checkAvailable, 10)
        }
      }
      checkAvailable()
    })
  }

  releaseConnection(db) {
    this.available.push(db)
  }

  async closeAll() {
    await Promise.all(this.connections.map((db) => db.close()))
    this.connections = []
    this.available = []
  }
}

// 设置超时时间
await db.exec("PRAGMA busy_timeout = 5000") // 5秒超时

// 使用 WAL 模式（Write-Ahead Logging）
await db.exec("PRAGMA journal_mode = WAL")

// 避免长事务
async function shortTransaction() {
  await db.exec("BEGIN IMMEDIATE") // 立即开始事务
  try {
    // 快速执行操作
    await db.run("UPDATE accounts SET balance = balance + 100 WHERE id = 1")
    await db.exec("COMMIT")
  } catch (error) {
    await db.exec("ROLLBACK")
    throw error
  }
}

// 定期备份内存数据库到文件
async function backupMemoryDB() {
  const memoryDB = new DatabaseSync(":memory:")
  const backupDB = new DatabaseSync("backup.db")

  // 执行备份
  memoryDB.exec(`
    ATTACH DATABASE 'backup.db' AS backup;
    CREATE TABLE backup.users AS SELECT * FROM main.users;
    DETACH DATABASE backup;
  `)
}

// 或者使用临时文件数据库
const tempDB = new DatabaseSync("temp.db")
// 程序退出时删除临时文件
process.on("exit", () => {
  fs.unlinkSync("temp.db")
})

// 使用 STRICT 表模式
await db.exec(`
  CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK(price >= 0),
    stock INTEGER CHECK(stock >= 0),
    is_available BOOLEAN
  ) STRICT
`)

// 数据类型转换
function sanitizeProductData(product) {
  return {
    name: String(product.name).trim(),
    price: parseFloat(product.price) || 0,
    stock: parseInt(product.stock) || 0,
    is_available: Boolean(product.is_available)
  }
}

// 性能诊断
async function diagnosePerformance() {
  // 检查数据库大小
  const dbSize = await db.get(
    "SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()"
  )
  console.log("数据库大小:", dbSize.size, "字节")

  // 检查索引使用情况
  const indexUsage = await db.all(`
    SELECT name, stat
    FROM sqlite_stat1
    ORDER BY stat DESC
  `)
  console.log("索引统计:", indexUsage)

  // 启用查询性能分析
  await db.exec("PRAGMA optimize")
}

// 优化配置
await db.exec("PRAGMA cache_size = 10000") // 增加缓存大小
await db.exec("PRAGMA temp_store = MEMORY") // 使用内存临时存储
await db.exec("PRAGMA mmap_size = 30000000") // 启用内存映射

// 使用文件锁
import fs from "fs"
import path from "path"

class DatabaseLock {
  constructor(dbPath) {
    this.dbPath = dbPath
    this.lockPath = `${dbPath}.lock`
  }

  async acquireLock(timeout = 5000) {
    const startTime = Date.now()

    while (Date.now() - startTime < timeout) {
      try {
        // 尝试创建锁文件
        fs.writeFileSync(this.lockPath, process.pid.toString(), { flag: "wx" })
        return true
      } catch (error) {
        // 如果锁文件已存在，等待一段时间后重试
        await new Promise((resolve) => setTimeout(resolve, 100))
      }
    }

    throw new Error("无法获取数据库锁")
  }

  releaseLock() {
    try {
      fs.unlinkSync(this.lockPath)
    } catch (error) {
      // 锁文件可能已被其他进程删除
    }
  }
}

// 使用示例
async function safeDatabaseOperation() {
  const lock = new DatabaseLock("data.db")

  try {
    await lock.acquireLock()

    // 执行数据库操作
    const db = await open({ filename: "data.db", driver: sqlite3.Database })
    await db.run("UPDATE users SET last_access = CURRENT_TIMESTAMP WHERE id = 1")
    await db.close()
  } finally {
    lock.releaseLock()
  }
}

import sqlite3 from "sqlite3"
import { open } from "sqlite"

class TodoApp {
  constructor(dbPath) {
    this.dbPath = dbPath
    this.db = null
  }

  async initialize() {
    this.db = await open({
      filename: this.dbPath,
      driver: sqlite3.Database
    })

    // 创建表结构
    await this.db.exec(`
      CREATE TABLE IF NOT EXISTS todos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        priority INTEGER DEFAULT 1,
        status TEXT DEFAULT 'pending',
        due_date DATE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `)

    // 创建索引
    await this.db.exec(
      "CREATE INDEX IF NOT EXISTS idx_todos_status ON todos(status)"
    )
    await this.db.exec(
      "CREATE INDEX IF NOT EXISTS idx_todos_priority ON todos(priority)"
    )
    await this.db.exec(
      "CREATE INDEX IF NOT EXISTS idx_todos_due_date ON todos(due_date)"
    )
  }

  async addTodo(title, description = "", priority = 1, dueDate = null) {
    const result = await this.db.run(
      `
      INSERT INTO todos (title, description, priority, due_date)
      VALUES (?, ?, ?, ?)
    `,
      [title, description, priority, dueDate]
    )

    return result.lastID
  }

  async getTodos(status = null, priority = null) {
    let sql = "SELECT * FROM todos WHERE 1=1"
    const params = []

    if (status) {
      sql += " AND status = ?"
      params.push(status)
    }

    if (priority) {
      sql += " AND priority = ?"
      params.push(priority)
    }

    sql += " ORDER BY priority DESC, due_date ASC, created_at DESC"

    return await this.db.all(sql, params)
  }

  async updateTodoStatus(id, status) {
    const result = await this.db.run(
      `
      UPDATE todos
      SET status = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `,
      [status, id]
    )

    return result.changes > 0
  }

  async deleteTodo(id) {
    const result = await this.db.run("DELETE FROM todos WHERE id = ?", [id])
    return result.changes > 0
  }

  async getOverdueTodos() {
    return await this.db.all(`
      SELECT * FROM todos
      WHERE status != 'completed'
      AND due_date < DATE('now')
      ORDER BY due_date ASC
    `)
  }

  async getStatistics() {
    return await this.db.get(`
      SELECT
        COUNT(*) as total,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
        COUNT(CASE WHEN status != 'completed' AND due_date < DATE('now') THEN 1 END) as overdue,
        AVG(priority) as avg_priority
      FROM todos
    `)
  }

  async close() {
    if (this.db) {
      await this.db.close()
    }
  }
}

// 使用示例
async function main() {
  const todoApp = new TodoApp("todos.db")
  await todoApp.initialize()

  try {
    // 添加待办事项
    const todo1 = await todoApp.addTodo(
      "学习 Node.js",
      "完成 SQLite 教程",
      2,
      "2024-12-31"
    )
    const todo2 = await todoApp.addTodo(
      "编写代码",
      "实现用户管理功能",
      1,
      "2024-12-15"
    )

    console.log("添加的待办事项ID:", todo1, todo2)

    // 获取所有待办事项
    const allTodos = await todoApp.getTodos()
    console.log("所有待办事项:", allTodos)

    // 获取高优先级待办事项
    const highPriorityTodos = await todoApp.getTodos(null, 1)
    console.log("高优先级待办事项:", highPriorityTodos)

    // 更新状态
    await todoApp.updateTodoStatus(todo1, "completed")

    // 获取统计信息
    const stats = await todoApp.getStatistics()
    console.log("统计信息:", stats)

    // 获取过期待办事项
    const overdueTodos = await todoApp.getOverdueTodos()
    console.log("过期待办事项:", overdueTodos)
  } finally {
    await todoApp.close()
  }
}

main().catch(console.error)

import sqlite3 from "sqlite3"
import { open } from "sqlite"

class BlogSystem {
  constructor(dbPath) {
    this.dbPath = dbPath
    this.db = null
  }

  async initialize() {
    this.db = await open({
      filename: this.dbPath,
      driver: sqlite3.Database
    })

    // 创建用户表
    await this.db.exec(`
      CREATE TABLE IF NOT EXISTS authors (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        bio TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `)

    // 创建文章表
    await this.db.exec(`
      CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        author_id INTEGER,
        status TEXT DEFAULT 'draft',
        views INTEGER DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
      )
    `)

    // 创建标签表
    await this.db.exec(`
      CREATE TABLE IF NOT EXISTS tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `)

    // 创建文章标签关联表
    await this.db.exec(`
      CREATE TABLE IF NOT EXISTS post_tags (
        post_id INTEGER,
        tag_id INTEGER,
        PRIMARY KEY (post_id, tag_id),
        FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
        FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
      )
    `)

    // 创建评论表
    await this.db.exec(`
      CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        post_id INTEGER,
        author_name TEXT NOT NULL,
        content TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
      )
    `)

    // 创建索引
    await this.db.exec(
      "CREATE INDEX IF NOT EXISTS idx_posts_author ON posts(author_id)"
    )
    await this.db.exec(
      "CREATE INDEX IF NOT EXISTS idx_posts_status ON posts(status)"
    )
    await this.db.exec(
      "CREATE INDEX IF NOT EXISTS idx_posts_created ON posts(created_at)"
    )
    await this.db.exec(
      "CREATE INDEX IF NOT EXISTS idx_comments_post ON comments(post_id)"
    )
  }

  async createAuthor(username, email, password, bio = "") {
    const result = await this.db.run(
      `
      INSERT INTO authors (username, email, password, bio)
      VALUES (?, ?, ?, ?)
    `,
      [username, email, password, bio]
    )

    return result.lastID
  }

  async createPost(authorId, title, content, status = "draft", tagNames = []) {
    const result = await this.db.run(
      `
      INSERT INTO posts (author_id, title, content, status)
      VALUES (?, ?, ?, ?)
    `,
      [authorId, title, content, status]
    )

    const postId = result.lastID

    // 处理标签
    if (tagNames.length > 0) {
      for (const tagName of tagNames) {
        // 获取或创建标签
        let tag = await this.db.get("SELECT id FROM tags WHERE name = ?", [tagName])

        if (!tag) {
          const tagResult = await this.db.run("INSERT INTO tags (name) VALUES (?)", [
            tagName
          ])
          tag = { id: tagResult.lastID }
        }

        // 建立关联
        await this.db.run("INSERT INTO post_tags (post_id, tag_id) VALUES (?, ?)", [
          postId,
          tag.id
        ])
      }
    }

    return postId
  }

  async getPosts(status = null, tag = null, limit = 10, offset = 0) {
    let sql = `
      SELECT
        p.*,
        a.username as author_name,
        GROUP_CONCAT(t.name) as tags
      FROM posts p
      JOIN authors a ON p.author_id = a.id
      LEFT JOIN post_tags pt ON p.id = pt.post_id
      LEFT JOIN tags t ON pt.tag_id = t.id
      WHERE 1=1
    `

    const params = []

    if (status) {
      sql += " AND p.status = ?"
      params.push(status)
    }

    if (tag) {
      sql += " AND t.name = ?"
      params.push(tag)
    }

    sql += " GROUP BY p.id ORDER BY p.created_at DESC LIMIT ? OFFSET ?"
    params.push(limit, offset)

    return await this.db.all(sql, params)
  }

  async getPostById(postId) {
    const post = await this.db.get(
      `
      SELECT
        p.*,
        a.username as author_name,
        a.bio as author_bio
      FROM posts p
      JOIN authors a ON p.author_id = a.id
      WHERE p.id = ?
    `,
      [postId]
    )

    if (!post) return null

    // 获取标签
    const tags = await this.db.all(
      `
      SELECT t.name
      FROM post_tags pt
      JOIN tags t ON pt.tag_id = t.id
      WHERE pt.post_id = ?
    `,
      [postId]
    )

    // 获取评论
    const comments = await this.db.all(
      `
      SELECT * FROM comments
      WHERE post_id = ?
      ORDER BY created_at DESC
    `,
      [postId]
    )

    return {
      ...post,
      tags: tags.map((t) => t.name),
      comments
    }
  }

  async addComment(postId, authorName, content) {
    const result = await this.db.run(
      `
      INSERT INTO comments (post_id, author_name, content)
      VALUES (?, ?, ?)
    `,
      [postId, authorName, content]
    )

    return result.lastID
  }

  async incrementPostViews(postId) {
    await this.db.run(
      `
      UPDATE posts
      SET views = views + 1
      WHERE id = ?
    `,
      [postId]
    )
  }

  async getStatistics() {
    const stats = await this.db.get(`
      SELECT
        (SELECT COUNT(*) FROM authors) as total_authors,
        (SELECT COUNT(*) FROM posts WHERE status = 'published') as published_posts,
        (SELECT COUNT(*) FROM posts WHERE status = 'draft') as draft_posts,
        (SELECT COUNT(*) FROM comments) as total_comments,
        (SELECT SUM(views) FROM posts) as total_views
    `)

    return stats
  }

  async close() {
    if (this.db) {
      await this.db.close()
    }
  }
}

// 使用示例
async function main() {
  const blog = new BlogSystem("blog.db")
  await blog.initialize()

  try {
    // 创建作者
    const authorId = await blog.createAuthor(
      "john_doe",
      "john@example.com",
      "password123",
      "A passionate writer"
    )

    // 创建文章
    const postId = await blog.createPost(
      authorId,
      "Node.js SQLite 教程",
      "这是一篇关于如何在 Node.js 中使用 SQLite 的详细教程...",
      "published",
      ["Node.js", "SQLite", "数据库"]
    )

    // 获取文章列表
    const posts = await blog.getPosts("published", null, 5, 0)
    console.log("文章列表:", posts)

    // 获取单篇文章详情
    const postDetail = await blog.getPostById(postId)
    console.log("文章详情:", postDetail)

    // 添加评论
    await blog.addComment(postId, "读者A", "很棒的文章，学到了很多！")

    // 增加浏览量
    await blog.incrementPostViews(postId)

    // 获取统计信息
    const stats = await blog.getStatistics()
    console.log("博客统计:", stats)
  } finally {
    await blog.close()
  }
}

main().catch(console.error)

   project/
   ├── src/
   │   ├── database/
   │   │   ├── connection.js    # 数据库连接配置
   │   │   ├── models/          # 数据模型
   │   │   └── migrations/      # 数据库迁移
   │   ├── services/            # 业务逻辑
   │   └── app.js              # 应用入口
   ├── data/                   # 数据库文件
   └── tests/                  # 测试文件