Running Prisma migrate in an Electron app

See https://github.com/awohletz/electron-prisma-trpc-example for a template that demonstrates this approach.

I use Prisma Migrate in my Electron app. Here's a mechanism to detect when I need to run migrations on app start.

In main.ts, I check if the DB needs creation (first app run) or if it needs a migration:

let needsMigration;
const dbExists = fs.existsSync(dbPath);
if (!dbExists) {
    needsMigration = true;
    // prisma for whatever reason has trouble if the database file does not exist yet.
    // So just touch it here
    fs.closeSync(fs.openSync(dbPath, 'w'));
} else {
    try {
        const latest: Migration[] = await prisma.$queryRaw`select * from _prisma_migrations order by finished_at`;
        needsMigration = last(latest)?.migration_name !== latestMigration;
    } catch (e) {
        log.error(e);
        needsMigration = true;
    }
}

Here, latestMigration is a hardcoded constant:

// This needs to be updated every time you create a migration!
export const latestMigration = "20220213142643_added_col_index";

Note that running migrate on an empty, merely-touched DB file will create the DB tables. There's no need to package a hardcoded template database to start with.

Then I run the migration if needed:

if (needsMigration) {
    try {
        const schemaPath = path.join(
            app.getAppPath().replace('app.asar', 'app.asar.unpacked'),
            'prisma',
            "schema.prisma"
        );
        log.info(`Needs a migration. Running prisma migrate with schema path ${schemaPath}`);

        // first create or migrate the database! If you were deploying prisma to a cloud service, this migrate deploy
        // command you would run as part of your CI/CD deployment. Since this is an electron app, it just needs
        // to run when the production app is started. That way if the user updates AriNote and the schema has
        // changed, it will transparently migrate their DB.
        await runPrismaCommand({
            command: ["migrate", "deploy", "--schema", schemaPath],
            dbUrl
        });

        // seed
        log.info("Seeding...");
        await seed(prisma);

    } catch (e) {
        log.error(e);
        process.exit(1);
    }
} else {
    log.info("Does not need migration");
}

seed is just a function that inserts initial data relevant to my app:

export async function seed(prisma: PrismaClient) {
    await prisma.workspace.upsert({
        create: {
            id: "default",
            name: "Default",
        },
        update: {},
        where: {
            id: "default"
        }
    })
}

Workflow

With this, to make a schema modification I follow these steps:

1. Edit the schema.prisma with the changes to the schema.
2. Create the migration with command: prisma migrate dev --name added_col_index. I'm naming it added_col_index in this particular example since I added a column.
3. Update latestMigration constant in the code with the generated migration folder name: export const latestMigration = "20220213142643_added_col_index"
4. Build, pack, and distribute the app as usual.
5. When users update the app, the updated code will contain a new latestMigration, which will make the app run Prisma Migrate on start, because the user's database does not contain that migration (in _prisma_migrations table). Prisma Migrate compares the migrations available in the prisma/migrations folder to the migrations run in the user's DB. It runs the new migration.
6. App opens the browser window as usual. The user only notices a little slower start time.

---

This has the manual step of updating latestMigration, but since I rarely create migrations, it works well enough.

To automate it, I could probably write some code that looks at the migrations folder (in the packed app) to see the latest migration file and compare that to the latest migration run in the DB.

It's actually not necessary to detect whether a migration is needed. I could just run migrate on every app start. However, that adds noticeably to the app startup time.

Comments

[haterb4]

Hello, your work has been very helpful to me, but I'm encountering a problem, and I don't know how to solve it. In your prisma.ts, there is this line that points to a non-existent directory:

const prismaPath = path.resolve(__dirname, "..", "..", "node_modules/prisma/build/index.js");

I believe this is due to the absence of the Prisma module after the build. I've been trying for days to include this module, but nothing seems to work; Electron Builder simply ignores it.

[Georg]

could you fix it? i also have the problem after building and trying to start the executable it says module not found ".prisma/client/default" ... which is from what i have red now that it is just not included even tho i tried to include it in the electron builder config