1.创建表

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('test.db'); // 存放db文件的目录地址

createTable('chat-messages', [
    {name: 'id', type: 'INTEGER PRIMARY KEY AUTOINCREMENT'},
    {name: 'created_at', type: "TIMESTAMP DEFAULT (datetime('now','localtime'))"},
    {name: 'updated_at', type: "TIMESTAMP DEFAULT (datetime('now','localtime'))"},
    {name: 'user_id', type: 'INTEGER'},
    {name: 'session_id', type: 'VARCHAR(64)'},
    {name: 'role_id', type: 'INTEGER'},
    {name: 'model', type: 'VARCHAR(32)'},
    {name: 'chat_id', type: 'INTEGER NOT NULL'},
    {name: 'message_id', type: 'INTEGER NOT NULL'},
    {name: 'title', type: 'VARCHAR(255)'},
    {name: 'role', type: 'VARCHAR(255)'},
    {name: 'content', type: 'TEXT'},
    {name: 'ip', type: 'VARCHAR(64)'},
]);

2.表的增删改

/**
 *  数据库封装方法
 */
function createTable(tableName, columns) {
    return new Promise((resolve, reject) => {
        const columnDefinitions = columns.map(column => `${column.name} ${column.type}`).join(', ');
        const query = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefinitions})`;
        db.run(query);
        resolve();
    })
    
}
  
function insertData(tableName, data) {
    const columns = Object.keys(data).join(', ');
    const placeholders = Object.keys(data).map(() => '?').join(', ');
    const values = Object.values(data);
    const query = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`;
    db.run(query, values);
}
  
function updateData(tableName, data, condition) {
    const setClause = Object.keys(data).map(column => `${column} = ?`).join(', ');
    const values = Object.values(data);
    const query = `UPDATE ${tableName} SET ${setClause} WHERE ${condition}`;
    db.run(query, values);
}
  
function deleteData(tableName, condition) {
    let query = `DELETE FROM ${tableName} WHERE ${condition}`;
    if (condition == "") query = `DELETE FROM ${tableName}`;
    db.run(query);
}

function getData(tableName, condition) {
    let dataQuery = `SELECT * FROM ${tableName}`;
    if (condition != undefined) dataQuery = `SELECT * FROM ${tableName} WHERE ${condition}`;
    return new Promise((resolve, reject) => {
        db.all(dataQuery, (error, rows) => {
            if (error) {
                reject(error);
            } else {
                resolve(rows);
            }
        });
    })
}


/**
 * 直接使用，测试
 */
function getRandomId() {
    return new Date().getTime() + String(Math.floor(Math.random() * 1000));
}

// 新增
insertData('chat-messages', {
   chat_id: getRandomId(),
   message_id: getRandomId(),
   model: 'model',
   role: 'assistant',
   content: 'test test test'
});

// 修改 (id为1的标题改为'标题修改111')
updateData('chat-messages', {
	title: '标题修改111'
}, {
	id: '1'
})

// 删除(id为1的数据)
deleteData('chat-messages', {
	id: '1'
});

// 查表
getData('chat-messages').then(rows => {
	console.log('rows', rows); // 表数据(全)
})

3. 封装调用

/**
 * 查询
 * @param { table_name } args  
 */
selectData(args) {
    const tableName = args.table_name;
    return new Promise((resolve, reject) => {
        getData(tableName).then(rows => {
            resolve(rows);
        }).catch(err => {
            reject(err);
        })
    })
}

/**
 * 修改
 * @param { table_name, data, condition } args 
 * condition: { id: id, ...conditions } 筛选条件
 */
editRow(args) {
    const { table_name, data, condition } = args;
    let params = "";
    Object.keys(condition).forEach(key => {
        if (params != "") {
            if (typeof(condition[key]) == 'string') {
                params += ` AND ${key} = '${condition[key]}'`
            } else {
                params += ` AND ${key} = ${condition[key]}`
            }
        } else {
            if (typeof(condition[key]) == 'string') {
                params += ` ${key} = '${condition[key]}'`
            } else {
                params += ` ${key} = ${condition[key]}`
            }
        }
    });
    updateData(table_name, data, params);
}

/**
 * 删除
 * @param { table_name, condition } args
 *  condition: { id: id, ...conditions }  删除条件，为空时，清空整个表
 */
delRow(args) {
    let params = "";
    const { table_name, condition } = args;
    
    if (condition != undefined && JSON.stringify(condition) != "{}") {
        Object.keys(condition).forEach(key => {
            if (params != "") {
                if (typeof(condition[key]) == 'string') {
                    params += ` AND ${key} = '${condition[key]}'`
                } else {
                    params += ` AND ${key} = ${condition[key]}`
                }
            } else {
                if (typeof(condition[key]) == 'string') {
                    params += ` ${key} = '${condition[key]}'`
                } else {
                    params += ` ${key} = ${condition[key]}`
                }
            }
        });
    }

    return new Promise((resolve, reject) => {
        deleteData(table_name, params);
        resolve();
    })
}