Open up SQLite query param, cached database functionality, inspired by #196

[nvjoshi2]

Background

In sqlite3, if you set uri=True you can pass query param arguments to the underlying database, including:

• mode = memory, ro, rw
• cache = shared

Examples:

# Open a database in read-only mode.
con = sqlite3.connect("file:template.db?mode=ro", uri=True)

# Don't implicitly create a new database file if it does not already exist.
# Will raise sqlite3.OperationalError if unable to open a database file.
con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)

# Create a cached in-memory database.
con = sqlite3.connect("file::memory:?cache=shared", uri=True)

# Create independent named in-memory database.
con1 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
con2 = sqlite3.connect("file:mem2?mode=memory&cache=shared", uri=True)

Options like cache=shared and mode=memory are particularly useful for unit testing (you can create an in-memory DB which stays alive between multiple connections).

The Issue

The issue is twofold:

1. databases strips out query params before passing the connection string to underling connection
2. databases closing the db connection after each query means that there is no living reference to a connection of the cached in-memory database once the query finishes. This causes the cached in-memory database to be deleted after each query even if we could set cache=shared

This leads to the following behavior:

import asyncio
import sqlite3

from databases import Database

async def example():
    database = Database("sqlite:///file::memory:?cache=shared", uri=True)
    await database.connect()
    await database.execute("CREATE TABLE Example(id INT)")
    rows = await database.fetch_all("SELECT * FROM Example") # throws sqlite3.OperationalError: no such table: Example

asyncio.run(example())

Proposed Solution

diff --git a/databases/backends/sqlite.py b/databases/backends/sqlite.py
index 46a3951..6335465 100644
--- a/databases/backends/sqlite.py
+++ b/databases/backends/sqlite.py
@@ -1,6 +1,8 @@
 import logging
+import sqlite3
 import typing
 import uuid
+from urllib.parse import urlencode
 
 import aiosqlite
 from sqlalchemy.dialects.sqlite import pysqlite
@@ -40,7 +42,9 @@ class SQLiteBackend(DatabaseBackend):
         # )
 
     async def disconnect(self) -> None:
-        pass
+        # if it extsis, remove reference to connection to cached in-memory database on disconnect
+        if self._pool._memref:
+            self._pool._memref = None
         # assert self._pool is not None, "DatabaseBackend is not running"
         # self._pool.close()
         # await self._pool.wait_closed()
@@ -52,12 +56,20 @@ class SQLiteBackend(DatabaseBackend):
 
 class SQLitePool:
     def __init__(self, url: DatabaseURL, **options: typing.Any) -> None:
-        self._url = url
+        self._database = url.database
+        self._memref = None
+        # add query params to database connection string
+        if url.options:
+            self._database += "?" + urlencode(url.options)
         self._options = options
 
+        if url.options and "cache" in url.options:
+            # reference to a connection to the cached in-memory database must be held to keep it from being deleted
+            self._memref = sqlite3.connect(self._database, **self._options)
+
     async def acquire(self) -> aiosqlite.Connection:
         connection = aiosqlite.connect(
-            database=self._url.database, isolation_level=None, **self._options
+            database=self._database, isolation_level=None, **self._options
         )
         await connection.__aenter__()
         return connection

This allows query params to be passed to the underlying connection and, when cache=shared, a reference to a connection to the cached in-memory database to be held (allowing it to persist) until Database.disconnect is called, leading to the following good behavior:

import asyncio
import sqlite3

from databases import Database

async def example():
    database = Database("sqlite:///file::memory:?cache=shared", uri=True)
    await database.connect()
    await database.execute("CREATE TABLE Example(id INT)")
    rows = await database.fetch_all("SELECT * FROM Example") #no error
    datbase.disconnect()

    database = Database("sqlite:///file::memory:?cache=shared", uri=True)
    await database.connect()
    rows = await database.fetch_all("SELECT * FROM Example") # throws sqlite3.OperationalError: no such table: Example

asyncio.run(example())

Also fixes: #196, #75

[aweigold]

I tested this locally and this is really useful, especially when using databases and wanting to run unit tests in an in memory database with sqlite.

Are there any concerns with this PR? Perhaps I could jump in and help address them. It would be a shame to have to maintain a fork just for this feature.

[farridav]

Any chance of a rebase @nvjoshi2 (presuming thats the reason this has not moved forwards)

[zanieb]

@farridav I actually implemented this in #561 it should be available.