3

I’m trying to make Django app that runs on sqlite3 db in memory (in production). The steps seems simple

  1. Get an in-memory database running
  2. Attach the disk database (file)
  3. Recreate tables / indexes and copy over contents
  4. Detach the disk database (file)

but his is all new to me and I have problems with implementing them.

So far I’ve written:

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': ':memory:',
    }
}

app.py

from django.apps import AppConfig
import sqlite3
import os
from StringIO import StringIO
from django.conf import settings

class EnquirerConfig(AppConfig):
    name = 'enquirer'

    def ready(self):
        con = sqlite3.connect(os.path.join(settings.BASE_DIR, 'db.sqlite3'))
        tempfile = StringIO()
        for line in con.iterdump():
            tempfile.write('%s\n' % line)
        con.close()
        tempfile.seek(0)

        sqlite = sqlite3.connect(":memory:")
        sqlite.cursor().executescript(tempfile.read())
        sqlite.commit()
        sqlite.row_factory = sqlite3.Row

This is all based on the following: How to load existing db file to memory in Python sqlite3? Django - in memory sqlite in production

I've also tried

if settings.DATABASES['default']['NAME'] == ':memory:':
    call_command('migrate', interactive=True)

However, this does not seem to work - I still have unapplied migrations and ‘no such table’ exception.

halfer
  • 19,824
  • 17
  • 99
  • 186
Pawel Kam
  • 1,684
  • 3
  • 14
  • 30
  • Can you explain why you'd want to do this? It can't be for the sake of performance, as you could only ever run a single process this way; you'd be much better off with a standard database plus several server processes. Plus, of course, you'd lose all your data whenever the process restarted. So why? – Daniel Roseman Jun 13 '17 at 07:28
  • The idea is to lose all data whenever restarted :) Users input confidential financial data, so to be able to say to them that all the process runs in memory and app stores no data is very important. – Pawel Kam Jun 13 '17 at 20:41
  • did you try my answer? – e4c5 Jun 13 '17 at 23:45
  • Currently working on it, probably will finish not sooner than tomorrow. Thanks for your remarks. As for the new db with each page load, this is actually a desired thing in this implementation. Question is, whether storing db in memory in production is a good idea. Perhaps making temporary db would be better, since it's the same as constant syncing db and since performance is secondary issue here. The key thing is so that is can be said to the client that his data is processed in memory or on his disk. Any thoughts? – Pawel Kam Jun 14 '17 at 01:08
  • As I have already explained in my answer. If you give sufficient memory to an RDBMS his data will always be processed in memory. – e4c5 Jun 15 '17 at 00:06

1 Answers1

1

You are barking up the wrong tree here.

The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:" ... When this is done, no disk file is opened. 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.

https://sqlite.org/inmemorydb.html

So you have to continuously keep syncing the data to disk. Which is exactly the same as using a disk based sqlite3 database.

Secondly, if your wsgi container uses two threads, you may end up with two different database and the same user seeing two different contents on subsequent page loads.

Last but not least, most unix like operating systems have an excellent file system cache. If enough memory is available it will keep frequently used file in it's cache there by speeding up access. This happens at kernel level and will be lot faster than whatever you can implement with python or even user space C.

The above statement applies to practically and database be it RDBMS or noSQL.

e4c5
  • 52,766
  • 11
  • 101
  • 134