Handling TypeORM migrations in Electron apps

anyo — Sun, 24 Nov 2024 23:35:32 +0000

Introduction

I'm currently building an app using Electron and decided to use SQLite with TypeORM for database management. Setting up TypeORM with Electron was relatively straightforward, but things got complicated when I started dealing with migrations.

Unlike in traditional web development, where I find migration handling fairly simple and well-documented, there isn't much guidance available for Electron apps. After spending time figuring it out, I decided to write this tutorial to share what I've learned.

Dependencies

I started with a basic Electron + TypeScript + React project and use pnpm as my package manager. This tutorial assumes you already have a working Electron app.

To set up the required libraries, install TypeORM, better-sqlite3, and their types:

pnpm add typeorm reflect-metadata better-sqlite3
pnpm add -D @types/node @types/better-sqlite3

Additionally, import reflect-metadata somewhere in the global place of your app:

import "reflect-metadata"

Note
I won't be covering how to create entities or migrations in this tutorial. If you're new to these concepts or need more details, I recommend checking out the TypeORM documentation on entities and migrations.

File structure

Let's start with the structure of my project:

app-name
├── src
│   ├── main
│   │   ├── database
│   │   │   ├── dataSource.ts
│   │   │   ├── entities
│   │   │   │   ├── user
│   │   │   │   │   ├── user.entity.ts
│   │   │   │   │   └── user.repository.ts
│   │   │   │   └── post
│   │   │   │       ├── post.entity.ts
│   │   │   │       └── post.repository.ts
│   │   │   └── migrations
│   │   │       ├── 1738490591309-createUsersTable.ts
│   │   │       └── 1738490598615-createPostsTable.ts
│   │   ├── ipc
│   │   │   ├── users.ts # createUser(), getUsers(), updateUser()...
│   │   │   ├── posts.ts # createPost(), getPosts(), updatePost()...
│   │   │   └── index.ts
│   │   ├── utils
│   │   │   └── ...
│   │   ├── index.ts
│   │   └── windowManager.ts
│   ├── preload
│   │   └── ...
│   ├── renderer
│   │   └── ... (React app)
│   └── shared
│       └── ...
├── forge.config.ts
├── package.json
├── pnpm-lock.yaml
├── tsconfig.json
├── webpack.main.config.ts
├── webpack.plugins.ts
├── webpack.renderer.config.ts
├── webpack.rules.ts
└── ...

Configuration details

package.json

Add the following scripts to your package.json.
The migrate:* scripts are optional but useful for common tasks.

TypeORM documentation: Using CLI > If entities files are in typescript

"scripts": {
    // ...
    "typeorm": "typeorm-ts-node-commonjs",
    "rebuild": "electron-rebuild -f -w better-sqlite3",
    "postinstall": "electron-rebuild -f -w better-sqlite3",
    "migrate:create": "sh -c 'pnpm typeorm migration:create ./src/main/database/migrations/$1' --",
    "migrate:up": "pnpm typeorm -d ./src/main/database/dataSource.ts migration:run",
    "migrate:down": "pnpm typeorm -d ./src/main/database/dataSource.ts migration:revert"
    // ...
}

Usage:

pnpm typeorm -h
pnpm rebuild
pnpm migrate:create [migrationName] # e.g., pnpm migrate:create createUsersTable
pnpm migrate:up
pnpm migrate:down

webpack.main.config.ts

The migrations bundling is done with webpack.
TypeORM has documentation on bundling migration files: Bundling Migration Files.

If glob is not installed, add it as a dev dependency:

pnpm add -D glob

Here is my full webpack config for the main process:

// webpack.main.config.ts
import * as glob from "glob";
import path from "path";
import { Configuration } from "webpack";

import { plugins } from "./webpack.plugins";
import { rules } from "./webpack.rules";

const indexEntryName = "index";
export const mainConfig: Configuration = {
  resolve: {
    extensions: [".js", ".ts", ".jsx", ".tsx", ".css", ".json"],
  },
  entry: {
    [indexEntryName]: "./src/main/index.ts",
    ...glob
      .sync(path.resolve("src/main/database/migrations/*.ts"))
      .reduce((entries: Record<string, string>, filename: string) => {
        const migrationName = path.basename(filename, ".ts");
        return Object.assign({}, entries, { [migrationName]: filename });
      }, {}),
  },
  output: {
    libraryTarget: "umd",
    filename: (pathData) => {
      return pathData.chunk?.name && pathData.chunk.name !== indexEntryName
        ? "database/migrations/[name].js"
        : "[name].js";
    },
  },
  plugins,
  module: {
    rules,
  },
  optimization: {
    minimize: false,
  },
};

src/main/database/dataSource.ts

Create a DataSource file for configuring database connection settings:

// src/main/database/dataSource.ts
import path from "node:path";
import "reflect-metadata";
import { DataSource } from "typeorm";

import { UserEntity } from "./entities/user/user.entity";
import { PostEntity } from "./entities/post/post.entity";

const isElectron = !!process.versions.electron; // simple trick to see if the data source is called from the Electron app or CLI (for migrations scripts)
const isProduction = process.env.NODE_ENV === "production";

let databasePath: string;

if (isElectron) {
  // eslint-disable-next-line @typescript-eslint/no-require-imports
  const { app } = require("electron");

  databasePath = path.join(
    app.getPath("userData"),
    app.isPackaged ? "app-name.sqlite" : "app-name.dev.sqlite"
  );
} else {
  // use hardcoded path for running migrations in development (macOS)
  databasePath = path.join(
    "/Users/user/Library/Application Support/app-name/",
    isProduction ? "app-name.sqlite" : "app-name.dev.sqlite"
  );
}

// https://typeorm.io/data-source-options#better-sqlite3-data-source-options
const dataSource = new DataSource({
  type: "better-sqlite3",
  database: databasePath,
  entities: [UserEntity, PostEntity],
  migrations: [
    path.join(__dirname, isElectron ? "database" : "", "/migrations/*.{js,ts}"),
  ],
  synchronize: false, // important
  logging: true // use this for debugging
});

export const entityManager = dataSource.createEntityManager();
export default dataSource;

src/main/index.ts

Add a setupDatabase function to initialize the database on every app launch:

// src/main/index.ts
import { app } from "electron";
import dataSource from "./database/dataSource";

// ...

const setupDatabase = async () => {
  try {
    await dataSource.initialize();
    console.info("Database initialized");
    const pendingMigrations = await dataSource.showMigrations();
    console.info("Pending migrations:", pendingMigrations);
    if (pendingMigrations) {
      console.info("Running migrations...");
      await dataSource.runMigrations();
      console.info("Migrations completed");
    }
  } catch (err) {
    console.error(err);
  }
};

app.whenReady().then(async () => {
    // ...
    await setupDatabase(); // do this before createWindow()
    // ...
});

// ...

Conclusion

I hope dealing with migrations in Electron apps will now be straightforward for you. This setup took me a while to figure out, so I'm glad to share it and hopefully save you some time. Thank you for reading!

DEV Community: anyo