一，index.js

const path = require('path');  // 引入路径处理模块
const SQL = require('./database');
const Database=require("better-sqlite3")
const db=new Database('./database.db',{verbose:console.log})


const data={
    ID:12,
    date: "2022-05-14 17:13:00",
    name: "测试数据",
    classification: "一号样品",
    remark: "",
    filename: "test.bmp"
}
const ids=[1,8,10]
// console.log(SQL.table_exist(db,'image_data'))
// SQL.table_create(db,'你好')
// SQL.table_add(db,data)
// SQL.table_select_full(db,'image_data')
// SQL.table_delete(db,9)
// SQL.table_updata(db,data)
// SQL.database_table(db)
// SQL.table_select_id(db,15)
// SQL.database_table_delete(db,'你好')
// SQL.table_search_ids(db,ids)

二，database.js代码

module.exports={
    //检测表单是否存在
    table_exist:function (db,table_name){
        try {
            const result = db.prepare('SELECT * FROM ' + table_name)
            console.log(result)
            return true
        }catch (SqliteError){
            return false
        }
    },
    //创建数据库表
    table_create:function (db,table_name){
        const sql = `
        CREATE TABLE ${table_name} (
        id INTEGER PRIMARY KEY,
        date VARCHAR (255) NOT NULL,
        name VARCHAR (255) NOT NULL,
        classification VARCHAR (255) NOT NULL,
        remark VARCHAR (255) ,
        filename VARCHAR (255) NOT NULL
        )
        `;
        db.exec(sql)
    },
    //增加数据
    table_add:function (db,data){
        const result=db.prepare('INSERT INTO image_data (date, name,classification,remark,filename) VALUES (@date, @name,@classification,@remark,@filename)').run(data)
        console.log(result.changes)
    },
    //删除数据
    table_delete:function (db,id){
        const result=db.prepare('DELETE from image_data WHERE id = ?').run(id)
        console.log(result)
    },
    //查询数据
    table_select_full:function (db,db_name){
        const result=db.prepare('SELECT * FROM '+db_name);
        const total=db.prepare('SELECT max(id) from image_data')
        console.log(result.all())
        console.log(result.all().length)
        console.log(Object.values(total.all()[0])[0])
    },
    //更新数据
    table_updata:function (db,data){
        const result = db.prepare('UPDATE image_data set (date, name,classification,remark,filename) = (@date, @name,@classification,@remark,@filename) WHERE id = @ID').run(data)
        console.log(result)
    },
    //查询数据库表
    database_table:function (db){
        const result = db.prepare('SELECT name FROM sqlite_master WHERE type ='table'')
        let temp = result.all()
        let data = []
        for(let i=0;i<temp.length;i++){
            data.push(temp[i].name)
        }
        console.log(data)
    },
    //指定ID是否存在
    table_select_id:function (db,id){
        const result=db.prepare('SELECT * from '+'image_data'+' WHERE id = '+id)
        console.log(result.all().length)

    },
    //删除表单
    database_table_delete:function (db,table_name){
        const sql = `
        DROP TABLE ${table_name}
        `;
        const result = db.exec(sql)
    },
    //多ID查询返回
    table_search_ids:function (db,ids){
        const result = db.prepare('select * from image_data where id in('+ids+')')
        console.log(result.all())
    }

}

三，依赖

yarn add better-sqlite --save