1.13使用 Node.js 操作 SQLite

 SQLite 是一个轻量级的嵌入式关系型数据库，适合小型项目、原型开发和本地数据存储。在 Node.js 中使用 SQLite 非常方便，常用的第三方库是 sqlite3。

一、安装依赖

首先，在项目中安装 sqlite3：

npm init -y npm install sqlite3

二、创建并连接数据库

创建一个 db.js 文件：

const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./mydb.sqlite', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the mydb SQlite database.');
});

这段代码首先导入了 sqlite3 模块，并设置了为 verbose 模式以便于调试。然后创建了一个新的 SQLite 数据库连接，如果指定的数据库文件不存在，SQLite 将自动创建一个新的。 

三、创建表

db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
)`, function(err) {
    if (err) {
        console.error(err.message);
    }
    console.log('Table created');
});

这里我们创建了一个名为 users 的表，包含三个字段：id、name 和 email。 

四、插入/更新/删除 数据

let sql = `INSERT INTO users(name, email) VALUES(?, ?)`;
db.run(sql, ["John Doe", "johndoe@example.com"], function(err) {
    if (err) {
        console.error(err.message);
    }
    console.log(`A new row has been inserted with rowid ${this.lastID}`);
});

let updateSql = `UPDATE users SET email = ? WHERE name = ?`;
db.run(updateSql, ["newemail@example.com", "John Doe"], function(err) {
    if (err) {
        console.error(err.message);
    }
    console.log(`Row(s) updated: ${this.changes}`);
});

let deleteSql = `DELETE FROM users WHERE name = ?`;
db.run(deleteSql, ["John Doe"], function(err) {
    if (err) {
        console.error(err.message);
    }
    console.log(`Row(s) deleted: ${this.changes}`);
});

五、查询数据

查询数据库中的数据可以使用 db.all() 方法：

db.all(`SELECT * FROM users`, [], (err, rows) => {
    if (err) {
        throw err;
    }
    rows.forEach((row) => {
        console.log(row.id, row.name, row.email);
    });
});

分页查询的基本原理

分页查询主要依靠两个 SQL 关键字：

• LIMIT：限制返回的记录数。
• OFFSET：跳过指定数量的记录。

-- 第 1 页（每页 10 条）
SELECT * FROM users LIMIT 10 OFFSET 0;

-- 第 2 页
SELECT * FROM users LIMIT 10 OFFSET 10;

-- 第 3 页
SELECT * FROM users LIMIT 10 OFFSET 20;

基础实现方法

使用 db.all() 方法

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./mydb.sqlite');

// 分页参数
const page = 1;
const pageSize = 10;
const offset = (page - 1) * pageSize;

// 分页查询
db.all(
  'SELECT * FROM users LIMIT ? OFFSET ?',
  [pageSize, offset],
  (err, rows) => {
    if (err) {
      console.error('查询失败:', err.message);
    } else {
      console.log('查询结果:', rows);
    }
  }
);

 计算总页数

// 查询总记录数
db.get('SELECT COUNT(*) as count FROM users', (err, result) => {
  if (err) {
    console.error('查询总记录数失败:', err.message);
  } else {
    const totalCount = result.count;
    const totalPages = Math.ceil(totalCount / pageSize);
    console.log(`总记录数: ${totalCount}, 总页数: ${totalPages}`);
  }
});

db.all 和 db.get 是两个常用的查询方法，但它们的功能和用途有明显区别：

1. 返回结果数量

• db.get：
  返回查询结果的第一条记录（单个对象）。如果没有匹配的记录，则返回 null。

• db.all：
  返回查询结果的所有记录（数组）。如果没有匹配的记录，则返回空数组 []。

2. 回调参数

• db.get：
  回调函数接收 (err, row)，其中 row 是单个对象。

• db.all：
  回调函数接收 (err, rows)，其中 rows 是数组。

3. db.get 的适用场景

• 查询唯一记录（如通过 ID 查询用户）。
• 聚合查询（如 COUNT, SUM, AVG）。
• 只需获取第一条记录的场景。

// 查询单个用户
db.get('SELECT * FROM users WHERE id = ?', [1], (err, row) => {
  if (err) {
    console.error(err);
  } else {
    console.log(row); // 返回单个用户对象或 null
  }
});

// 聚合查询
db.get('SELECT COUNT(*) as total FROM users', (err, row) => {
  console.log(row.total); // 返回总数
});

4. db.all 的适用场景

• 查询多条记录（如获取所有用户）。
• 分页查询（结合 LIMIT 和 OFFSET）。
• 需要遍历所有结果的场景。

// 查询所有用户
db.all('SELECT * FROM users', (err, rows) => {
  if (err) {
    console.error(err);
  } else {
    console.log(rows); // 返回用户数组
    rows.forEach(user => {
      console.log(user.name);
    });
  }
});

// 分页查询
db.all('SELECT * FROM users LIMIT ? OFFSET ?', [10, 0], (err, rows) => {
  // 返回第一页的 10 条记录
});

db.serialize() 是一个重要的方法，用于控制 SQL 语句的执行顺序

1. SQLite 的并发特性

• SQLite 是文件型数据库，同一时间只能有一个写操作。
• 多个读操作可以并行，但写操作会阻塞所有其他操作。

2. db.serialize() 的作用

• 确保同一连接上的 SQL 语句按顺序执行。
• 防止并发执行导致的数据不一致问题。

db.serialize(() => {
  // 这里的 SQL 语句会按顺序执行
  db.run('SQL 语句 1');
  db.run('SQL 语句 2');
  // ...
});

db.serialize(() => {
  // 创建表
  db.run('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)');
  
  // 插入数据
  db.run('INSERT INTO users (name) VALUES (?)', ['张三']);
  
  // 查询数据
  db.get('SELECT * FROM users WHERE id = 1', (err, row) => {
    console.log(row); // 可以确保数据已插入
  });
});

事务需要顺序执行

db.serialize(() => {
  // 开始事务
  db.run('BEGIN TRANSACTION');
  
  // 执行多个操作
  db.run('INSERT INTO users (name) VALUES (?)', ['李四']);
  db.run('UPDATE accounts SET balance = balance - 100 WHERE user_id = 1');
  
  // 提交事务
  db.run('COMMIT', (err) => {
    if (err) {
      db.run('ROLLBACK'); // 出错时回滚
    }
  });
});

• 事务中的 SQL 必须按顺序执行，否则可能导致数据不一致。
• 例如，COMMIT 必须在所有操作完成后执行。

六、关闭数据库连接

db.close((err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Close the database connection.');
});