Support SQLite URI mode and arguments

[brendanlong]

Background

In sqlite3, if you set uri=True, you can pass query param arguments to the underlying database, including options like cache=shared and mode=memory, which are useful for unit testing (you can create an in-memory DB which stays alive between multiple connections).

For example, without the query params, :memory: databases are completely separate:

import sqlite3

db1 = sqlite3.connect(":memory:")
db1.execute("CREATE TABLE Example(id INT)")
db1.execute("SELECT * FROM Example")

db2 = sqlite3.connect(":memory:")
db2.execute("SELECT * FROM Example") # sqlite3.OperationalError: no such table: Example

But if we pass cache=shared, they're shared:

import sqlite3

db1 = sqlite3.connect("file::memory:?cache=shared", uri=True)
db1.execute("CREATE TABLE Example(id INT)")
db1.execute("SELECT * FROM Example")

db2 = sqlite3.connect("file::memory:?cache=shared", uri=True)
db2.execute("SELECT * FROM Example") # no error

mode=memory lets you create named memory DB's so you can share them by name instead of having a single shared namespace.

The problem

The problem is that databases handles query params itself and doesn't pass them to the underlying connection:

import asyncio
import sqlite3

from databases import Database

async def example():
    _ = sqlite3.connect("file::memory:?cache=shared", uri=True) # Hold a reference open since SQLite deletes the shared DB when ths last reference closes
    db1 = Database("sqlite:///file::memory:?cache=shared", uri=True)
    await db1.execute("CREATE TABLE Example(id INT)")
    await db1.execute("SELECT * FROM Example")
    db2 = Database("sqlite:///file::memory:?cache=shared", uri=True)
    await db2.execute("SELECT * FROM Example") # sqlite3.OperationalError: no such table: Example

asyncio.run(example())

Proposed solutions

I messed around with this locally and come up with this solution:

diff --git a/databases/backends/sqlite.py b/databases/backends/sqlite.py
index 6a733ac..7f46c76 100644
--- a/databases/backends/sqlite.py
+++ b/databases/backends/sqlite.py
@@ -1,5 +1,6 @@
 import logging
 import typing
+from urllib.parse import urlencode
 import uuid
 
 import aiosqlite
@@ -51,12 +52,14 @@ class SQLiteBackend(DatabaseBackend):
 
 class SQLitePool:
     def __init__(self, url: DatabaseURL, **options: typing.Any) -> None:
-        self._url = url
+        self._database = url.database
+        if url.options:
+            self._database += "?" + urlencode(url.options)
         self._options = 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

... which basically just passes options through, but it doesn't seem to match how other backends work (with kwargs and URL query params being treated identically).

If you wanted to make options and kwargs interchangeable, you could do something like:

function_kwarg_names = {"timeout", "detect_types", "isolation_level", "factory", "check_same_thread", "cached_statements", "uri"}
self._function_options = {key: value for (key, value) in options.items() if key in function_kwargs}
self._uri_options = {key: value for (key, value) in options.items() if key not in function_kwargs}
....
self._database = url.database + "?" + urlencode(self._uri_options)

I can make a PR if that would be helpful but I'm not really sure how to approach this.

This would provide a workaround to fix #75

[yeralin]

Any update on this?