Electron + Sqlite3 + Vue3 + Ts +Sequelize数据库创建连接封装

本文链接：https://blog.csdn.net/Angel_moon777/article/details/126850477

框架搭建文档参考Electron + Vue3 + Vite + Ts + Sqlite3 + Sequelize搭建笔记

DbUtils.ts // 封装文件

const db = require('sqlite3').verbose()

const path = require("path")
const fs = require("fs")
const {Sequelize} = require('sequelize');

import {localFilePath} from "@/config";
import {creatFolder, fileExistence} from "@/utils/file";
import writers from "@/db/writers/tables/writers";


export default class DbUtils {
    // 所有要存放或者要读取的本地文件根路径
    public localFilePath: string = localFilePath()
    protected dbPathTem: string = '' // 临时的数据库文件路径
    public readonly defaultFolder: string = 'dbFile'

    constructor() {
        this.setLocalFilePath()
    }

    public setLocalFilePath = (path: string = localFilePath()): void => {
        this.localFilePath = path
    }

    /**
     * @description 保存数据库文件
     * @param {string} dbNameIsFullPath dbName是否完整路径,如果不是会自动拼接
     * @param {string} dbName     数据库文件名称
     * @param {string} folderName   存放的文件夹名称(默认dbs)，如果没有则自动创建
     * @return {string}  如果数据库已经存在，则返回路径，否则返回空字符串
     */
    public createDbFile(dbNameIsFullPath: boolean = true, dbName: string = this.dbPathTem, folderName?: string): Promise<string> {
        return new Promise((resolve, reject) => {
            if (!this.judgeDbExists(dbNameIsFullPath, dbName, folderName)) {
                const dbFile = new db.Database(this.dbPathTem, (err: any) => {
                    if (err) {
                        console.error('创建数据库失败=> ', err)
                        resolve('')
                    } else {
                        console.log(`创建数据库${dbName}成功`)
                        resolve(this.dbPathTem)
                    }
                })
            } else {
                console.log(`数据库${dbName}已经存在`)
                resolve(this.dbPathTem)
            }
        })
    }

    /**
     * @description db文件路径
     * @param {string} dbName
     * @param {string} folderName 存放于哪个文件夹下,如果没有会自动创建
     */
    public dbPath(dbName: string, folderName: string = this.defaultFolder): string {
        return path.join(creatFolder(folderName), dbName + ".db")
    }

    /**
     * @description 判断db文件是否存在, 并设置临时路径
     * @param {boolean} dbNameIsFullPath dbName是否完整路径,如果不是会自动拼接
     * @param {string} dbName
     * @param {string} folderName 存放于哪个文件夹下,如果没有会自动创建
     */
    public judgeDbExists(dbNameIsFullPath: boolean = true, dbName: string, folderName: string = this.defaultFolder): boolean {
        this.dbPathTem = dbNameIsFullPath ? dbName : this.dbPath(dbName, folderName)
        return fileExistence(true, this.dbPathTem)
    }

    /**
     * @description db文件路径
     * @param {boolean} dbNameIsFullPath dbName是否完整路径,如果不是会自动拼接
     * @param {string} dbName 文件名
     * @param {string} folderName 存放于哪个文件夹下,如果没有会自动创建
     */
    public connectDb(dbNameIsFullPath: boolean = true, dbName: string = this.dbPathTem, folderName: string = this.defaultFolder): Promise<any> {
        return new Promise((resolve, reject) => {
            if (!this.judgeDbExists(dbNameIsFullPath, dbName, folderName)) {
                console.error(`数据库${dbName}不存在，请先创建`)
                resolve(false)
            } else {
                const dbInstance: any = new db.Database(this.dbPathTem, (err: any) => {
                    console.log(2)
                    if (err) {
                        console.error('连接数据库失败=> ', err)
                        resolve(false)
                    } else {
                        console.log('连接数据库成功', dbInstance)
                        resolve(dbInstance)
                    }
                })
            }
        })
    }

    /**
     * @description 自动创建并连接异步函数
     * @param {boolean} dbNameIsFullPath dbName是否完整路径,如果不是会自动拼接
     * @param {string} dbName 文件名
     * @param {string} folderName 存放于哪个文件夹下,如果没有会自动创建
     */
    public async creatAndConnectDb(dbNameIsFullPath: boolean, dbName: string, folderName: string = this.defaultFolder) {
        if (!this.judgeDbExists(dbNameIsFullPath, dbName, folderName)) {
            const res = await this.createDbFile()
        }
        return await this.connectDb()
    }

