Drizzle Team•9mo ago

How to handle `The database connection is not open` error when using `:memory:` w/ better-sqlite3?

We ship a desktop app and we are seeing the error in the screenshot in Sentry. Sadly, I can't replicate it locally; it only happens to some users. We aren't using prepared queries but the stack trace shows that too. We used to use prepared queries and the error was coming from where we call prepare but I removed that, now it's coming from one of the first places we make a query to the DB (at KeyValueStore.get in the stack trace). This is the method we use to get a Drizzle instance:

const getDB = async (env: EnvKey, accountID: string, dataDirPath: string, retryAttempt = 0): Promise<{
  db: DrizzleDB
  dbClose: () => Promise<void>
}> => {
  const logger = getLogger(env, 'drizzle')

  logger.info('initializing in-memory database', {
    accountID,
    retryAttempt,
  })

  try {
    const sqlite = new Database(':memory:')
    sqlite.pragma('journal_mode = WAL')
    const db = drizzle(sqlite, {
      schema,
      logger: {
        logQuery: (query, params) => {
          logger.debug(query, { params })
        },
      },
    })

    logger.debug('migrating database', { retryAttempt })

    migrate(db, {
      migrationsFolder: DRIZZLE_DIR_PATH,
    })

    return {
      db,
      dbClose: async () => {
        logger.debug('closing in-memory database')
        sqlite.close()
      },
    }
  } catch (err) {
    logger.error('error migrating database', {
      retryAttempt,
    }, err)

    if (retryAttempt > 1) {
      throw new MMError(env, -1, 'error migrating database', `retryAttempt: ${retryAttempt}, error: ${err?.message || JSON.stringify(err)}`)
    }

    return getDB(env, accountID, dataDirPath, retryAttempt + 1)
  }
}

const getDB = async (env: EnvKey, accountID: string, dataDirPath: string, retryAttempt = 0): Promise<{
  db: DrizzleDB
  dbClose: () => Promise<void>
}> => {
  const logger = getLogger(env, 'drizzle')

  logger.info('initializing in-memory database', {
    accountID,
    retryAttempt,
  })

  try {
    const sqlite = new Database(':memory:')
    sqlite.pragma('journal_mode = WAL')
    const db = drizzle(sqlite, {
      schema,
      logger: {
        logQuery: (query, params) => {
          logger.debug(query, { params })
        },
      },
    })

    logger.debug('migrating database', { retryAttempt })

    migrate(db, {
      migrationsFolder: DRIZZLE_DIR_PATH,
    })

    return {
      db,
      dbClose: async () => {
        logger.debug('closing in-memory database')
        sqlite.close()
      },
    }
  } catch (err) {
    logger.error('error migrating database', {
      retryAttempt,
    }, err)

    if (retryAttempt > 1) {
      throw new MMError(env, -1, 'error migrating database', `retryAttempt: ${retryAttempt}, error: ${err?.message || JSON.stringify(err)}`)
    }

    return getDB(env, accountID, dataDirPath, retryAttempt + 1)
  }
}

And right after we return db, the KeyValueStore queries are run. It's weird to me that migrations aren't resulting in an error if the DB is indeed not open/closed. This might be related to better-sqlite3 and not Drizzle too but I'm not sure. Any ideas on how I can debug this or at least somehow await for DB to be ready(?)?

1 Reply

alexblokh•9mo ago

cc: @⚡Z.E.U.S⚡ since Drizzle does neither open or close connections to the DB and it's solely up to either driver or that's a business logic written by the author and it does seem that you're trying to run queries on the closed DB maybe it's related to the lifecycle issues let's say app goes asleep and db connection is closed, but not restored during awake prepareQuery is an internal api that's needed by the betterSQlite driver it got called internally you've mentioned it failed on statement preparation and then you removed those, try to put them back and invoke a better sqlite driver call directly, something like select 1; I'd bet it's going to fail

Gaming

Programming

How to handle `The database connection is not open` error when using `:memory:` w/ better-sqlite3?