Using an in-memory sqlite database

Using Django Deployment
1

Hi all,

in a recent project, using Django 2.2.7, the idea was to use an additional in-memory database, as in this configuration:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'memory': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': ':memory:',
    }
}

In order to get the memory db initialized, we have this command in the config for one app called memory_db_only:

class MemoryDbOnlyConfig(AppConfig):
    name = 'memory_db_only'

    def ready(self):
        call_command('migrate',
                     app_label='memory_db_only',
                     verbosity=1,
                     interactive=False,
                     database='memory')

When starting a shell (python manage.py shell) and querying some model from the memory db, it works fine:

>>> from memory_db_only.models import MyModel
>>> MyModel.objects.using("memory").all()
<QuerySet []>

However, when we add a view to the memory_db_only app, like

def get_objects(request):
    obj = MyModel.objects.using("memory").all()
    return HttpResponse(str(obj))

an exception is raised:

OperationalError at /memory/objects
no such table: memory_db_only_mymodel

I would highly appreciate advice on how to setup Django in this particular scenario.
Thanks
Christoph

2

Did you migrate? You’d still need to do that.

3

Pardon my novice question, but really curious why would you use in-memory database won’t it be a bottleneck?

1 Like
4

I guess if its a small project and speed is important you could use an in memory DB.

But I think you are right @Devil - I wouldn’t use it on anything with scale.

Having said that SQLite on a SSD is pretty fast, but maybe there are other reasons for using in memory SQLite

5

Update: Using file::memory: for the name of the memory database fixes the problem.

@ carltongibson: The migrations are run automatically whenever the server restarts, due to the changes to MemoryDbOnlyConfig

@ Devil: In our current project, we would like to use a relational cache, in order to store relational data from an external resource in a non-permanent manner.

@ andyide: Since we only use it for caching limited amounts of data, we don’t expect to see any scalability issues.

2 Likes
6

‘memory’: {
‘ENGINE’: ‘django.db.backends.sqlite3’,
‘NAME’: ‘file::memory:’,
}

Hi I did that and it is still the same issue.

1 Like
7

Ok, now I’m confused.

Quoting directly from the sqlite docs:

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.

This gives me the impression that you would need to run migrate for every instance that opens a connection. Am I missing something here? (Also, I’m finding it difficult to understand what the value of this would be in a Django environment.)

8

@KenWhitesell
file::memory:?cache=shared

We can use this. It would point to same cached on different connections.

9

You’re still going to run into problems when you have more than one process or your process restarts.

From the same docs:

Of course, all database connections sharing the in-memory database need to be in the same process. The database is automatically deleted and memory is reclaimed when the last connection to the database closes.

So each process instance of your project is going to be accessing a different database, and when the process is restarted, it all goes away.

1 Like
10

Hi, I was wondering if someone else has had this issue, I haven’t been able to fix it with file::memory: or file::memory:?cache=shared as the db name. Is there a proper fix for this? The issue being that when running the server, no database tables are found because the migrations have not been applied.

11

From refreshing my memory by rereading this thread, I have to ask - why are you trying to do this?

See the AppConfig class above for how you can run migrate on this database during the startup process.

12

since we don’t really need a database (other than for some frameworks, as mentioned), we thought the easiest way to solve this was to use the in memory db. We can also use a file db, but I still wanted to make sure if in-memory was still possible.