    /**
     * @description 自动创建并连接异步函数-使用Sequelize工具
     * @param {boolean} dbNameIsFullPath dbName是否完整路径,如果不是会自动拼接
     * @param {string} dbName 文件名
     * @param {string} folderName 存放于哪个文件夹下,如果没有会自动创建
     */
    public async sequelizeOrm(dbNameIsFullPath: boolean, dbName: string, folderName: string = this.defaultFolder): Promise<boolean> {
        if (!this.judgeDbExists(dbNameIsFullPath, dbName, folderName)) {
            const res = await this.createDbFile()
            if (!res) return false
        }
        try {
            // 如果需要可以创建连接池 https://sequelize.org/docs/v6/other-topics/connection-pool/
            const dataBase = await new Sequelize({
                dialect: 'sqlite',
                storage: this.dbPathTem
            })
            await dataBase.authenticate();
            console.log('Connection has been established successfully. || 成功连接数据库！');
            return dataBase
        } catch (error) {
            console.error('Unable to connect to the database:', error);
            return false
        }
    }

    // 创建连接多个表
    public async createConnectTables(tables: any[], db:any){
        return await Promise.all(tables.map(table => new Promise((resolve, reject) => resolve(db.define(table.name, table.data)))))
    }
}

// export default new dbUtils()

@/config

export const localFilePath = (): string => __dirname.substring(0, __dirname.indexOf('resources') + 9)

@/utils/file

import { localFilePath } from "@/config";
const fs = require("fs")
const path = require("path")

/**
 * @description 创建文件夹
 * @param {string} folderName 文件夹名
 * @param {string} folderBeforePath 文件夹存放于哪个路径下
 */
export function creatFolder(folderName: string, folderBeforePath: string = localFilePath()) {
    try {
        const folderPath = path.join(folderBeforePath, folderName)
        if(!fs.existsSync(path.join(localFilePath(), folderName))){
            console.error(folderName + '文件夹不存在，正在创建...路径为 => \n', folderPath)
            fs.mkdirSync(folderPath)
            return folderPath
        } else {
            console.log(folderName + '文件夹已经存在,路径为=>', folderPath)
            return folderPath
        }
    } catch (e) {
        console.error('创建文件夹出错=>', e)
        return false
    }
}

// 创建文件
export function creatFile() {

}

/**
 * @description 判断文件是否存在
 * @param {string} fileNameIsFullPath fileName是否完整路径
 * @param {string} fileName 文件名
 * @param {string} folderName 存放于哪个文件夹或路径下
 * @return { string } 如果文件存在，则返回路径，否则返回空字符串
 */
export function fileExistence(fileNameIsFullPath: boolean, fileName: string, folderName: string = ''): boolean {
    try {
        return fs.existsSync(fileNameIsFullPath ? fileName : path.join(localFilePath()), folderName, fileName)
    } catch (e) {
        console.error('检测文件是否存在出错=>', e)
        return false
    }
}

使用示例

import DbUtils from "@/db/DbUtils";
const { Sequelize, Model, DataTypes } = require("sequelize");
import writers from "./tables/writers"


export default class Writer extends DbUtils{
    private tableWriters: any
    private tableTest: any
    public dataBase: any

    public constructor() {
        super();
    }

    // 创建数据库、连接数据库、创建表
    public async creatTable() {
        const dataBase = await this.sequelizeOrm(false, 'writer')
        // this.tableWriters = await dataBase.define(writers.name, writers.data); // 本来想这么写，可是总报错，就放弃了
        const res = await this.createConnectTables([ writers], dataBase) // 其实这个封装没必要，封装了也可以，同时连接多个数据库
        this.tableWriters = res[0]
        // 将模型与数据库同步 参数文档 https://sequelize.org/api/v7/interfaces/syncoptions
        await (async () => {
            // @ts-ignore
            await dataBase.sync({ force: false }); //当为true时，先删除表后再创建， 默认false
        })();
        this.dataBase = dataBase
        return true
    }

    // 下面这些就可以直接调用了
    public async insertWriter (content: string) {
        const res = await this.tableWriters.create({ content: encodeURIComponent(content) });
        res.save()
        return res
    }

    public async getWriters () {
        const res = await this.tableWriters.findAll();
        return res
    }

}

./tables/writers是数据库字段

const { DataTypes } = require("sequelize");
// 如果后期需要新增字段需要手动定义migration(数据库迁移)。直接添加是不可行的
export default {
    name: 'writers', // 表名
    data: {
        id: {
            type: DataTypes.INTEGER, // INTEGER类型
            primaryKey: true, // 主键
            autoIncrement: true, // 自增长
        },
        content: {
            type: DataTypes.TEXT,
            allowNull : false
            // defaultValue: '0', // 默认值
        },
        desc: {
            type: DataTypes.TEXT, //
            allowNull: true, // 不允许为空
            defaultValue: '没有简介', // 默认值
        },
        // createTime: { // 自带
        //     type: DataTypes.STRING,
        //     allowNull : false
        // },
        // updateTime: { // 自带
        //     type: DataTypes.STRING,
        //     allowNull : false
        // },
        createArea: {
            type: DataTypes.STRING,
            allowNull : false,
            defaultValue: '北京市', // 默认值
        },
    }
}