Run migrations on local sqlite database when app is run for the first time

[round willow]

Hi guys, quite a problem I try to tackle.

Background:
I am trying to create an Electron/React/Drizzle/better-sqlite3 app. Database should be hosted locally on users computer, in AppData folder.
I have created migration files and added them to "asar" folder when application is built.
It works great on dev (I should create some kind of storage for information if migration should run, but it is fine for now if it runs all the time), but when I build and install app, sometimes I get error referring that database is not created.
The biggest problem is that if I check created database with DBeaver, it has no tables so migration didn't run and yes I get errors in my app that table doesn't exist.

This is my first try to create an Electron app. Can you maybe help me get this right? What am I doing wrong?

app
  .whenReady()
  .then(() => {
    runMigrations()
  })
  .then(() => {
    electronApp.setAppUserModelId('com.electron')

    app.on('browser-window-created', (_, window) => {
      optimizer.watchWindowShortcuts(window)
    })

    createWindow()

    app.on('activate', function () {
      if (BrowserWindow.getAllWindows().length === 0) createWindow()
    })
  })

const migrateDb = (database: typeof db) => {
  const migrationsPath =
    process.env.NODE_ENV === 'development'
      ? 'public/drizzle' // Local development path
      : join(app.getAppPath(), 'resources', 'app.asar.unpacked', 'public', 'drizzle')

  migrate(database, { migrationsFolder: migrationsPath })
}

const main = () => {
  try {
    migrateDb(db)
    console.log('Migration successful')
  } catch (e) {
    console.log(e)
  }
}

export default main

const dbPath =
  process.env.NODE_ENV === 'development'
    ? './animal_care.db'
    : join(app.getPath('appData'), 'animal-care', 'animal_care.db')

const sqlite = new Database(dbPath)
sqlite.pragma('journal_mode = WAL')
const db = drizzle(sqlite, { schema })

export default db

Thank you in advance! 🙂

[ember bone]

Hey @round willow did you manage to solve this?

mind sharing how you managed to setup better-sqlite3. I cannot get it to work with my setup using electron/rebuild

[round willow]

hey @ember bone , what do you mean you cannot get it to work?
I installed better-sqlite3 and then ranelectron-rebuild -f -w better-sqlite3 from @electron/rebuild package

[ember bone]

I got it working. Found some examples from github

Sorry mate

[round willow]

for everyone, not sure the way I solved this problem is good but it works for me

first, I initialize db

let dbInstance: BetterSQLite3Database<typeof schema> | null = null

export const createDatabase = () => {
  if (dbInstance) {
    return dbInstance
  }

  const isDev = is.dev
  const productionDbPath = join(app.getPath('appData'), 'animal-care')

  if (!isDev && !fs.existsSync(productionDbPath)) {
    fs.mkdirSync(productionDbPath)
  }

  const dbPath = isDev ? './animal_care.db' : join(productionDbPath, 'animal_care.db')

  const sqlite = new Database(dbPath)
  sqlite.pragma('journal_mode = WAL')
  const db = drizzle(sqlite, { schema })

  dbInstance = db
  return db
}

export const db = dbInstance ?? createDatabase()

then my migration function

const migrateDb = (database: typeof db) => {
  const migrationsPath =
    process.env.NODE_ENV === 'development'
      ? 'public/drizzle' // Local development path
      : join(app.getAppPath(), 'public', 'drizzle')

  migrate(database, { migrationsFolder: migrationsPath })
}

const main = () => {
  try {
    migrateDb(db)
  } catch (e) {
    log.error(e)
  }
}

export default main

and then use it when app is starting

app
  .whenReady()
  .then(() => {
    createDatabase()
  })
  .then(() => {
    runMigrations()
  })
  .then(() => {
    electronApp.setAppUserModelId('com.electron')

    app.on('browser-window-created', (_, window) => {
      optimizer.watchWindowShortcuts(window)
    })

    createWindow()

    app.on('activate', function () {
      if (BrowserWindow.getAllWindows().length === 0) createWindow()
    })
  })

migrations should run only when necessary (this is work in progress for me, some kind of state management), but this works fine for my use case.
Also my problem was that I wasn't targeting correct folder in production, getAppPath is returning asar folder, so there you can target any folder/file as it was in development file structure 🙂

Last thing, I was relying on that DB is created automatically if it doesn't exist, but what was the problem, folder I was targeting didn't exist, so I am first checking for its existence and creating it if necessary.