Bug: Unable to use in-memory database

[Azratosh]

Description

When setting the DB_FILE_PATH key in the .env file to ":memory:" or :memory:, an sqlite3.OperationalError is raised on every query:

Ignoring exception in on_message
Traceback (most recent call last):
  File "/home/max/PycharmProjects/SAM/venv/lib/python3.9/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/home/max/PycharmProjects/SAM/bot/admin/admin.py", line 465, in on_message
    if not message.author.bot and self._db_connector.is_botonly(message.channel.id):
  File "/home/max/PycharmProjects/SAM/bot/persistence/database_connector.py", line 635, in is_botonly
    result = db_manager.execute(queries.IS_CHANNEL_BOTONLY, (channel_id,))
sqlite3.OperationalError: no such table: BotOnlyChannel
ERROR 2021-08-19 10:14:23,345: Exception while calling command. Message was: !remindme list by Azratosh#7331 in channel bot
ERROR 2021-08-19 10:14:23,346: Traceback (most recent call last):
  File "/home/max/PycharmProjects/SAM/venv/lib/python3.9/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/max/PycharmProjects/SAM/bot/logger/logger.py", line 70, in wrapper
    await func(*args, **kwargs)
  File "/home/max/PycharmProjects/SAM/bot/remindme/remindme.py", line 84, in remindme_list
    reminder_jobs = self._db_connector.get_reminder_jobs_for_user(ctx.author.id)
  File "/home/max/PycharmProjects/SAM/bot/persistence/database_connector.py", line 182, in get_reminder_jobs_for_user
    result = db_manager.execute(queries.GET_REMINDER_JOBS_FOR_USER, (user_id,))
sqlite3.OperationalError: no such table: RemindmeJobs
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/home/max/PycharmProjects/SAM/venv/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "/home/max/PycharmProjects/SAM/venv/lib/python3.9/site-packages/discord/ext/commands/core.py", line 1348, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "/home/max/PycharmProjects/SAM/venv/lib/python3.9/site-packages/discord/ext/commands/core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/home/max/PycharmProjects/SAM/venv/lib/python3.9/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such table: RemindmeJobs

Steps To Reproduce

1. Set key DB_FILE_PATH to :memory:
2. Attempt to run any command

Cause

From the sqlite documentation:

Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

:arrow_right: The in-memory database is deleted right after DatabaseConnector is instantiated (or the DatabaseManager context manager is used).

Proposed Solutions

• Keep a single connection active when a :memory: database is used
• Use a separate key that appends ?mode=memory&cache=shared to the database's URI filename and use a connection pool. The database is deleted when the last connection closes, so at least one connection must always be open.
• Edit the comment in .env.example, telling the developer to just use a separate persistent database for testing and debugging :brain::boom:

[Katakompe]

I recommend using the first suggested approach: Keep a single connection active when a :memory: database is used This can be done by making a singleton that is used to get the database connection in the DatabaseManager instead of using a new connection each time.

@PKlempe is that fine for you?