Running Prisma migrate in an Electron app
Tags: architecture
Published: 2022-02-13 15:19:52

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:

```typescript
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:
```typescript
// 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:
```typescript
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:
```typescript
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.