skip to content
Logo 裁晨

Electron中使用typorm，better-sqlite3实现本地数据sqlite存储

/ 5 min read

View more blogs with the tag 技术 , View more blogs with the tag vue , View more blogs with the tag electron
Table of Contents

前言

本文主讲在 electron 中使用 typeOrm 和 better-sqlite3 实现本地数据存储以及读取，以及开发过程中遇到问题的解决方案

阅读须知：

  1. 代码使用 TypeScript，vue-setup
  2. 脚手架 electron-vite
  3. 开发环境：window,node:v16 .15.1,pnpm:v7 .9.0

本文知识点

  • 用 typeOrm 创建连接时，在 Electron 中需要调整的参数
  • 解决 better-sqlite3 使用内置 node 版本和项目使用的 Electron 内置 node 版本不一致

主进程编写

安装

pnpm i typeorm
pnpm i better-sqlite3

实现连接数据库方法

src\main\service\database.ts
import path from "path";
import { DataSource } from "typeorm";
import { app } from "electron";
import { is } from "@electron-toolkit/utils";
import { BetterSqlite3ConnectionOptions } from "typeorm/driver/better-sqlite3/BetterSqlite3ConnectionOptions";
import { MessageModel } from "./model";


export class DataBase {
  dataSource: DataSource;


  //初始化数据库文件
  constructor(database: string) {
    //使用该方法获取缓存目录从而实现软件升级或卸载数据保留
    // 例：windows下文件存储位置
    // C:\Users\WHWAN\AppData\Roaming\pc-client\data\message.db
    let basePath = path.join(app.getPath("appData"), app.getName(), `./data/${database}.db`);
    let options: BetterSqlite3ConnectionOptions = {
      type: "better-sqlite3",
      entities: [MessageModel],
      database: basePath,
      synchronize: true,
    };
    this.dataSource = new DataSource(options);
  }
}

使用出现如下报错

Error: The module '...node_modules\better-sqlite3\build\Release\better_sqlite3.node'
was compiled against a different Node.js version using
NODE_MODULE_VERSION $XYZ. This version of Node.js requires
NODE_MODULE_VERSION $ABC. Please try re-compiling or re-installing
the module (for instance, using `npm rebuild` or `npm install`).

安装

pnpm i -D electron-rebuild

在package.json中添加命令并执行，即可构建对应版本better-sqlite3

"rebuild": "electron-rebuild -f -w better-sqlite3"

使用示例

创建表结构

import { Entity, PrimaryColumn, Column } from "typeorm";


@Entity()
export class MessageModel {
  @PrimaryColumn({ type: "int" })
  id: number | undefined;


  @Column({ type: "int8", nullable: false })
  roomId: number | undefined;


  @Column({ type: "text", nullable: false })
  content: string | undefined;


  @Column({ type: "text", nullable: false })
  type: string | undefined;
}

实现 CRUD

主进程实现

src\main\service\messageService.ts
import { ipcMain } from "electron";
import { DataSource } from "typeorm";
import { WindowsMain } from "../windows";
import { DataBase } from "./database";


//创建数据查询Modal
export interface MsgListDTO extends ListDTO {
  roomId: number;
}
//列表查询基类
export interface ListDTO {
  pageNum: number;
  pageSize: number;
  sort: number;
}


//实现MessageService
export class MessageService {
  static instance: MessageService;
  dataSource: DataSource;


  //使用单例模式
  static getInstance() {
    if (!this.instance) {
      this.instance = new MessageService();
    }
    return this.instance;
  }


  constructor() {
    //创建数据库
    this.dataSource = new DataBase("message").dataSource;
  }


  //初始化主角进程监听事件
  init() {
    //新增数据监听
    ipcMain.on("create-message", async (_event, data: { winViewId: number; val: MessageModel }) => {
      const info = new MessageModel();
      info.roomId = data.val.roomId;
      info.content = data.val.content;
      info.type = data.val.type;
      const res = await this.create(info);
      const win = WindowsMain.getInstance().getWin(data.winViewId);
      win && win.webContents.send("update-messages", res);
    });


    //获取数据列表监听
    ipcMain.on("get-message", async (_event, data: { winViewId: number; params: MsgListDTO }) => {
      const res = await this.getList(data.params);
      const win = WindowsMain.getInstance().getWin(data.winViewId);
      win && win.webContents.send("get-messages", res);
    });
  }


  //实现新增方法
  async create(message: MessageModel) {
    await this.dataSource.initialize();
    const res = await this.dataSource.manager.save(message);
    await this.dataSource.destroy();
    return res;
  }


  //实现分页查询
  async getList(options: MsgListDTO) {
    await this.dataSource.initialize();
    const skip = options.pageSize * options.pageNum - options.pageSize;
    const sort = options.sort === 2 ? "ASC" : "DESC";
    const listAndCount = await this.dataSource
      .createQueryBuilder(MessageModel, "message")
      .where(`message.roomId = ${options.roomId}`)
      .orderBy("message.id", sort)
      .skip(skip)
      .take(options.pageSize)
      .getManyAndCount();
    await this.dataSource.destroy();
    return { list: listAndCount[0], count: listAndCount[1] };
  }
}

渲染进程新增及查询

//创建消息体
export interface MessageSqlModel {
  id?: number;
  roomId: number;
  content: string;
  type: string;
}
const message = ref<MessageSqlModel>({
  roomId: 1,
  content: "你好",
  type: "text",
});
//发送消息
const onSend = () => {
  window.electron.ipcRenderer.send("create-message", {
    winViewId: window.winViewId,
    val: { ...message.value },
  });
};
//获取更新消息
window.electron.ipcRenderer.on("update-messages", (_event, data) => {
  console.log("update-messages", data);
});


//分页查询
window.electron.ipcRenderer.on("get-messages", (_event, data) => {
  console.log("get-messages", data);
});
window.electron.ipcRenderer.send("get-message", {
  winViewId: window.winViewId,
  params: {
    roomId: 1,
    sort: "ASC",
    pageNum: 1,
    pageSize: 5,
  },
});

后记

本文为学习中记录一下遇到的问题

参考文档