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: '北京市', // 默认值
},
}
}