better-sqlite3之exec方法

在 better-sqlite3 中，.exec() 方法用于执行包含多个 SQL 语句的字符串。与预编译语句相比，这种方法性能较差且安全性较低，但有时它是必要的，特别是当你需要从外部文件（如 SQL 脚本）中执行多个 SQL 语句时。

使用 `.exec()` 方法

以下是如何使用 .exec() 方法来执行从文件中读取的 SQL 脚本，并确保正确处理错误和事务回滚。

示例代码

假设你有一个名为 migrate-schema.sql 的 SQL 文件，其中包含多个 SQL 语句，以下是完整的示例代码：

-- 创建 users 表
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建 posts 表
CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入一些初始用户数据
INSERT INTO users (name, age) VALUES ('Alice', 28);
INSERT INTO users (name, age) VALUES ('Bob', 25);
INSERT INTO users (name, age) VALUES ('Charlie', 30);

-- 插入一些初始帖子数据
INSERT INTO posts (user_id, title, content) VALUES (1, 'My First Post', 'This is my first post.');
INSERT INTO posts (user_id, title, content) VALUES (1, 'Another Post', 'This is another post.');
INSERT INTO posts (user_id, title, content) VALUES (2, 'Hello World', 'Hello everyone!');

-- 更新 Alice 的年龄为 29
UPDATE users SET age = 29 WHERE name = 'Alice';

-- 删除 Bob 的所有帖子
DELETE FROM posts WHERE user_id = (SELECT id FROM users WHERE name = 'Bob');

-- 查询所有用户及其帖子
SELECT u.id AS user_id, u.name, p.id AS post_id, p.title, p.content
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

const fs = require('fs');
const path = require('path');
const Database = require('better-sqlite3');

// 打开数据库连接
const db = new Database('mydb.sqlite');

// 读取 SQL 文件内容
const migrationFilePath = path.join(__dirname, 'migrate-schema.sql');
const migration = fs.readFileSync(migrationFilePath, 'utf8');

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

    // 执行 SQL 文件中的所有语句
    db.exec(migration);

    // 提交事务
    db.exec('COMMIT;');
    console.log('Migration completed successfully.');
} catch (error) {
    // 如果发生错误，回滚事务
    db.exec('ROLLBACK;');
    console.error('An error occurred during migration:', error.message);
}

// 关闭数据库连接
db.close();
console.log('Database connection closed.');

详细解释

读取 SQL 文件：
- 使用 fs.readFileSync() 读取 SQL 文件的内容。这里我们使用 path.join() 来确保路径的兼容性。
开始事务：
- 在执行 SQL 语句之前，首先调用 db.exec('BEGIN TRANSACTION;') 开始一个事务。这可以确保所有的 SQL 语句要么全部成功，要么全部失败，从而保持数据的一致性。
执行 SQL 文件中的所有语句：
- 使用 db.exec(migration) 执行从文件中读取的所有 SQL 语句。注意，.exec() 可以执行包含多个 SQL 语句的字符串。
提交事务：
- 如果所有 SQL 语句都成功执行，则调用 db.exec('COMMIT;') 提交事务。
错误处理和事务回滚：
- 如果在执行 SQL 语句的过程中发生错误，捕获异常并调用 db.exec('ROLLBACK;') 回滚事务，以防止部分更新导致的数据不一致问题。
关闭数据库连接：
- 最后，调用 db.close() 关闭数据库连接。

错误处理和日志记录

为了更好地调试和维护，建议增加更多的错误处理和日志记录。例如，可以在捕获异常时记录详细的错误信息：

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

    // 执行 SQL 文件中的所有语句
    db.exec(migration);

    // 提交事务
    db.exec('COMMIT;');
    console.log('Migration completed successfully.');
} catch (error) {
    // 如果发生错误，回滚事务
    db.exec('ROLLBACK;');
    
    // 记录详细的错误信息
    console.error('An error occurred during migration:');
    console.error('Error message:', error.message);
    console.error('Stack trace:', error.stack);
}

注意事项

安全性：由于 .exec() 直接执行 SQL 字符串，因此存在 SQL 注入的风险。尽量避免直接将用户输入插入到 .exec() 调用中。如果必须这样做，请先进行严格的验证和清理。
性能：与预编译语句相比，.exec() 的性能较差。如果可能的话，尽量使用预编译语句来提高性能和安全性。
事务管理：当执行多个 SQL 语句时，务必使用事务来确保数据一致性。如果没有使用事务，部分语句的成功执行可能会导致数据库处于不一致状态。

完整示例

以下是一个更完整的示例，展示了如何结合信号处理机制来确保在应用程序退出时正确关闭数据库连接：

const fs = require('fs');
const path = require('path');
const process = require('process');
const Database = require('better-sqlite3');

// 打开数据库连接
const db = new Database('mydb.sqlite');

// 监听进程退出事件和其他终止信号
function handleExit() {
    try {
        db.close();
        console.log('Database connection closed gracefully.');
    } catch (error) {
        console.error('Error closing database:', error.message);
    }
}

['exit', 'SIGINT', 'SIGTERM', 'SIGHUP'].forEach((signal) => {
    process.on(signal, handleExit);
});

// 读取 SQL 文件内容
const migrationFilePath = path.join(__dirname, 'migrate-schema.sql');
const migration = fs.readFileSync(migrationFilePath, 'utf8');

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

    // 执行 SQL 文件中的所有语句
    db.exec(migration);

    // 提交事务
    db.exec('COMMIT;');
    console.log('Migration completed successfully.');
} catch (error) {
    // 如果发生错误，回滚事务
    db.exec('ROLLBACK;');
    console.error('An error occurred during migration:');
    console.error('Error message:', error.message);
    console.error('Stack trace:', error.stack);
}

// 模拟长时间运行的任务
setTimeout(() => {
    console.log('Long-running task completed.');
}, 60000); // 1分钟