How to use ormar using sqlite with mmap?

[jinserk]

Hi,

I have one more question how to use mmap enabled sqlite3 with ormar. How can I do this?

Thank you!

[collerek]

This one gonna be more tricky.
Ormar already modifies sqlite settings but only to enforce foreign key usage.

To not to it manually (call PRAGMA mmap_size=268435456; before each query) you need to modify the factory for encode/databases connection pool.

This is (out of context) more or less what ormar does for you

class Connection(sqlite3.Connection):
    def __init__(self, *args: Any, **kwargs: Any) -> None:  # pragma: no cover
        super().__init__(*args, **kwargs)
        self.execute("PRAGMA foreign_keys=1;")

database = databases.Database(DATABASE_URL)
backend = database._backend
backend._options["factory"] = Connection
old_pool = backend._pool
backend._pool = old_pool.__class__(backend._database_url, **backend._options)

So you need to provide your own Connection class and pass it to factory setting.
If ormar sees that you already has factory it won't be replaced but that means you need to keep the fk query.

so your custom connection factory class can be something like:

class Connection(sqlite3.Connection):
    def __init__(self, *args: Any, **kwargs: Any) -> None:  # pragma: no cover
        super().__init__(*args, **kwargs)
        self.execute("PRAGMA foreign_keys=1; PRAGMA mmap_size=268435456;")

[jinserk]

I wonder if there exist more safe way in using ormar.. I'm afraid of overwriting the default connection so that some future update from ormar could not be reflected. Anyway, thank you so much!

[collerek]

Yeah you can set options when creating a database instance, you can include some options in url to db too, but for foreign_keys and mmap I think you have to execute the pragma query there is no other way around that I saw (but maybe I'm mistaken). So in order for this to work auto you need to provide your own factory for connection.

If I recall correctly you can simplify the above example to:

class Connection(sqlite3.Connection):
    def __init__(self, *args: Any, **kwargs: Any) -> None:  # pragma: no cover
        super().__init__(*args, **kwargs)
        self.execute("PRAGMA foreign_keys=1; PRAGMA mmap_size=268435456;")

database = databases.Database(URL, factory=Connection) # use this database in ormar
# note that you can use it only for sqlite if u use different backends for different envs.

Don't think I will erase this one anytime soon as apart from foreign_key I dont want to force any settings.

[jinserk]

Thank you for the clarification! In the last example you gave, that means I don't need to do anymore like this:

backend = database._backend
backend._options["factory"] = Connection
old_pool = backend._pool
backend._pool = old_pool.__class__(backend._database_url, **backend._options)

Is it correct?

[collerek]

Yep i think so.
The code you mentioned is my own that i use inside ormar as users declare options during creation of database. I check if they provided a factory and if not add my own that enforces foreign keys. You should be able to simply pass factory in instantiation.

[jinserk]

Sounds great, thank you again!